オンプレミスまたは 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 ロールを使用しない継続的なレプリケーションをセットアップします。

注記

次のスクリプトを使用して、プライマリキーまたは一意のキーのないすべてのテーブルを検索できます。


USE [DBname]
SELECT SCHEMA_NAME(schema_id) AS schema_name, name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasPrimaryKey') = 0
        AND  OBJECTPROPERTY(object_id, 'TableHasUniqueCnst') = 0
ORDER BY schema_name, table_name;

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

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

トピック

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

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

AWS DMSSQL Server の継続的なレプリケーションでは、プライマリキーを持つテーブルにはネイティブ 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 の詳細については、「エンドポイント設定」を参照してください。

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 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 self_managed_user
grant select on msdb.dbo.backupmediafamily to self_managed_user
grant select on msdb.dbo.backupfile to self_managed_user

ソースデータベースで次のスクリプトを実行します。


use Source_DB
    Go
    EXEC sp_addrolemember N'db_owner', N'DMS_user'

最後に、追加の接続属性 (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 self_managed_user
GRANT select on msdb.dbo.backupmediafamily TO self_managed_user
GRANT select on msdb.dbo.backupfile TO self_managed_user

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'
```
- パラメータ @pollinginterval は秒単位で測定され、推奨値 86399 に設定されます。つまり、@pollinginterval = 86399 の場合、トランザクションログは 86,399 秒 (約 1 日) の変更を保持します。手順 exec sp_cdc_start_job 'capture' によって設定が開始されます。
  
  注記
  SQL Server の一部のバージョンでは、pollinginterval が 3599 秒以上に設定されている場合、値はデフォルトの 5 秒にリセットされます。この場合、が T ログエントリAWS DMSを読み取る前に、T ログエントリが消去されます。この既知の問題の影響を受ける SQL Server のバージョンを確認するには、「このマイクロソフト KB 記事」をご参照ください。
  
  マルチ AZ で Amazon RDS を使用している場合は、フェイルオーバー時に適切な値を持つようにセカンダリも設定してください。
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or preferred value>
```

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 DMS3.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 DMS3.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 によるトランザクションログの増加に対処するには

データベース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 との値を設定します。 maxtrans maxtrans*maxscansは、ソースデータベースからレAWS DMSプリケートされるテーブルに対して生成されるイベントの平均数と等しくなります。

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

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

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

注記

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 DMS3.5.3 以降

注記

AWS DMS3.5.2 以前

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

注記

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

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