オンプレミスまたはのセルフマネージドSQLサーバーの変更のキャプチャ EC2 クラウドSQLサーバー DB インスタンスでの継続的なレプリケーションの設定

SQL サーバーからの継続的なレプリケーションのためのデータ変更のキャプチャ

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

トピック

オンプレミスまたは Amazon でのセルフマネージドSQLサーバーのデータ変更のキャプチャ EC2
クラウドSQLサーバー DB インスタンスでの継続的なレプリケーションの設定

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

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

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

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

継続的なレプリケーション (CDC) 用にSQLサーバーデータベースを設定する場合、次のいずれかを実行できます。

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

セルフマネージドSQLサーバーでの継続的なレプリケーションの設定

このセクションでは、sysadmin ロールの有無にかかわらず、セルフマネージドSQLサーバーで継続的なレプリケーションを設定する方法について説明します。

トピック

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

セルフマネージドSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールの使用

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

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

プライマリキーを持つテーブルの場合、 AWS DMS は通常、ソースで必要なアーティファクトを設定できます。ただし、セルフマネージド型のSQLサーバーソースインスタンスの場合は、まずSQLサーバーディストリビューションを手動で設定してください。これを行うと、sysadmin アクセス許可を持つ AWS DMS ソースユーザーは、プライマリキーを持つテーブルのパブリケーションを自動的に作成できます。

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


sp_get_distributor

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

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

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

CDC をセットアップする

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

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

プライマリキーのないテーブルの場合は、データベースに 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「サーバードキュメント」を参照してください。

スタンドアロンSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールなし

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

注記

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

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

「」の説明に従って、レプリケーション用の Microsoft SQL Server をセットアップしますSQL サーバーからの継続的なレプリケーションのためのデータ変更のキャプチャ。
ソースデータベースで 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サーバードキュメントのCREATEUSER「 (Transact-SQL）」を参照してください。Azure SQL データベースの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

最後に、ソースSQLサーバーエンドポイントに追加の接続属性 (ECA) を追加します。
```
enableNonSysadminWrapper=true;
```

可用性グループ環境のSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールなし

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

注記

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

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

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

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

注記
MS-REPLICATION ディストリビューターをローカルとして設定するか、関連付けられたリンクサーバー経由で sysadmin ユーザー以外のユーザーにアクセスできるようにする方法のいずれかを設定する必要があります。
1 つのタスクエンドポイントオプション内で sp_repldone を排他的に使用する が有効になっている場合は、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サーバードキュメントのCREATEUSER「 (Transact-SQL）」を参照してください。Azure SQL データベースの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' 
```

クラウドSQLサーバー DB インスタンスでの継続的なレプリケーションの設定

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

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

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

Amazon RDSは、がソースSQLサーバーインスタンスで進行中の変更に使用するレプリケーションアーティファクトを設定 AWS DMS するための sysadmin 権限を付与しません。次の手順のように、Amazon RDSインスタンスの MS-CDC を必ずオンにします (マスターユーザー権限を使用）。

クラウドSQLサーバー 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 以降を使用してレプリケートするSQLサーバーインスタンスRDSの場合は、保持期間がデフォルト値の 5 秒に設定されていることを確認します。3DMS.5.2 以下から 3.5.3 DMS 以降にアップグレードまたは移行する場合は、新しいインスタンスまたはアップグレードされたインスタンスでタスクが実行された後にポーリング間隔の値を変更します。次のスクリプトは、保持期間を 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 バージョン 3SQL.5.2 以下を使用してレプリケートする Azure MI およびSQLサーバーインスタンスRDSの場合は、次のコマンドを使用します。
```
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 の一部のバージョンでは、の値が 3599 秒を超えるpollingintervalと、値はデフォルトの 5 秒にリセットされます。この場合、T-Log エントリは AWS DMS が読み取る前に消去されます。この既知の問題によって影響を受けるSQLサーバーバージョンを確認するには、この Microsoft KB の記事「」を参照してください。
  
  マルチ AZ RDSで Amazon を使用している場合は、フェイルオーバー時に適切な値を持つようにセカンダリも設定してください。
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
```

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

注記

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

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

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

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

AWS DMS 3.5.3 以降の場合

注記

SQL サーバーログRDSのバックアップ機能のの初期リリースは、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 RDSRESTORE DATABASEの機能には影響しません。 Amazon S3 を使用してレプリケートする場合は、 RDS for SQL Server ソースに追加のストレージを割り当てることをお勧めしますDMS。必要なストレージ量を見積もる 1 つの方法は、 DMSがレプリケーションを開始または再開するバックアップを特定し、RDStlog backupメタデータ関数を使用して後続のすべてのバックアップのファイルサイズを合計することです。tlog backup 関数の詳細については、「Amazon RDS ユーザーガイド」の「利用可能なトランザクションログのバックアップの一覧表示」を参照してください。

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

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

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

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 レプリケーションが失敗する可能性があります。ソースデータベースのsys.dm_cdc_errors動的管理ビューを使用して、MS CDCキャプチャジョブのエラーをモニタリングできます。トランザクションログのサイズのモニタリングには、DBCC SQLPERF(LOGSPACE) 管理コマンドを使用します。

MS-CDC

データベースLog Space Used %の AWS DMS がからレプリケートされていることを確認し、継続的に増加することを検証します。
```
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'

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

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

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

AWS DMS タスクがタスクの再開または続行に必要なログシーケンス番号 (LSNs) を見つけられない場合、タスクが失敗し、完全な再ロードが必要になることがあります。

注記

AWS DMS を使用して for RDS SQL Server ソースからデータをレプリケートする場合、Amazon RDSインスタンスの停止開始イベント後にレプリケーションを再開しようとすると、エラーが発生することがあります。これは、SQLサーバーエージェントプロセスが、停止開始イベント後に再起動したときにキャプチャジョブプロセスを再起動するためです。これにより、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 サーバーをソースとして使用する

ソースとしての Azure SQL Database の使用

SQL サーバーからの継続的なレプリケーションのためのデータ変更のキャプチャ

トピック

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

セルフマネージドSQLサーバーでの継続的なレプリケーションの設定

トピック

セルフマネージドSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールの使用

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

CDC をセットアップする

スタンドアロンSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールなし

注記

注記

注記

可用性グループ環境のSQLサーバーでの継続的なレプリケーションの設定: sysadmin ロールなし

注記

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

注記

注記

注記

注記

クラウドSQLサーバー DB インスタンスでの継続的なレプリケーションの設定

クラウドSQLサーバー DB インスタンスの MS-CDC を有効にするには

注記

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

注記

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

AWS DMS 3.5.3 以降の場合

注記

AWS DMS 3.5.2 以下の場合

MS-CDC

注記

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