As etapas a seguir abordam como configurar um banco de dados autogerenciado do SQL Server para uso com o Datastream:
Ative a CDC para o banco de dados de origem. Para fazer isso, conecte-se ao banco de dados e execute o seguinte comando em um prompt SQL ou em um terminal:
USE [DATABASE_NAME] GO EXEC sys.sp_cdc_enable_db GO
Substitua
DATABASE_NAME
pelo nome do banco de dados de origem.Ative a CDC nas tabelas em que você precisa capturar alterações:
USE [DATABASE_NAME] EXEC sys.sp_cdc_enable_table @source_schema = N'SCHEMA_NAME', @source_name = N'TABLE_NAME', @role_name = NULL GO
Substitua:
DATABASE_NAME
: o nome do banco de dados de origemSCHEMA_NAME
: o nome do esquema a que as tabelas pertencem.TABLE_NAME
: o nome da tabela para a qual você quer ativar o CDC.
Inicie o agente SQL Server e verifique se ele está sendo executado durante todo o processo. Se o agente do SQL Server permanecer inativo por um período prolongado, os registros poderão ser truncados, resultando em uma perda permanente dos dados de mudança que não foram lidos pelo Datastream.
Para informações sobre como executar o SQL Server Agent, consulte Inicie, interrompa ou reinicie uma instância do SQL Server Agent.
Crie um usuário do Datastream:
Conecte-se ao banco de dados de origem e insira o seguinte comando:
USE DATABASE_NAME;
Crie um login para usar ao configurar o perfil de conexão no Datastream.
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
Crie um usuário:
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
Atribua a eles o papel
db_datareader
:EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
Conceda a permissão
VIEW DATABASE STATE
a ele:GRANT VIEW DATABASE STATE TO 'USER_NAME';
Adicione este usuário ao banco de dados
master
:USE master; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
Etapas adicionais necessárias para o método CDC dos registros de transação
As etapas descritas nesta seção são necessárias apenas quando você configura o banco de dados de origem do SQL Server para uso com o método CDC de registros de transações.
Conecte-se ao banco de dados de origem e atribua as funções
db_owner
edb_denydatawriter
ao usuário:USE DATABASE_NAME; EXEC sp_addrolemember 'db_owner', 'USER_NAME'; EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
Conceda permissões
SELECT
para a funçãosys.fn_dblog
.USE master; GRANT SELECT ON sys.fn_dblog TO USER_NAME;
Adicione o usuário ao banco de dados msdb e atribua as seguintes permissões:
USE msdb; CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN; GRANT SELECT ON dbo.sysjobs TO USER_NAME;
Atribua as seguintes permissões ao usuário no banco de dados
master
:USE master; GRANT VIEW SERVER STATE TO YOUR_LOGIN;
Defina o período de retenção em que você quer que as mudanças estejam disponíveis na fonte.
USE [DATABASE_NAME] 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 como86399
. Isso significa que o registro de transações retém as alterações por 86.399 segundos (um dia). A execução do procedimentosp_cdc_start_job 'capture
inicia as configurações.Se houver jobs de limpeza ou captura em execução no seu banco de dados, interrompa-os. Para mais informações, consulte Administrar e monitorar a captura de dados de mudança.
Configure uma proteção contra truncamento de log.
Para garantir que o leitor do CDC tenha tempo suficiente para ler os registros, permitindo o truncamento de registro para evitar o uso do espaço de armazenamento, configure uma salvaguarda de truncamento de registro:
- Acesse o banco de dados com um cliente do SQL Server.
Crie um procedimento armazenado que execute uma transação ativa por um período que especificado para evitar o truncamento de registros:
CREATE PROCEDURE dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time INT AS BEGIN DECLARE @transactionLog TABLE (beginLSN BINARY(10), endLSN BINARY(10)) INSERT @transactionLog EXEC sp_repltrans DECLARE @currentDateTime DATETIME = GETDATE() DECLARE @cutoffDateTime DATETIME = DATEADD(MINUTE, -@transaction_logs_retention_time, @currentDateTime) DECLARE @firstValidLSN BINARY(10) = NULL DECLARE @lastValidLSN BINARY(10) = NULL DECLARE @firstTxnTime DATETIME = NULL DECLARE @lastTxnTime DATETIME = NULL SELECT TOP 1 @lastTxnTime = t.logStartTime, @lastValidLSN = t.beginLSN FROM ( SELECT beginLSN AS beginLSN, (SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime FROM @transactionLog ) t ORDER BY t.beginLSN DESC -- If all transactions are before cutoff, clear everything IF (@lastTxnTime < @cutoffDateTime) BEGIN EXEC sp_repldone NULL, NULL, 0, 0, 1 END ELSE BEGIN -- Find the earliest transaction SELECT TOP 1 @firstTxnTime = t.logStartTime, @firstValidLSN = ISNULL(@firstValidLSN, t.beginLSN) FROM ( SELECT beginLSN AS beginLSN, (SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), beginLSN, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT)) AS logStartTime FROM @transactionLog ) t ORDER BY t.beginLSN ASC IF (@firstTxnTime < @cutoffDateTime) BEGIN -- Identify the earliest and latest LSNs within VLogs before cutoff SELECT @firstValidLSN = SUBSTRING(MAX(t.lsnMarkers), 1, 10), @lastValidLSN = SUBSTRING(MAX(t.lsnMarkers), 11, 10) FROM ( SELECT MIN(beginLSN + endLSN) AS lsnMarkers FROM @transactionLog GROUP BY SUBSTRING(beginLSN, 1, 4) ) t WHERE ( SELECT TOP 1 [begin time] FROM fn_dblog(stuff(stuff(CONVERT(CHAR(24), t.lsnMarkers, 1), 19, 0, ':'), 11, 0, ':'), DEFAULT) WHERE Operation = 'LOP_BEGIN_XACT' ) < @cutoffDateTime EXEC sp_repldone @firstValidLSN, @lastValidLSN, 0, 0, 0 END END END;
Crie outro procedimento armazenado. Desta vez, você cria um job que executa o procedimento armazenado que você criou na etapa anterior de acordo com um valor cadência:
CREATE PROCEDURE [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time INT AS BEGIN DECLARE @database_name VARCHAR(MAX) SET @database_name = (SELECT DB_NAME());; DECLARE @command_str VARCHAR(MAX); SET @command_str = CONCAT('Use ', @database_name,'; exec dbo.DatastreamLogTruncationSafeguard @transaction_logs_retention_time = ' + CAST(@transaction_logs_retention_time AS VARCHAR(10))); DECLARE @job_name VARCHAR(MAX); SET @job_name = CONCAT(@database_name, '_', 'DatastreamLogTruncationSafeguardJob1') DECLARE @current_time INT = CAST(FORMAT(GETDATE(), 'HHmmss') AS INT); -- Schedule the procedure to run after every 5 minutes. IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysjobs WHERE name = @job_name ) BEGIN EXEC msdb.dbo.sp_add_job @job_name = @job_name, @enabled = 1, @description = N'Execute the procedure every 5 minutes.' ; EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name, @step_name = N'Execute_DatastreamLogTruncationSafeguard', @subsystem = N'TSQL', @command = @command_str; DECLARE @schedule_name_1 VARCHAR(MAX); SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryFiveMinutesSchedule') EXEC msdb.dbo.sp_add_schedule @schedule_name = @schedule_name_1, @freq_type = 4, -- daily start @freq_subday_type = 4, -- every X minutes daily @freq_interval = 1, @freq_subday_interval = 5, @active_start_time = @current_time; EXEC msdb.dbo.sp_attach_schedule @job_name = @job_name, @schedule_name = @schedule_name_1 ; -- Add a schedule that runs the stored procedure on the SQL Server Agent startup. DECLARE @schedule_name_agent_startup VARCHAR(MAX); SET @schedule_name_agent_startup = CONCAT(@database_name, '_', 'DatastreamSqlServerAgentStartupSchedule') EXEC msdb.dbo.sp_add_schedule @schedule_name = @schedule_name_agent_startup, @freq_type = 64, -- start on SQL Server Agent startup @active_start_time = @current_time; EXEC msdb.dbo.sp_attach_schedule @job_name = @job_name, @schedule_name = @schedule_name_agent_startup ; EXEC msdb.dbo.sp_add_jobserver @job_name = @job_name, @server_name = @@servername ; END END;
Executar o procedimento armazenado que cria o job do Datastream.
DECLARE @transaction_logs_retention_time INT = (INT) EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
Substitua INT pelo número de minutos que você quer e manter os registros. Exemplo:
- O valor de
60
define o tempo de retenção como 1 hora. - O valor de
24 * 60
define o tempo de retenção como 1 dia - O valor de
3 * 24 * 60
define o tempo de retenção como três dias
- O valor de
A seguir
- Saiba mais sobre como o Datastream funciona com fontes do SQL Server.