Acquisizione delle modifiche ai dati per la replica continua dal server SQL - AWS Servizio di migrazione del Database
Acquisizione delle modifiche per il server autogestito in locale o in SQL EC2Configurazione della replica continua su un'istanza DB di Cloud Server SQL

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

Acquisizione delle modifiche ai dati per la replica continua dal server SQL

Questo argomento descrive come configurare la CDC replica su un'origine SQL Server.

Acquisizione delle modifiche ai dati per SQL Server autogestiti in locale o su Amazon EC2

Per acquisire le modifiche da un database Microsoft SQL Server di origine, assicurati che il database sia configurato per i backup completi. Configura il database in modalità di ripristino completo o in modalità di registrazione in blocco.

Per un'origine SQL Server autogestita, AWS DMS utilizza quanto segue:

Replica MS-REPLICATION

Per acquisire le modifiche di tabelle con chiavi primarie. È possibile configurarlo automaticamente assegnando i privilegi di sysadmin all'utente dell' AWS DMS endpoint sull'istanza del Server di origine. SQL Oppure puoi seguire i passaggi in questa sezione per preparare l'origine e utilizzare un utente che non dispone dei privilegi di sysadmin per l'endpoint. AWS DMS

MS- CDC

Per acquisire le modifiche di tabelle senza chiavi primarie. Abilita MS- CDC a livello di database e per tutte le tabelle singolarmente.

Quando si configura un database SQL Server per la replica continua (CDC), è possibile effettuare una delle seguenti operazioni:

  • Configurare la replica continua utilizzando il ruolo sysadmin.

  • Configurare la replica continua in modo che non utilizzi il ruolo sysadmin.

Configurazione della replica continua su un server autogestito SQL

Questa sezione contiene informazioni sulla configurazione della replica continua su un SQL server autogestito con o senza l'utilizzo del ruolo sysadmin.

Configurazione della replica continua su un server SQL autogestito: utilizzo del ruolo sysadmin

AWS DMS la replica continua per SQL Server utilizza la replica nativa SQL del server per le tabelle con chiavi primarie e Change Data Capture (CDC) per le tabelle senza chiavi primarie.

Prima di configurare la replica continua, consulta Prerequisiti per l'utilizzo della replica in corso () CDC da un'origine Server SQL.

Per le tabelle con chiavi primarie, in genere AWS DMS è possibile configurare gli artefatti richiesti sull'origine. Tuttavia, per le istanze di origine SQL del server gestite automaticamente, assicurati di configurare prima la distribuzione del SQL server manualmente. Dopo averlo fatto, gli utenti di AWS DMS origine con autorizzazione sysadmin possono creare automaticamente la pubblicazione per le tabelle con chiavi primarie.

Per verificare se la distribuzione è già stata configurata, eseguire il comando seguente.

sp_get_distributor

Se il risultato è NULL per la distribuzione delle colonne, la distribuzione non è configurata. Puoi utilizzare la procedura seguente per configurare la distribuzione.

Per configurare la distribuzione

  1. Connect al database di origine del SQL server utilizzando lo strumento SQL Server Management Studio (SSMS).

  2. Apri il menu contestuale (pulsante destro del mouse) della cartella Replica, quindi scegli Configura distribuzione. Viene visualizzata la Configurazione guidata della distribuzione.

  3. Segui la procedura guidata per immettere i valori predefiniti e creare la distribuzione.

Configurazione di CDC

AWS DMS la versione 3.4.7 e successive possono configurare automaticamente MS CDC per il database e tutte le tabelle se non si utilizza una replica di sola lettura. Per utilizzare questa funzionalità, imposta true. SetUpMsCdcForTables ECA Per informazioni suECAs, vedereImpostazioni degli endpoint.

Per le versioni AWS DMS precedenti alla 3.4.7 o per una replica di sola lettura come sorgente, effettuate le seguenti operazioni:

  1. Per le tabelle senza chiavi primarie, imposta MS- per il database. CDC Per farlo, utilizza un account a cui è assegnato il ruolo sysadmin ed esegui il comando seguente.

    use [DBname]
