オンプレミスまたは EC2 でのセルフマネージド型 SQL Server の変更のキャプチャ Cloud SQL Server DB インスタンスでの継続的なレプリケーションのセットアップ

SQL Server からの継続的なレプリケーションのデータ変更をキャプチャする

このトピックでは、SQL Server ソースで CDC レプリケーションを設定する方法について説明します。

トピック

オンプレミスまたは Amazon EC2 上のセルフマネージド型 SQL Server のデータ変更のキャプチャ
Cloud SQL Server DB インスタンスでの継続的なレプリケーションのセットアップ

オンプレミスまたは Amazon EC2 上のセルフマネージド型 SQL Server のデータ変更のキャプチャ

Microsoft SQL Server ソースデータベースからの変更をキャプチャするには、データベースが完全バックアップ用に構成されていることを確認してください。データベースをフルリカバリモードまたは一括ログモードで構成します。

セルフマネージド型 SQL Server ソースの場合、AWS DMS は以下を使用します。

MS レプリケーション: プライマリキーを持たないテーブルの変更をキャプチャします。これは、ソース SQL Server インスタンスで AWS DMS エンドポイントユーザーに sysadmin 権限を付与することで自動的に設定できます。または、このセクションで説明されているステップに従ってソースを準備し、AWS DMS エンドポイントに sysadmin 以外のユーザーを使用することもできます。
MS-CDC: プライマリキーを持たないテーブルの変更をキャプチャします。MS-CDC は、すべてのテーブルのデータベースレベルで個別に有効にします。

継続的なレプリケーション (CDC) に SQL Server データベースをセットアップする場合、次のいずれかの操作を実行できます：

sysadmin ロールを使用する継続的なレプリケーションをセットアップします
sysadmin ロールを使用しない継続的なレプリケーションをセットアップします。

セルフマネージド型 SQL Server での継続的なレプリケーションのセットアップ

このセクションには、sysadmin ロールを使用する場合と使用しない場合の、セルフマネージド型 SQL Server での継続的なレプリケーションの設定に関する情報が記載されています。

トピック

セルフマネージド型 SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールを使用
スタンドアロン SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし
可用性グループ環境の SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし

セルフマネージド型 SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールを使用

SQL Server の AWS DMS 継続的レプリケーションでは、プライマリキーのあるテーブルにはネイティブ SQL Server レプリケーションを使用し、プライマリキーのないテーブルには変更データキャプチャ (CDC) を使用します。

継続的レプリケーションを設定する前に、「SQL Server のソースからの継続的なレプリケーション (CDC) を使用するための前提条件」をご参照ください。

プライマリキーがあるテーブルの場合、AWS DMS は基本的にソースで必要なアーティファクトを設定できます。ただし、セルフ管理 SQL Server ソースインスタンスの場合、最初に、SQL Server のディストリビューションを手動で設定する必要があります。その後、sysadmin アクセス許可のある AWS DMS ソースユーザーは、プライマリキーのあるテーブル公開を自動作成できます。

ディストリビューションがすでに設定されているかどうかを確認するには、以下のコマンドを実行します。


sp_get_distributor

列ディストリビューションの結果が NULL の場合、ディストリビューションは設定されていません。ディストリビューションを設定するには、次の手順を使用します。

ディストリビューションを設定するには

SQL Server Management Studio (SSMS) ツールを使用して SQL Server ソースデータベースに接続します。
[Replication] (レプリケーション) フォルダのコンテキスト (右クリック) メニューを開き、[Configure Distribution] (ディストリビューション設定) を選択します。ディストリビューションの構成ウィザードが開きます。
ウィザードに従ってデフォルト値を入力し、ディストリビューションを作成します。

CDC をセットアップするには

AWS DMS バージョン 3.4.7 以降では、読み取り専用レプリカを使用していない場合、データベースとすべてのテーブルに MS CDC を自動的にセットアップできます。この機能を使用するには、SetUpMsCdcForTables ECA を true に設定します。ECA の詳細については、「エンドポイント設定」を参照してください。

AWS DMS 3.4.7 以前のバージョン、または読み取り専用レプリカをソースとして使用する場合は、次の手順を実行します。

プライマリキーがないテーブルの場合、データベースの MS-CDC をセットアップします。そのためには、sysadmin ロールが割り当てられたアカウントを使用し、次のコマンドを実行します。
```
use [DBname]
EXEC sys.sp_cdc_enable_db
```
次に、ソーステーブルごとに MS-CDC を設定します。一意キーはあるがプライマリキーがないテーブルごとに、次のクエリを実行して MS-CDC を設定します。
```
exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@index_name = N'unique_index_name',
@role_name = NULL,
@supports_net_changes = 1
GO
```
プライマリキーと一意キーの両方がないテーブルごとに、次のクエリを実行して MS-CDC を設定します。
```
exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO
```

特定のテーブルの MS-CDC をセットアップする方法の詳細については、 SQL Server のドキュメントをご参照ください。

スタンドアロン SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし

