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

以降のセクションでは、Cloud SQL for SQL Server データベースを構成する方法について説明します。

Cloud SQL インスタンスに接続します。これを行うには、Cloud Shell プロンプトで gcloud sql connect コマンドを使用します。
次のコマンドを実行して、データベースで CDC を有効にします。
```
EXEC msdb.dbo.gcloudsql_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
```
注: CDC を有効にするテーブルごとにコマンドを実行する必要があります。
ソースに変更を適用する保持期間を設定します。
```
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 プロシージャを実行すると、設定が開始されます。
スナップショット分離を有効にします。

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

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

スナップショット分離を有効にするには:
1. SQL Server クライアントを使用してデータベースに接続します。
2. 次のコマンドを実行します。
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
DATABASE_NAME はデータベースの名前に置き換えます。

ログの切り捨ての防止対策を設定する

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

DATABASE_NAME は、ダミーテーブルを作成するデータベースの名前に置き換えます。

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

CREATE PROCEDURE dbo.DatastreamLogTruncationSafeguard
AS
BEGIN
  -- Start a new transaction
  BEGIN TRANSACTION;
  INSERT INTO dbo.gcp_datastream_truncation_safeguard (char_column) VALUES ('a')
  -- Wait for one day before ending the transaction
  WAITFOR DELAY '23:59';
  -- Commit the transaction
  COMMIT TRANSACTION;
END;

別のストアドプロシージャを作成します。今回は、前のステップで作成したストアドプロシージャを毎日実行するジョブを作成します。

CREATE PROCEDURE dbo.SetUpDatastreamJob 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')

  DECLARE @job_name_1 VARCHAR(MAX);
  Set @job_name_1 = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1')
  -- Schedule the procedure to run again tomorrow
  IF NOT EXISTS (
    select * from msdb.dbo.sysjobs
    WHERE name = @job_name_1
  )
  BEGIN
    EXEC msdb.dbo.sp_add_job
    @job_name = @job_name_1,
    @enabled = 1,
    @description = N'Execute the procedure every day' ;

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

    -- Add a schedule that runs the stored procedure every day.
    DECLARE @start_time_1 time;
    SET @start_time_1 = DATEADD(MINUTE, 1, GETDATE());

    DECLARE @schedule_name_1 VARCHAR(MAX);
    Set @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEverydaySchedule1')

    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_interval = 1,
    @active_start_time = @formatted_start_time_1;

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

    -- 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_1,
    @schedule_name = @schedule_name_agent_startup ;

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

  DECLARE @job_name_2 VARCHAR(MAX);
  Set @job_name_2 = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob2')

  IF NOT EXISTS (
    select * from msdb.dbo.sysjobs
    WHERE name = @job_name_2
  )
  BEGIN
    EXEC msdb.dbo.sp_add_job
    @job_name = @job_name_2,
    @enabled = 1,
    @description = N'Procedure execution every day' ;

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

    DECLARE @start_time_2 time;
    SET @start_time_2 = DATEADD(HOUR, 12, GETDATE());
    DECLARE @formatted_start_time_2 INT;
    SET @formatted_start_time_2 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_2, 114), ':' ,''));
    DECLARE @schedule_name_2 VARCHAR(MAX);
    Set @schedule_name_2 = CONCAT(@database_name, '_', 'DatastreamEverydaySchedule2')

    EXEC msdb.dbo.sp_add_schedule
    @schedule_name = @schedule_name_2,
    @freq_type = 4,  -- daily start
    @freq_interval = 1,
    @active_start_time = @formatted_start_time_2;

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

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

データストリームジョブを作成するストアドプロシージャを実行します。

EXEC dbo.SetUpDatastreamJob

Datastream ユーザーを作成する:
1. Google Cloud コンソールで Cloud SQL の [インスタンス] ページに移動します。
  
  Cloud SQL の [インスタンス] に移動
2. ユーザーを作成します。
3. ユーザーに db_owner と db_denydatawriter のロールを割り当てます。
```
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
```