EXEC sys.sp_cdc_enable_db

  2. Quindi, imposta MS- CDC per ciascuna delle tabelle di origine. Per ogni tabella con chiavi univoche ma nessuna chiave primaria, esegui la seguente query per configurare 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. Per ogni tabella senza chiave primaria o senza chiavi univoche, esegui la seguente query per configurare MS-CDC.

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

Per ulteriori informazioni sulla configurazione di MS- CDC per tabelle specifiche, consulta la documentazione del SQL server.

Configurazione della replica continua su un SQL server autonomo: senza il ruolo sysadmin

Questa sezione descrive come configurare la replica continua per un'origine di database SQL Server autonoma che non richiede che l'account utente disponga dei privilegi di amministratore di sistema.

Nota

Dopo aver eseguito i passaggi descritti in questa sezione, l'DMSutente non amministratore di sistema disporrà delle autorizzazioni per eseguire le seguenti operazioni:

  • Leggere le modifiche dal file di log delle transazioni online

  • Accedere al disco per leggere le modifiche dai file di backup del log delle transazioni

  • Aggiungere o modificare la pubblicazione che utilizza DMS

  • Aggiungere articoli alla pubblicazione

  1. Configurare Microsoft SQL Server for Replication come descritto inAcquisizione delle modifiche ai dati per la replica continua dal server SQL.

  2. Attivare MS- REPLICATION nel database di origine. È possibile abilitare la replica manualmente oppure eseguendo l'attività una sola volta come utente sysadmin.

  3. Crea lo schema awsdms nel database di origine utilizzando lo script seguente:

    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. Crea la procedura [awsdms].[rtm_dump_dblog] nel database master utilizzando lo script seguente:

    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. Crea il certificato nel database master utilizzando lo script seguente:

    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. Crea l'accesso del certificato utilizzando lo script seguente: 

    Use [master]
Go

CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];

  7. Aggiungi l'accesso al ruolo del server sysadmin utilizzando lo script seguente:

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

  8. Aggiungi la firma a [master].[awsdms].[rtm_dump_dblog] con il certificato utilizzando lo script seguente: 

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

    Se la stored procedure viene ricreata, è necessario aggiungere nuovamente la firma.

  9. Crea [awsdms].[rtm_position_1st_timestamp] sul database master utilizzando lo script seguente:

    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. Crea il certificato nel database master utilizzando lo script seguente:

    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. Crea l'accesso del certificato utilizzando lo script seguente:

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

  12. Aggiungi l'accesso al ruolo sysadmin utilizzando lo script seguente:

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

  13. Aggiungi la firma a [master].[awsdms].[rtm_position_1st_timestamp] con il certificato utilizzando lo script seguente:

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

  14. Concedi all'DMSutente l'accesso di esecuzione alla nuova stored procedure utilizzando lo script seguente:

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

  15. Crea un utente con le autorizzazioni e i ruoli descritti di seguito in ciascuno dei seguenti database:

    Nota

    È necessario creare l'account utente dmsnosysadmin con lo stesso account su ogni replica. SID La seguente SQL query può aiutare a verificare il valore dell'account dmsnosysadmin su ogni replica. SID Per ulteriori informazioni sulla creazione di un utente, vedere CREATEUSER(Transact-SQL) nella documentazione del SQL server Microsoft. Per altre informazioni sulla creazione di account SQL utente per il SQL database di Azure, vedi Active Geo-Replication.

    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]

    Nel database di origine esegui lo script seguente:

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

  16. Infine, aggiungi un attributo di connessione aggiuntivo (ECA) all'endpoint del server di origine: SQL

    enableNonSysadminWrapper=true;

Configurazione della replica continua su un SQL server in un ambiente di gruppo di disponibilità: senza il ruolo sysadmin

