Esta página foi traduzida pela API Cloud Translation.

Configure uma base de dados do SQL Server autogerida para CDC

Esta página descreve como configurar a captura de dados de alterações (CDC) para transmitir dados de uma base de dados do SQL Server autogerida para um destino suportado, como o BigQuery ou o Cloud Storage.

Ative a CDC para a base de dados de origem. Para o fazer, ligue-se à base de dados e execute o seguinte comando num pedido de SQL ou num terminal:
```
USE [DATABASE_NAME]
GO
EXEC sys.sp_cdc_enable_db
GO
```
Substitua DATABASE_NAME pelo nome da base de dados de origem.
Ative a CDC nas tabelas para as quais precisa de 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
```
Nota: tem de executar o comando para cada tabela para a qual quer ativar a CDC.

Substitua o seguinte:
- DATABASE_NAME: o nome da base de dados de origem
- SCHEMA_NAME: o nome do esquema ao qual as tabelas pertencem
- TABLE_NAME: o nome da tabela para a qual quer ativar a CDC
Inicie o SQL Server Agent e certifique-se de que está sempre em execução. Se o SQL Server Agent permanecer inativo durante um período prolongado, os registos podem ser truncados, o que leva a uma perda permanente dos dados de alterações que não foram lidos pelo Datastream.

Para obter informações sobre a execução do SQL Server Agent, consulte o artigo Inicie, pare ou reinicie uma instância do SQL Server Agent.

Nota: recomendamos que defina o reinício automático para o serviço SQL Server Agent para evitar que o agente fique inativo durante mais tempo.
Ative o isolamento de instantâneo.

Quando preenche dados a partir da sua base de dados do SQL Server, é importante garantir instantâneos consistentes. Se não aplicar as definições descritas nesta secção, as alterações feitas à base de dados durante o processo de preenchimento podem originar duplicados ou resultados incorretos, especialmente para tabelas sem chaves primárias.

A ativação do isolamento de instantâneos cria uma vista temporária da base de dados no início do processo de preenchimento. Isto garante que os dados copiados permanecem consistentes, mesmo que outros utilizadores estejam a fazer alterações às tabelas dinâmicas ao mesmo tempo. A ativação do isolamento de instantâneos pode ter um ligeiro impacto no desempenho, mas é essencial para uma extração de dados fiável.

Para ativar o isolamento de instantâneo:
1. Estabeleça ligação à sua base de dados através de um cliente do SQL Server.
2. Execute o seguinte comando:
```
ALTER DATABASE DATABASE_NAME SET ALLOW_SNAPSHOT_ISOLATION ON;
```
Substitua DATABASE_NAME pelo nome da sua base de dados.
Crie um utilizador da stream de dados:
1. Ligue-se à base de dados de origem e introduza o seguinte comando:
```
USE DATABASE_NAME;
```
2. Crie um início de sessão para usar durante a configuração do perfil de associação no Datastream.
```
CREATE LOGIN YOUR_LOGIN WITH PASSWORD = 'PASSWORD';
```
3. Crie um utilizador:
```
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```
4. Atribua-lhe a função de db_datareader:
```
EXEC sp_addrolemember 'db_datareader', 'USER_NAME';
```
5. Conceda-lhe a autorização VIEW DATABASE STATE:
```
GRANT VIEW DATABASE STATE TO USER_NAME;
```
6. Adicione este utilizador à base de dados do master:
```
USE master;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
```

Passos adicionais necessários para o método CDC dos registos de transações

Os passos descritos nesta secção só são necessários quando configura a base de dados do SQL Server de origem para utilização com o método CDC dos registos de transações.

Faça a ligação à base de dados de origem e atribua as funções db_owner e db_denydatawriter ao utilizador:

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

Conceda autorizações do SELECT para a função sys.fn_dblog.

USE master;
GRANT SELECT ON sys.fn_dblog TO USER_NAME;

Adicione o utilizador à base de dados msdb e atribua-lhe as seguintes autorizações:

USE msdb;
CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
GRANT SELECT ON dbo.sysjobs TO USER_NAME;

Atribua as seguintes autorizações ao utilizador na base de dados master:
```
  USE master;
  GRANT VIEW SERVER STATE TO YOUR_LOGIN;
```
Defina o intervalo de sondagem para o qual quer que as alterações estejam disponíveis na sua 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. Isto significa que o registo de transações retém as alterações durante 86 399 segundos (um dia). A execução do procedimento sp_cdc_start_job 'capture inicia as definições.
Se existirem tarefas de limpeza ou captura em execução na sua base de dados, pare-as. Para mais informações, consulte o artigo Administre e monitorize a captura de dados de alterações.

Configure uma salvaguarda de truncagem de registos.

Para se certificar de que o leitor de CDC tem tempo suficiente para ler os registos, ao mesmo tempo que permite o corte de registos para evitar a utilização do espaço de armazenamento, pode configurar uma salvaguarda de corte de registos:

Estabeleça ligação à base de dados através de um cliente do SQL Server.

Crie um procedimento armazenado que execute uma transação ativa durante um período que especificar para evitar a truncagem do registo:

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, cria uma tarefa que executa o procedimento armazenado que criou no passo 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 a tarefa do fluxo de dados.
```
DECLARE @transaction_logs_retention_time INT = (INT)
EXEC [dbo].[SetUpDatastreamJob] @transaction_logs_retention_time
```
Substitua INT pelo número de minutos durante os quais quer reter os registos. Por exemplo:
- O valor de 60 define o tempo de retenção para 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

O que se segue?

Saiba como o Datastream funciona com origens do SQL Server.

Configure uma base de dados do SQL Server autogerida para CDC Mantenha tudo organizado com as coleções Salve e categorize o conteúdo com base nas suas preferências.

Passos adicionais necessários para o método CDC dos registos de transações

O que se segue?

Configure uma base de dados do SQL Server autogerida para CDC