Amazon RDS for SQL Server で変更データキャプチャパラメータを構成する

AWS Database Migration Service (AWS DMS) は、データベースと分析ワークロードを AWS に迅速かつ安全に移行するためのマネージド型のマイグレーションおよびレプリケーションサービスです。移行中もソースデータベースは完全に稼働し続けるため、データベースに依存するアプリケーションのダウンタイムを最小限に抑えることができます。AWS DMS は、最も広く使用されている商用およびオープンソースのソースデータベースとターゲットデータベースの間でデータを移行できます。

SQL Server は Microsoft が開発したリレーショナルデータベースです。 Amazon Relational Database Service (Amazon RDS) for SQL Server を使用すると、クラウド上で SQL Server の展開を簡単に設定、運用、スケーリングできます。Amazon RDS はデータ レプリケーションをサポートしており、変更データキャプチャ (CDC) を有効にすることが、AWS DMS と Amazon RDS for SQL Server を使用するための前提条件の 1 つとなっています。CDC はテーブルのデータに加えられた変更をキャプチャします。それぞれの変更に関するメタデータを格納し、後で参照できます。

この投稿では、CDC パラメータについて深く掘り下げ、AWS DMS の設定時の影響について説明するとともに、いくつかのベストプラクティスについても説明します。

前提条件

この投稿に沿って進めるには、次の AWS サービスに精通している必要があります。

• AWS DMS
• Amazon RDS for SQL Server

さらに、このソリューションに必要なリソースを起動するのに十分な権限を持つ AWS アカウントが必要です。

AWS DMS は Amazon RDS for SQL Server とどのように連携するのか

Amazon RDS for SQL Server の場合、AWS DMS は Microsoft の関数を使用してトランザクションログを読み取り、デフォルトで上位 50,000 イベントを取得します。AWS DMS は、AWS DMS タスクで定義されたテーブルに関連する特定のパーティション ID のデータベースログを照会することから始まります。パーティション ID は、フルロードと CDC の両方で、各テーブルの再ロード、タスクの再起動、タスクの再開時に読み取られます。AWS DMS はオブジェクト ID を取得し、それらのオブジェクト ID に対応するデータパーティション ID を取得します。パーティション ID を取得した後、関連するパーティションをトランザクションログからフェッチします。このサイクルは 1 秒ごとに実行されます。

次の図は、アーキテクチャを示しています。この例では、Amazon RDS for SQL Server をソースとして使用しています。AWS DMS タスクのターゲットは、サポートされている任意のエンドポイントになります。

AWS DMS は、Microsoft の関数を使ってトランザクションログを読み取り、AWS の DMS タスクの対象となるテーブルとソースデータベースで CDC が有効になっている必要があります。

なぜ AWS DMS で CDC が必要なのか ?

テーブルで CDC が有効になると、SQL Server は cdc スキーマにテーブルを作成します。作成されたテーブル (変更テーブル) には変更データが格納され、トラッキングされているスキーマとテーブルに基づいて名前が付けられます。たとえば、dbo スキーマに customer という名前のテーブルがある場合、dbo.customer テーブルに対するすべての変更を記録するために cdc.customer_CT という名前のテーブルが cdc スキーマに作成されます。

AWS DMS は変更テーブルを読み取りません。AWS DMS は、変更がトランザクションログに確実に記録されるように CDC を有効にする必要があります。前のセクションで説明したように、AWS DMS は Microsoft の関数を使ってトランザクションログから変更を読み取ります。ソース側の次のテーブルを考えてみましょう。

