SQL Server エージェントのジョブを AWS Step Functions に置き換える

Microsoft SQL Server から Amazon Aurora PostgreSQL への移行の場合に、SQL エージェントのジョブを簡単に移動できないことにお気づきかもしれません。Aurora PostgreSQL ではジョブエージェントツールがサポートされていません。この制限を克服するには、AWS Step Functions を使用して SQL エージェントのジョブを置き換えます。

このブログ記事では、ステップ関数を作成して、SQL ストアドプロシージャを実行する SQL エージェントのジョブを置き換える方法を示します。

ソリューションを実行するためのステップ

このソリューションを実行するためのコードと AWS CloudFormation テンプレートは、この GitHub Amazon Repository にあります。

ソリューションを作成するために、CloudFormation テンプレートを使用して以下を準備します。

パブリックで利用可能なスナップショットからの SQL Server データベース用の Amazon RDS。
ステートマシン用の IAM ロール。
Step Functions アクティビティ。
Step Functions ステートマシン。
Step Functions ステートマシンを起動するための Amazon CloudWatch Events ルール。

CloudFormation テンプレートを使用して上記のリソースを準備した後に、以下の操作を実行します。

Step Function のワーカープログラムを実装します。
Step Function のワーカープログラムを実行します。

前提条件および仮定

このソリューションをご自身で使用するには、以下が必要です。

AWS のサービスへのアクセスを提供する AWS アカウント。このブログ記事では、ネットワーキングの考慮事項を単純化するために、同じ VPC でサービスを設定します。
Eclipse IDE (ダウンロード可能です)。

追加

RDS データベース用のデフォルトのマスターユーザー名 (AWSUser など)。AWS CloudFormation スタックの作成中に、マスターパスワードを選択します

AWS CloudFormation を使用する場合、スタックと呼ばれる単一のユニットとして関連リソースを管理します。スタックを作成、更新、削除することによって、リソースのコレクションを作成、更新、削除します。スタックのすべてのリソースは、スタックの AWS CloudFormation テンプレートによって定義されます。

ステップ 1: CloudFormation スタックを作成する

GitHub Amazon Repository から StepFunctionCloudFormation.yml をダウンロードします。
AWS マネジメントコンソールにサインインして、 CloudFormation コンソールを開きます。以下に示すように [スタックの作成] を選択します。

以下に示すように StepFunctionCloudFormation.yml ファイルをアップロードし、[次へ] を選択します。

詳細ページで次の項目を指定します。

スタックの名前
データベース用のマスターユーザーのパスワード

以下に示すように [次へ] を選択します。

[Review Details (詳細の確認)] ページが表示されるまで、[次へ] を選択します。IAM リソースの作成を確認して、以下に示すように [作成] を選択します。

CloudFormation スタックの完了には多少時間がかかる可能性があります。CloudFormation スタックが完了した後に、リソースが作成されたことを確認する必要があります。CloudFormation スタックが完了しない場合、AWS マネジメントコンソールで CloudFormation スタックのイベントをチェックしてください。

ステップ 2: ステップ関数のアクティビティを検証する

CloudFormation スクリプトが完了した後に、Step Function アクティビティを作成する必要があります。以下の手順で Step Functions コンソールに移動してステップ関数のアクティビティの作成をチェックすることができます。

AWS マネジメントコンソールにサインインして、左上側の [サービス] リンクを選択します。
テキストボックスに Step Functions と入力します。Step Functions に対する結果が表示されます。以下に示すようにその結果を選択します。

3.Step Function コンソールに移動したら、Step Function アクティビティが作成されたかどうか確認します。以下に示すアクティビティの Amazon Resource Number (ARN) を書き留めます。ワーカーを後で実行する場合に、これが必要となります。

ステップ 3: Step Function ステートマシンを検証する

CloudFormation スクリプトが完了したら、Step Functions ステートマシンが作成されます。次の操作で、Step Functions ステートマシンが作成されたかどうか確認します。

以下に示すように、AWS マネジメントコンソールにサインインして、左上側の [サービス] を選択します。
テキストボックスに Step Functions と入力します。Step Functions に対する結果が表示されます。以下に示すようにその結果を選択します。

Step Functions コンソールに移動すると、デフォルトでステートマシンのページが表示されます。以下に示すように、SqlAgent-StateMachine を選択します。以下に示すように IAM ロール ARN を含む SqlAgent-StateMachine の詳細が表示されます。

