I Miss You SQL Server Agent: Part 2

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

Currently, SQL Azure doesn’t have the concept of a SQL Server Agent. In this blog series we are attempting to create a light-weight substitute using a Windows Azure Worker role. In the first blog post of the series, I covered how the Windows Azure worker roles compare to SQL Server Agent, and got started with Visual Studio and some code. In this blog post I am going to create a mechanism to complete the “job” once per day.

Creating a Database

Windows Azure is a stateless platform, where the worker role could be moved to a different server in the data center at any time. Because of this, we need to persist the state of the job completion ourselves; so the obvious choice is SQL Azure. To do this I have created a database under my SQL Azure server, called SQLServerAgent (the database name msdb is reserved). In this database I created a table called jobactivity, which is a simplified version of the on-premise SQL Server Agent table sysjobactivity. Here is the creation script I used:

 CREATE TABLE [dbo].[jobactivity](
    [job_id] uniqueidentifier NOT NULL PRIMARY KEY,
    [job_name] nvarchar(100) NOT NULL,
    [start_execution_date] datetime NOT NULL,
    [stop_execution_date] datetime NULL,
) 

The job_id represents the daily instance of the object, the job_name is an arbitrary key for the job being executed, we can use this table to run many jobs with different names.

Tracking Jobs Starting And Stopping

I also need a couple of stored procedures that add a row to the table when the job starts, and set the stop execution date when the job ends. The StartJob stored procedure ensures that the job has not been started for this day before it adds a row for the job execution as a signal that one worker role has started the job. It conveniently allows us to have multiple worker roles acting as SQL Server Agents, without executing the job multiple times.

 CREATE PROCEDURE StartJob (
    @job_name varchar(100),
    @job_id uniqueidentifier OUTPUT)
AS

BEGIN TRANSACTION

SELECT    @job_id
FROM    [jobactivity]
WHERE    DATEDIFF(d, [start_execution_date], GetDate()) = 0 
    AND [job_name] = @job_name

IF (@@ROWCOUNT=0)
BEGIN
    -- Has Not Been Started
    SET @job_id = NewId()
    INSERT INTO [jobactivity] 
        ([job_id],[job_name],[start_execution_date])
        VALUES (@job_id, @job_name, GetDate())
END
ELSE
BEGIN 
    SET @job_id = NULL
END

COMMIT TRAN

The other stored procedure, StopJob, looks like this:

 CREATE PROCEDURE [dbo].[StopJob](
    @job_id uniqueidentifier)
    
AS

UPDATE [jobactivity]
SET [stop_execution_date] = GetDate()
WHERE job_id = @job_id

Now let’s write some C# in the worker role code to call our new stored procedures.

 protected Guid? StartJob(String jobName)
{
    using (SqlConnection sqlConnection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["SQLServerAgent"].
            ConnectionString))
    {
        try
        {
            // Open the connection
            sqlConnection.Open();

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

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

            sqlCommand.Parameters.AddWithValue("@job_name", jobName);

            // WWB: Sql Job Id Output Parameter
            SqlParameter jobIdSqlParameter = new 
                SqlParameter("@job_id", SqlDbType.UniqueIdentifier);
            jobIdSqlParameter.Direction = ParameterDirection.Output;
            sqlCommand.Parameters.Add(jobIdSqlParameter);

            sqlCommand.ExecuteNonQuery();

            if (jobIdSqlParameter.Value == DBNull.Value)
                return (null);
            else
                return ((Guid)jobIdSqlParameter.Value);
        }
        catch (SqlException)
        {
            // WWB: SQL Exceptions Means It Is Not Started
            return (null);
        }
    }
}

protected void StopJob(Guid jobId)
{
    using (SqlConnection sqlConnection = new SqlConnection(
        ConfigurationManager.ConnectionStrings["SQLServerAgent"].
            ConnectionString))
    {
        // Open the connection
        sqlConnection.Open();

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

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

        sqlCommand.Parameters.AddWithValue("@job_id", jobId);

        sqlCommand.ExecuteNonQuery();
    }
}

Now let’s tie it altogether in the Run() method of the worker role, we want our spTestJob stored procedure to execute once a day right after 1:00 pm.

 public override void Run()
{
    Trace.WriteLine("WorkerRole1 entry point called", "Information");

    while (true)
    {
        DateTime nextExecutionTime = new DateTime(
            DateTime. UtcNow.Year, 
            DateTime. UtcNow.Month, DateTime. UtcNow.Day,
            13, 0, 0);
        if (DateTime. UtcNow > nextExecutionTime)
        {
            // WWB: After 1:00 pm, Try to Get a Job Id.
            Guid? jobId = StartJob("TestJob");
            if (jobId.HasValue)
            {
                Trace.WriteLine("Working", "Information");

                // WWB: This Method Has the Code That Execute
                // A Stored Procedure, The Actual Job
                ExecuteTestJob();

                StopJob(jobId.Value);
            }

            // WWB: Sleep For An Hour
            // This Reduces The Calls To StartJob
            Thread.Sleep(3600000);
        }
        else
        {
            // WWB: Check Every Minute
            Thread.Sleep(60000);
        }
    }
}

Notice that there isn’t any error handling code in the sample above, what happens then there is an exception? What happens when SQL Azure returns a transient error? What happens when the worker role is recycled to a different server in the data center? These issues I will try to address in part 3 of this series, by adding additional code.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.