Captura de cambios para SQL Server autoadministrado en las instalaciones o en Amazon EC2 Configuración de la replicación continua en una instancia de base de datos de SQL Server de la nube

Captura de cambios en los datos para la replicación continua desde SQL Server

Este tema describe cómo configurar la replicación de CDC en un origen de SQL Server.

Temas

Captura de cambios de datos para SQL Server autoadministrado en las instalaciones o en Amazon EC2
Configuración de la replicación continua en una instancia de base de datos de SQL Server de la nube

Captura de cambios de datos para SQL Server autoadministrado en las instalaciones o en Amazon EC2

Para capturar los cambios de una base de datos de origen de Microsoft SQL Server, asegúrese de que la base de datos esté configurada para realizar copias de seguridad completas. Configure la base de datos en modo de recuperación total o en modo de registro masivo.

Para un origen autoadministrado de SQL Server, AWS DMS utiliza lo siguiente:

Replicación de MS: Para capturar cambios para las tablas con las claves principales. Puede configurar esta opción automáticamente concediendo privilegios sysadmin al usuario del punto de conexión de AWS DMS en la instancia de origen de SQL Server. También puede seguir los pasos en esta sección para preparar el origen y utilizar un usuario que no tenga privilegios de sysadmin para el punto de conexión de AWS DMS.
MS-CDC: Para capturar cambios para las tablas sin las claves principales. Habilite MS-CDC en el nivel de base de datos y para todas las tablas de forma individual.

Al configurar una base de datos de SQL Server para replicación continua (CDC), puede elegir una de las siguientes opciones:

Configurar la replicación continua con el rol sysadmin.
Configurar la replicación continua para que no se utilice el rol sysadmin.

Configurar la replicación continua en un SQL Server autoadministrado

Esta sección contiene información sobre cómo configurar la replicación continua en un SQL Server autoadministrado con o sin el rol sysadmin.

Temas

Configuración de la replicación continua en un SQL Server autoadministrado: uso del rol sysadmin
Configuración de la replicación continua en un SQL Server independiente: sin el rol de sysadmin
Configuración de la replicación continua en un SQL Server en un entorno de grupos de disponibilidad: sin el rol de sysadmin

Configuración de la replicación continua en un SQL Server autoadministrado: uso del rol sysadmin

La replicación continua de AWS DMS para SQL Server utiliza la replicación nativa de SQL Server para tablas con claves principales y la captura de datos de cambios (CDC) para tablas sin claves principales.

Antes de configurar la replicación continua, consulte Requisitos previos para el uso de la replicación continua (CDC) desde un origen de SQL Server.

Para las tablas con claves principales, AWS DMS puede configurar generalmente los artefactos necesarios en el origen. Sin embargo, para las instancias de origen de SQL Server autoadministradas, asegúrese de configurar primero la distribución de SQL Server de forma manual. Después de hacerlo, los usuarios de origen de AWS DMS con permiso sysadmin pueden crear automáticamente la publicación para tablas con claves principales.

Para comprobar si la distribución ya se ha configurado, ejecute el siguiente comando.


sp_get_distributor

Si el resultado es NULL para la distribución de columnas, la distribución no se ha configurado. Puede usar el siguiente procedimiento para configurar la distribución.

Configuración de la distribución

Conéctese a la base de datos de origen de SQL Server mediante la herramienta SQL Server Management Studio (SSMS).
Abra el menú contextual (haga clic con el botón derecho) para la carpeta Replicación y elija Configurar distribución. Aparecerá el asistente para configurar la distribución.
Siga el asistente para especificar los valores predeterminados y crear la distribución.

Configuración de CDC

La versión 3.4.7 de AWS DMS y las más recientes pueden configurar MS CDC para la base de datos y todas las tablas automáticamente si no utiliza una réplica de solo lectura. Para utilizar esta característica, establezca ECA SetUpMsCdcForTables como verdadero. Para obtener más información sobre ECA, consulte Configuración del punto de conexión.

Para versiones AWS DMS anteriores a la 3.4.7 o para una réplica de solo lectura como origen, lleve a cabo los siguientes pasos:

