Menangkap perubahan data untuk replikasi berkelanjutan dari SQL Server - AWS Layanan Migrasi Database
Menangkap perubahan untuk SQL Server yang dikelola sendiri di tempat atau di tempat EC2Menyiapkan replikasi yang sedang berlangsung pada instance cloud SQL Server DB

Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.

Menangkap perubahan data untuk replikasi berkelanjutan dari SQL Server

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

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 SQL Server sumber. 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 pada tingkat basis data dan untuk semua tabel secara masing-masing.

Saat menyiapkan basis data SQL Server untuk replikasi yang sedang berlangsung (CDC), Anda dapat melakukan salah satu dari berikut ini.

  • Atur replikasi yang sedang berlangsung menggunakan peran sysadmin.

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

Menyiapkan replikasi yang sedang berlangsung pada SQL Server yang dikelola sendiri

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

Menyiapkan replikasi yang sedang berlangsung pada 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 (CDC) dari sumber SQL Server.

Untuk tabel dengan kunci utama, umumnya AWS DMS dapat mengkonfigurasi artefak yang diperlukan pada sumber. Namun, untuk instans sumber SQL Server yang dikelola sendiri, pastikan untuk terlebih dahulu mengonfigurasi distribusi SQL Server 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

  1. Sambungkan ke basis data sumber SQL Server Anda menggunakan alat SQL Server Management Studio (SSMS).

  2. Buka menu konteks (klik kanan) untuk folder Replikasi, dan pilih Konfigurasi Distribusi. Wizard Konfigurasi Distribusi muncul.

  3. 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 tentang ECAs, 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:

  1. Untuk tabel tanpa kunci primer, atur MS-CDC untuk basis data. Untuk melakukannya, gunakan akun yang memiliki peran sysadmin, dan jalankan perintah berikut.

    use [DBname]
EXEC sys.sp_cdc_enable_db

  2. Selanjutnya, atur MS-CDC untuk setiap tabel sumber. Untuk setiap tabel dengan kunci unik tetapi tanpa kunci primer, jalankan kueri 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

  3. Untuk setiap tabel tanpa bukti 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 lebih lanjut tentang pengaturan MS-CDC untuk tabel tertentu, lihat Dokumentasi SQL Server.

Menyiapkan replikasi yang sedang berlangsung pada 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, pengguna DMS 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 digunakan DMS

  • Tambahkan artikel ke publikasi

  1. Siapkan Microsoft SQL Server untuk Replikasi seperti yang dijelaskan dalam. Menangkap perubahan data untuk replikasi berkelanjutan dari SQL Server

  2. Aktifkan MS-REPLICATION pada database sumber. Ini dapat dilakukan secara manual atau dengan menjalankan tugas sekali sebagai pengguna sysadmin.

  3. 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

  4. 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

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

  6. Buat login dari sertifikat menggunakan skrip berikut: 

    Use [master]
Go

CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];

  7. Tambahkan login ke peran server sysadmin menggunakan skrip berikut:

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

  8. 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.

  9. 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

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

  11. 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];

  12. Tambahkan login ke peran sysadmin menggunakan skrip berikut:

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

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

  14. Berikan pengguna DMS mengeksekusi akses ke prosedur tersimpan baru menggunakan skrip berikut:

    use master
go
GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;

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

    catatan

    Anda harus membuat akun pengguna dmsnosysadmin dengan SID yang sama pada setiap replika. Kueri SQL berikut dapat membantu memverifikasi nilai SID akun dmsnosysadmin pada setiap replika. Untuk informasi selengkapnya tentang membuat pengguna, lihat MEMBUAT PENGGUNA (Transact-SQL) di dokumentasi server Microsoft SQL. Untuk informasi selengkapnya tentang membuat akun pengguna SQL untuk database Azure SQL, 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

  16. 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 mengatur 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, pengguna DMS 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 digunakan DMS

  • Tambahkan artikel ke publikasi

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

  1. Siapkan Microsoft SQL Server untuk Replikasi seperti yang dijelaskan dalam. Menangkap perubahan data untuk replikasi berkelanjutan dari SQL Server

  2. Aktifkan MS-REPLICATION pada database sumber. Ini dapat dilakukan secara manual atau dengan menjalankan tugas sekali menggunakan pengguna sysadmin.

    catatan

    Anda harus mengkonfigurasi distributor MS-REPLICATION sebagai lokal atau dengan cara yang memungkinkan akses ke pengguna non-sysadmin melalui server tertaut terkait.

  3. Jika opsi Exclusive use sp_repldone dalam satu titik akhir tugas diaktifkan, hentikan pekerjaan MS-REPLICATION Log Reader.

  4. Lakukan langkah-langkah berikut pada setiap replika:

    1. Buat skema [awsdms] [awsdms] di database master:

      CREATE SCHEMA [awsdms]

    2. 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

    3. 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

    4. 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'

    5. Buat login dari sertifikat:

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

    6. Tambahkan login ke peran server sysadmin:

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

    7. 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.

    8. 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

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

    10. Buat login dari sertifikat:

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

    11. Tambahkan login ke peran server sysadmin:

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

    12. 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.

    13. Buat pengguna dengan izin/peran berikut di setiap database berikut:

      catatan

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

      SELECT @@servername servername, name, sid, create_date, modify_date
  FROM sys.server_principals
  WHERE name = 'dmsnosysadmin';

    14. 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;

    15. 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

    16. 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 instance cloud SQL Server DB