Questa sezione descrive come configurare la replica continua per un'origine del database SQL Server in un ambiente di gruppo di disponibilità che non richiede che l'account utente disponga dei privilegi di amministratore di sistema.

Nota

Dopo aver eseguito i passaggi descritti in questa sezione, l'DMSutente non amministratore di sistema disporrà delle autorizzazioni per eseguire le seguenti operazioni:

  • Leggere le modifiche dal file di log delle transazioni online

  • Accedere al disco per leggere le modifiche dai file di backup del log delle transazioni

  • Aggiungere o modificare la pubblicazione che utilizza DMS

  • Aggiungere articoli alla pubblicazione

Per configurare la replica continua senza utilizzare l'utente sysadmin in un ambiente di gruppo di disponibilità

  1. Configurare Microsoft SQL Server for Replication come descritto inAcquisizione delle modifiche ai dati per la replica continua dal server SQL.

  2. Attivare MS- REPLICATION nel database di origine. È possibile abilitare la replica manualmente oppure eseguendo l'attività una sola volta come utente sysadmin.

    Nota

    È necessario configurare il REPLICATION distributore MS come locale o in modo da consentire l'accesso a utenti non amministratori di sistema tramite il server collegato associato.

  3. Se l'opzione Usa esclusivamente sp_repldone all'interno di un endpoint a singola attività è abilitata, interrompi il job MS-Log Reader. REPLICATION

  4. Per ogni replica completa le seguenti operazioni:

    1. Crea lo schema [awsdms][awsdms] nel database master:

      CREATE SCHEMA [awsdms]

    2. Crea la funzione dei valori di tabella [awsdms].[split_partition_list] nel 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. Crea la procedura [awsdms].[rtm_dump_dblog] nel 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. Crea un certificato nel database 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'

    5. Crea un accesso del certificato:

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

    6. Aggiungi l'accesso al ruolo del server sysadmin:

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

    7. Aggiungi la firma alla procedura [master].[awsdms].[rtm_dump_dblog] con il certificato:

      USE [master]
GO

ADD SIGNATURE
  TO [master].[awsdms].[rtm_dump_dblog]
  BY CERTIFICATE [awsdms_rtm_dump_dblog_cert]
  WITH PASSWORD = '@hardpassword1';
      Nota

      Se la stored procedure viene ricreata, è necessario aggiungere nuovamente la firma.

    8. Crea la procedura [awsdms].[rtm_position_1st_timestamp] nel 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. Crea un certificato nel 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. Crea un accesso del certificato:

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

    11. Aggiungi l'accesso al ruolo del server sysadmin:

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

    12. Aggiungi la firma alla procedura [master].[awsdms].[rtm_position_1st_timestamp] con il certificato:

      USE [master]
GO
ADD SIGNATURE
  TO [master].[awsdms].[rtm_position_1st_timestamp]
  BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
  WITH PASSWORD = '@hardpassword1';
      Nota

      Se la stored procedure viene ricreata, è necessario aggiungere nuovamente la firma.

    13. Crea un utente con le autorizzazioni e i ruoli descritti di seguito in ciascuno dei seguenti database:

      Nota

      È necessario creare l'account utente dmsnosysadmin con lo stesso account su ogni replica. SID La seguente SQL query può aiutare a verificare il valore dell'account dmsnosysadmin su ogni replica. SID Per ulteriori informazioni sulla creazione di un utente, vedere CREATEUSER(Transact-SQL) nella documentazione del SQL server Microsoft. Per altre informazioni sulla creazione di account SQL utente per il SQL database di Azure, vedi Active Geo-Replication.

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

    14. Fornisci le autorizzazioni del database master per ogni replica:

      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. Fornisci le autorizzazioni del database msdb per ogni replica:

      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. Aggiungi il ruolo db_owner a dmsnosysadmin nel database di origine. Dal momento che il database è sincronizzato, è possibile aggiungere il ruolo solo nella replica primaria.

      use <source DB>