CREATE TABLE [dbo].[dmstest](
 [id] [int] NOT NULL,
 [name] [varchar](50) NULL,
CONSTRAINT [PK_dmstest] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

このテーブルに UPDATE ステートメントを発行して [name] 列を更新すると、 [RowLog Contents 0] と [RowLog Contents 1] に変更情報が更新されます。AWS DMS がソース上で実行する次のクエリの一部を示します。

select top 50000 [Current LSN], [operation], [RowLog Contents 0], [RowLog Contents 1] -- After Image

クエリの結果を見ると、2 番目のレコードに CDC を有効にした後に発行した UPDATE ステートメントの状態がトランザクションログにキャプチャされていることが分かります。

CDC パラメータの理解

CDC では、2 つのジョブが作成されます。

• キャプチャジョブ – トランザクションログファイルを読み取り、変更をトラッキングするテーブルにプッシュします。
• クリーンアップジョブ – 保持期間を過ぎた変更トラッキングテーブルのレコードを削除します。

以下は、AWS DMS に関連する CDC パラメータです。

• max_trans – 各スキャンサイクルで処理するトランザクションの最大数
• max_scans – ログからすべての行を抽出するために実行するスキャンサイクルの最大数
• continuous – キャプチャジョブを継続的に実行するか (1)、1回だけ実行するか (0) を示します
• polling_interval – ログスキャンサイクル間の秒数
• retention – 変更行がテーブルに保持される分数

AWS DMS は変更テーブルを読み取らないので、トランザクションログの変更の保持を制御するために CDC パラメータを調整する必要があります。

次のセクションでは、max_trans、max_scans、polling_interval パラメータがトランザクションログ内のレコードの保持にどのように役立つのか、AWS DMS が変更をキャプチャするのに十分な期間変更が保持されるように調整する方法について説明します。

CDC パラメータの実行

これらのパラメーターを説明するために、次の手順を踏みます。

1. dmscdc データベースとその中の dmstestcdc テーブルを作成してください。

   create database dmscdc;
   
   use dmscdc;
   
   CREATE TABLE dbo.dmstestcdc(n INT NOT NULL PRIMARY KEY);
   

   SQL
2. データベース dmscdc およびテーブル dmstestcdc で CDC を有効にします。

   exec msdb.dbo.rds_cdc_enable_db 'dmscdc';
   
   use dmscdc;
   exec sys.sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'dmstestcdc' ,
   @role_name = 'CDCRole',
   @supports_net_changes = 1;

   SQL

   CDC パラメータを調整して、ログレコードが十分な期間保持されるようにする必要があります。そうすることで、AWS DMS がトランザクションレコード、つまり、ソースデータベースのトランザクションログファイルで探している特定のログシーケンス番号 (LSN) を照会できるようになります。これらは次の要因に左右されます。

   • ターゲットがソースに比べてどれくらいトランザクションが遅れているか
   • CDC ジョブの実行頻度、つまり特定のポーリング間隔はどのくらいか
   • Maxtrans と Maxscans の値は何か。これらのパラメータは、CDC が各実行でどれくらいのトランザクションを処理するかを決定します。
3. 次のように取り込みジョブを構成してください。取り込みジョブのパラメーターを変更する場合は、毎回 CDC ジョブを停止して再起動する必要があります。この場合、pollinginterval を変更する必要があります。

   EXECUTE sys.sp_cdc_change_job
   @job_type = N'capture',
   @pollinginterval = 3599;--Setting the polling interval for 1 hour
   
   EXEC sys.sp_cdc_stop_job @job_type = N'capture';
   EXEC sys.sp_cdc_start_job @job_type = N'capture';

   SQL
4. 次のコマンドを実行して CDC パラメータを確認してください。

   exec sys.sp_cdc_help_jobs;

   SQL

   job_id	job_type	job_name	maxtrans	maxscans	continuous	pollinginterval	retention	threshold
   A49487C5-BF3C-4A8C-9385-6AFA7A3541B9	capture	cdc.dmscdc_capture	500	10	1	3599	0	0
   17511020-59D2-4C9E-BEA9-0578C0D23B11	cleanup	cdc.dmscdc_cleanup	0	0	0	0	4320	5000

   前述の設定では、キャプチャジョブは 1 時間の周期で 5,000 レコード (maxtrans * maxscans) を処理します。

5. テーブル dmstestcdc にレコードをいくつか挿入して確認してください。

   DECLARE @max AS INT, @min AS INT;
   SET @max = 100000;
   SET @min = 1;
   
   WHILE @min <= @max
   BEGIN
   INSERT INTO dbo.dmstestcdc VALUES(@min);
   SET @min=@min+1;
   END

   SQL

   キャプチャジョブは、トランザクションログから前の取引を読み取り、それらを replicated されたものとしてマークします。この場合は 100,001 レコードです。CDC ジョブが実行されると、キャプチャジョブはそれらの取引を完了としてマークします。

6. 次のクエリを実行して CDC セッションを確認してください。これにより 10 行が取得されるはずです。このクエリにより、CDC が処理したレコード数が分かります。今回の場合は 5,000 件です。

   SELECT tran_count,start_time,end_time, scan_phase from sys.dm_cdc_log_scan_sessions where scan_phase<>'Aggregate' order by end_time desc

   SQL

前述のレコードは、通常 5 分ごとに行われる Amazon RDS for SQL Server のトランザクションログのバックアップ時にトランザクションログから削除されます。これにより、トランザクションログのサイズを維持し、LSN を進めることができます。残りのレコード (95,001件) は、次のキャプチャジョブの実行時に取り込まれます。

SQL Server は CDC によってトランザクションが読み取られた後でしか トランザクションログをフラッシュしません。トランザクションログに保持するレコード数と AWS DMS のレプリケーションラグのバランスを取る必要があります。この場合、ポーリング間隔を短くすることで、キャプチャジョブのパラメータを積極的に設定します。その結果、トランザクションログから LSN が欠落するシナリオが発生する可能性があります。トランザクションログの切り捨てを回避して、変更が十分な期間トランザクションログに保持されるようにするには、次のコマンドを実行してポーリング間隔を 1 日に設定することをお勧めします。

use dbname

EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 86399

exec sp_cdc_stop_job 'capture'

exec sp_cdc_start_job 'capture'

CDC の履歴情報のキャプチャ

キャプチャジョブの履歴情報を監視するには、sys.dm_cdc_log_scan_sessions テーブルを照会できます。このテーブルには、現在のデータベース内の各ログスキャンセッションに対して 1 行が含まれています。最大 32 のスキャンセッションが含まれます。次のクエリを実行して、最新の 10 レコードを取得します。

SELECT session_id, start_time, end_time, duration, scan_phase,
error_count, tran_count,command_count,last_commit_cdc_time, latency, empty_scan_count, failed_sessions_count
FROM sys.dm_cdc_log_scan_sessions order by end_time desc OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

以下はサンプル出力です。

ベストプラクティスと既知の問題

このセクションでは、CDC パラメーターに関するベストプラクティスと考慮事項をいくつか説明します。

Multi-AZ インスタンスでのフェイルオーバー時にトランザクションログレコードが切り捨てられる

プライマリインスタンスで CDC パラメータを変更した場合は、必ず rds_set_configuration コマンドを実行して、フェイルオーバー時にも変更内容が保持されるようにしてください。

例えば、dms_test データベース上で次のサンプルコマンドを実行して、maxtrans と pollinginterval パラメータを設定できます。

USE dms_test;

EXEC sys.sp_cdc_change_job
@job_type = 'capture',
@maxtrans = 10000,
@pollinginterval = 6000;

フェイルオーバー後もこれらの値が保持されるように、次のコマンドを実行してください。

EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 10000;
EXEC rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , 6000;

AWS DMS レプリケーションインスタンスの計画的フェイルオーバーまたはメンテナンス

Amazon RDS for SQL Server の場合、ソースでメンテナンス作業を行う際、あるいは関連する AWS DMS レプリケーションインスタンスの計画的なスケーリングを行う際に、AWS DMS タスクが停止する度にキャプチャジョブが実行されないようにする必要があります。キャプチャジョブが実行されると、スキャンされたイベントは Amazon Simple Storage Service (Amazon S3) で 5 分ごとにトランザクションログバックアップが行われるときにトランザクションログから削除されます。

1. 次のコマンドを実行してキャプチャジョブを停止してください。

   exec sp_cdc_stop_job 'capture'

   SQL
2. AWS DMS タスクを停止します。
3. 必要なメンテナンスを完了します。
4. AWS DMS タスクを再開します。
5. ソースの遅延が 0 になるのを待ちます。
6. 次のコマンドを実行してキャプチャジョブを開始します。

   exec sp_cdc_start_job 'capture'

   SQL

AWS DMS タスクは、上記の手順に従わない場合、次のエラーメッセージで失敗します。

2023-10-06T15:02:05 [SOURCE_CAPTURE ]E: Failed to access LSN '0000019f:00007fff:0008' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:813)