[Definition (定義)] タブを選択することで、以下に示すようにステートマシンの定義を確認することもできます。

このステートマシンを起動すると、フィールド (“StartAt” :“SqlAgent”) で参照されるステートでシステムが開始されます。タスクステート (“Type”: “Task”) は、ステートマシンによって実行される作業の単一のユニットを表します。 Resource フィールドは、実行する特定のタスクを一意に識別する URI であり、ここでは ARN です。上記の定義ではタスクは SqlAgentActivity です。このステートには (“End”: true) フィールドがあるため、実行が停止して結果を返します。

注意: ステートマシンのすべての作業はアクティビティタスクによって実行されます。タスクはアクティビティまたは Lambda 関数にすることができます。SQL エージェントのジョブで SQL ストアドプロシージャを完了するのに 10 分以上かかるため、当社のソリューションではアクティビティタスクを選択しました。AWS Lambda には 5 分という厳しいタイムアウト制限があるため、この特定の SQL エージェントのジョブの置き換えには適していません。

ステップ 4: パブリックで利用可能なスナップショットからの SQL Server データベース用の Amazon RDS をカスタマイズする

CloudFormation スクリプトが完了したら、RDS に SQL Server データベースが作成されます。次の操作によって、CloudFormation コンソールでこれを検証します。

CloudFormation コンソールで、[出力] タブを選択します。SQLDatabaseEndpoint および SqlAgentActivityArn の値を書き留めます。ワーカーを後で実行する場合に、これらが必要となります。

置き換えている SQL ジョブを表示するには、SQL Server Management Studio を使用し、作成した SQL RDS インスタンスにアクセスします。SQL Server Management Studio を使用して SQL Server を実行する RDS インスタンスに接続する手順については、RDS のユーザーガイドを参照してください。

注意: SQL Server Management Studio 経由で SQL RDS インスタンスにアクセスできない場合は、セキュリティグループ内で RDS インスタンスがリッスンしているポートを開きます。このブログで紹介した CloudFormation テンプレートを使用している場合、ポート番号は 1433 です。

セキュリティグループ内で RDS インスタンスがリッスンしているポートを開くには、次の手順に従います。

https://console.thinkwithwp.com/rds/ で Amazon RDS コンソールを開きます。
RDS コンソールウィンドウの左側にあるナビゲーションペインから [インスタンス] を選択します。
変更する特定の DB インスタンスを選択します。
DB インスタンスのページで、[接続] セクションが表示されるまで下にスクロールします。セキュリティグループのルールの下で、変更するセキュリティグループを選択します。以下に示すように選択します。
セキュリティグループのページで、変更するセキュリティグループを選択し、以下に示すように、[インバウンド] タブを選択して、[編集] を選択します。[インバウンドのルールの編集] ダイアログボックスが表示されます。
[ルールの追加] を選択します。新規ルールの行が追加されます。
以下に示すように、[タイプ] ドロップダウンリストで [MS SQL]、[ソース] ドロップダウンリストで [My IP] を選択します。この操作で、ソースの下の [プロトコル]、[ポート範囲]、[IP アドレス] テキストボックスが入力されます。操作が終わったら [保存] を選択します。これで、お使いのマシンからポート 1433 へのインバウンドアクセスが可能になります。

SQL RDS インスタンスに接続したら、次の操作を実行します。
- Object Explorer で、DB インスタンスを拡張します。
- SQL Server エージェントを拡張し、次にジョブを拡張します。
- [NumberOfCustomer] ジョブを右クリックして、[プロパティ] を選択します。
- [プロパティ] で [ステップ] を選択します。
- [ステップ] で、NumberOfCustomers という名前のステップをダブルクリックします。以下に示すように、ステップが NumberofCustDelay ストアドプロシージャを実行することがわかります。このストアドプロシージャはテーブルの顧客数を返し、10 分間の待機時間を発生させます。2 時間ごとに SQL エージェントによって実行される予定です。

ステップ 5: Step Functions ステートマシンの実行を開始するための CloudWatch イベントのルールを検証する

CloudFormation スクリプトが完了したら、CloudWatch イベントのルールが作成されます。AWS マネジメントコンソールを使用して、以下に示すように CloudWatch イベントのルールが作成されたかどうか確認します。