Para las tablas sin claves principales, configure MS-CDC para la base de datos. Para ello, utilice una cuenta que tenga asignado el rol sysadmin y ejecute el siguiente comando.
```
use [DBname]
EXEC sys.sp_cdc_enable_db
```
A continuación, configure MS-CDC para cada una de las tablas de origen. Para cada tabla con claves únicas pero sin clave principal, ejecute la siguiente consulta para la que desee configurar 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
```
Para cada tabla sin claves principales ni claves únicas, ejecute la siguiente consulta para la que desee configurar MS-CDC.
```
exec sys.sp_cdc_enable_table
@source_schema = N'schema_name',
@source_name = N'table_name',
@role_name = NULL
GO
```

Para obtener más información sobre cómo configurar MS-CDC para tablas específicas, consulte la documentación de SQL Server.

Configuración de la replicación continua en un SQL Server independiente: sin el rol de sysadmin

Esta sección describe cómo configurar la replicación continua para un origen de base de datos de SQL Server independiente que no requiera que la cuenta de usuario tenga privilegios de sysadmin.

nota

Tras ejecutar los pasos de esta sección, el usuario de DMS que no sea administrador de sistemas tendrá permisos para hacer lo siguiente:

Lea los cambios del archivo de registro de transacciones en línea
Acceso al disco para leer los cambios de los archivos de copia de seguridad del registro transaccional
Añadir o modificar la publicación que utiliza DMS
Añadir artículos a la publicación

Configure Microsoft SQL Server para la replicación como se describe en Captura de cambios en los datos para la replicación continua desde SQL Server.
Habilite MS-REPLICATION en la base de datos de origen. Esto se puede hacer manualmente o ejecutando la tarea una vez como usuario sysadmin.

Cree el esquema de awsdms en la base de datos de origen mediante el siguiente script:


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

Cree el procedimiento [awsdms].[rtm_dump_dblog] en la base de datos maestra mediante el siguiente script:


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

Cree el certificado en la base de datos maestra mediante el siguiente script:


Use [master]
Go

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

WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions';

Cree el inicio de sesión del certificado mediante el siguiente script:


Use [master]
Go

CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];

Agregue el inicio de sesión al rol del servidor de sysadmin mediante el siguiente script:
```
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
```
Agregue la firma a [master].[awsdms].[rtm_dump_dblog] con el certificado, mediante el siguiente script:
```
Use [master]
GO
ADD SIGNATURE
TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@5trongpassword';
```
nota
Si vuelve a crear el procedimiento almacenado, tendrá que volver a agregar la firma.

Cree [awsdms]. [rtm_position_1st_timestamp] en la base de datos maestra mediante el siguiente script:


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

Cree el certificado en la base de datos maestra mediante el siguiente script:


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

Cree el inicio de sesión del certificado mediante el siguiente script:


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

Agregue el inicio de sesión al rol de sysadmin mediante el siguiente script:


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

Agregue la firma a [master].[awsdms].[rtm_position_1st_timestamp] con el certificado, mediante el siguiente script:


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

Conceda al usuario de DMS acceso de ejecución al nuevo procedimiento almacenado mediante el siguiente script:
```
use master
go
GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;
```

Cree un usuario con los siguientes permisos y roles en cada una de las siguientes bases de datos:

nota

Debe crear la cuenta de usuario dmsnosysadmin con el mismo SID en cada réplica. La siguiente consulta SQL puede ayudar a comprobar el valor del SID de la cuenta dmsnosysadmin en cada réplica. Para obtener más información sobre la creación de un usuario, consulte CREATE USER (Transact-SQL) en la documentación de Microsoft SQL Server. Para obtener más información sobre la creación de cuentas de usuario de SQL para la base de datos de Azure SQL, consulte Replicación geográfica activa.


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]

Ejecute el siguiente script en la base de datos de origen:


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

Por último, agregue un atributo de conexión adicional (ECA) al punto de conexión de SQL Server de origen:
```
enableNonSysadminWrapper=true;
```

Configuración de la replicación continua en un SQL Server en un entorno de grupos de disponibilidad: sin el rol de sysadmin

En esta sección se describe cómo configurar la replicación continua para un origen de base de datos de SQL Server en un entorno de grupo de disponibilidad que no requiera que la cuenta de usuario tenga privilegios de sysadmin.

nota

Tras ejecutar los pasos de esta sección, el usuario de DMS que no sea administrador de sistemas tendrá permisos para hacer lo siguiente:

Lea los cambios del archivo de registro de transacciones en línea
Acceso al disco para leer los cambios de los archivos de copia de seguridad del registro transaccional
Añadir o modificar la publicación que utiliza DMS
Añadir artículos a la publicación

Configuración de la replicación continua sin usar el usuario sysadmin en un entorno de grupos de disponibilidad

Configure Microsoft SQL Server para la replicación como se describe en Captura de cambios en los datos para la replicación continua desde SQL Server.
Habilite MS-REPLICATION en la base de datos de origen. Esto se puede hacer manualmente o ejecutando la tarea una vez mediante un usuario sysadmin.

nota
Debe configurar el distribuidor de MS-REPLICATION como local o de forma que permita el acceso a los usuarios que no sean sysadmin a través del servidor vinculado asociado.
Si la opción de punto de conexión Usar exclusivamente sp_repldone en una tarea única está habilitada, detenga el trabajo del lector de registros de MS-REPLICATION.

Realice los siguientes pasos en cada réplica:

Cree el esquema [awsdms][awsdms] en la base de datos maestra:
```
CREATE SCHEMA [awsdms]
```

Cree la función con valor de tabla [awsdms].[split_partition_list] en la base de datos maestra:


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

Cree el procedimiento [awsdms].[rtm_dump_dblog] en la base de datos maestra:


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

Cree un certificado en la base de datos maestra:


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

Cree un inicio de sesión desde el certificado:


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

Agregue el inicio de sesión al rol del servidor sysadmin:


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

Agregue la firma al procedimiento [master].[awsdms].[rtm_dump_dblog] con el certificado:
```
USE [master]
GO

