O Google usa tecnologia de IA na tradução de conteúdos para seu idioma de preferência. As traduções com IA podem ter erros.

Configurar um banco de dados do SQL Server autogerenciado para CDC

Para transmitir atualizações de captura de dados alterados (CDC) de um banco de dados autogerenciado do SQL Server com o Datastream, use tabelas de alteração ou registros de transações. Os dois métodos replicam as mudanças em destinos compatíveis, como o BigQuery ou o Cloud Storage. Antes de criar um stream, ative o CDC e o isolamento de snapshot, execute o SQL Server Agent, crie um usuário do Datastream e configure a retenção de registros.

Configurar o banco de dados de origem

Para configurar o banco de dados de origem:

Verifique se o banco de dados usa o modelo de recuperação completo. Conecte-se ao banco de dados e execute o seguinte comando em um prompt SQL ou em um terminal:
```
USE master
GO
ALTER DATABASE [DATABASE_NAME] SET RECOVERY FULL
GO
```
Substitua DATABASE_NAME pelo nome do banco de dados de origem.

Observação: depois de definir o modelo de recuperação como FULL, o banco de dados permanece em um estado de recuperação pseudo-simples até que você faça um backup completo do banco de dados. É necessário fazer um backup completo do banco de dados antes que o registro de transações comece a reter registros para a captura de dados alterados.
Ative a CDC no 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
```
Observação :é preciso executar o comando para cada tabela em que você quer ativar a CDC.

Substitua:
- DATABASE_NAME: o nome do banco de dados de origem.
- SCHEMA_NAME: o nome do esquema ao qual as tabelas pertencem.
- TABLE_NAME: o nome da tabela em que você quer ativar a CDC.
Inicie o agente SQL Server e verifique se ele está sendo executado durante todo o processo. Se o SQL Server Agent permanecer inativo por um período prolongado, os registros poderão ser truncados, levando a uma perda permanente dos dados de alteração que não foram lidos pelo Datastream.

Para informações sobre como executar o SQL Server Agent, consulte Iniciar, interromper ou reiniciar uma instância do SQL Server Agent.

Observação: recomendamos ativar a reinicialização automática para o serviço do SQL Server Agent para evitar que o agente fique inativo por mais tempo.
Ativar o isolamento de snapshot.

Ao preencher dados do banco de dados do SQL Server, é importante garantir snapshots consistentes. Se você não aplicar as configurações descritas nesta seção, as alterações feitas no banco de dados durante o processo de preenchimento podem levar a duplicatas ou resultados incorretos. A aplicação das configurações de isolamento de snapshot é necessária se o stream incluir tabelas sem chaves primárias.

Ativar o isolamento de snapshot cria uma visualização temporária do banco de dados no início do processo de preenchimento. Isso garante que os dados copiados permaneçam consistentes, mesmo que outros usuários estejam fazendo alterações nas tabelas ativas ao mesmo tempo. Ativar o isolamento de snapshots pode ter um pequeno impacto no desempenho, mas é essencial para uma extração de dados confiável.

Para ativar o isolamento de snapshot:
1. Acesse o banco de dados com um cliente do SQL Server.
2. Execute este comando:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Substitua DATABASE_NAME pelo nome do banco de dados.
Crie um usuário do Datastream:
1. Conecte-se ao banco de dados de origem e digite o seguinte comando:
```
USE DATABASE_NAME;
```
2. Crie um login para usar ao configurar o perfil de conexão no Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. Crie um usuário:
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. Atribua o papel db_datareader a eles:
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. Conceda a permissão VIEW DATABASE STATE a eles:
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. Adicione esse usuário ao banco de dados master:
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

Etapas extras necessárias para o método CDC de registros de transação

As etapas descritas nesta seção só são necessárias ao configurar 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 os papéis db_owner e db_denydatawriter ao usuário:

USE DATABASE_NAME;
EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';

Conceda permissões de SELECT para a função sys.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 a ele:

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 intervalo de sondagem em que você quer que as mudanças estejam disponíveis na origem.
```
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 como 86399. Isso significa que o registro de transações retém as alterações por 86.399 segundos (um dia). A execução do procedimento sp_cdc_start_job 'capture inicia as configurações.
Se houver jobs de limpeza ou captura em execução no banco de dados, interrompa-os. Para mais informações, consulte Administrar e monitorar a captura de dados alterados.

Configurar uma proteção contra truncamento de registros.

Para garantir que o leitor do CDC tenha tempo suficiente para ler os registros e, ao mesmo tempo, permitir o truncamento de registros para evitar o uso do espaço de armazenamento, configure uma proteção de truncamento de registros:

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 especificado para evitar o truncamento do registro:

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), SUBSTRING(t.lsnMarkers, 1, 10), 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ê vai criar um job que executa o procedimento armazenado que você criou na etapa anterior de acordo com uma cadência especificada:

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;

Execute 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 reter 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 3 dias.

A seguir

Saiba mais sobre como o Datastream funciona com origens do SQL Server.