As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.
Capturar alterações de dados para replicação contínua no SQL Server
Este tópico descreve como configurar a replicação de CDC em uma origem do SQL Server.
Tópicos
Capturar dados alterados no SQL Server autogerenciado on-premises ou no Amazon EC2
Para capturar as alterações de um banco de dados de origem do Microsoft SQL Server, verifique se o banco de dados está configurado para backups completos. Configure o banco de dados no modo de recuperação total ou no modo de registro em log em massa.
Para uma origem SQL Server autogerenciado, o AWS DMS utiliza o seguinte:
- Replicação de MS
-
Para capturar alterações em tabelas com chaves primárias. É possível configurar isso automaticamente fornecendo privilégios de sysadmin ao usuário do endpoint do AWS DMS na instância do SQL Server de origem. Como alternativa, é possível seguir as etapas fornecidas nesta seção para preparar a origem e utilizar um usuário sem privilégios de sysadmin no endpoint do AWS DMS.
- MS-CDC
-
Para capturar alterações em tabelas sem chaves primárias. Ative a MS-CDC no nível do banco de dados e em todas as tabelas individualmente.
Ao configurar um banco de dados SQL Server para replicação contínua (CDC), é possível seguir um destes procedimentos:
-
Configurar a replicação contínua utilizando o perfil sysadmin.
-
Configurar a replicação contínua para não utilizar o perfil sysadmin.
Configurar a replicação contínua em um SQL Server autogerenciado
Esta seção contém informações sobre como configurar a replicação contínua em um servidor SQL autogerenciado com ou sem a utilização do perfil sysadmin.
Tópicos
Configurar a replicação contínua em um SQL Server autogerenciado: utilizando o perfil sysadmin
A replicação contínua do AWS DMS para SQL Server utiliza a replicação do SQL Server nativo para tabelas com chaves primárias e a captura de dados de alteração (CDC) para tabelas sem chaves primárias.
Antes de configurar a replicação contínua, consulte Utilizar replicação contínua (CDC) a partir de uma origem do SQL Server.
Para tabelas com chaves primárias, o AWS DMS pode configurar os artefatos necessários na origem. No entanto, para instâncias de origem do SQL Server que são autogerenciadas, configure primeiro a distribuição do SQL Server manualmente. Depois disso, os usuários de origem do AWS DMS com permissão de sysadmin podem criar automaticamente a publicação das tabelas com chaves primárias.
Para verificar se a distribuição já foi configurada, execute o comando a seguir.
sp_get_distributor
Se o resultado for NULL
para a distribuição de colunas, a distribuição não estará configurada. É possível utilizar o procedimento a seguir para configurar a distribuição.
Como configurar a distribuição
-
Conecte-se ao banco de dados de origem do SQL Server utilizando a ferramenta SQL Server Management Studio (SSMS).
-
Abra o menu de contexto (clique com o botão direito do mouse) da pasta Replicação e escolha Configurar distribuição. O assistente de configuração da distribuição é exibido.
-
Siga o assistente para inserir os valores padrão e criar a distribuição.
Como configurar a CDC
O AWS DMS versão 3.4.7 e superior pode configurar o MS CDC para o banco de dados e todas as tabelas automaticamente se você não estiver utilizando uma réplica somente para leitura. Para utilizar esse recurso, defina o ECA SetUpMsCdcForTables
como verdadeiro. Para obter mais informações sobre ECAs, consulte Configurações de endpoint.
Para as versões do AWS DMS anteriores a 3.4.7 ou para uma réplica somente leitura como origem, execute as seguintes etapas:
Para tabelas sem chaves primárias, configure a MS-CDC para o banco de dados. Para fazer isso, utilize uma conta que tenha o perfil sysadmin atribuído a ela e execute o comando a seguir.
use [DBname] EXEC sys.sp_cdc_enable_db
Configure a MS-CDC para cada uma das tabelas de origem. Para cada tabela com chaves exclusivas, mas sem chave primária, execute a consulta a seguir para configurar a 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 tabela sem chave primária nem chaves exclusivas, execute a consulta a seguir para configurar a MS-CDC.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL GO
Para obter mais informações sobre como configurar a MS-CDC para tabelas específicas, consulte a Documentação do SQL Server
Configurar a replicação contínua em um SQL Server autônomo: sem o perfil sysadmin
Esta seção descreve como configurar a replicação contínua para uma origem de banco de dados SQL Server autônomo que não exige que a conta do usuário tenha privilégios de sysadmin.
nota
Depois de executar as etapas desta seção, o usuário do DMS que não for administrador de sistema terá permissões para fazer o seguinte:
Ler as alterações do arquivo de log de transações on-line.
Acessar o disco para ler as alterações dos arquivos de backup do log de transações.
Adicionar ou alterar a publicação que o DMS usa.
Adicionar artigos à publicação.
Configure o Microsoft SQL Server para replicação conforme descrito em Capturar alterações de dados para replicação contínua no SQL Server.
Ative MS-REPLICATION no banco de dados de origem. Isso pode ser feito manualmente ou executando a tarefa uma vez como usuário sysadmin.
Crie o esquema
awsdms
no banco de dados de origem utilizando o seguinte 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
Crie o procedimento
[awsdms].[rtm_dump_dblog]
no banco de dados mestre utilizando o seguinte 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
Crie o certificado no banco de dados mestre utilizando o seguinte 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';
Crie o login no certificado utilizando o seguinte script:
Use [master] Go CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];
Adicione o login ao perfil do servidor sysadmin utilizando o seguinte script:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
Adicione a assinatura ao [master].[awsdms].[rtm_dump_dblog] utilizando o certificado e o seguinte script:
Use [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@5trongpassword';
nota
Se você recriar o procedimento armazenado, será necessário adicionar a assinatura novamente.
Crie o [awsdms].[rtm_position_1st_timestamp] no banco de dados principal usando o seguinte 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
Crie o certificado no banco de dados mestre utilizando o seguinte 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';
Crie o login no certificado utilizando o seguinte script:
Use [master] Go CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert];
Adicione o login ao perfil sysadmin utilizando o seguinte script:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];
Adicione a assinatura ao [master].[awsdms].[rtm_position_1st_timestamp] utilizando o certificado e o seguinte 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 ao usuário do DMS acesso de execução ao novo procedimento armazenado usando o seguinte script:
use master go GRANT execute on [awsdms].[rtm_position_1st_timestamp] to dms_user;
Crie um usuário com as seguintes permissões e perfis em cada um dos seguintes bancos de dados:
nota
Crie a conta de usuário dmsnosysadmin com o mesmo SID em cada réplica. A consulta SQL a seguir pode ajudar a verificar o valor do SID da conta dmsnosysadmin em cada réplica. Para obter mais informações sobre como criar um usuário, consulte CREATE USER (Transact-SQL)
na Documentação do Microsoft SQL Server . Para obter mais informações sobre a criação de contas de usuário do SQL para o banco de dados SQL do Azure, consulte Replicação geográfica ativa . 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]
Execute o seguinte script no banco de dados de origem:
EXEC sp_addrolemember N'db_owner', N'DMS_user' use Source_DB go
Por fim, adicione um atributo de conexão extra (ECA) ao endpoint do SQL Server de origem:
enableNonSysadminWrapper=true;
Configurar a replicação contínua em um SQL Server em um ambiente de grupo de disponibilidade: sem o perfil sysadmin
Esta seção descreve como configurar a replicação contínua para uma origem de banco de dados SQL Server em um ambiente de grupo de disponibilidade que não exige que a conta do usuário tenha privilégios de sysadmin.
nota
Depois de executar as etapas desta seção, o usuário do DMS que não for administrador de sistema terá permissões para fazer o seguinte:
Ler as alterações do arquivo de log de transações on-line.
Acessar o disco para ler as alterações dos arquivos de backup do log de transações.
Adicionar ou alterar a publicação que o DMS usa.
Adicionar artigos à publicação.
Como configurar a replicação contínua sem utilizar o usuário sysadmin em um ambiente de grupo de disponibilidade
Configure o Microsoft SQL Server para replicação conforme descrito em Capturar alterações de dados para replicação contínua no SQL Server.
Ative MS-REPLICATION no banco de dados de origem. Isso pode ser feito manualmente ou executando a tarefa uma vez utilizando um usuário sysadmin.
nota
Configure o distribuidor MS-REPLICATION como local ou de uma forma que permita acesso a usuários que não sejam administradores de sistema por meio do servidor vinculado associado.
Se a opção do endpoint Usar exclusivamente sp_repldone em uma única tarefa estiver ativada, interrompa o trabalho do MS-REPLICATION Log Reader.
Execute as seguintes etapas em cada réplica:
Crie o esquema
[awsdms]
[awsdms] no banco de dados mestre:CREATE SCHEMA [awsdms]
Crie o perfil com valor de tabela
[awsdms].[split_partition_list]
no banco de dados mestre: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
Crie o procedimento
[awsdms].[rtm_dump_dblog]
no banco de dados mestre: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
Crie um certificado no banco de dados mestre:
USE [master] GO CREATE CERTIFICATE [awsdms_rtm_dump_dblog_cert] ENCRYPTION BY PASSWORD = N'@hardpassword1' WITH SUBJECT = N'Certificate for FN_DUMP_DBLOG Permissions'
Crie um login no certificado:
USE [master] GO CREATE LOGIN awsdms_rtm_dump_dblog_login FROM CERTIFICATE [awsdms_rtm_dump_dblog_cert];
Adicione o login ao perfil do servidor sysadmin:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_dump_dblog_login];
-
Adicione a assinatura ao procedimento [master].[awsdms].[rtm_dump_dblog] utilizando o certificado:
USE [master] GO ADD SIGNATURE TO [master].[awsdms].[rtm_dump_dblog] BY CERTIFICATE [awsdms_rtm_dump_dblog_cert] WITH PASSWORD = '@hardpassword1';
nota
Se você recriar o procedimento armazenado, será necessário adicionar a assinatura novamente.
Crie o procedimento
[awsdms].[rtm_position_1st_timestamp]
no banco de dados mestre: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
Crie um certificado no banco de dados mestre:
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';
Crie um login no certificado:
USE [master] GO CREATE LOGIN awsdms_rtm_position_1st_timestamp_login FROM CERTIFICATE [awsdms_rtm_position_1st_timestamp_cert];
Adicione o login ao perfil do servidor sysadmin:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awsdms_rtm_position_1st_timestamp_login];
Adicione a assinatura ao procedimento
[master].[awsdms].[rtm_position_1st_timestamp]
utilizando o 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
Se você recriar o procedimento armazenado, será necessário adicionar a assinatura novamente.
Crie um usuário com as seguintes permissões/perfis em cada um dos seguintes bancos de dados:
nota
Crie a conta de usuário dmsnosysadmin com o mesmo SID em cada réplica. A consulta SQL a seguir pode ajudar a verificar o valor do SID da conta dmsnosysadmin em cada réplica. Para obter mais informações sobre como criar um usuário, consulte CREATE USER (Transact-SQL)
na Documentação do Microsoft SQL Server . Para obter mais informações sobre a criação de contas de usuário do SQL para o banco de dados SQL do Azure, consulte Replicação geográfica ativa . SELECT @@servername servername, name, sid, create_date, modify_date FROM sys.server_principals WHERE name = 'dmsnosysadmin';
Conceda permissões no banco de dados mestre em 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 permissões no banco de dados msdb em 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
Adicione o perfil
db_owner
aodmsnosysadmin
no banco de dados de origem. Como o banco de dados é sincronizado, é possível adicionar o perfil somente na réplica primária.use <source DB> GO EXEC sp_addrolemember N'db_owner', N'dmsnosysadmin'
Configurar a replicação contínua em uma instância de banco de dados SQL Server na nuvem
Esta seção descreve como configurar a CDC em uma instância de banco de dados SQL Server hospedada na nuvem. Uma instância do SQL Server hospedada na nuvem é uma instância em execução no Amazon RDS para SQL Server, uma instância gerenciada do Azure SQL ou qualquer outra instância gerenciada do SQL Server na nuvem. Para obter informações sobre as limitações da replicação contínua para cada tipo de banco de dados, consulte Limitações de uso do SQL Server como origem do AWS DMS.
Antes de configurar a replicação contínua, consulte Utilizar replicação contínua (CDC) a partir de uma origem do SQL Server.
Ao contrário das origens autogerenciadas do SQL Server, o Amazon RDS para SQL Server não é compatível com a MS-Replication. Portanto, o AWS DMS precisa utilizar a MS-CDC para tabelas com ou sem chaves primárias.
O Amazon RDS não concede privilégios de sysadmin para definir os artefatos de replicação que o AWS DMS utiliza para alterações contínuas em uma instância do SQL Server de origem. Ative a MS-CDC na instância do Amazon RDS (utilizando privilégios de usuário mestre) como no procedimento a seguir.
Para ativar a MS-CDC em uma instância de banco de dados do SQL Server na nuvem
-
Execute as consultas a seguir no nível do banco de dados.
Para uma instância de banco de dados RDS para SQL Server, utilize essa consulta.
exec msdb.dbo.rds_cdc_enable_db '
DB_name
'Para uma instância de banco de dados gerenciada do Azure SQL, utilize essa consulta.
USE
DB_name
GO EXEC sys.sp_cdc_enable_db GO -
Para cada tabela com uma chave primária, execute a consulta a seguir para ativar a 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 tabela com chaves exclusivas, mas sem chave primária, execute a consulta a seguir para ativar a 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 tabela sem chave primária e sem chaves exclusivas, execute a consulta a seguir para habilitar a MS-CDC.
exec sys.sp_cdc_enable_table @source_schema = N'schema_name', @source_name = N'table_name', @role_name = NULL GO
-
Defina o período de retenção:
Para instâncias do RDS para SQL Server que estão sendo replicadas usando o DMS versão 3.5.3 e superiores, garanta que o período de retenção esteja definido com o valor padrão de 5 segundos. Se você estiver atualizando ou migrando do DMS 3.5.2 e versões anteriores para o DMS 3.5.3 e versões posteriores, altere o valor do intervalo de sondagem depois que as tarefas estiverem em execução na instância nova ou atualizada. O script a seguir define o período de retenção como 5 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 instâncias do Azure SQL MI e do RDS para SQL Server que estão sendo replicadas usando o DMS versão 3.5.2 e anteriores, use os seguintes 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'
O parâmetro
@pollinginterval
é medido em segundos com um valor recomendado definido como 86399. Isso significa que o log de transações retém as alterações por 86.399 segundos (um dia) quando@pollinginterval = 86399
. O procedimentoexec sp_cdc_start_job 'capture'
inicia as configurações.nota
Com algumas versões do SQL Server, se o valor de
pollinginterval
for definido como mais de 3599 segundos, o valor será redefinido para os cinco segundos padrão. Quando isso acontece, as entradas do T-Log são descartadas antes que oAWS DMS possa lê-las. Para determinar quais versões do SQL Server são afetadas por esse problema conhecido, consulte este artigo da Microsoft KB. Se estiver utilizando o Amazon RDS com multi-AZ, defina também o secundário para ter os valores corretos em caso de failover.
exec rdsadmin..rds_set_configuration 'cdc_capture_pollinginterval' , <5 or 86399>
Como manter o período de retenção quando uma tarefa de replicação do AWS DMS é interrompida por mais de uma hora
nota
As etapas a seguir não são necessárias para replicação de uma origem do RDS para SQL Server usando o DMS 3.5.3 e superiores.
-
Interrompa o trabalho que está truncando os logs de transações utilizando este comando:
exec sp_cdc_stop_job 'capture'
-
Encontre sua tarefa no console do AWS DMS e retome-a.
-
Escolha a guia Monitoramento e marque a métrica
CDCLatencySource
. -
Quando a métrica
CDCLatencySource
for igual a 0 (zero) e permanecer nesse valor, reinicie o trabalho truncando os logs de transações com o seguinte comando:exec sp_cdc_start_job 'capture'
Lembre-se de iniciar o trabalho que trunca os logs de transações do SQL Server. Caso contrário, o armazenamento na instância do SQL Server poderá ficar cheio.
Configurações recomendadas ao utilizar o RDS para SQL Server como origem para o AWS DMS
Para AWS DMS 3.5.3 e superior
nota
A versão inicial do recurso de backup de log do RDS para SQL Server está habilitada por padrão para endpoints que você criou ou modificou após o lançamento do DMS versão 3.5.3. Para usar esse recurso para endpoints existentes, modifique o endpoint sem fazer nenhuma alteração.
O AWS DMS versão 3.5.3 introduz compatibilidade com leitura de backups de log. O DMS depende principalmente da leitura dos logs de transações ativos para replicar eventos. Se o backup de uma transação for feito antes que o DMS possa lê-la no log ativo, a tarefa acessará os backups do RDS sob demanda e lerá os logs de backup subsequentes até alcançar o log de transações ativo. Para garantir que o DMS tenha acesso aos backups de log, defina o período de retenção de backup automatizado do RDS para pelo menos um dia. Para ter informações sobre como configurar o período de retenção de backup automatizado, consulte Período de retenção de backup no Guia do usuário do Amazon RDS.
Uma tarefa do DMS que acessa os backups de log utiliza o armazenamento na instância do RDS. Observe que a tarefa só acessa os backups de log necessários para a replicação. O Amazon RDS remove esses backups baixados em algumas horas. Essa remoção não afeta os backups do Amazon RDS retidos no Amazon S3 ou a funcionalidade RESTORE DATABASE
do Amazon RDS. É recomendável alocar armazenamento adicional em sua origem do RDS para SQL Server, se pretender replicar usando o DMS. Uma forma de estimar a quantidade de armazenamento necessária é identificar o backup a partir do qual o DMS iniciará ou retomará a replicação e somar os tamanhos dos arquivos de todos os backups subsequentes usando a função de metadados tlog backup
do RDS. Para ter mais informações sobre a função tlog backup
, consulte Listar os backups de logs de transações disponíveis no Guia do usuário do Amazon RDS.
Como alternativa, você pode optar por habilitar a escalabilidade automática do armazenamento e/ou acionar a escalabilidade de armazenamento com base na métrica FreeStorageSpace
do CloudWatch para sua instância do Amazon RDS.
É altamente recomendável que você não inicie ou retome a partir de um ponto muito distante nos backups do log de transações, pois isso pode fazer com que o armazenamento em sua instância do SQL Server fique cheio. Nesses casos, é aconselhável iniciar uma carga máxima. A replicação do backup do log de transações é mais lenta do que a leitura dos logs de transações ativos. Para ter mais informações, consulte Processamento de backup do log de transações do RDS para SQL Server.
Observe que o acesso aos backups de log requer privilégios adicionais. Para ter mais informações, consulte os detalhes em Configurar permissões para replicação contínua a partir de um banco de dados do SQL Server na nuvem . Conceda esses privilégios antes que a tarefa comece a ser replicada.
Para o AWS DMS 3.5.2 e versões anteriores
Quando você trabalha com o Amazon RDS para SQL Server como origem, o trabalho de captura do MS-CDC depende dos parâmetros maxscans
e maxtrans
. Esses parâmetros governam o número máximo de verificações que a captura do MS-CDC faz no log de transações e o número de transações que são processadas para cada verificação.
Para bancos de dados, em que o número de transações é maior que maxtrans*maxscans
, aumentar o valor de polling_interval
pode causar um acúmulo de registros no log de transações. Por sua vez, esse acúmulo pode levar a um aumento no tamanho do log de transações.
Observe que o AWS DMS não depende da tarefa de captura da MS-CDC. A tarefa de captura da MS-CDC marca as entradas do log de transações como processadas. Isso permite que a tarefa de backup do log de transações remova as entradas do log de transações.
É recomendável monitorar o tamanho do log de transações e o sucesso das tarefas da MS-CDC. Se as tarefas da MS-CDC falharem, o log de transações poderá crescer excessivamente e causar falhas na replicação do AWS DMS. É possível monitorar erros do trabalho de captura da MS-CDC utilizando a visualização de gerenciamento dinâmico sys.dm_cdc_errors
no banco de dados de origem. É possível monitorar o tamanho do log de transações utilizando o comando de gerenciamento DBCC SQLPERF(LOGSPACE)
.
Como abordar o aumento do log de transações causado pela MS-CDC
-
Verifique o
Log Space Used %
do banco de dados que AWS DMS está replicando e valide se ele aumentar continuamente.DBCC SQLPERF(LOGSPACE)
-
Identifique o que está bloqueando o processo de backup do log de transações.
Select log_reuse_wait, log_reuse_wait_desc, name from sys.databases where name = db_name();
Se o valor de
log_reuse_wait_desc
for igual aREPLICATION
, a retenção do backup do log será causada pela latência na MS-CDC. -
Aumente o número de eventos processados pelo trabalho de captura aumentando os valores dos parâmetros
maxtrans
emaxscans
.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 resolver esse problema, defina os valores de maxscans
e maxtrans
para que maxtrans*maxscans
seja igual ao número médio de eventos gerados para tabelas que o AWS DMS replica do banco de dados de origem a cada dia.
Se você definir esses parâmetros acima do valor recomendado, os trabalhos de captura processarão todos os eventos nos logs de transações. Se você definir esses parâmetros abaixo do valor recomendado, a latência da MS-CDC aumentará e o log de transações também.
A identificação dos valores apropriados para maxscans
e maxtrans
pode ser difícil porque as alterações na workload produzem um número variável de eventos. Nesse caso, é recomendável configurar o monitoramento na latência da MS-CDC. Para obter mais informações, consulte Monitorar o processomaxtrans
e maxscans
dinamicamente com base nos resultados do monitoramento.
Se a tarefa AWS DMS não puder encontrar os números de sequência de log (LSNs) necessários para retomar ou continuar a tarefa, a tarefa poderá falhar e exigir uma recarga máxima.
nota
Ao utilizar o AWS DMS para replicar dados de uma origem RDS para SQL Server, é possível encontrar erros ao tentar retomar a replicação após um evento de interrupção-inicialização da instância do Amazon RDS. Isso ocorre porque o processo do SQL Server Agent reinicia o processo da tarefa de captura quando ele é reiniciado após o evento de interrupção-inicialização. Isso ignora o intervalo de pesquisa da MS-CDC.
Por esse motivo, em bancos de dados com volumes de transações menores do que o processamento da tarefa de captura da MS-CDC, isso pode fazer com que os dados sejam processados ou marcados como replicados e copiados no backup antes que o AWS DMS possa retomar de onde parou, resultando no seguinte erro:
[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 esse problema, defina os valores de maxtrans
e de maxscans
conforme recomendado anteriormente.