2010-08-02

I Miss You SQL Server Agent: Part 2 – SQL Azure Team Blog – Site Home – MSDN Blogsを簡単に翻訳したエントリーです。

今のところ、SQL AzureはSQL Server Agentの概念を持っていません。このシリーズでは、Windows Azureワーカーロールを使用して、簡単に代用できるものを作成したいと思います。このシリーズの最初の投稿で、Visual Studioとコードで、Windows AzureワーカーロールでSQL Server Agentと同等のものを作成する方法を紹介しました。
このエントリーでは、一日に一回ジョブを実行するメカニズムを作成します。

 

データベースの作成

Windows Azureは、ワーカーロールは、そのうちデータセンターで異なるサーバに移動する可能性のあるステートレスなプラットフォームです。このため、ジョブが完了するまでジョブの状態を記録し続ける必要があります。その為に迷うことなくSQL Azureを選択します。SQL Azureサーバ下に、SQLServerAgentと呼ばれるデータベースを作成します(データベース名msdbは予め予約されています)。このデータベースに、オンプレミスのSQL Server Agentテーブルsysjobactivityの簡単バージョンであるjobactivityと呼ばれるテーブルを作成します。
私が使用したスクリプトは以下のものです。

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,
) 

job_idはオブジェクトの日次インスタンスを表し、job_nameはジョブ実行の為の任意のキーです。異なる名前を使用することで多くのジョブを走らせる為に、このテーブルを使用します。

 

ジョブの開始と停止の追跡

 

ジョブ開始時にテーブルにデータを追加する為のストアドプロシージャと、ジョブ停止時に実行ストップを更新する為のストアドプロシージャの２つが必要です。Startjobストアドプロシージャは、ワーカーロールがジョブを開始する為のシグナルを行に登録する前に、ジョブが始まらないことを保証します。

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

もう一つのストアドプロシージャStopJobは、次のようになります。

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

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

ストアドプロシージャを呼び出すワーカーロールを書きます。

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();
    }
}

1日1回、午後１時にspTestJobストアドプロシージャを実行するために、ワーカーロールのRunメソッドを実行します。

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);
        }
    }
}

上のコードにエラーハンドリングのコードがないことに気づいたでしょうか。エラーが発生したらどうなるでしょうか。SQL Azureがエラーを返したらどうなるでしょうか。データセンターで異なるサーバでワーカーロールがリサイクルしたらどうなるでしょうか。それらの問題への対処については、このシリーズの3回目でコードを追加して対処したいと思います。