ADD SIGNATURE
  TO [master].[awsdms].[rtm_dump_dblog]
  BY CERTIFICATE [awsdms_rtm_dump_dblog_cert]
  WITH PASSWORD = '@hardpassword1';
```
nota
Si vuelve a crear el procedimiento almacenado, tendrá que volver a agregar la firma.

Cree el procedimiento [awsdms].[rtm_position_1st_timestamp] en la base de datos maestra:


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

Cree un certificado en la base de datos maestra:


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

Cree un inicio de sesión desde el certificado:


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

Agregue el inicio de sesión al rol del servidor sysadmin:


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

Agregue la firma al procedimiento [master].[awsdms].[rtm_position_1st_timestamp] mediante el certificado:
```
USE [master]
GO
ADD SIGNATURE
  TO [master].[awsdms].[rtm_position_1st_timestamp]
  BY CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert]
  WITH PASSWORD = '@hardpassword1';
```
nota
Si vuelve a crear el procedimiento almacenado, tendrá que volver a agregar la firma.
Cree un usuario con los siguientes permisos o roles en cada una de las siguientes bases de datos:

nota
Debe crear la cuenta de usuario dmsnosysadmin con el mismo SID en cada réplica. La siguiente consulta SQL puede ayudar a comprobar el valor del SID de la cuenta dmsnosysadmin en cada réplica. Para obtener más información sobre la creación de un usuario, consulte CREATE USER (Transact-SQL) en la documentación de Microsoft SQL Server. Para obtener más información sobre la creación de cuentas de usuario de SQL para la base de datos de Azure SQL, consulte Replicación geográfica activa.
```
SELECT @@servername servername, name, sid, create_date, modify_date
  FROM sys.server_principals
  WHERE name = 'dmsnosysadmin';
