Seleziona le tue preferenze relative ai cookie

Utilizziamo cookie essenziali e strumenti simili necessari per fornire il nostro sito e i nostri servizi. Utilizziamo i cookie prestazionali per raccogliere statistiche anonime in modo da poter capire come i clienti utilizzano il nostro sito e apportare miglioramenti. I cookie essenziali non possono essere disattivati, ma puoi fare clic su \"Personalizza\" o \"Rifiuta\" per rifiutare i cookie prestazionali.

Se sei d'accordo, AWS e le terze parti approvate utilizzeranno i cookie anche per fornire utili funzionalità del sito, ricordare le tue preferenze e visualizzare contenuti pertinenti, inclusa la pubblicità pertinente. Per continuare senza accettare questi cookie, fai clic su \"Continua\" o \"Rifiuta\". Per effettuare scelte più dettagliate o saperne di più, fai clic su \"Personalizza\".

Preferenze
Contattaci
Feedback
AWS Documentation
Crea un Account AWS

Acquisizione delle modifiche ai dati per la replica continua da SQL Server

PDF
RSS
Modalità Focus
Acquisizione delle modifiche ai dati per la replica continua da SQL Server - AWS Servizio di migrazione del Database
Acquisizione delle modifiche per SQL Server autogestito in locale o in EC2Configurazione della replica continua su un'istanza database di SQL Server nel cloud

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

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

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

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

Per acquisire le modifiche di 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 amministratore di sistema all'utente dell' AWS DMS endpoint sull'istanza di origine di SQL Server. Oppure puoi seguire i passaggi in questa sezione per preparare l'origine e utilizzare un utente che non dispone dei privilegi di amministratore di sistema per l'endpoint. AWS DMS

Acquisizione MS-CDC

Per acquisire le modifiche di tabelle senza chiavi primarie. Abilita l'acquisizione 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 eseguire 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 SQL Server autogestito

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 SQL Server autogestito: utilizzo del ruolo sysadmin

AWS DMS la replica continua per SQL Server utilizza la replica nativa di SQL Server per le tabelle con chiavi primarie e l'acquisizione dei dati delle modifiche (CDC) per le tabelle senza chiavi primarie.

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

Per le tabelle con chiavi primarie, in genere AWS DMS è possibile configurare gli artefatti richiesti sull'origine. Tuttavia, per le istanze di origine SQL Server autogestite, assicurati di configurare prima manualmente la distribuzione di SQL Server. 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. Connettiti al database di origine 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.

Per configurare CDC

AWS DMS la versione 3.4.7 e successive possono configurare MS CDC per il database e tutte le tabelle automaticamente se non si utilizza una replica di sola lettura. Per usare questa funzionalità, imposta l'attributo aggiuntivo di connessione SetUpMsCdcForTables su true. Per informazioni su, vedere. ECAs Impostazioni 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, configura l'acquisizione MS-CDC per il database. 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, configura l'acquisizione MS-CDC per ciascuna delle tabelle di origine. Per ogni tabella con chiavi univoche ma senza una chiave primaria, esegui la seguente query per configurare l'acquisizione 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 una chiave primaria né chiavi univoche, esegui la seguente query per configurare l'acquisizione 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 di SQL Server.

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

In questa sezione viene descritto come configurare la replica continua per un'origine database SQL Server indipendente che non richieda all'account utente di disporre di privilegi sysadmin.

Nota

Dopo aver eseguito i passaggi indicati in questa sezione, l'utente DMS non sysadmin disporrà delle autorizzazioni necessarie per:

  • 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 utilizzata da DMS

  • Aggiungere articoli alla pubblicazione

  1. Configura Microsoft SQL Server per la replica come descritto in Acquisizione delle modifiche ai dati per la replica continua da SQL Server.

  2. Abilita la replica MS-REPLICATION sul 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. Fornisci all'utente DMS l'accesso in 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 SID per ogni replica. La seguente query SQL può verificare il valore SID dell'account dmsnosysadmin per ogni replica. Per ulteriori informazioni sulla creazione di un utente, consulta CREATE USER (Transact-SQL) nella documentazione di Microsoft SQL Server. Per ulteriori informazioni sulla creazione di account utente SQL per il database Azure SQL, consulta 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 aggiuntivo di connessione all'endpoint SQL Server di origine:

    enableNonSysadminWrapper=true;

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

