Menangkap perubahan untuk SQL Server yang dikelola sendiri di tempat atau di EC2 Menyiapkan replikasi yang sedang berlangsung pada instans DB SQL Server cloud

Menangkap perubahan data untuk replikasi berkelanjutan dari Server SQL

Topik ini menjelaskan cara mengatur CDC replikasi pada sumber SQL Server.

Topik

Menangkap perubahan data untuk SQL Server yang dikelola sendiri di tempat atau di Amazon EC2
Menyiapkan replikasi yang sedang berlangsung pada instans DB SQL Server cloud

Menangkap perubahan data untuk SQL Server yang dikelola sendiri di tempat atau di Amazon EC2

Untuk menangkap perubahan dari database Microsoft SQL Server sumber, pastikan database dikonfigurasi untuk backup penuh. Konfigurasikan basis data dalam modus pemulihan penuh atau mode bulk-logged.

Untuk sumber SQL Server yang dikelola sendiri, AWS DMS gunakan yang berikut ini:

MS-Replication: Untuk menangkap perubahan untuk tabel dengan kunci primer. Anda dapat mengonfigurasi ini secara otomatis dengan memberikan hak istimewa sysadmin kepada pengguna AWS DMS endpoint pada instance Server sumber. SQL Atau Anda dapat mengikuti langkah-langkah di bagian ini untuk menyiapkan sumber dan menggunakan pengguna yang tidak memiliki hak sysadmin untuk titik akhir. AWS DMS
MS- CDC: Untuk menangkap perubahan untuk tabel tanpa kunci primer. Aktifkan MS- CDC di tingkat database dan untuk semua tabel secara individual.

Saat menyiapkan database SQL Server untuk replikasi berkelanjutan (CDC), Anda dapat melakukan salah satu hal berikut:

Atur replikasi yang sedang berlangsung menggunakan peran sysadmin.
Atur replikasi yang sedang berlangsung untuk tidak menggunakan peran sysadmin.

Menyiapkan replikasi yang sedang berlangsung di Server yang dikelola sendiri SQL

Bagian ini berisi informasi tentang pengaturan replikasi yang sedang berlangsung pada SQL server yang dikelola sendiri dengan atau tanpa menggunakan peran sysadmin.

Topik

Menyiapkan replikasi yang sedang berlangsung di SQL Server yang dikelola sendiri: Menggunakan peran sysadmin
Menyiapkan replikasi yang sedang berlangsung di SQL Server mandiri: Tanpa peran sysadmin
Menyiapkan replikasi yang sedang berlangsung di SQL Server di lingkungan grup ketersediaan: Tanpa peran sysadmin

Menyiapkan replikasi yang sedang berlangsung di SQL Server yang dikelola sendiri: Menggunakan peran sysadmin

AWS DMS replikasi berkelanjutan untuk SQL Server menggunakan replikasi SQL Server asli untuk tabel dengan kunci utama, dan mengubah pengambilan data (CDC) untuk tabel tanpa kunci utama.

Sebelum menyiapkan replikasi yang sedang berlangsung, lihat Prasyarat untuk menggunakan replikasi berkelanjutan () dari sumber Server CDC SQL.

Untuk tabel dengan kunci utama, umumnya AWS DMS dapat mengkonfigurasi artefak yang diperlukan pada sumber. Namun, untuk instance sumber SQL Server yang dikelola sendiri, pastikan untuk mengkonfigurasi distribusi SQL Server terlebih dahulu secara manual. Setelah Anda melakukannya, pengguna AWS DMS sumber dengan izin sysadmin dapat secara otomatis membuat publikasi untuk tabel dengan kunci utama.

Untuk memeriksa apakah distribusi sudah dikonfigurasi, jalankan perintah berikut.


sp_get_distributor

Jika hasilnya NULL untuk distribusi kolom, maka distribusi tidak dikonfigurasi. Anda dapat menggunakan prosedur berikut untuk mengatur distribusi.

Mengatur distribusi

Connect ke database sumber SQL Server Anda menggunakan alat SQL Server Management Studio (SSMS).
Buka menu konteks (klik kanan) untuk folder Replikasi, dan pilih Konfigurasi Distribusi. Wizard Konfigurasi Distribusi muncul.
Ikuti wizard untuk memasukkan nilai default dan membuat distribusi.

Untuk mengatur CDC

