I Miss You SQL Server Agent: Part 1

發行項
07/30/2010

[This article was contributed by the SQL Azure team.]

Currently, SQL Azure doesn’t support running SQL Server Agent in the cloud. If you need SQL Server Agent type functionality for SQL Azure, you can use a Windows Azure worker role and some custom code, I will show you how in this blog post series.

“Secret agent man, secret agent man
They've given you a number and taken away your name” – Johnny Rivers

SQL Server Agent

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs. SQL Server Agent uses SQL Server to store job information. Jobs contain one or more job steps. Each step contains its own task, for example, backing up a database. SQL Server Agent can run a job on a schedule, in response to a specific event, or on demand. For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday; if the backup encounters a problem, SQL Server Agent can record the event and notify you. SQL Server Agent is installed with the on-premise Enterprise, Datacenter and Standard editions of SQL Server, however is not available in the Express or Compact edition.

Worker Roles

A worker role is basically a Windows service in the cloud; that understands the concept of starting, stopping, and configuration refreshes. When started, well designed worker roles enter into an endless loops that:

Checks for an event that signals processing.
Perform an action.
Sleep until the event needs to be checked again.

They are purposefully abstract so that they can be used for multiple purposes.

Disclaimer: Because worker roles are purposefully abstract and SQL Server Agent is a well-developed, well rounded product that has been around for years, I will not be replicating SQL Server Agents complete functionality with a worker roles. Instead what I will attempt to do is provide some simple code to convey the idea that you can accomplish (with some coding) many of the tasks that SQL Server Agent does from a Windows Azure Worker role.

Getting Started

To get started, I create a Cloud project in Visual Studio, which includes a Worker role. You can read more about the basics of doing this here.

This generates me some sample code in a file called WorkerRole.cs that looks like this:

 public override void Run()
{
    // This is a sample worker implementation. Replace with your logic.
    Trace.WriteLine("WorkerRole1 entry point called", "Information");

    while (true)
    {
        Thread.Sleep(10000);
        Trace.WriteLine("Working", "Information");
    }
}

The first thing I want to do is write some code that will execute a stored procedure on my SQL Azure database, this would be the equivalent of running a Transact-SQL script in a step within a SQL Server Agent job.

My code looks like this:

 protected void ExecuteTestJob()
{
    using (SqlConnection sqlConnection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["AdventureWorksLTAZ2008R2"].
            ConnectionString))
    {
        try
        {
            // Open the connection
            sqlConnection.Open();

            SqlCommand sqlCommand = new SqlCommand(
                "spTest", sqlConnection);

            sqlCommand.CommandType =
                System.Data.CommandType.StoredProcedure;

            sqlCommand.ExecuteNonQuery();
        }
        catch (SqlException)
        {
            Trace.WriteLine("SqlException","Error");
            throw;
        }
    }
}

Typically, you will be executing stored procedures on SQL Azure that don’t return any data, using the SqlCommand.ExecuteNonQuery() method. There is nowhere for a returning result set to go, i.e. no output; this is similar to SQL Server Agent.

Handling Exceptions

Notice that the code above does a very poor job of handling exceptions, which is the first hurdle you need to figure out when writing your own version of SQL Server agent in a Windows Azure worker role. With SQL Server Agent you are given a logical choice, if the step succeeds go the next step or if the step fails quit the job and report the failure. You can simulate the options in SQL Server Agent by using try/catch in your worker role. In the third blog post in this series I will address some of the error handling issues.

The option that you do not have is for SQL Server agent is to stop running on a failure. This is the same as with a worker role, on an unhandled exception the worker role is recycled and restarted, you can find out more about this here. We are going to use the worker roles recycle and restarted functionality in our error handling, covered in Part 3 of the blog series.

Summary

In part 2 of this blog series, I will cover how to execute the stored procedure at a specific time during the day. Do you have questions, concerns, comments? Post them below and we will try to address them.

共用方式為