ソースでキャプチャジョブを停止した後に LSN が切り捨てられているのを確認した場合、アクティブなトランザクションログ内に切り捨てを防ぐ CDC イベントがない可能性があります。これはデータベースがアイドル状態にあるか、トランザクション数が少ない場合に発生する可能性があります。この場合、手順は次のとおりです。

1. 次のコマンドを実行してキャプチャジョブを停止してください。

   exec sp_cdc_stop_job 'capture'

   SQL
2. AWS DMS タスクを停止する前に、CDC 対応のデータベースにいくつかのトランザクションまたは変更があることを確認してください。毎秒 DML ステートメントを実行するスクリプトを実行できます。テストスクリプトを作成する場合は、このセクションの後半に記載されている手順に従ってください。
3. AWS DMS タスクを停止します。
4. 必要なメンテナンスを完了させます。
5. AWS DMS タスクを再開します。
6. ソースレイテンシを監視して同期を待ちます。
7. ステップ 2 で設定したスクリプトを停止します。
8. 次のコマンドを実行してキャプチャジョブを開始します。

   exec sp_cdc_start_job 'capture'

   SQL

ステップ 2 で言及されたテストスクリプトを実行するためのスクリプトを設定するには、以下の手順に従ってください。以下のスクリプトでは、dbo スキーマの下に test_table という名前のテーブルを作成し、その test_table テーブルで CDC を有効にします。次に、SQL Server エージェントジョブを設定し、上記のテーブルにレコードを挿入してから削除します。これにより、CDC ジョブによってピックアップされる必要のあるトランザクションログに変更があり、トランザクションログの切り捨てを防ぐことができます。