このセクションでは、ユーザーアカウントで sysadmin アクセス権限を必要としないスタンドアロン SQL Server データベースソースの継続的なレプリケーションをセットアップする方法について説明します。

注記

このセクションのステップを実行すると、システム管理者以外の DMS ユーザーには、以下を実行するアクセス許可が付与されます。

オンライントランザクションログファイルからの変更の読み取り
トランザクションログのバックアップファイルから変更を読み取るためのディスクアクセス
DMS が使用するパブリケーションの追加または変更
パブリケーションへの記事の追加

SQL Server からの継続的なレプリケーションのデータ変更をキャプチャするの説明に従って、レプリケーション向けに Microsoft SQL Server をセットアップします。
ソースデータベースで MS-REPLICATION を有効にします。これは手動で実行することも、sysadmin ユーザーとしてタスクを 1 回実行することによっても行うことができます。

次のスクリプトを使用して、ソースデータベースに awsdms スキーマを作成します。


use master
go
create schema awsdms
go


-- Create the table valued function [awsdms].[split_partition_list] on the Master database, as follows:
USE [master]
GO

set ansi_nulls on
go

set quoted_identifier on
go

if (object_id('[awsdms].[split_partition_list]','TF')) is not null

drop function [awsdms].[split_partition_list];

go

create function [awsdms].[split_partition_list]

(

@plist varchar(8000), —A delimited list of partitions

@dlm nvarchar(1) —Delimiting character

)

returns @partitionsTable table —Table holding the BIGINT values of the string fragments

(

pid bigint primary key

)   

as

begin

declare @partition_id bigint;

declare @dlm_pos integer;

declare @dlm_len integer;

set @dlm_len = len(@dlm);

while (charindex(@dlm,@plist)>0)

begin

set @dlm_pos = charindex(@dlm,@plist);

set @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint);

insert into @partitionsTable (pid) values (@partition_id)

set @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist));

end

set @partition_id = cast (ltrim(rtrim(@plist)) as bigint);

insert into @partitionsTable (pid) values ( @partition_id );

return

end

GO

次のスクリプトを使用して Master データベースに [awsdms].[rtm_dump_dblog] プロシージャを作成します。


use [MASTER]

go

if (object_id('[awsdms].[rtm_dump_dblog]','P')) is not null drop procedure [awsdms].[rtm_dump_dblog];
go


set ansi_nulls on
go

set quoted_identifier on
GO



CREATE procedure [awsdms].[rtm_dump_dblog]

(

@start_lsn varchar(32),

@seqno integer,

@filename varchar(260),

@partition_list varchar(8000), — A comma delimited list: P1,P2,... Pn

@programmed_filtering integer,

@minPartition bigint,

@maxPartition bigint

)

as begin

declare @start_lsn_cmp varchar(32); — Stands against the GT comparator

SET NOCOUNT ON — – Disable "rows affected display"

set @start_lsn_cmp = @start_lsn;

if (@start_lsn_cmp) is null

set @start_lsn_cmp = '00000000:00000000:0000';

if (@partition_list is null)

begin

RAISERROR ('Null partition list waspassed',16,1);

return

end

if (@start_lsn) is not null

set @start_lsn = '0x'+@start_lsn;

if (@programmed_filtering=0)



SELECT

[Current LSN],

[operation],

[Context],

[Transaction ID],

[Transaction Name],

[Begin Time],

[End Time],

[Flag Bits],

[PartitionID],

[Page ID],

[Slot ID],

[RowLog Contents 0],

[Log Record],

[RowLog Contents 1]

FROM

fn_dump_dblog (

@start_lsn, NULL, N'DISK', @seqno, @filename,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default)

where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS

and

(

( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )

or

( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')

and

( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and (datalength([RowLog Contents 0]) in (0,1))))

and [PartitionID] in ( select * from master.awsdms.split_partition_list (@partition_list,','))

)

or

([operation] = 'LOP_HOBT_DDL')

)


else


SELECT

[Current LSN],

[operation],

[Context],

[Transaction ID],

[Transaction Name],

[Begin Time],

[End Time],

[Flag Bits],

[PartitionID],

[Page ID],

[Slot ID],

[RowLog Contents 0],

[Log Record],

[RowLog Contents 1] — After Image

FROM

fn_dump_dblog (

@start_lsn, NULL, N'DISK', @seqno, @filename,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default,

default, default, default, default, default, default, default)