In questa sezione viene descritto come configurare la replica continua per un'origine database SQL Server in un ambiente del gruppo di disponibilità che non richieda all'account utente di disporre di privilegi sysadmin.

Nota

Dopo aver eseguito i passaggi indicati in questa sezione, l'utente DMS non sysadmin disporrà delle autorizzazioni necessarie per:

  • 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 utilizzata da DMS

  • Aggiungere articoli alla pubblicazione

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

  1. Configura Microsoft SQL Server per la replica come descritto in Acquisizione delle modifiche ai dati per la replica continua da SQL Server.

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

    Nota

    È necessario configurare il distributore della replica MS-REPLICATION come locale o in modo da consentire l'accesso a utenti non sysadmin tramite il server collegato e associato.

  3. Se l'opzione di endpoint Usa esclusivamente sp_repldone in un singola attività è abilitata, interrompi il processo di lettura dei log della replica MS-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 SID per ogni replica. La seguente query SQL può verificare il valore SID dell'account dmsnosysadmin per ogni replica. Per ulteriori informazioni sulla creazione di un utente, consulta CREATE USER (Transact-SQL) nella documentazione di Microsoft SQL Server. Per ulteriori informazioni sulla creazione di account utente SQL per il database Azure SQL, consulta 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 database di SQL Server nel cloud

In questa sezione viene descritto come configurare CDC su un'istanza database SQL Server ospitata nel cloud. Un'istanza SQL Server ospitata nel cloud è un'istanza in esecuzione su Amazon RDS per SQL Server, un'istanza gestita da Azure SQL o qualsiasi altra istanza gestita da SQL Server nel cloud. Per informazioni sulle limitazioni alla replica continua per ogni tipo di database, consulta Limitazioni all'utilizzo di SQL Server come origine per AWS DMS.

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

Diversamente dalle origini Microsoft SQL Server autogestite, Amazon RDS per SQL Server non supporta MS-Replication. 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 AWS DMS utilizzare per le modifiche in corso in un'istanza di SQL Server di origine. Assicurati di attivare l'acquisizione MS-CDC per l'istanza Amazon RDS (utilizzando i privilegi di utente master) come indicato nella procedura seguente.

Per attivare l'acquisizione MS-CDC per un'istanza database SQL Server nel cloud

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

    Utilizza questa query per un'istanza database RDS per SQL Server.

    exec msdb.dbo.rds_cdc_enable_db 'DB_name'

    Utilizza questa query per un'istanza database gestita da Azure SQL.

    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 l'acquisizione 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 senza una chiave primaria, esegui la seguente query per attivare l'acquisizione 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 una chiave primaria né chiavi univoche, esegui la seguente query per attivare l'acquisizione 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:

    • Per le istanze di RDS per SQL Server che vengono replicate utilizzando la versione DMS 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 il passaggio da DMS 3.5.2 e versioni precedenti a DMS 3.5.3 e versioni 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 per SQL Server che vengono replicate utilizzando la versione DMS 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

      In alcune versioni di SQL Server, se il valore di pollinginterval è impostato su più di 3599 secondi, viene ripristinato a cinque secondi predefiniti. Quando ciò accade, le voci T-Log vengono eliminate prima di poterle leggere. AWS DMS Per determinare quali versioni di SQL 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 la versione secondaria 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 passaggi seguenti non sono necessari per la replica di un codice sorgente RDS per SQL Server utilizzando 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 log delle transazioni di SQL Server. In caso contrario, lo spazio di storage dell'istanza SQL Server potrebbe esaurirsi.

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