1. テストテーブルを作成します。

   create table dbo.test_table (id int not null PRIMARY KEY);

   SQL
2. 新しいテーブルを CDC に追加してください。

   use dmscdc;
   exec sys.sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'test_table' ,
   @role_name = 'CDCRole',
   @supports_net_changes = 1;

   SQL
3. Amazon RDS で SQL Server エージェントジョブを作成し、1 分ごとにレコードを挿入または削除します。エージェントジョブで適切な owner_login_name と database_name の値を使用してください。

   USE [msdb]
   
   GO
   
   /****** Object: Job [aws_dms_traffic_to_test_table] Script Date: 10/9/2023 4:17:28 PM ******/
   
   BEGIN TRANSACTION
   
   DECLARE @ReturnCode INT
   
   SELECT @ReturnCode = 0
   
   /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 10/9/2023 4:17:28 PM ******/
   
   IF NOT EXISTS (SELECT
   name FROM msdb.dbo.syscategories WHERE
   name=N'[Uncategorized (Local)]' AND category_class=1)
   
   BEGIN
   
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   END
   
   DECLARE @jobId BINARY(16)
   
   EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'aws_dms_traffic_to_test_table', 
   
    @enabled=1, 
   
    @notify_level_eventlog=0, 
   
    @notify_level_email=0, 
   
    @notify_level_netsend=0, 
   
    @notify_level_page=0, 
   
    @delete_level=0, 
   
    @description=N'No description available.', 
   
    @category_name=N'[Uncategorized (Local)]', 
   
    @owner_login_name=N'admin', @job_id = @jobId OUTPUT
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   /****** Object: Step [generate_traffic] Script Date: 10/9/2023 4:17:28 PM ******/
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'generate_traffic', 
   
    @step_id=1, 
   
    @cmdexec_success_code=0, 
   
    @on_success_action=1, 
   
    @on_success_step_id=0, 
   
    @on_fail_action=2, 
   
    @on_fail_step_id=0, 
   
    @retry_attempts=0, 
   
    @retry_interval=0, 
   
    @os_run_priority=0, @subsystem=N'TSQL', 
   
    @command=N'insert into dbo.test_table values (30); delete from dbo.test_table where id = 30; ', 
   
    @database_name=N'dmscdc', 
   
    @flags=0
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'schedule for running DML statements for generating user_traffic', 
   
    @enabled=1, 
   
    @freq_type=4, 
   
    @freq_interval=1, 
   
    @freq_subday_type=4, 
   
    @freq_subday_interval=1, 
   
    @freq_relative_interval=0, 
   
    @freq_recurrence_factor=0, 
   
    @active_start_date=20231006, 
   
    @active_end_date=99991231, 
   
    @active_start_time=0, 
   
    @active_end_time=235959, 
   
    @schedule_uid=N'84a2b2ab-4234-40a3-add4-c04d561ad88f'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
   
   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
   COMMIT TRANSACTION
   
   GOTO EndSave
   
   QuitWithRollback:
   
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
   
   EndSave:
   
   GO
   

   SQL
