Amazon RDS for SQL Server データベースを構成する

次の手順では、Datastream で使用するために Amazon RDS SQL Server データベースを構成する方法について説明します。

移行元データベースの変更データキャプチャ（CDC）を有効にします。ソースデータベース用の CDC を有効にするには、データベースに接続し、SQL プロンプト、ターミナル、または Amazon RDS ダッシュボードを使用して次のコマンドを実行します。
```
EXEC msdb.dbo.rds_cdc_enable_db 'DATABASE_NAME'
```
DATABASE_NAME は、ソースデータベースの名前に置き換えます。
変更を取得する必要がある各テーブルで CDC を有効にします。
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_enable_table
@source_schema = N'SCHEMA_NAME',
@source_name = N'TABLE_NAME',
@role_name = NULL
GO
```
次のように置き換えます。
- DATABASE_NAME: ソースデータベースの名前
- SCHEMA_NAME: テーブルが属するスキーマの名前
- TABLE_NAME: CDC を有効にするテーブルの名前
SQL Server エージェントを起動し、常に実行していることを確認します。SQL Server エージェントが長期間停止している場合、ログが切り捨てられ、Datastream で読み取られなかった変更データが完全に失われる可能性があります。

SQL Server エージェントの実行については、SQL Server エージェントのインスタンスを起動、停止、再起動するをご覧ください。

注:エージェントが長時間停止するのを防ぐために、SQL Server エージェントのサービスの　自動再起動の設定をおすすめします。
スナップショット分離を有効にします。

SQL Server データベースからデータをバックフィルする際は、整合性のあるスナップショットを確保することが重要です。このセクションで説明している設定を適用しない場合、バックフィルプロセス中にデータベースに加えた変更により、特に主キーのないテーブルでは、重複や誤った結果が生じる可能性があります。

スナップショット分離を有効にすると、バックフィルプロセスの開始時にデータベースの一時的なビューが作成されます。これにより、他のユーザーが同時にライブテーブルに変更を加えた場合でも、コピーされるデータの整合性が確実に維持されます。スナップショット分離を有効にすると、パフォーマンスにわずかな影響を与える可能性がありますが、信頼性の高いデータ抽出には不可欠です。

スナップショット分離を有効にするには:
1. SQL Server クライアントを使用してデータベースに接続します。
2. 次のコマンドを実行します。
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
DATABASE_NAME は、データベースの名前に置き換えます。
Datastream ユーザーを作成する:
1. ソースデータベースに接続し、次のコマンドを入力します。
```
USE DATABASE_NAME;
```
2. Datastream で接続プロファイルを設定するときに使用するログインを作成します。
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. ユーザーを作成し、db_owner ロールと db_denydatawriter ロールを割り当てます。
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
```
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
```
4. このユーザーを master データベースに追加します。
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

トランザクションログの CDC メソッドに必要な追加手順

このセクションで説明する手順は、トランザクションログのCDC メソッドで使用するためにソース SQL Server データベースを構成する場合にのみ必要です。

sys.fn_dblog 関数に SELECT 権限を付与します。

USE master;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;

ユーザーを msdb データベースに追加し、次の権限を割り当てます。

USE msdb;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT SELECT ON dbo.sysjobs TO USER_NAME;

master データベース内のユーザーに次の権限を割り当てます。
```
  USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;
