Configurar um banco de dados do Cloud SQL para SQL Server

As etapas a seguir mostram como configurar um banco de dados do Cloud SQL para SQL Server para usar com o Datastream:

  1. Conectar-se à sua instância do Cloud SQL; Você pode fazer isso usando o O comando gcloud sql connect na prompt do Cloud Shell.

  2. Ative a CDC no banco de dados executando o seguinte comando:

    EXEC msdb.dbo.gcloudsql_cdc_enable_db 'DATABASE_NAME'
    

    Substitua DATABASE_NAME pelo nome do banco de dados de origem.

  3. Ative a CDC nas tabelas em que você precisa capturar mudanças:

    USE [DATABASE_NAME]
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'SCHEMA_NAME',
    @source_name = N'TABLE_NAME',
    @role_name = NULL
    GO
    
  4. Ative o isolamento de snapshots.

    Ao preencher dados do seu banco de dados do SQL Server, é importante garantir instantâneos consistentes. Se você não aplicar as configurações descritas neste as alterações feitas no banco de dados durante o processo de preenchimento podem levar a resultados duplicados ou incorretos, especialmente para tabelas sem chaves primárias.

    Ativar o isolamento de snapshots cria uma visualização temporária do seu 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 ao mesmo tempo. Ativar o isolamento de snapshots pode ter um pequeno impacto no desempenho, mas essencial para uma extração confiável dos dados.

    Para ativar o isolamento de snapshots:

    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 seu banco de dados.

  5. Crie um usuário do Datastream:

    1. No console do Google Cloud, acesse a página Instâncias do Cloud SQL.

      Acesse "Instâncias do Cloud SQL"

    2. Criar um usuário e atribuir os papéis db_owner e db_denydatawriter a eles:

    CREATE USER USER_NAME FOR LOGIN YOUR_LOGIN;
    
    EXEC sp_addrolemember 'db_owner', 'USER_NAME';
    EXEC sp_addrolemember 'db_denydatawriter', 'USER_NAME';
    

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.

  1. 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 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 nas configurações.

  2. 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:

    1. Acesse o banco de dados com um cliente do SQL Server.
    2. 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)
        );
      
    3. 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
        -- Start a new transaction
        BEGIN TRANSACTION;
        INSERT INTO dbo.gcp_datastream_truncation_safeguard (char_column) VALUES ('a')
      
      DECLARE @formatted_time VARCHAR(5)
      SET @formatted_time = CONVERT(VARCHAR(5), DATEADD(MINUTE, @transaction_logs_retention_time, 0), 108);
        -- Wait for X minutes before ending the transaction
        WAITFOR DELAY @formatted_time;
        -- Commit the transaction
        COMMIT TRANSACTION;
      END;
      
    4. Crie outro procedimento armazenado. Dessa vez, você vai criar 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')
      
          -- Add 3 schedules to the job to run again after specified time.
          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 to run an active transaction for x minutes.';
      
      EXEC msdb.dbo.sp_add_jobstep @job_name = @job_name,
      @step_name = N'Execute_DatastreamLogTruncationSafeguard',
      @subsystem = N'TSQL',
      @command = @command_str;
      
        -- Add a schedule that runs the stored procedure every given minutes starting now.
        DECLARE @schedule_name_1 VARCHAR(MAX);
        SET @schedule_name_1 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesFromNow')
      
        DECLARE @start_time_1 time;
        SET @start_time_1 = DATEADD(SECOND, 1, GETDATE());
        DECLARE @formatted_start_time_1 INT;
        SET @formatted_start_time_1 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_1, 114), ':' ,''));
      
        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 = @transaction_logs_retention_time,
        @active_start_time = @formatted_start_time_1;
      
        EXEC msdb.dbo.sp_attach_schedule
        @job_name = @job_name,
        @schedule_name = @schedule_name_1 ;
      
        -- Add a schedule that runs the stored procedure after every given minutes starting after some delay.
        DECLARE @schedule_name_2 VARCHAR(MAX);
        Set @schedule_name_2 = CONCAT(@database_name, '_', 'DatastreamEveryGivenMinutesAfterDelay');
      
        DECLARE @start_time_2 time;
        SET @start_time_2 = DATEADD(MINUTE, @transaction_logs_retention_time / 2, GETDATE());
      
        DECLARE @formatted_start_time_2 INT;
        SET @formatted_start_time_2 = CONVERT(INT, REPLACE(CONVERT(VARCHAR(8), @start_time_2, 114), ':' ,''));
      
        EXEC msdb.dbo.sp_add_schedule
        @schedule_name = @schedule_name_2,
        @freq_type = 4,  -- daily start
        @freq_subday_type = 4,  -- every x minutes daily
        @freq_interval = 1,
        @freq_subday_interval = @transaction_logs_retention_time,
        @active_start_time = @formatted_start_time_2;
      
        EXEC msdb.dbo.sp_attach_schedule
        @job_name = @job_name,
        @schedule_name = @schedule_name_2 ;
      
        -- 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 = @formatted_start_time_1;
      
        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;
      
    5. 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 3 dias
  3. Ative a recuperação pontual (PITR, na sigla em inglês).

A seguir