Esta página foi traduzida pela API Cloud Translation.

Configurar um banco de dados autogerenciado do SQL Server

As etapas a seguir mostram 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
```
Observação :é necessário 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 a que as tabelas pertencem.
- TABLE_NAME: o nome da tabela em que você quer ativar a CDC
Inicie o SQL Server Agent e verifique se ele está em execução o tempo todo. Se o SQL Server Agent permanecer inativo por um período prolongado, os registros poderão ficar truncados, levando à 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 que você ative a reinicialização automática do serviço do SQL Server Agent para evitar que o agente fique inativo por mais tempo.

Crie um usuário do Datastream:

Conecte-se ao banco de dados de origem e digite 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 e atribua os papéis db_owner e db_denydatawriter a ele:

CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;

EXEC sp_addrolemember 'db_owner', 'USER_NAME';
EXEC sp_addrolemember 'db_denydatawriter', '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ção.

Conceda permissões 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 período de armazenamento em que você quer que as alterações 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.
Interrompa-os se houver algum job de limpeza ou captura em execução no banco de dados. Para mais informações, consulte Administrar e monitorar a captura de dados alterados.

Configure uma proteção contra truncamento de log.

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

Acesse o banco de dados com um cliente do SQL Server.

Crie uma tabela fictícia no banco de dados:

USE [DATABASE_NAME];
CREATE TABLE dbo.gcp_datastream_truncation_safeguard (
  [id] INT IDENTITY(1,1) PRIMARY KEY,
  CreatedDate DATETIME DEFAULT GETDATE(),
  [char_column] CHAR(8)
  );

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), 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 criado 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;

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 para 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 três dias