Per AWS DMS 3.5.3 e versioni successive

Nota

La versione iniziale della funzionalità di backup dei log di RDS per SQL Server è abilitata per impostazione predefinita per gli endpoint creati o modificati dopo il rilascio della versione DMS 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. DMS si basa principalmente sulla lettura dei log delle transazioni attivi per replicare gli eventi. Se viene eseguito il backup di una transazione prima che DMS possa leggerla dal registro attivo, l'attività accede ai backup RDS su richiesta e legge i registri di backup successivi fino a quando non raggiunge il log delle transazioni attivo. Per garantire che DMS abbia accesso ai backup dei log, imposta il periodo di conservazione dei backup automatizzati RDS 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'attività DMS che accede ai backup dei log utilizza lo storage sull'istanza RDS. Si noti 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 backup di Amazon RDS conservati in Amazon S3 o sulla funzionalità di Amazon RDS. RESTORE DATABASE È consigliabile allocare spazio di archiviazione aggiuntivo sulla sorgente 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 inizierà o riprenderà la replica e sommare le dimensioni dei file di tutti i backup successivi utilizzando la funzione di metadati RDS. tlog backup Per ulteriori informazioni sulla tlog backup funzione, consulta Elenco dei 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 al parametro CloudWatch FreeStorageSpace per la tua istanza Amazon RDS.

Ti consigliamo vivamente di non avviare o riprendere i backup del registro delle transazioni da un punto troppo indietro nel tempo, poiché ciò potrebbe comportare il riempimento dello spazio di archiviazione sull'istanza di SQL Server. 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 RDS per SQL Server.

Si noti che l'accesso ai backup dei log richiede privilegi aggiuntivi. Per ulteriori informazioni, vedere quanto descritto in dettaglio in Configura le autorizzazioni per la replica continua da un database SQL Server cloud 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 acquisizione MS-CDC si basa sui parametri e. maxscans maxtrans Questi parametri determinano il numero massimo di scansioni eseguite dall'acquisizione MS-CDC 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 di acquisizione MS-CDC. Il processo di acquisizione MS-CDC contrassegna le voci del log 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 log delle transazioni e l'esito dei processi di acquisizione MS-CDC. Se i job MS-CDC falliscono, il log delle transazioni potrebbe crescere eccessivamente e causare errori di replica. AWS DMS È possibile monitorare gli errori del processo di acquisizione MS-CDC utilizzando la vista di gestione dinamica sys.dm_cdc_errors nel database di origine. È possibile monitorare la dimensione del log delle transazioni utilizzando il comando di gestione DBCC SQLPERF(LOGSPACE).

Per risolvere l'aumento delle dimensioni del log delle transazioni causato dall'acquisizione MS-CDC

  1. Verificate la provenienza Log Space Used % da cui AWS DMS viene eseguita la replica del database e verificate 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 valore log_reuse_wait_desc è uguale a REPLICATION, la conservazione del backup del log è causata dalla latenza dell'acquisizione 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, imposta 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 si impostano questi parametri su un valore inferiore a quello consigliato, la latenza dell'acquisizione MS-CDC aumenta e il log delle transazioni incrementa le dimensioni.

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, consigliamo di configurare il monitoraggio della latenza dell'acquisizione MS-CDC. Per ulteriori informazioni, consulta Monitorare il processo nella documentazione di SQL 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 per 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 di arresto e avvio. Questo processo ignora l'intervallo di polling dell'acquisizione MS-CDC.

Per questo motivo, nei database con volumi di transazioni inferiori all'elaborazione del processo di acquisizione MS-CDC, 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.

In questa pagina

PrivacyCondizioni del sitoPreferenze cookie
© 2025, Amazon Web Services, Inc. o società affiliate. Tutti i diritti riservati.