4. AWS DMS コンソールで、AWS DMS タスクのテーブル選択ルールにワイルドカード (%) を使用してこのテーブルを複製する場合は、マッピングルールを使用してこのテーブルを AWS DMS タスクから除外してください。

   {
   "rule-type": "selection",
   "rule-id": "1",
   "rule-name": "1",
   "object-locator": {
   "schema-name": "dbo",
   "table-name": "test_table"
   },
   "rule-action": "exclude",
   "filters": []
   },

   JSON

SQL Server インスタンスの RDS の計画的な再起動またはフェイルオーバー

RDS for SQL Server エージェントサービスは、RDS for SQL Server インスタンスの再起動やフェイルオーバーが発生するたびに再起動し、これにより再起動またはフェイルオーバー後に CDC ジョブが再実行されます。トランザクションログの切り捨てを回避するには、次の手順に従ってください。

1. AWS DMS タスクを停止します。
2. フェイルオーバー後に元に戻すため、現在の maxtrans と maxscans の値を取得します。

   sys.sp_cdc_help_jobs;

   SQL
3. CDC の設定を変更して、maxtrans と maxscans を 1 に設定します。

   EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@maxtrans = 1, @maxscans = 1
   exec sp_cdc_stop_job 'capture'
   GO

   SQL
4. フェイルオーバー後も CDC パラメーターを保持するため、次のステートメントを実行してください。

   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 1;
   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxscans' , 1;

   SQL
5. RDS for SQL Server インスタンスを再起動します。
6. AWS DMS タスクを再開します。
7. キャプチャされた構成で、キャプチャされたジョブを再起動します。次のスクリプトでは、maxtrans を 500、maxscans を 10 と想定していますが、ステップ 2 でキャプチャした値を使用する必要があります。

   EXEC sys.sp_cdc_change_job @job_type = 'capture', @maxtrans = 500, @maxscans = 10
   exec sp_cdc_stop_job 'capture'
   exec sp_cdc_start_job 'capture'
   GO

   SQL
8. フェイルオーバー後も CDC パラメータを保持するために、次のステートメントを実行してください。

   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxtrans' , 500;
   EXEC rdsadmin..rds_set_configuration 'cdc_capture_maxscans' , 10;

   SQL

後片付け

定期的な課金を避けるために、リソースをクリーンアップしてください。

1. AWS DMS コンソールで、設定したすべての AWS DMS タスクを削除します。
2. 次のコマンドを実行してデータベースを削除します。

   EXECUTE msdb.dbo.rds_drop_database N'dmscdc'

   SQL

結論

この投稿では、Amazon RDS for SQL Server をソースとして AWS DMS タスクを構成する際の CDC パラメーターの設定の重要性を説明し、さらにベストプラクティスについても説明しました。

翻訳はソリューションアーキテクトの Yoshinori Sawada が担当しました。原文はこちらです。

著者について

Suchindranath Hegde は、Amazon Web Services のデータマイグレーションスペシャリストソリューションアーキテクトです。AWS DMS を使用して AWS クラウドへのデータ移行に関するガイダンスと技術支援をお客様に提供しています。

Abhishek Chaturvedi は、Amazon Web Services の DMS チームのシニアデータベースエンジニアです。

Mahesh Kansara は、Amazon Web Services のデータベースエンジニアリングマネージャーです。彼は開発およびエンジニアリングチームと密接に協力し、移行およびレプリケーションサービスの改善に取り組んでいます。また、お客様と協力し、さまざまなデータベースおよび分析プロジェクトに関するリードとテクニカルサポートを提供し、AWS を使用する際のソリューションの価値向上をサポートしています。

Junu Thankappan は、Amazon Web Services のシニアデータベースエンジニアです。彼は AWS RDS チームに所属し、商用データベースエンジンと SQL Server に注力しています。