Bagian ini menjelaskan cara mengatur CDC pada instance database SQL Server yang dihosting cloud. Instance SQL server yang dihosting cloud adalah instance yang berjalan di Amazon RDS for SQL Server, Instans Azure SQL Manged, atau instans SQL Server cloud terkelola lainnya. 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 (CDC) dari sumber SQL Server.

Tidak seperti sumber Microsoft SQL Server yang dikelola sendiri, Amazon RDS for 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 sysadmin untuk menyetel artefak replikasi yang AWS DMS digunakan untuk perubahan yang sedang berlangsung dalam instance SQL Server sumber. Pastikan untuk mengaktifkan MS-CDC untuk instans Amazon RDS (menggunakan hak pengguna master) seperti pada prosedur berikut.

Untuk mengaktifkan MS-CDC untuk instance cloud SQL Server DB

  1. Jalankan salah satu query berikut di tingkat database.

    Untuk contoh RDS untuk SQL Server DB, gunakan kueri ini.

    exec msdb.dbo.rds_cdc_enable_db 'DB_name'

    Untuk instans DB terkelola Azure SQL, gunakan kueri ini.

    USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO

  2. 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 kueri 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

  3. Mengatur periode retensi:

    • Untuk instance RDS untuk SQL Server yang mereplikasi menggunakan DMS versi 3.5.3 dan di atasnya, pastikan periode retensi diatur ke nilai default 5 detik. Jika Anda memutakhirkan atau berpindah 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 pollinginterval diatur ke lebih dari 3599 detik, nilai reset ke default lima detik. 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 RDS untuk sumber SQL Server mereplikasi menggunakan DMS 3.5.3 dan di atasnya.

  1. Hentikan tugas yang memotong log transaksi dengan menggunakan perintah berikut. 

    exec sp_cdc_stop_job 'capture'

  2. Temukan tugas Anda di AWS DMS konsol dan lanjutkan tugas.

  3. Pilih tab Monitoring, dan periksa CDCLatencySource metriknya.

  4. 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'

Jangan lupa untuk memulai tugas yang memotong log transaksi SQL Server. Jika tidak, penyimpanan pada instans SQL Server Anda dapat terisi.

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

Untuk AWS DMS 3.5.3 dan di atas

catatan

Rilis awal fitur cadangan 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. DMS terutama bergantung pada pembacaan dari log transaksi aktif untuk mereplikasi peristiwa. Jika transaksi dicadangkan sebelum DMS dapat membacanya dari log aktif, tugas mengakses cadangan RDS 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 otomatis RDS setidaknya satu hari. Untuk informasi tentang menyetel periode penyimpanan cadangan otomatis, lihat Periode retensi cadangan di Panduan Pengguna Amazon RDS.

Tugas DMS 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 cadangan Amazon RDS yang disimpan di Amazon S3, atau fungsionalitas Amazon RDS. RESTORE DATABASE Dianjurkan untuk mengalokasikan penyimpanan tambahan pada RDS Anda untuk sumber SQL Server 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 Panduan Pengguna Amazon RDS.

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

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 untuk 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 SQL Server cloud Pastikan Anda memberikan hak istimewa ini sebelum tugas mulai mereplikasi.

Untuk AWS DMS 3.5.2 dan di bawah

Saat Anda bekerja dengan Amazon RDS for SQL Server sebagai sumber, pekerjaan pengambilan MS-CDC bergantung pada parameter dan. maxscans maxtrans Parameter ini mengatur jumlah maksimum pemindaian yang dilakukan penangkapan MS-CDC 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 penangkapan MS-CDC. Pekerjaan penangkapan MS-CDC 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 pekerjaan MS-CDC. Jika pekerjaan MS-CDC gagal, log transaksi dapat tumbuh secara berlebihan dan menyebabkan kegagalan replikasi. AWS DMS Anda dapat memantau kesalahan pekerjaan pengambilan MS-CDC 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

  1. Periksa apakah database Log Space Used % AWS DMS mereplikasi dari dan memvalidasi bahwa itu meningkat terus menerus.

    DBCC SQLPERF(LOGSPACE)

  2. 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.

  3. 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, latensi MS-CDC 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 latensi MS-CDC. 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 untuk SQL Server, Anda mungkin mengalami kesalahan saat mencoba melanjutkan replikasi setelah peristiwa stop-start dari instans Amazon RDS. Hal ini disebabkan proses SQL Server Agent memulai kembali proses pengambilan pekerjaan ketika restart setelah peristiwa stop-start. Ini melewati interval pemungutan suara MS-CDC.

Karena itu, pada database dengan volume transaksi lebih rendah dari pemrosesan pekerjaan penangkapan MS-CDC, 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.