AWS DMS versi 3.4.7 dan yang lebih besar dapat mengatur MS CDC untuk database Anda dan semua tabel Anda secara otomatis jika Anda tidak menggunakan replika hanya-baca. Untuk menggunakan fitur ini, atur SetUpMsCdcForTables ECA ke true. Untuk informasi tentangECAs, lihatPengaturan titik akhir.

Untuk versi yang AWS DMS lebih awal dari 3.4.7, atau untuk replika hanya-baca sebagai sumber, lakukan langkah-langkah berikut:

Untuk tabel tanpa kunci primer, atur MS- CDC untuk database. Untuk melakukannya, gunakan akun yang memiliki peran sysadmin, dan jalankan perintah berikut.
```
use [DBname]
EXEC sys.sp_cdc_enable_db
```

Selanjutnya, atur MS- CDC untuk masing-masing tabel sumber. Untuk setiap tabel dengan kunci unik tetapi tidak ada kunci utama, jalankan query berikut untuk mengatur 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

Untuk setiap tabel tanpa kunci primer atau tanpa kunci unik, jalankan kueri berikut untuk mengatur MS-CDC.


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

Untuk informasi selengkapnya tentang pengaturan MS- CDC untuk tabel tertentu, lihat dokumentasi SQL Server.

Menyiapkan replikasi yang sedang berlangsung di SQL Server mandiri: Tanpa peran sysadmin

Bagian ini menjelaskan cara mengatur replikasi berkelanjutan untuk sumber database SQL Server mandiri yang tidak memerlukan akun pengguna untuk memiliki hak istimewa sysadmin.

catatan

Setelah menjalankan langkah-langkah di bagian ini, DMS pengguna non-sysadmin akan memiliki izin untuk melakukan hal berikut:

Baca perubahan dari file log transaksi online
Akses disk untuk membaca perubahan dari file cadangan log transaksional
Menambahkan atau mengubah publikasi yang menggunakan DMS
Tambahkan artikel ke publikasi

Siapkan Microsoft SQL Server untuk Replikasi seperti yang dijelaskan dalamMenangkap perubahan data untuk replikasi berkelanjutan dari Server SQL.
Aktifkan MS- REPLICATION pada database sumber. Ini dapat dilakukan secara manual atau dengan menjalankan tugas sekali sebagai pengguna sysadmin.

Buat awsdms skema pada database sumber menggunakan skrip berikut:


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

Buat [awsdms].[rtm_dump_dblog] prosedur pada database Master menggunakan skrip berikut:


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

Buat sertifikat pada database Master menggunakan skrip berikut:


Use [master]
Go

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

WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';

Buat login dari sertifikat menggunakan skrip berikut:


Use [master]
Go

CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];

Tambahkan login ke peran server sysadmin menggunakan skrip berikut:


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

Tambahkan tanda tangan ke [master]. [awsdms]. [rtm_dump_dblog] menggunakan sertifikat, menggunakan skrip berikut:
```
Use [master]
GO
ADD SIGNATURE
TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@5trongpassword';
```
catatan
Jika Anda membuat ulang prosedur yang disimpan, Anda perlu menambahkan tanda tangan lagi.

Buat [awsdms]. [rtm_position_1st_timestamp] pada database Master menggunakan skrip berikut:


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

Buat sertifikat pada database Master menggunakan skrip berikut:


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

Buat login dari sertifikat menggunakan skrip berikut:


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

Tambahkan login ke peran sysadmin menggunakan skrip berikut:


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

Tambahkan tanda tangan ke [master]. [awsdms]. [rtm_position_1st_timestamp] menggunakan sertifikat, menggunakan skrip berikut:


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

Berikan DMS pengguna mengeksekusi akses ke prosedur tersimpan baru menggunakan skrip berikut:
```
use master
go
GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;
```

Buat pengguna dengan izin dan peran berikut di masing-masing database berikut:

catatan

Anda harus membuat akun pengguna dmsnosysadmin dengan yang sama pada setiap replika. SID SQLKueri berikut dapat membantu memverifikasi nilai akun SID dmsnosysadmin pada setiap replika. Untuk informasi selengkapnya tentang membuat pengguna, lihat CREATEUSER(Bertransaksi-SQL) di dokumentasi SQL server Microsoft. Untuk informasi selengkapnya tentang membuat akun SQL pengguna untuk SQL database Azure, lihat Replikasi geo aktif.


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]