GO 
EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin'

Configurazione della replica continua su un'istanza DB di Cloud Server SQL

Questa sezione descrive come eseguire la configurazione CDC su un'istanza di database SQL Server ospitata nel cloud. Un'istanza SQL server ospitata nel cloud è un'istanza in esecuzione su Amazon RDS for SQL Server, un'istanza SQL gestita di Azure o qualsiasi altra istanza di server cloud gestita. SQL Per informazioni sulle limitazioni alla replica continua per ogni tipo di database, consulta Limitazioni all'utilizzo SQL del server come fonte per AWS DMS.

Prima di configurare la replica continua, consulta Prerequisiti per l'utilizzo della replica in corso () CDC da un'origine Server SQL.

A differenza delle sorgenti Microsoft SQL Server autogestite, Amazon RDS for SQL Server non supporta la replica MS-Replica. Pertanto, AWS DMS deve utilizzare MS- CDC per le tabelle con o senza chiavi primarie.

Amazon RDS non concede i privilegi di amministratore di sistema per l'impostazione degli artefatti di replica da utilizzare per le modifiche in corso in un' AWS DMS istanza del server di origine. SQL Assicurati di attivare MS- CDC per l'RDSistanza Amazon (utilizzando i privilegi di utente principale) come nella procedura seguente.

Per attivare MS- CDC per un'istanza DB di Cloud SQL Server

  1. Esegui una delle seguenti query a livello di database.

    Per un'istanza di RDS for SQL Server DB, usa questa query.

    exec msdb.dbo.rds_cdc_enable_db 'DB_name'

    Per un'istanza DB SQL gestita da Azure, usa questa query.

    USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO

  2. Per ogni tabella con una chiave primaria, esegui la seguente query per attivare 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

    Per ogni tabella con chiavi univoche ma nessuna chiave primaria, esegui la seguente query per attivare 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

    Per ogni tabella senza chiave primaria né chiavi univoche, esegui la seguente query per attivare MS-CDC.

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

  3. Imposta il periodo di conservazione:

    • RDSPer le istanze SQL del Server che vengono replicate utilizzando la DMS versione 3.5.3 e successive, assicurati che il periodo di conservazione sia impostato sul valore predefinito di 5 secondi. Se stai effettuando l'aggiornamento o stai passando dalla versione DMS 3.5.2 e precedenti alla versione DMS 3.5.3 e successive, modifica il valore dell'intervallo di polling dopo l'esecuzione delle attività sull'istanza nuova o aggiornata. Lo script seguente imposta il periodo di conservazione su 5 secondi:

      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'

    • Per le istanze di Azure SQL MI e RDS SQL Server che si stanno replicando usando la DMS versione 3.5.2 e precedenti, usa i seguenti comandi:

      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'

      Il parametro @pollinginterval viene misurato in secondi con un valore consigliato impostato su 86399. Ciò significa che il log delle transazioni mantiene le modifiche per 86.399 secondi (un giorno) quando @pollinginterval = 86399. La procedura exec sp_cdc_start_job 'capture' avvia le impostazioni.

      Nota

      Con alcune versioni di SQL Server, se il valore di pollinginterval è impostato su più di 3599 secondi, viene ripristinato il valore predefinito di cinque secondi. Quando ciò accade, le voci T-Log vengono eliminate prima AWS DMS di poterle leggere. Per determinare quali versioni SQL del server sono interessate da questo problema noto, consulta questo articolo della Knowledge Base di Microsoft.

      Se utilizzi Amazon RDS con Multi-AZ, assicurati di impostare anche il dispositivo secondario in modo che abbia i valori corretti in caso di failover.

      exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
Per mantenere il periodo di conservazione quando un'attività di AWS DMS replica viene interrotta per più di un'ora
Nota