```

Conceda permisos en la base de datos maestra en cada réplica:


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;

Conceda permisos en la base de datos msdb en cada réplica:


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

Agregue el rol db_owner a dmsnosysadmin en la base de datos de origen. Como la base de datos está sincronizada, solo puede agregar el rol en la réplica principal.
```
use <source DB>
GO 
EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin' 
```

Configuración de la replicación continua en una instancia de base de datos de SQL Server de la nube

En esta sección se describe cómo configurar CDC en una instancia de base de datos de SQL Server alojada en la nube. Una instancia de SQL Server alojada en la nube es una instancia que se ejecuta en Amazon RDS para SQL Server, una instancia administrada por Azure SQL o cualquier otra instancia de SQL Server administrada en la nube. Para obtener información sobre las limitaciones de la replicación continua para cada tipo de base de datos, consulte Restricciones en el uso de SQL Server como origen para AWS DMS.

Antes de configurar la replicación continua, consulte Requisitos previos para el uso de la replicación continua (CDC) desde un origen de SQL Server.

A diferencia de los orígenes autoadministrados de Microsoft SQL Server, Amazon RDS para SQL Server no es compatible con MS-Replication. Por lo tanto, AWS DMS necesita utilizar MS-CDC para las tablas con claves principales o sin ellas.

Amazon RDS no concede privilegios de sysadmin para configurar los artefactos de replicación que utiliza AWS DMS para los cambios en curso en una instancia de SQL Server de origen. Asegúrese de activar MS-CDC para la instancia de Amazon RDS (mediante privilegios de usuario principal) como se explica en el procedimiento siguiente.

Activación de MS-CDC para una instancia de base de datos de SQL Server de la nube

Ejecute una de las siguientes consultas en el nivel de base de datos.

Para una instancia de base de datos de RDS para SQL Server, utilice esta consulta.
```
exec msdb.dbo.rds_cdc_enable_db 'DB_name'
```
Para una instancia de base de datos administrada por Azure SQL, utilice esta consulta.
```
USE DB_name 
GO 
EXEC sys.sp_cdc_enable_db 
GO
```

Para cada tabla con una clave principal, ejecute la siguiente consulta para la que desee activar 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

Para cada tabla con claves únicas pero sin clave principal, ejecute la siguiente consulta para la que desee activar 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

Para cada tabla sin claves principales ni claves únicas, ejecute la siguiente consulta para la que desee activar MS-CDC.


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

Establezca el periodo de retención:
- En el caso de las instancias de RDS para SQL Server que se replican con DMS versión 3.5.3 o posteriores, asegúrese de que el periodo de retención esté establecido en el valor predeterminado de cinco segundos. Si va a actualizar o a pasar de DMS 3.5.2 y versiones anteriores a DMS 3.5.3 y versiones posteriores, cambie el valor del intervalo de sondeo una vez que las tareas se estén ejecutando en la instancia nueva o actualizada. En el siguiente script se define el periodo de retención en cinco segundos:
```
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'
```
- Para las instancias de Azure SQL MI y RDS para SQL Server que se replican con DMS versión 3.5.2 y posteriores, utilice los siguientes comandos:
```
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'
```
  El parámetro @pollinginterval se mide en segundos con un valor recomendado establecido en 86399. Esto significa que el registro de transacciones retiene los cambios durante 86 399 segundos (un día) cuando @pollinginterval = 86399. El procedimiento exec sp_cdc_start_job 'capture' inicia la configuración.
  
  nota
  En algunas versiones de SQL Server, si el valor de pollinginterval está establecido en más de 3599 segundos, se restablece a los cinco segundos predeterminados. Cuando esto sucede, las entradas de T-Log se purgan antes de que AWS DMS pueda leerlas. Para determinar qué versiones de SQL Server se ven afectadas por este problema conocido, consulte este artículo de Microsoft KB.
  
  Si utiliza Amazon RDS con Multi-AZ, asegúrese de configurar también la secundaria para que tenga los valores correctos en caso de conmutación por error.
```
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
```

Cómo mantener el periodo de retención cuando una tarea de replicación de AWS DMS se detiene durante más de una hora

nota

Los siguientes pasos no son necesarios para un origen de RDS para SQL Server que replique con DMS 3.5.3 y versiones posteriores.

Detenga el trabajo truncando los registros de transacciones mediante el uso del siguiente comando.
```
exec sp_cdc_stop_job 'capture'
```
Busque la tarea en la consola de AWS DMS y reanude la tarea.
Elija la pestaña Monitoreo y compruebe la métrica CDCLatencySource.
Una vez que la métrica CDCLatencySource sea igual a 0 (cero) y permanezca sin cambios, vuelva a iniciar el trabajo truncando los registros de transacción mediante el siguiente comando.
```
exec sp_cdc_start_job 'capture'
```

Recuerde iniciar el trabajo que trunca los registros de transacciones de SQL Server. De lo contrario, es posible que el almacenamiento de la instancia de SQL Server se llene.

Configuración recomendada cuando se utiliza RDS para SQL Server como origen de AWS DMS

Para AWS DMS 3.5.3 y versiones posteriores

nota

La versión inicial de la característica de copia de seguridad de registros de RDS para SQL Server está habilitada de forma predeterminada para los puntos de conexión que haya creado o modificado después del lanzamiento de la versión 3.5.3 de DMS. Para utilizar esta característica con los puntos de conexión existentes, modifique el punto de conexión sin realizar ningún cambio.

AWS DMS versión 3.5.3 agrega compatibilidad para leer de copias de seguridad del registro. DMS se basa principalmente en la lectura de los registros de transacciones activos para replicar eventos. Si se hace una copia de seguridad de una transacción antes de que DMS pueda leerla desde el registro activo, la tarea accede a las copias de seguridad de RDS bajo demanda y lee los registros de copias de seguridad posteriores hasta que se pone al día con el registro de transacciones activo. Para garantizar que DMS tenga acceso a las copias de seguridad del registro, establezca el periodo de retención de las copias de seguridad automatizadas de RDS en al menos un día. Para obtener información sobre cómo configurar el periodo de retención de copias de seguridad automatizadas, consulte Periodo de retención de copia de seguridad en la Guía del usuario de Amazon RDS.

Una tarea de DMS que accede a las copias de seguridad del registro utiliza el almacenamiento en la instancia de RDS. Tenga en cuenta que la tarea solo accede a las copias de seguridad del registro necesarias para la replicación. Amazon RDS elimina estas copias de seguridad descargadas en un par de horas. Esta eliminación no afecta a las copias de seguridad de Amazon RDS retenidas en Amazon S3 ni a la funcionalidad RESTORE DATABASE de Amazon RDS. Se recomienda asignar almacenamiento adicional al origen de RDS para SQL Server si tiene intención de replicar con DMS. Una forma de calcular la cantidad de almacenamiento necesario es identificar la copia de seguridad a partir de la cual DMS iniciará o reanudará la replicación y sumar los tamaños de archivo de todas las copias de seguridad posteriores mediante la función de metadatos tlog backup de RDS. Para obtener más información sobre la función tlog backup, consulte Publicación de las copias de seguridad del registro de transacciones disponibles en la Guía del usuario de Amazon RDS.

Como alternativa, puede optar por habilitar el escalado automático del almacenamiento o desencadenar el escalado del almacenamiento basado en la métrica FreeStorageSpace de CloudWatch para su instancia de Amazon RDS.

Se recomienda encarecidamente que no inicie ni reanude desde un punto demasiado remoto en las copias de seguridad del registro de transacciones, ya que esto puede provocar que se llene el espacio de almacenamiento de la instancia de SQL Server. En estos casos, se recomienda iniciar una carga completa. La replicación desde la copia de seguridad del registro de transacciones es más lenta que leer de los registros de transacciones activos. Para obtener más información, consulte Procesamiento de copias de seguridad del registro de transacciones en RDS para SQL Server.

Tenga en cuenta que el acceso a las copias de seguridad del registro requiere privilegios adicionales. Para obtener más información, consulte los detalles en Configuración de permisos para la replicación continua desde una base de datos de SQL Server en la nube . Asegúrese de conceder estos privilegios antes de que la tarea comience a replicarse.

Para AWS DMS 3.5.2 y versiones anteriores

Cuando trabaja con Amazon RDS para SQL Server como origen, el trabajo de captura de MS-CDC se basa en los parámetros maxscans y maxtrans. Estos parámetros rigen el número máximo de escaneos que la captura de MS-CDC realiza en el registro de transacciones y el número de transacciones que se procesan para cada escaneo.

En el caso de las bases de datos, en las que el número de transacciones es superior a maxtrans*maxscans, el aumento del valor polling_interval puede provocar una acumulación de registros de transacciones activos. A su vez, esta acumulación puede provocar un aumento del tamaño del registro de transacciones.

Tenga en cuenta que AWS DMS no depende del trabajo de captura de MS-CDC. El trabajo de captura de MS-CDC marca las entradas del registro de transacciones como procesadas. Esto permite que el trabajo de copia de seguridad del registro de transacciones elimine las entradas del registro de transacciones.

Le recomendamos que monitoree el tamaño del registro de transacciones y el éxito de los trabajos de MS-CDC. Si los trabajos de MS-CDC producen un error, el registro de transacciones podría crecer excesivamente y provocar errores de replicación de AWS DMS. Puede monitorear los errores de los trabajos de captura de MS-CDC mediante la vista de administración dinámica sys.dm_cdc_errors de la base de datos de origen. Puede monitorear el tamaño del registro de transacciones mediante el comando de gestión DBCC SQLPERF(LOGSPACE).

Solución al aumento del registro de transacciones provocado por MS-CDC

Compruebe Log Space Used % desde la que se está replicando la base de datos de AWS DMS y valide que aumente de forma continua.
```
DBCC SQLPERF(LOGSPACE)
```
Identifique qué es lo que bloquea el proceso de copia de seguridad del registro de transacciones.
```
Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();
```
Si el valor log_reuse_wait_desc es igual a REPLICATION, la retención de la copia de seguridad del registro se debe a la latencia en MS-CDC.

Aumente el número de eventos procesados por el trabajo de captura aumentando los valores de los parámetros maxtrans y 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'

Para solucionar este problema, establezca los valores de maxscans y maxtrans de forma que maxtrans*maxscans sea igual al número medio de eventos generados para las tablas que AWS DMS replica a partir de la base de datos de origen para cada día.

Si establece estos parámetros por encima del valor recomendado, los trabajos de captura procesan todos los eventos de los registros de transacciones. Si establece estos parámetros por debajo del valor recomendado, la latencia de MS-CDC aumenta y el registro de transacciones crece.

Puede resultar difícil identificar los valores adecuados para maxscans y maxtrans, ya que los cambios en la carga de trabajo producen un número variable de eventos. En este caso, le recomendamos que configure el monitoreo de la latencia de MS-CDC. Para obtener más información, consulte Monitorear el proceso en la documentación de SQL Server. A continuación, configure maxtrans y maxscans de forma dinámica en función de los resultados del monitoreo.

Si la tarea de AWS DMS no encuentra los números de secuencia de registro (LSN) necesarios para reanudar o continuar la tarea, es posible que se produzca un error en la tarea y que sea necesario volver a cargarla.

nota

Cuando se utiliza AWS DMS para replicar datos de un origen de RDS para SQL Server, es posible que se produzcan errores al intentar reanudar la replicación tras un evento de parada e inicio de la instancia de Amazon RDS. Esto se debe a que el proceso del agente de SQL Server reinicia el proceso del trabajo de captura cuando se reinicia después del evento de parada e inicio. Esto evita el intervalo de sondeo de MS-CDC.

Por este motivo, en las bases de datos con volúmenes de transacciones inferiores al procesamiento de trabajos de captura de MS-CDC, esto puede provocar que los datos se procesen o se marquen como replicados y se les haga una copia de seguridad antes de que AWS DMS pueda reanudar desde donde se detuvieron, lo que provoca el siguiente error:


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

Para mitigar este problema, defina los valores maxtrans y maxscans tal como se recomendó anteriormente.

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Uso de SQL Server como origen

Uso de la base de datos de Microsoft Azure SQL como origen