Jalankan skrip berikut pada database sumber:


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

Terakhir, tambahkan Extra Connection Attribute (ECA) ke titik akhir SQL Server sumber:
```
enableNonSysadminWrapper=true;
```

Menyiapkan replikasi yang sedang berlangsung di SQL Server di lingkungan grup ketersediaan: Tanpa peran sysadmin

Bagian ini menjelaskan cara menyiapkan replikasi berkelanjutan untuk sumber database SQL Server di lingkungan grup ketersediaan yang tidak memerlukan akun pengguna untuk memiliki hak istimewa sysadmin.

catatan

Setelah menjalankan langkah-langkah di bagian ini, DMS pengguna non-sysadmin akan memiliki izin untuk melakukan hal berikut:

Baca perubahan dari file log transaksi online
Akses disk untuk membaca perubahan dari file cadangan log transaksional
Menambahkan atau mengubah publikasi yang menggunakan DMS
Tambahkan artikel ke publikasi

Untuk mengatur replikasi yang sedang berlangsung tanpa menggunakan pengguna sysadmin di lingkungan Grup Ketersediaan

Siapkan Microsoft SQL Server untuk Replikasi seperti yang dijelaskan dalamMenangkap perubahan data untuk replikasi berkelanjutan dari Server SQL.
Aktifkan MS- REPLICATION pada database sumber. Ini dapat dilakukan secara manual atau dengan menjalankan tugas sekali menggunakan pengguna sysadmin.

catatan
Anda harus mengkonfigurasi MS- REPLICATION distributor sebagai lokal atau dengan cara yang memungkinkan akses ke pengguna non-sysadmin melalui server terkait terkait.
Jika Exclusive use sp_repldone dalam opsi titik akhir tugas tunggal diaktifkan, hentikan pekerjaan MS- Log Reader. REPLICATION

Lakukan langkah-langkah berikut pada setiap replika:

Buat skema [awsdms] [awsdms] di database master:
```
CREATE SCHEMA [awsdms]
```

Buat fungsi [awsdms].[split_partition_list] tabel bernilai pada database Master:


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

Buat [awsdms].[rtm_dump_dblog] prosedur pada database Master:


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

Buat sertifikat pada Master Database:


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

Buat login dari sertifikat:


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

Tambahkan login ke peran server sysadmin:


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

Tambahkan tanda tangan ke [master]. [awsdms]. Prosedur [rtm_dump_dblog] menggunakan sertifikat:
```
USE [master]
GO

ADD SIGNATURE
  TO [master].[awsdms].[rtm_dump_dblog]
  BY CERTIFICATE [awsdms_rtm_dump_dblog_cert]
  WITH PASSWORD = '@hardpassword1';
```
catatan
Jika Anda membuat ulang prosedur yang disimpan, Anda perlu menambahkan tanda tangan lagi.

Buat [awsdms].[rtm_position_1st_timestamp] prosedur pada database Master:


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

Buat sertifikat pada database 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';

Buat login dari sertifikat:


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

Tambahkan login ke peran server sysadmin:


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

Tambahkan tanda tangan ke [master].[awsdms].[rtm_position_1st_timestamp] prosedur menggunakan sertifikat:
```
USE [master]
GO
ADD SIGNATURE
  TO [master].[awsdms].[rtm_position_1st_timestamp]
  BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
  WITH PASSWORD = '@hardpassword1';
```
catatan
Jika Anda membuat ulang prosedur yang disimpan, Anda perlu menambahkan tanda tangan lagi.
Buat pengguna dengan izin/peran berikut di setiap database berikut:

catatan
Anda harus membuat akun pengguna dmsnosysadmin dengan yang sama pada setiap replika. SID SQLKueri berikut dapat membantu memverifikasi nilai akun SID dmsnosysadmin pada setiap replika. Untuk informasi selengkapnya tentang membuat pengguna, lihat CREATEUSER(Bertransaksi-SQL) di dokumentasi SQL server Microsoft. Untuk informasi selengkapnya tentang membuat akun SQL pengguna untuk SQL database Azure, lihat Replikasi geo aktif.
```
SELECT @@servername servername, name, sid, create_date, modify_date
  FROM sys.server_principals
  WHERE name = 'dmsnosysadmin';
```

Berikan izin pada database master pada setiap replika:


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;

Berikan izin pada database msdb pada setiap replika:


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