I seguenti passaggi non sono necessari per la replica dei sorgenti di RDS for SQL Server utilizzando la versione DMS 3.5.3 e versioni successive.

  1. Arresta il processo di troncamento dei log delle transazioni utilizzando il comando seguente. 

    exec sp_cdc_stop_job 'capture'

  2. Trova l'attività sulla AWS DMS console e riprendila.

  3. Scegli la scheda Monitoraggio e seleziona il parametro CDCLatencySource.

  4. Una volta che il parametro CDCLatencySource è uguale a 0 (zero) e si stabilizza su tale valore, riavvia l'attività che tronca i log delle transazioni utilizzando il seguente comando.

    exec sp_cdc_start_job 'capture'

Ricordati di avviare il processo che tronca i SQL log delle transazioni del server. In caso contrario, lo spazio di archiviazione sull'istanza del SQL Server potrebbe esaurirsi.

Impostazioni consigliate quando si utilizza RDS for SQL Server come fonte per AWS DMS

Per AWS DMS 3.5.3 e versioni successive

Nota

La versione iniziale della funzionalità RDS for SQL Server log backup è abilitata per impostazione predefinita per gli endpoint creati o modificati dopo il rilascio della DMS versione 3.5.3. Per utilizzare questa funzionalità per gli endpoint esistenti, modifica l'endpoint senza apportare modifiche.

AWS DMS la versione 3.5.3 introduce il supporto per la lettura dai backup dei log. DMSsi basa principalmente sulla lettura dei log delle transazioni attivi per replicare gli eventi. Se una transazione è stata sottoposta a backup prima di essere in DMS grado di leggerla dal registro attivo, l'operazione accede ai RDS backup su richiesta e legge i log di backup successivi finché non raggiunge il log delle transazioni attivo. Per garantire che DMS abbia accesso ai backup dei log, imposta il periodo di conservazione dei backup RDS automatici su almeno un giorno. Per informazioni sull'impostazione del periodo di conservazione dei backup automatizzati, consulta Periodo di conservazione dei backup nella Amazon RDS User Guide.

Un'DMSattività di accesso ai backup dei log utilizza lo storage sull'RDSistanza. Tieni presente che l'attività accede solo ai backup dei log necessari per la replica. Amazon RDS rimuove questi backup scaricati in un paio d'ore. Questa rimozione non influisce sui RDS backup Amazon conservati in Amazon S3 o sulle funzionalità di Amazon. RDS RESTORE DATABASE È consigliabile allocare spazio di archiviazione aggiuntivo sulla fonte RDS for SQL Server se si intende effettuare la replica utilizzando. DMS Un modo per stimare la quantità di storage necessaria consiste nell'identificare il backup da cui DMS verrà avviata o ripresa la replica e sommare le dimensioni dei file di tutti i backup successivi utilizzando la funzione dei metadati. RDS tlog backup Per ulteriori informazioni sulla tlog backup funzione, consulta Elencare i backup dei log delle transazioni disponibili nella Amazon RDS User Guide.

In alternativa, puoi scegliere di abilitare la scalabilità automatica dello storage e/o attivare la scalabilità dello storage in base alla metrica CloudWatch FreeStorageSpace per la tua istanza Amazon. RDS

Ti consigliamo vivamente di non avviare o riprendere i backup del log delle transazioni da un punto troppo indietro nel tempo, poiché ciò potrebbe comportare il riempimento dello spazio di archiviazione dell'istanza Server. SQL In questi casi, è consigliabile avviare un caricamento completo. La replica dal backup del registro delle transazioni è più lenta della lettura dai log delle transazioni attivi. Per ulteriori informazioni, consulta Elaborazione del backup del registro delle transazioni per For Server RDS SQL.

Si noti che l'accesso ai backup dei log richiede privilegi aggiuntivi. Per ulteriori informazioni, vedere quanto descritto in dettaglio in Imposta le autorizzazioni per la replica continua da un database del server cloud SQL Assicurarsi di concedere questi privilegi prima che l'attività inizi a replicarsi.

Per la versione AWS DMS 3.5.2 e versioni precedenti