AWS マネジメントコンソールにサインインして、左上側の [サービス] を選択します。
テキストボックスに CloudWatch と入力します。以下に示すように CloudWatch エントリが表示されたら、選択します。

CloudWatch コンソールで、以下に示すように左のナビゲーションペインにある [ルール] を選択します。

[ルール] ページで、以下に示すように 「CloudWatchEventRule」 を含むルールを選択します。

ルールの概要が表示されます。以下に示すように、リソース名が「SqlAgent-StateMachine」の Step Function ステートマシンがルールの対象であることを確認してください。以下の CloudWatch ルールは、予定されたジョブのエージェントと同様に 30 分ごとに SqlAgent-StateMachine を呼び出します。

注意: CloudFormation スクリプトが正常に終了した後、CloudWatch イベントのルールが開始されるまでに最大 30 分かかることがあります。

ステップ 6: Step Functions のワーカープログラムを実装する

このステップでは、ワーカープログラムを実装します。開始するには、お使いのマシンに sqljobstepfunction プロジェクトをダウンロードして、残りの手順に従います。

sqljobstepfunction プロジェクトをダウンロードします。
- この GitHub Amazon Repository のリンクに移動します。
- 以下に示すように [Clone or download] を選択します。

ダウンロードボタンを選択すると、[Download ZIP] へのオプションが表示されます。以下に示すように [Download ZIP] を選択します。

.ZIP ファイルを保存し、解凍します。

2.プロジェクトをダウンロードして解凍した後に、Eclipse IDE にプロジェクトをインポートします。Eclipse IDE でプロジェクトをインポートする手順は Eclipse.org ウェブサイトにあります。
3.Eclipse でプロジェクトをインポートした後に、以下に示すように SQLJobApp.java クラスを探します。

Eclipse メインコードペインで SQLJobApp.java クラスをダブルクリックして開きます。以下に示すように Java コードが表示されます。

ワーカープログラムの Java コードによって、次の内容が実行されます。

GetActivityTask API アクションを使用してアクティビティのステップ関数をポーリングします。
JDBC を使用して NumOfCustDelay ストアドプロシージャを実行することで、アクティビティの作業を実行します。
SendTaskSuccess および SendTaskFailure API アクションを使用して結果を返します。

package sqljobstepfunction;

import com.amazonaws.ClientConfiguration;
import com.amazonaws.auth.DefaultAWSCredentialsProviderChain;
import com.amazonaws.regions.*;
import com.amazonaws.services.stepfunctions.AWSStepFunctions;
import com.amazonaws.services.stepfunctions.AWSStepFunctionsClientBuilder;
import com.amazonaws.services.stepfunctions.model.GetActivityTaskRequest;
import com.amazonaws.services.stepfunctions.model.GetActivityTaskResult;
import com.amazonaws.services.stepfunctions.model.SendTaskFailureRequest;
import com.amazonaws.services.stepfunctions.model.SendTaskSuccessRequest;
import com.amazonaws.util.json.Jackson;
import com.fasterxml.jackson.databind.JsonNode;


