I Miss You SQL Server Agent: Part 2 - SQL Azure Team Blog - Site Home - MSDN Blogs

I Miss You SQL Server Agent: Part 2

I Miss You SQL Server Agent: Part 2

Rate This
  • Comments 6

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.

  • Hi Wayne,

       This is an awesome series on how you build Windows Azure applications to talk to SQLAzure. I'd like to point out - there is a non-code solution to running jobs - if you are willing to dedicate a client machine to talk to SQL Azure. Quite simply, you can issue SQL statements to perform maintenance tasks using SQLCMD with the FQDN of your SQL Azure server. You can then use the Windows task schedule to execute the SQLCMD command/file as needed.

    Cheers,

    Bill

  • For those interested on using this approach to replicate the Database Mail functionality, I just published a post that explains how to change the original SQL Server 2008 Database Mail stored procedures to make them run on SQL Azure. The post is in Italian at the moment but I can traslate it for you. Just ask.

    vpolizzi.wordpress.com/.../replicare-le-funzionalita-di-database-mail-su-sql-azure

  • Hi Vittorio,

    Can you please translate the db mail functionality post into english.So, that it would be very much usefull.

  • Hi Rakesh, you can find a translated version of the post here: vpolizzi.wordpress.com/.../database-mail-on-sql-azure

  • Hi great post, easy to follow along and implement.  Can please you explain how I can modify the above code to process every 10, 15 or 30 mins instead of once a day.

    i am trying to apply this to an email log table to process through queue emails sitting in a table in my Sql Azure.  

    Having emails go out once a day will work but ideally i'd like to speed it up to at minimum 10mins.

    best regards

  • sorry about the second sentence above, i've been coding all night and tired.  Should have wrote "Can you please explain" and not "Can please you explain"

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post