Tambahkan db_owner peran dmsnosysadmin ke database sumber. Karena database disinkronkan, Anda dapat menambahkan peran pada replika utama saja.
```
use <source DB>
GO 
EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin' 
```

Menyiapkan replikasi yang sedang berlangsung pada instans DB SQL Server cloud

Bagian ini menjelaskan cara menyiapkan instans CDC database SQL Server yang dihosting cloud. Instance SQL server yang dihosting cloud adalah instance yang berjalan di Amazon RDS untuk SQL Server, Instans Azure SQL Manged, atau instance Server cloud terkelola lainnya. SQL Untuk informasi tentang batasan replikasi berkelanjutan untuk setiap jenis database, lihatKeterbatasan dalam menggunakan SQL Server sebagai sumber AWS DMS.

Sebelum menyiapkan replikasi yang sedang berlangsung, lihat Prasyarat untuk menggunakan replikasi berkelanjutan () dari sumber Server CDC SQL.

Tidak seperti sumber Microsoft SQL Server yang dikelola sendiri, Amazon RDS untuk SQL Server tidak mendukung replikasi MS. Oleh karena itu, AWS DMS perlu menggunakan MS- CDC untuk tabel dengan atau tanpa kunci primer.

Amazon RDS tidak memberikan hak istimewa sysadmin untuk menyetel artefak replikasi yang AWS DMS digunakan untuk perubahan yang sedang berlangsung dalam instance Server sumber. SQL Pastikan untuk mengaktifkan MS- CDC untuk RDS instance Amazon (menggunakan hak pengguna master) seperti pada prosedur berikut.

Untuk mengaktifkan MS- CDC untuk instance DB SQL Server cloud

Jalankan salah satu query berikut di tingkat database.

Untuk instance RDS untuk SQL Server DB, gunakan kueri ini.
```
exec msdb.dbo.rds_cdc_enable_db 'DB_name'
```
Untuk instans DB SQL terkelola Azure, gunakan kueri ini.
```
USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO
```

Untuk setiap tabel dengan kunci utama, jalankan query berikut untuk mengaktifkan 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

Untuk setiap tabel dengan kunci unik tetapi tidak ada kunci utama, jalankan query berikut untuk mengaktifkan 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

Untuk setiap tabel tanpa kunci primer atau kunci unik, jalankan kueri berikut untuk mengaktifkan MS-CDC.


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