import java.util.concurrent.TimeUnit;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQLJobApp {

	public static void main(final String[] args) throws Exception {

       String connectionUrl = "jdbc:sqlserver://<データベースエンドポイント >;databaseName=CustomerDB;user=AWSUser;password=<パスワード>";
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String result = null;

		ClientConfiguration clientConfiguration = new           
                                                       ClientConfiguration();
		clientConfiguration.setSocketTimeout((int) 
                                TimeUnit.SECONDS.toMillis(70));

		AWSStepFunctions client = 
                 AWSStepFunctionsClientBuilder.standard()
                .withRegion(Regions.US_EAST_1)
                .withCredentials(new DefaultAWSCredentialsProviderChain())
                .withClientConfiguration(clientConfiguration)
                .build();


	while (true) {
	 GetActivityTaskResult getActivityTaskResult = 
                        client.getActivityTask(new GetActivityTaskRequest()
					.withActivityArn("<ACTIVITY_ARN を入力>"));

	if (getActivityTaskResult.getTaskToken() != null) {
	try {
	JsonNode json = Jackson.jsonNodeOf(getActivityTaskResult.getInput());
	String command = json.get("Command").textValue();
	                    
	 if (command.equals("start"))    
	 try {
						    
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
						con = 
        DriverManager.getConnection(connectionUrl);

	  String SQL = "EXEC [dbo].[NumOfCustDelay]";
		   stmt = con.createStatement();
		   rs = stmt.executeQuery(SQL);

		 while (rs.next()) {
							 
             System.out.println(rs.getString("NumberOfCustomers"));
		 result = rs.getString("NumberOfCustomers");
		}

		} 
        catch (Exception e) {
		 e.printStackTrace();
	  } 
        finally {

		if (rs != null)
		try {
		     rs.close();
		} 
            catch (Exception e) {
			e.printStackTrace();
	      }
		if (stmt != null)
		try {
		    stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (con != null)
		try {
		     con.close();
		} catch (Exception e) {
		         e.printStackTrace();
		}

		}
	      else
	         result = "fail";
					
	 client.sendTaskSuccess(new SendTaskSuccessRequest().withOutput(result)
				.withTaskToken(getActivityTaskResult.getTaskToken()));
	} catch (Exception e) {
	  client.sendTaskFailure(
	  new SendTaskFailureRequest().withTaskToken(getActivityTaskResult.getTaskToken()));
      }
			} else {
				Thread.sleep(1000);
			}
		}
	}

}

注意: この例のクラスでは、お客様がツールキットのユーザーガイドで説明されている「AWS 認証情報の設定」を行っていると想定しています。

上記のコードで次の操作を実行します。
- 以下に示すように、ステップ 1 で書き留めたデータベースエンドポイント、および接続して CloudFormation スタック作成中に選択したマスターデータベースパスワードを入力します。
```
String connectionUrl = "jdbc:sqlserver://<データベースエンドポイント >;databaseName=CustomerDB;user=AWSUser;password=<パスワード>";
```
- 以下に示すように、GetActivityTaskRequest().withActivityArn() コンストラクタのパラメータリストで、ACTIVITY_ARN 値を前のステップ 3 で書き留めた ARN に置き換えます。
```
GetActivityTaskResult getActivityTaskResult = 
                        client.getActivityTask(new GetActivityTaskRequest()
					.withActivityArn("<ACTIVITY_ARN を入力>"));
```
- Eclipse でプロジェクトを構築します。

このワーカープログラムをローカルマシンで実行していても、Amazon EC2、Amazon ECS、またはモバイルデバイスでもアクティビティをホストすることができます。

ステップ 7: ワーカーを実行する

ワーカーにアクティビティ用のステートマシンのポーリングをさせるには、ワーカーを実行する必要があります。Eclipse で SqlJobApp.java を実行します。開始するには、次の手順に従います。

以下のスクリーンショットに示すように、次の手順に従って SQLApp.java プログラムを実行します。
- SQLJobApp.java クラスを右クリック
- メニューから [Run As] を選択
- [Java Application] を選択

Java プログラムが成功したら、以下に示すように、Eclipse IDE コンソールおよび AWS マネジメントコンソールの SqlAgent-StateMachine のページに結果が表示されます。

Eclipse IDE コンソールの場合は以下のように表示されます。

AWS マネジメントコンソールの場合は以下のように表示されます。

注意: CloudFormation スクリプトが正常に終了した後、SqlAgent-StateMachine が開始されるまでに最大 30 分かかることがあります。

結論

このブログ記事では、ストアドプロシージャを実行する SQL エージェントのジョブを AWS のステップ関数に置き換えるための完全なソリューションを示しました。この例は SQL Server に特化していますが、概念は Amazon Aurora PostgreSQL のような他のデータベースに適用できます。

このブログ記事に関するコメントは、以下の「コメント」セクションからお送りください。この記事のソリューションの実行に関して質問がございましたら、「コメント」セクションに書き込んでいただけます。

その他の参考資料

この記事がお役に立つのであれば、Picking Your First Database to Migrate to Amazon RDS or Amazon Aurora with PostgreSQL-compatibility にも関心を持っていただけるかもしれません。

今回のブログ投稿者について

Zafar Kapadia は、アマゾンウェブサービスのクラウドアプリケーションアーキテクトです。アプリケーション開発および最適化プロジェクトを担当しています。また、熱心なクリケット選手であり、さまざまなローカルリーグでプレーしています。

Amazon Web Services ブログ