Quando utilizzi Amazon RDS for SQL Server come sorgente, il processo di CDC acquisizione MS si basa sui parametri maxscans emaxtrans. Questi parametri determinano il numero massimo di scansioni eseguite da MS- CDC capture nel registro delle transazioni e il numero di transazioni elaborate per ogni scansione.

Per i database in cui il numero di transazioni è superiore a maxtrans*maxscans, l'aumento del valore di polling_interval può causare un accumulo di record del log delle transazioni attivo. Questo accumulo può, a sua volta, portare a un aumento delle dimensioni del log delle transazioni.

Si noti che AWS DMS non si basa sul processo CDC MS-capture. Il processo CDC MS-capture contrassegna le voci del registro delle transazioni come elaborate. Ciò consente al processo di backup del log delle transazioni di rimuovere le voci dal log delle transazioni.

Si consiglia di monitorare la dimensione del registro delle transazioni e il successo dei processi CDC MS-Jobs. Se i CDC job MS- falliscono, il log delle transazioni potrebbe crescere eccessivamente e causare errori di AWS DMS replica. È possibile monitorare gli errori del job di CDC acquisizione MS utilizzando la visualizzazione di gestione sys.dm_cdc_errors dinamica nel database di origine. È possibile monitorare la dimensione del log delle transazioni utilizzando il comando di gestione DBCC SQLPERF(LOGSPACE).

Per risolvere l'aumento del registro delle transazioni causato da MS- CDC

  1. Verifica da dove AWS DMS viene eseguita Log Space Used % la replica del database e verifica che aumenti continuamente.

    DBCC SQLPERF(LOGSPACE)

  2. Identifica cosa blocca il processo di backup del log delle transazioni.

    Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();

    Se il log_reuse_wait_desc valore è ugualeREPLICATION, la conservazione del backup del registro è causata dalla latenza in MS-. CDC

  3. Aumenta il numero di eventi elaborati dal processo di acquisizione incrementando i valori dei parametri maxtrans e 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'

Per risolvere questo problema, impostate i valori di maxscans e maxtrans in modo che maxtrans*maxscans siano uguali al numero medio di eventi generati per le tabelle AWS DMS replicate dal database di origine per ogni giorno.

Se imposti questi parametri su un valore superiore a quello consigliato, i processi di acquisizione elaborano tutti gli eventi nei log delle transazioni. Se impostate questi parametri al di sotto del valore consigliato, la CDC latenza MS aumenta e il registro delle transazioni cresce.

L'identificazione dei valori appropriati per maxscans e maxtrans può essere difficile perché le modifiche del carico di lavoro producono un numero variabile di eventi. In questo caso, si consiglia di configurare il monitoraggio della CDC latenza MS. Per ulteriori informazioni, consulta Monitorare il processo nella documentazione SQL del server. Quindi configura maxtrans e maxscans in modo dinamico in base ai risultati del monitoraggio.

Se l' AWS DMS attività non riesce a trovare i numeri di sequenza di registro (LSNs) necessari per riprendere o continuare l'attività, l'operazione potrebbe non riuscire e richiedere un ricaricamento completo.

Nota

Quando si utilizza AWS DMS per replicare i dati da un'origine RDS for SQL Server, è possibile che si verifichino errori nel tentativo di riprendere la replica dopo un evento di stop-start dell'istanza Amazon. RDS Ciò è dovuto al fatto che il processo di SQL Server Agent riavvia il processo di acquisizione quando viene riavviato dopo l'evento stop-start. In questo modo viene ignorato l'intervallo di polling di MS. CDC

Per questo motivo, nei database con volumi di transazioni inferiori all'elaborazione del job di MS- CDC Capture, ciò può causare l'elaborazione o la marcatura dei dati come replicati e di backup prima che AWS DMS possano riprendere da dove si era interrotta, con il seguente errore:

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

Per mitigare questo problema, imposta i valori maxtrans e maxscans come consigliato in precedenza.