[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.