where [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS

and

(

( [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )

or

( [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')

and

( ( [context] in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX' and (datalength([RowLog Contents 0]) in (0,1))))

and ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition)

)

or

([operation] = 'LOP_HOBT_DDL')

)



SET NOCOUNT OFF — Re-enable "rows affected display"

end

GO

次のスクリプトを使用して、Master データベースに証明書を作成します。


Use [master]
Go

CREATE CERTIFICATE [awsdms_rtm_dump_dblog_cert] ENCRYPTION BY PASSWORD = N'@5trongpassword'

WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';

次のスクリプトを使用して、証明書からログインを作成します。


Use [master]
Go

CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];

次のスクリプトを使用して、ログインを sysadmin サーバーロールに追加します。
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
```
次のスクリプトを使用して、証明書を使って署名を [master].[awsdms].[rtm_dump_dblog] に追加します。
```
Use [master]
GO
ADD SIGNATURE
TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@5trongpassword';
```
注記
ストアドプロシージャを再作成する場合は、署名を再度追加する必要があります。

次のスクリプトを使用して、マスターデータベースに [awsdms].[rtm_position_1st_timestamp] を作成します。


use [master]
    if object_id('[awsdms].[rtm_position_1st_timestamp]','P') is not null
    DROP PROCEDURE [awsdms].[rtm_position_1st_timestamp];
    go
    create procedure [awsdms].[rtm_position_1st_timestamp]
    (
    @dbname                sysname,      -- Database name
    @seqno                 integer,      -- Backup set sequence/position number within file
    @filename              varchar(260), -- The backup filename
    @1stTimeStamp          varchar(40)   -- The timestamp to position by
    ) 
    as begin

    SET NOCOUNT ON       -- Disable "rows affected display"

    declare @firstMatching table
    (
    cLsn varchar(32),
    bTim datetime
    )

    declare @sql nvarchar(4000)
    declare @nl                       char(2)
    declare @tb                       char(2)
    declare @fnameVar                 nvarchar(254) = 'NULL'

    set @nl  = char(10); -- New line
    set @tb  = char(9)   -- Tab separator

    if (@filename is not null)
    set @fnameVar = ''''+@filename +''''

    set @sql='use ['+@dbname+'];'+@nl+
    'select top 1 [Current LSN],[Begin Time]'+@nl+
    'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @fnameVar+','+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default)'+@nl+
    'where operation=''LOP_BEGIN_XACT''' +@nl+
    'and [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl

    --print @sql
    delete from  @firstMatching 
    insert into @firstMatching  exec sp_executesql @sql    -- Get them all

    select top 1 cLsn as [matching LSN],convert(varchar,bTim,121) as [matching Timestamp] from @firstMatching;

    SET NOCOUNT OFF      -- Re-enable "rows affected display"

    end
    GO

次のスクリプトを使用して、Master データベースに証明書を作成します。


Use [master]
Go
CREATE CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
ENCRYPTION BY PASSWORD = '@5trongpassword'
WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';

次のスクリプトを使用して、証明書からログインを作成します。


Use [master]
Go
CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert];

次のスクリプトを使用して、sysadmin ロールにログインを追加します。
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];
```

次のスクリプトに従い、証明書を使用して [master].[awsdms].[rtm_position_1st_timestamp] に署名を追加します。


Use [master]
    GO
    ADD SIGNATURE
    TO [master].[awsdms].[rtm_position_1st_timestamp]
    BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
    WITH PASSWORD = '@5trongpassword';

次のスクリプトを使用して、新しいストアドプロシージャへの実行アクセス権を DMS ユーザーに付与します。
```
use master
go
GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;
```
次の各データベースに、次の権限とロールを持つユーザーを作成します。

注記
dmsnosysadmin ユーザーアカウントは、各レプリカで同じ SID を使用して作成する必要があります。次の SQL クエリは、各レプリカの dmsnosysadmin アカウントの SID 値を確認するのに役立ちます。ユーザー作成の詳細については、「Microsoft SQL Server ドキュメント」の「CREATE USER (Transact-SQL)」を参照してください。Azure SQL Database の SQL ユーザーアカウントの作成の詳細については、「アクティブな地理的レプリケーション」を参照してください。
```
use master
go
grant select on sys.fn_dblog to [DMS_user]
grant view any definition to [DMS_user]
grant view server state to [DMS_user]—(should be granted to the login).
grant execute on sp_repldone to [DMS_user]
grant execute on sp_replincrementlsn to [DMS_user]
grant execute on sp_addpublication to [DMS_user]
grant execute on sp_addarticle to [DMS_user]
grant execute on sp_articlefilter to [DMS_user]
grant select on [awsdms].[split_partition_list] to [DMS_user]
grant execute on [awsdms].[rtm_dump_dblog] to [DMS_user]
```
```
use MSDB
go
grant select on msdb.dbo.backupset to [DMS_user]
grant select on msdb.dbo.backupmediafamily to [DMS_user]
grant select on msdb.dbo.backupfile to [DMS_user]
```
ソースデータベースで次のスクリプトを実行します。
```
EXEC sp_addrolemember N'db_owner', N'DMS_user'
use Source_DB
go
```
最後に、追加の接続属性 (ECA) をソースの SQL Server エンドポイントに追加します。
```
enableNonSysadminWrapper=true;
```

可用性グループ環境の SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし

このセクションでは、ユーザーアカウントに sysadmin 権限を必要としない可用性グループ環境で、SQL Server データベースソースの継続的なレプリケーションをセットアップする方法について説明します。

注記

このセクションのステップを実行すると、システム管理者以外の DMS ユーザーには、以下を実行するアクセス許可が付与されます。

オンライントランザクションログファイルからの変更の読み取り
トランザクションログのバックアップファイルから変更を読み取るためのディスクアクセス
DMS が使用するパブリケーションの追加または変更
パブリケーションへの記事の追加

可用性グループ環境で sysadmin ユーザーを使用せずに継続的なレプリケーションをセットアップするには

SQL Server からの継続的なレプリケーションのデータ変更をキャプチャするの説明に従って、レプリケーション向けに Microsoft SQL Server をセットアップします。
ソースデータベースで MS-REPLICATION を有効にします。これは手動で実行することも、sysadmin ユーザーを使用してタスクを 1 回実行することによっても行うことができます。

注記
MS-REPLICATION ディストリビューターをローカルとして設定するか、関連するリンクサーバーを経由して sysadmin 以外のユーザーがアクセスできるように設定する必要があります。
[Exclusively use sp_repldone within a single task] エンドポイントオプションが有効になっている場合は、MS-REPLICATION Log Reader ジョブは停止します。

各レプリカで次のステップを実行します。

master データベースに [awsdms] [awsdms] スキーマを作成します。
```
CREATE SCHEMA [awsdms]
```

Masterデータベースに [awsdms].[split_partition_list] テーブル値関数を作成します。


USE [master]
GO

SET ansi_nulls on
GO
  
SET quoted_identifier on
GO

IF (object_id('[awsdms].[split_partition_list]','TF')) is not null
  DROP FUNCTION [awsdms].[split_partition_list];
GO

CREATE FUNCTION [awsdms].[split_partition_list] 
( 
  @plist varchar(8000),    --A delimited list of partitions    
  @dlm nvarchar(1)    --Delimiting character
) 
RETURNS @partitionsTable table --Table holding the BIGINT values of the string fragments
(
  pid bigint primary key
) 
AS 
BEGIN
  DECLARE @partition_id bigint;
  DECLARE @dlm_pos integer;
  DECLARE @dlm_len integer;  
  SET @dlm_len = len(@dlm);
  WHILE (charindex(@dlm,@plist)>0)
  BEGIN 
    SET @dlm_pos = charindex(@dlm,@plist);
    SET @partition_id = cast( ltrim(rtrim(substring(@plist,1,@dlm_pos-1))) as bigint);
    INSERT into @partitionsTable (pid) values (@partition_id)
    SET @plist = substring(@plist,@dlm_pos+@dlm_len,len(@plist));
  END 
  SET @partition_id = cast (ltrim(rtrim(@plist)) as bigint);
  INSERT into @partitionsTable (pid) values (  @partition_id  );
  RETURN
END
GO

Masterデータベースに [awsdms].[rtm_dump_dblog] プロシージャを作成します。


USE [MASTER] 
GO

IF (object_id('[awsdms].[rtm_dump_dblog]','P')) is not null
  DROP PROCEDURE [awsdms].[rtm_dump_dblog]; 
GO

SET ansi_nulls on
GO 

SET quoted_identifier on 
GO
                                    
CREATE PROCEDURE [awsdms].[rtm_dump_dblog]
(
  @start_lsn            varchar(32),
  @seqno                integer,
  @filename             varchar(260),
  @partition_list       varchar(8000), -- A comma delimited list: P1,P2,... Pn
  @programmed_filtering integer,
  @minPartition         bigint,
  @maxPartition         bigint
) 
AS 
BEGIN

  DECLARE @start_lsn_cmp varchar(32); -- Stands against the GT comparator

  SET NOCOUNT ON  -- Disable "rows affected display"

  SET @start_lsn_cmp = @start_lsn;
  IF (@start_lsn_cmp) is null
    SET @start_lsn_cmp = '00000000:00000000:0000';

  IF (@partition_list is null)
    BEGIN
      RAISERROR ('Null partition list was passed',16,1);
      return
      --set @partition_list = '0,';    -- A dummy which is never matched
    END

  IF (@start_lsn) is not null
    SET @start_lsn = '0x'+@start_lsn;

  IF (@programmed_filtering=0)
    SELECT
      [Current LSN],
      [operation],
      [Context],
      [Transaction ID],
      [Transaction Name],
      [Begin Time],
      [End Time],
      [Flag Bits],
      [PartitionID],
      [Page ID],
      [Slot ID],
      [RowLog Contents 0],
      [Log Record],
      [RowLog Contents 1] -- After Image
    FROM
      fn_dump_dblog (
        @start_lsn, NULL, N'DISK', @seqno, @filename,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default,
        default, default, default, default, default, default, default)
    WHERE 
      [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
      AND
      (
        (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
        OR
        (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
          AND
          ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
          AND       
          [PartitionID] in ( select * from master.awsdms.split_partition_list (@partition_list,','))
        )
      OR
      ([operation] = 'LOP_HOBT_DDL')
    )
    ELSE
      SELECT
        [Current LSN],
        [operation],
        [Context],
        [Transaction ID],
        [Transaction Name],
        [Begin Time],
        [End Time],
        [Flag Bits],
        [PartitionID],
        [Page ID],
        [Slot ID],
        [RowLog Contents 0],
        [Log Record],
        [RowLog Contents 1] -- After Image
      FROM
        fn_dump_dblog (
          @start_lsn, NULL, N'DISK', @seqno, @filename,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default,
          default, default, default, default, default, default, default)
      WHERE [Current LSN] collate SQL_Latin1_General_CP1_CI_AS > @start_lsn_cmp collate SQL_Latin1_General_CP1_CI_AS -- This aims for implementing FN_DBLOG based on GT comparator.
      AND
      (
        (  [operation] in ('LOP_BEGIN_XACT','LOP_COMMIT_XACT','LOP_ABORT_XACT') )
        OR
        (  [operation] in ('LOP_INSERT_ROWS','LOP_DELETE_ROWS','LOP_MODIFY_ROW')
          AND
          ( ( [context]   in ('LCX_HEAP','LCX_CLUSTERED','LCX_MARK_AS_GHOST') ) or ([context] = 'LCX_TEXT_MIX') )
          AND ([PartitionID] is not null) and ([PartitionID] >= @minPartition and [PartitionID]<=@maxPartition)
        )
        OR
        ([operation] = 'LOP_HOBT_DDL')
      )
      SET NOCOUNT OFF -- Re-enable "rows affected display"
END
GO

Master データベースに次のとおり証明書を作成します。


USE [master]
GO
CREATE CERTIFICATE [awsdms_rtm_dump_dblog_cert]
  ENCRYPTION BY PASSWORD = N'@hardpassword1'
  WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions'

CSR から次のとおり証明書を作成します。


USE [master]
GO
CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE
  [awsdms_rtm_dump_dblog_cert];

sysadmin サーバーロールにログイン情報を追加します。
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
```
この証明書を使用して署名を [master].[awsdms].[rtm_dump_dblog] プロシージャに追加します。
```
USE [master]
GO

ADD SIGNATURE
  TO [master].[awsdms].[rtm_dump_dblog]
  BY CERTIFICATE [awsdms_rtm_dump_dblog_cert]
  WITH PASSWORD = '@hardpassword1';
```
注記
ストアドプロシージャを再作成する場合は、署名を再度追加する必要があります。

Masterデータベースに [awsdms].[rtm_position_1st_timestamp] プロシージャを作成します。


USE [master]
IF object_id('[awsdms].[rtm_position_1st_timestamp]','P') is not null
  DROP PROCEDURE [awsdms].[rtm_position_1st_timestamp];
GO
CREATE PROCEDURE [awsdms].[rtm_position_1st_timestamp]
(
  @dbname                sysname,      -- Database name
  @seqno                 integer,      -- Backup set sequence/position number within file
  @filename              varchar(260), -- The backup filename
  @1stTimeStamp          varchar(40)   -- The timestamp to position by
) 
AS 
BEGIN
  SET NOCOUNT ON       -- Disable "rows affected display"

  DECLARE @firstMatching table
  (
    cLsn varchar(32),
    bTim datetime
  )
  DECLARE @sql nvarchar(4000)
  DECLARE @nl                       char(2)
  DECLARE @tb                       char(2)
  DECLARE @fnameVar                 sysname = 'NULL'

  SET @nl  = char(10); -- New line
  SET @tb  = char(9)   -- Tab separator

  IF (@filename is not null)
    SET @fnameVar = ''''+@filename +''''
  SET @filename = ''''+@filename +''''
  SET @sql='use ['+@dbname+'];'+@nl+
    'SELECT TOP 1 [Current LSN],[Begin Time]'+@nl+
    'FROM fn_dump_dblog (NULL, NULL, NULL, '+ cast(@seqno as varchar(10))+','+ @filename +','+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default,'+@nl+
    @tb+'default, default, default, default, default, default, default)'+@nl+
    'WHERE operation=''LOP_BEGIN_XACT''' +@nl+
    'AND [Begin Time]>= cast('+''''+@1stTimeStamp+''''+' as datetime)'+@nl

    --print @sql
    DELETE FROM @firstMatching 
    INSERT INTO @firstMatching  exec sp_executesql @sql    -- Get them all
    SELECT TOP 1 cLsn as [matching LSN],convert(varchar,bTim,121) AS[matching Timestamp] FROM @firstMatching;

    SET NOCOUNT OFF      -- Re-enable "rows affected display"

END
GO

Master データベースに次のとおり証明書を作成します。


USE [master]
GO
CREATE CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
  ENCRYPTION BY PASSWORD = N'@hardpassword1'
  WITH SUBJECT = N'Certificate for FN_POSITION_1st_TIMESTAMP Permissions';

CSR から次のとおり証明書を作成します。


USE [master]
GO
CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE
  [awsdms_rtm_position_1st_timestamp_cert];

sysadmin サーバーロールにログイン情報を追加します。


ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];

この証明書を使用して、署名を [master].[awsdms].[rtm_position_1st_timestamp] プロシージャに追加します。
```
USE [master]
GO
ADD SIGNATURE
  TO [master].[awsdms].[rtm_position_1st_timestamp]
  BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
  WITH PASSWORD = '@hardpassword1';
```
注記
ストアドプロシージャを再作成する場合は、署名を再度追加する必要があります。
次の各データベースに次のアクセス権限またはロールを持つユーザーを作成します。

注記
dmsnosysadmin ユーザーアカウントは、各レプリカで同じ SID を使用して作成する必要があります。次の SQL クエリは、各レプリカの dmsnosysadmin アカウントの SID 値を確認するのに役立ちます。ユーザー作成の詳細については、「Microsoft SQL Server ドキュメント」の「CREATE USER (Transact-SQL)」を参照してください。Azure SQL Database の SQL ユーザーアカウントの作成の詳細については、「アクティブな地理的レプリケーション」を参照してください。
```
SELECT @@servername servername, name, sid, create_date, modify_date
  FROM sys.server_principals
  WHERE name = 'dmsnosysadmin';
```

各レプリカの master データベースに対するアクセス権限を付与します。


USE master
GO 

GRANT select on sys.fn_dblog to dmsnosysadmin;
GRANT view any definition to dmsnosysadmin;
GRANT view server state to dmsnosysadmin -- (should be granted to the login).
GRANT execute on sp_repldone to dmsnosysadmin;
GRANT execute on sp_replincrementlsn to dmsnosysadmin;
GRANT execute on sp_addpublication to dmsnosysadmin;
GRANT execute on sp_addarticle to dmsnosysadmin;
GRANT execute on sp_articlefilter to dmsnosysadmin;
GRANT select on [awsdms].[split_partition_list] to dmsnosysadmin;
GRANT execute on [awsdms].[rtm_dump_dblog] to dmsnosysadmin;
GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dmsnosysadmin;

各レプリカの msdb データベースに対するアクセス権限を付与します。


USE msdb
GO
GRANT select on msdb.dbo.backupset to dmsnosysadmin
GRANT select on msdb.dbo.backupmediafamily to dmsnosysadmin
GRANT select on msdb.dbo.backupfile to dmsnosysadmin

db_owner ロールをソースデータベースの dmsnosysadmin に追加します。データベースは同期しているため、プライマリレプリカにのみロールを追加します。
```
use <source DB>
GO 
EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin' 
```

Cloud SQL Server DB インスタンスでの継続的なレプリケーションのセットアップ

このセクションでは、クラウドでホストされている SQL Server のデータベースインスタンスで CDC をセットアップする方法について説明します。クラウドでホストされる SQL Serverインスタンスは、Amazon RDS for SQL Server、Azure SQL Manged Instance、またはその他のマネージド型 Cloud SQL Server インスタンス上で実行されるインスタンスです。各データベースタイプでの継続的なレプリケーションの制限については、「SQL Server を AWS DMS のソースとして使用する場合の制限」を参照してください。

継続的レプリケーションを設定する前に、SQL Server のソースからの継続的なレプリケーション (CDC) を使用するための前提条件をご参照ください。

セルフ管理 SQL Server ソースとは異なり、Amazon RDS for SQL Server では MS レプリケーションはサポートされません。したがって、AWS DMS はプライマリキーの有無にかかわらずテーブルに MS-CDC を使用する必要があります。

Amazon RDS は、ソース SQL Server インスタンスの継続的変更に AWS DMS が使用するレプリケーションアーティファクトを設定するために、sysadmin 権限を付与しません。次の手順に従って、(管理ユーザーアクセス権限を使用して) Amazon RDS インスタンスの MS-CDC をオンにする必要があります。

Cloud SQL Server DB インスタンスで MS-CDC を有効にするには

次のクエリのいずれかをデータベースレベルで実行します。

RDS for SQL Server DB インスタンスの場合は、次のクエリを使用します。
```
exec msdb.dbo.rds_cdc_enable_db 'DB_name'
```
Azure SQL マネージド DB インスタンスの場合は、次のクエリを使用します。
```
USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO
```

プライマリキーがあるテーブルごとに、次のクエリを実行して MS-CDC を有効にします。


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL,
@supports_net_changes = 1
GO

一意キーはあるがプライマリキーがないテーブルごとに、次のクエリを実行して MS-CDC を有効にします。


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@index_name = N'unique_index_name',
@role_name = NULL,
@supports_net_changes = 1
GO

プライマリキーと一意キーの両方がないテーブルごとに、次のクエリを実行して MS-CDC を有効にします。


exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO

保持期間を設定します。
- DMS バージョン 3.5.3 以降を使用してレプリケートする RDS for SQL Server インスタンスの場合、保持期間がデフォルト値の 5 秒に設定されていることを確認します。DMS 3.5.2 以前から DMS 3.5.3 以降にアップグレードまたは移動する場合、新しいまたはアップグレードされたインスタンスでタスクが実行された後にポーリング間隔の値を変更します。以下のスクリプトでは、保持期間を 5 秒に設定しています。
```
use dbname
EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@pollinginterval = 5
exec sp_cdc_stop_job 'capture'
exec sp_cdc_start_job 'capture'
```
- DMS バージョン 3.5.2 以下を使用してレプリケートする Azure SQL MI および RDS for SQL Server インスタンスの場合は、次のコマンドを使用します。
```
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'
```
  パラメータ @pollinginterval は秒単位で測定され、推奨値 86399 に設定されます。つまり、@pollinginterval = 86399 の場合、トランザクションログは 86,399 秒 (約 1 日) の変更を保持します。手順 exec sp_cdc_start_job 'capture' によって設定が開始されます。
  
  注記
  SQL Server の一部のバージョンでは、pollinginterval が 3599 秒以上に設定されている場合、値はデフォルトの 5 秒にリセットされます。この場合、T-ログエントリは AWS DMS が読み取りする前にパージされます。この既知の問題の影響を受ける SQL Server のバージョンを確認するには、「このマイクロソフト KB 記事」をご参照ください。
  
  マルチ AZ で Amazon RDS を使用している場合は、フェイルオーバー時に適切な値を持つようにセカンダリも設定してください。
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
```

AWS DMS レプリケーションタスクが 1 時間以上停止しても保持期間を維持する

注記

DMS 3.5.3 以降を使用して RDS for SQL Server ソースをレプリケートする場合、以下の手順は必要ありません。

次のコマンドを使用して、トランザクションログを切り捨てるジョブを停止します。
```
exec sp_cdc_stop_job 'capture'
```
AWS DMS コンソールで自分のタスクを見つけ、タスクを再開します。
[Monitoring] (モニタリング) タブを選択し、CDCLatencySource メトリクスを選択します。
CDCLatencySource メトリクスが 0 (ゼロ) に等しく、そのままの場合、次のコマンドを使用して、トランザクションログ切り捨てジョブを再開します。
```
exec sp_cdc_start_job 'capture'
```

必ず SQL Server トランザクションログを切り捨てるジョブをスタートしてください。そうしないと、SQL Server インスタンスのストレージがいっぱいになる可能性があります。

RDS for SQL Server を AWS DMS のソースとして使用する場合の推奨設定

AWS DMS 3.5.3 以降の場合

注記

RDS for SQL Server ログバックアップ機能の初回リリースは、DMS バージョン 3.5.3 のリリース後に作成または変更されたエンドポイントに対して、デフォルトで有効になっています。既存のエンドポイントに対してこの機能を使用するには、変更を加えずにエンドポイントを変更します。

AWS DMS バージョン 3.5.3 では、ログバックアップからの読み取りのサポートが導入されています。DMS は、主にアクティブトランザクションログからの読み取りに依存してイベントをレプリケートします。DMS がアクティブなログから読み取れるようになる前にトランザクションがバックアップされた場合、タスクは RDS バックアップにオンデマンドでアクセスし、アクティブトランザクションログに追いつくまで後続のバックアップログから読み取ります。DMS がログバックアップにアクセスできるようにするには、RDS 自動バックアップ保持期間を少なくとも 1 日に設定します。自動バックアップ保持期間の設定の詳細については、「Amazon RDS ユーザーガイド」の「バックアップの保存期間」を参照してください。

ログバックアップにアクセスする DMS タスクは、RDS インスタンス上のストレージを利用します。タスクは、レプリケーションに必要なログバックアップにのみアクセスすることに注意してください。Amazon RDS は、ダウンロードしたこれらのバックアップを数時間で削除します。この削除は、Amazon S3 に保持されている Amazon RDS バックアップや Amazon RDS RESTORE DATABASE 機能には影響しません。DMS を使用してレプリケートする予定の場合、RDS for SQL Server ソースに追加のストレージを割り当てることをお勧めします。必要なストレージ量を見積もる方法の 1 つは、DMS がレプリケーションを開始または再開するバックアップを特定し、RDS tlog backup メタデータ関数を使用して後続のすべてのバックアップのファイルサイズを合計することです。tlog backup 関数の詳細については、「Amazon RDS ユーザーガイド」の「利用可能なトランザクションログのバックアップの一覧表示」を参照してください。

または、Amazon RDS インスタンスの CloudWatch FreeStorageSpace メトリクスに基づいてストレージの自動スケーリングを有効にしたり、ストレージのスケーリングをトリガーしたりすることができます。

SQL Server インスタンスのストレージがいっぱいになる可能性があるため、トランザクションログのバックアップをあまりに遠すぎる時点から開始または再開しないことを強くお勧めします。このような場合は、フルロードを開始することをお勧めします。トランザクションログバックアップからのレプリケートは、アクティブトランザクションログからの読み取りよりも遅くなります。詳細については、「RDS for SQL Server のトランザクションログのバックアップ処理」を参照してください。

ログバックアップにアクセスするには、追加の権限が必要であることに注意してください。詳細については、「クラウド SQL Server データベースからの継続的なレプリケーションのアクセス許可を設定する」の説明を参照してください。タスクのレプリケートを開始する前に、これらの権限が付与されていることを確認してください。

AWS DMS 3.5.2 以下の場合

Amazon RDS for SQL Server をソースとして使用する場合、MS-CDC キャプチャジョブはパラメータ maxscans と maxtrans に依存しています。このようなパラメータは、MS-CDC キャプチャがトランザクションログに対して実行するスキャンの最大数と、スキャンごとに処理されるトランザクション数を制御します。

データベースでは、トランザクション数が maxtrans*maxscans の場合、polling_interval 値を増やすとアクティブなトランザクションログレコードが蓄積されてしまう可能性があります。これにより、トランザクションログのサイズが増大する可能性があります。

AWS DMS は、MS-CDC キャプチャジョブには依存しないことに注意します。MS-CDC キャプチャジョブは、トランザクションログエントリを処理済みとしてマークします。これにより、トランザクションログのバックアップジョブはトランザクションログからエントリを削除できます。

トランザクションログのサイズと MS-CDC ジョブの正常な実行はモニタリングすることをお勧めします。MS-CDC ジョブが失敗した場合、トランザクションログが過度に増大し、AWS DMS レプリケーションが失敗する可能性があります。MS-CDC キャプチャジョブのエラーは、ソースデータベースの sys.dm_cdc_errors 動的管理ビューを使用してモニタリングできます。トランザクションログのサイズのモニタリングには、DBCC SQLPERF(LOGSPACE) 管理コマンドを使用します。

MS-CDC によるトランザクションログの増加に対処するには

AWS DMS がレプリケートしているデータベースの Log Space Used % を確認して、継続的に増加しているかを検証します。
```
DBCC SQLPERF(LOGSPACE)
```
トランザクションログのバックアッププロセスをブロックしている要因を特定します。
```
Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();
```
log_reuse_wait_desc 値が REPLICATION と等しい場合、ログバックアップの保持は MS-CDC のレイテンシーが原因です。

maxtrans と maxscans パラメータの値を増やして、キャプチャジョブが処理するイベント数を増やします。


EXEC sys.sp_cdc_change_job @job_type = 'capture' ,@maxtrans = 5000, @maxscans = 20 
exec sp_cdc_stop_job 'capture'
exec sp_cdc_start_job 'capture'

この問題に対処するには、maxtrans*maxscans が AWS DMS によりソースデータベースからレプリケートされるテーブルで毎日生成されるイベントの平均数と等しくなるように、maxscans と maxtrans の値を設定します。

このようなパラメータを推奨値よりも高く設定すると、キャプチャジョブはトランザクションログ内のすべてのイベントを処理します。このようなパラメータを推奨値より低く設定すると、MS-CDC のレイテンシーが増加し、トランザクションログが増大します。

ワークロードの変化により生成されるイベントの数が変化するため、maxtrans と maxscansの適切な値を特定することが困難である場合があります。この場合、MS-CDC のレイテンシーのモニタリングを設定することをお勧めします。詳細については、SQL Server ドキュメントの「プロセスを監視する」を参照してください。その後、モニタリング結果に基づいてmaxtrans と maxscans を動的に設定します。

AWS DMS タスクがタスクの再開または続行に必要なログシーケンス番号 (LSN) を見つけられない場合、タスクは失敗して、再度フルロードが必要になる可能性があります。

注記

AWS DMS を使用して RDS for SQL Server ソースからデータをレプリケートする場合、Amazon RDS インスタンスの停止または開始のイベントの後にレプリケーションを再開しようとすると、エラーが発生する可能性があります。これは、SQL Server エージェントプロセスが停止または開始のイベントの後に再起動する際、キャプチャジョブプロセスを再起動するためです。これにより、MS-CDC のポーリング間隔がバイパスされます。

このため、MS-CDC キャプチャジョブの処理よりもトランザクション量が少ないデータベースでは、AWS DMS が停止した時点から再開する前に、データが処理されるか、レプリケートとバックアップとしてマークされ、次のエラーが発生する可能性があります。


[SOURCE_CAPTURE ]E: Failed to access LSN '0000dbd9:0006f9ad:0003' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:764)

この問題を軽減するには、maxtrans と maxscans の値を上記の推奨のとおりに設定します。

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

SQL Server のソースとしての使用

Azure SQL データベースのソースとしての使用

SQL Server からの継続的なレプリケーションのデータ変更をキャプチャする

トピック

オンプレミスまたは Amazon EC2 上のセルフマネージド型 SQL Server のデータ変更のキャプチャ

セルフマネージド型 SQL Server での継続的なレプリケーションのセットアップ

トピック

セルフマネージド型 SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールを使用

ディストリビューションを設定するには

CDC をセットアップするには

スタンドアロン SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし

注記

注記

注記

可用性グループ環境の SQL Server での継続的なレプリケーションのセットアップ: sysadmin ロールなし

注記

可用性グループ環境で sysadmin ユーザーを使用せずに継続的なレプリケーションをセットアップするには

注記

注記

注記

注記

Cloud SQL Server DB インスタンスでの継続的なレプリケーションのセットアップ

Cloud SQL Server DB インスタンスで MS-CDC を有効にするには

注記

AWS DMS レプリケーションタスクが 1 時間以上停止しても保持期間を維持する

注記

RDS for SQL Server を AWS DMS のソースとして使用する場合の推奨設定

AWS DMS 3.5.3 以降の場合

注記

AWS DMS 3.5.2 以下の場合

MS-CDC によるトランザクション ログの増加に対処するには

注記

MS-CDC によるトランザクションログの増加に対処するには