```
ソースで変更を有効にする保持期間を設定します。
```
USE [DATABASE_NAME]
EXEC sys.sp_cdc_change_job @job_type = 'capture' , @pollinginterval = 86399
EXEC sp_cdc_stop_job 'capture'
EXEC sp_cdc_start_job 'capture'
```
@pollinginterval パラメータは秒単位で測定され、推奨値は 86399 に設定されています。つまり、トランザクションログは、86,399 秒間（1 日）変更を保持します。sp_cdc_start_job 'capture プロシージャを実行すると、設定が開始されます。

ログの切り捨ての防止対策を設定します。

CDC のリーダーがログの読み取りに十分な時間を確保しながら、ログの切り捨てによって保存容量が不足しないようにするには、ログの切り捨ての防止対策を設定します。

SQL Server クライアントを使用してデータベースに接続します。

データベースにダミーテーブルを作成します。

USE [DATABASE_NAME];
CREATE TABLE dbo.gcp_datastream_truncation_safeguard (
  [id] INT IDENTITY(1,1) PRIMARY KEY,
  CreatedDate DATETIME DEFAULT GETDATE(),
  [char_column] CHAR(8)
  );

ログの切り捨てを防止するために指定した期間にアクティブなトランザクションを実行するストアドプロシージャを作成します。

CREATE PROCEDURE [dbo].[DatastreamLogTruncationSafeguard] @transaction_logs_retention_time INT
AS
BEGIN
  -- Start a new transaction
  BEGIN TRANSACTION;
  INSERT INTO dbo.gcp_datastream_truncation_safeguard (char_column) VALUES ('a')

DECLARE @formatted_time VARCHAR(5)
SET @formatted_time = CONVERT(VARCHAR(5), DATEADD(MINUTE, @transaction_logs_retention_time, 0), 108);
  -- Wait for X minutes before ending the transaction
  WAITFOR DELAY @formatted_time;
  -- Commit the transaction
  COMMIT TRANSACTION;
END;

別のストアドプロシージャを作成します。今回は、前の手順で作成したストアドプロシージャを指定した間隔に従って実行するジョブを作成します。

CREATE PROCEDURE [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time INT
AS
BEGIN
  DECLARE @database_name VARCHAR(MAX)
  SET @database_name =  (SELECT DB_NAME());;

  DECLARE @command_str VARCHAR(MAX);
  SET @command_str = CONCAT('Use ', @database_name,'; exec dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time = ' + CAST(@transaction_logs_retention_time AS VARCHAR(10)));

  DECLARE @job_name VARCHAR(MAX);
SET @job_name =
  CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1')

    -- Add 3 schedules to the job to run again after specified time.
    IF
      NOT EXISTS(
        SELECT *
        FROM msdb.dbo.sysjobs
        WHERE name = @job_name
      )
        BEGIN
          EXEC
            msdb.dbo.sp_add_job
              @job_name
  = @job_name,
  @enabled = 1,
  @description = N'Execute the procedure to run an active transaction for x minutes.';

EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
@step_name = N'Execute_DatastreamLogTruncationSafeguard',
@subsystem = N'TSQL',
@command = @command_str;

  -- Add a schedule that runs the stored procedure every given minutes starting now.
  DECLARE @schedule_name_1 VARCHAR(MAX);
  SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesFromNow')

  DECLARE @start_time_1 time;
  SET @start_time_1 = DATEADD(SECOND, 1, GETDATE());
  DECLARE @formatted_start_time_1 INT;
  SET @formatted_start_time_1 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_1, 114), ':' ,''));

  EXEC msdb.dbo.sp_add_schedule
  @schedule_name = @schedule_name_1,
  @freq_type = 4,  -- daily start
  @freq_subday_type = 4,  -- every X minutes daily
  @freq_interval = 1,
  @freq_subday_interval = @transaction_logs_retention_time,
  @active_start_time = @formatted_start_time_1;

  EXEC msdb.dbo.sp_attach_schedule
  @job_name = @job_name,
  @schedule_name = @schedule_name_1 ;

  -- Add a schedule that runs the stored procedure after every given minutes starting after some delay.
  DECLARE @schedule_name_2 VARCHAR(MAX);
  Set @schedule_name_2 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesAfterDelay');

  DECLARE @start_time_2 time;
  SET @start_time_2 = DATEADD(MINUTE, @transaction_logs_retention_time / 2, GETDATE());

  DECLARE @formatted_start_time_2 INT;
  SET @formatted_start_time_2 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_2, 114), ':' ,''));

  EXEC msdb.dbo.sp_add_schedule
  @schedule_name = @schedule_name_2,
  @freq_type = 4,  -- daily start
  @freq_subday_type = 4,  -- every x minutes daily
  @freq_interval = 1,
  @freq_subday_interval = @transaction_logs_retention_time,
  @active_start_time = @formatted_start_time_2;

  EXEC msdb.dbo.sp_attach_schedule
  @job_name = @job_name,
  @schedule_name = @schedule_name_2 ;

  -- Add a schedule that runs the stored procedure on the SQL Server Agent startup.
  DECLARE @schedule_name_agent_startup VARCHAR(MAX);
  Set @schedule_name_agent_startup = CONCAT(@database_name, '_', 'DatastreamSqlServerAgentStartupSchedule')

  EXEC msdb.dbo.sp_add_schedule
  @schedule_name = @schedule_name_agent_startup,
  @freq_type = 64,  -- start on SQL Server Agent startup
  @active_start_time = @formatted_start_time_1;

  EXEC msdb.dbo.sp_attach_schedule
  @job_name = @job_name,
  @schedule_name = @schedule_name_agent_startup ;

  EXEC msdb.dbo.sp_add_jobserver
  @job_name = @job_name,
  @server_name = @@servername ;
  END
END;

データストリームジョブを作成するストアドプロシージャを実行します。
```
DECLARE @transaction_logs_retention_time INT = (INT)
EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
```
INT は、ログを保持する分数に置き換えます。次に例を示します。
- 値が 60 の場合、保持期間は 1 時間です。
- 値が 24 * 60 の場合、保持期間は 1 日です。
- 値が 3 * 24 * 60 の場合、保持期間は 3 日です。

Amazon RDS for SQL Server データベースを構成する

トランザクション ログの CDC メソッドに必要な追加手順

トランザクションログの CDC メソッドに必要な追加手順