Mengatur periode retensi:
- RDSUntuk instance SQL Server yang mereplikasi menggunakan DMS versi 3.5.3 ke atas, pastikan periode retensi diatur ke nilai default 5 detik. Jika Anda memutakhirkan atau memindahkan dari DMS 3.5.2 ke bawah ke DMS 3.5.3 ke atas, ubah nilai interval polling setelah tugas berjalan pada instance baru atau yang ditingkatkan. Skrip berikut menetapkan periode retensi ke 5 detik:
```
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'
```
- Untuk Azure SQL MI dan RDS untuk instance SQL Server yang mereplikasi menggunakan DMS versi 3.5.2 dan di bawahnya, gunakan perintah berikut:
```
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'
```
  Parameter@pollinginterval diukur dalam hitungan detik dengan nilai yang disarankan, yang ditetapkan menjadi 86399. Ini berarti bahwa log transaksi mempertahankan perubahan selama 86.399 detik (satu hari) ketika. @pollinginterval = 86399 Prosedur exec sp_cdc_start_job 'capture' memulai pengaturan.
  
  catatan
  Dengan beberapa versi SQL Server, jika nilai diatur ke lebih dari 3599 detik, nilai disetel ulang ke default lima detik. pollinginterval Ketika ini terjadi, entri T-Log dibersihkan sebelum AWS DMS dapat membacanya. Untuk menentukan versi SQL Server mana yang terpengaruh oleh masalah yang diketahui ini, lihat artikel Microsoft KB ini.
  
  Jika Anda menggunakan Amazon RDS dengan Multi-AZ, pastikan Anda juga mengatur sekunder Anda untuk memiliki nilai yang tepat jika terjadi failover.
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
```

Untuk mempertahankan periode retensi ketika tugas AWS DMS replikasi dihentikan selama lebih dari satu jam

catatan

Langkah-langkah berikut tidak diperlukan untuk replikasi sumber RDS for SQL Server menggunakan DMS 3.5.3 dan di atasnya.

Hentikan tugas yang memotong log transaksi dengan menggunakan perintah berikut.
```
exec sp_cdc_stop_job 'capture'
```
Temukan tugas Anda di AWS DMS konsol dan lanjutkan tugas.
Pilih tab Monitoring, dan periksa CDCLatencySource metriknya.
Setelah metrik CDCLatencySource sama dengan 0 (nol) dan tidak berubah, ulang kembali tugas yang memotong log transaksi menggunakan perintah berikut.
```
exec sp_cdc_start_job 'capture'
```

Ingatlah untuk memulai pekerjaan yang memotong log transaksi SQL Server. Jika tidak, penyimpanan pada instance SQL Server Anda mungkin terisi.

Pengaturan yang disarankan saat menggunakan RDS SQL Server sebagai sumber AWS DMS

Untuk AWS DMS 3.5.3 dan di atas

catatan

Rilis awal fitur pencadangan log RDS untuk SQL Server diaktifkan secara default untuk titik akhir yang Anda buat atau modifikasi setelah rilis DMS versi 3.5.3. Untuk menggunakan fitur ini untuk titik akhir yang ada, ubah titik akhir tanpa membuat perubahan apa pun.

AWS DMS versi 3.5.3 memperkenalkan dukungan untuk membaca dari cadangan log. DMSterutama bergantung pada pembacaan dari log transaksi aktif untuk mereplikasi peristiwa. Jika transaksi dicadangkan sebelum DMS dapat membacanya dari log aktif, tugas mengakses RDS cadangan sesuai permintaan dan membaca dari log cadangan berikutnya hingga mencapai log transaksi aktif. Untuk memastikan bahwa DMS memiliki akses ke pencadangan log, atur periode retensi cadangan RDS otomatis menjadi setidaknya satu hari. Untuk informasi tentang menyetel periode penyimpanan cadangan otomatis, lihat Periode retensi cadangan di Panduan RDS Pengguna Amazon.

DMSTugas mengakses cadangan log menggunakan penyimpanan pada instance. RDS Perhatikan bahwa tugas hanya mengakses cadangan log yang diperlukan untuk replikasi. Amazon RDS menghapus cadangan yang diunduh ini dalam beberapa jam. Penghapusan ini tidak memengaruhi RDS cadangan Amazon yang disimpan di Amazon S3, atau fungsionalitas Amazon. RDS RESTORE DATABASE Dianjurkan untuk mengalokasikan penyimpanan tambahan pada sumber SQL Server Anda RDS jika Anda berniat untuk mereplikasi menggunakan. DMS Salah satu cara untuk memperkirakan jumlah penyimpanan yang dibutuhkan adalah dengan mengidentifikasi cadangan dari mana DMS akan memulai atau melanjutkan replikasi dari, dan menambahkan ukuran file dari semua backup berikutnya menggunakan fungsi metadata. RDS tlog backup Untuk informasi selengkapnya tentang tlog backup fungsi ini, lihat Mencantumkan cadangan log transaksi yang tersedia di RDSPanduan Pengguna Amazon.

Sebagai alternatif, Anda dapat memilih untuk mengaktifkan penskalaan otomatis penyimpanan dan/atau memicu penskalaan penyimpanan berdasarkan metrik untuk instans Amazon Anda. CloudWatch FreeStorageSpace RDS

Kami sangat menyarankan agar Anda tidak memulai atau melanjutkan dari titik yang terlalu jauh ke belakang dalam pencadangan log transaksi, karena dapat menyebabkan penyimpanan pada instance SQL Server Anda terisi. Dalam kasus seperti itu, disarankan untuk memulai beban penuh. Mereplikasi dari cadangan log transaksi lebih lambat daripada membaca dari log transaksi aktif. Untuk informasi selengkapnya, lihat Pemrosesan cadangan log transaksi RDS untuk SQL Server.

Perhatikan bahwa mengakses cadangan log memerlukan hak istimewa tambahan. Untuk informasi selengkapnya, lihat seperti yang dijelaskan di Menyiapkan izin untuk replikasi berkelanjutan dari database Server cloud SQL Pastikan Anda memberikan hak istimewa ini sebelum tugas mulai mereplikasi.

Untuk AWS DMS 3.5.2 dan di bawah

Ketika Anda bekerja dengan Amazon RDS untuk SQL Server sebagai sumber, pekerjaan CDC penangkapan MS bergantung pada parameter maxscans danmaxtrans. Parameter ini mengatur jumlah maksimum pemindaian yang dilakukan oleh MS- CDC capture pada log transaksi dan jumlah transaksi yang diproses untuk setiap pemindaian.

Untuk database, di mana sejumlah transaksi lebih besar darimaxtrans*maxscans, meningkatkan polling_interval nilai dapat menyebabkan akumulasi catatan log transaksi aktif. Pada gilirannya, akumulasi ini dapat menyebabkan peningkatan ukuran log transaksi.

Perhatikan bahwa AWS DMS tidak bergantung pada pekerjaan MS- CDC capture. Pekerjaan MS- CDC capture menandai entri log transaksi sebagai telah diproses. Hal ini memungkinkan tugas backup log transaksi untuk menghapus entri dari log transaksi.

Kami menyarankan Anda memantau ukuran log transaksi dan keberhasilan MS- CDC pekerjaan. Jika CDC pekerjaan MS gagal, log transaksi dapat tumbuh secara berlebihan dan menyebabkan kegagalan AWS DMS replikasi. Anda dapat memantau kesalahan pekerjaan MS- CDC capture menggunakan tampilan manajemen sys.dm_cdc_errors dinamis di database sumber. Anda dapat memantau ukuran log transaksi menggunakan perintah DBCC SQLPERF(LOGSPACE) manajemen.

Untuk mengatasi peningkatan log transaksi yang disebabkan oleh MS- CDC

Periksa apakah database Log Space Used % AWS DMS mereplikasi dari dan memvalidasi bahwa itu meningkat terus menerus.
```
DBCC SQLPERF(LOGSPACE)
```
Identifikasi apa yang memblokir proses pencadangan log transaksi.
```
Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();
```
Jika log_reuse_wait_desc nilainya samaREPLICATION, retensi cadangan log disebabkan oleh latensi di MS-. CDC

Tingkatkan jumlah peristiwa yang diproses oleh pekerjaan penangkapan dengan meningkatkan nilai maxtrans dan maxscans parameter.


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'

Untuk mengatasi masalah ini, tetapkan nilai maxscans dan maxtrans sehingga maxtrans*maxscans sama dengan jumlah rata-rata peristiwa yang dihasilkan untuk tabel yang AWS DMS mereplikasi dari database sumber untuk setiap hari.

Jika Anda menetapkan parameter ini lebih tinggi dari nilai yang disarankan, pekerjaan penangkapan akan memproses semua peristiwa di log transaksi. Jika Anda menetapkan parameter ini di bawah nilai yang disarankan, MS- CDC latensi meningkat dan log transaksi Anda bertambah.

Mengidentifikasi nilai yang sesuai untuk maxscans dan maxtrans bisa sulit karena perubahan beban kerja menghasilkan berbagai jumlah peristiwa. Dalam hal ini, kami menyarankan Anda mengatur pemantauan pada MS- CDC latensi. Untuk informasi selengkapnya, lihat Memantau proses dalam dokumentasi SQL Server. Kemudian konfigurasikan maxtrans dan maxscans secara dinamis berdasarkan hasil pemantauan.

Jika AWS DMS tugas tidak dapat menemukan nomor urutan log (LSNs) yang diperlukan untuk melanjutkan atau melanjutkan tugas, tugas mungkin gagal dan memerlukan pemuatan ulang lengkap.

catatan

Saat menggunakan AWS DMS untuk mereplikasi data dari sumber RDS for SQL Server, Anda mungkin mengalami kesalahan saat mencoba melanjutkan replikasi setelah peristiwa stop-start instance Amazon. RDS Hal ini disebabkan proses SQL Server Agent memulai kembali proses pengambilan pekerjaan saat restart setelah peristiwa stop-start. Ini melewati interval MS- CDC polling.

Karena itu, pada database dengan volume transaksi lebih rendah dari pemrosesan pekerjaan MS- CDC capture, ini dapat menyebabkan data diproses atau ditandai sebagai direplikasi dan dicadangkan sebelum AWS DMS dapat dilanjutkan dari tempat berhenti, yang mengakibatkan kesalahan berikut:


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

Untuk mengurangi masalah ini, tetapkan maxscans nilai maxtrans dan seperti yang direkomendasikan sebelumnya.

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

Menggunakan SQL server sebagai sumber

Menggunakan basis data Azure SQL sebagai sumber