Como usar Change Data Capture em SQL Server — passo a passo

Change Data Capture em SQL Server permite registar alterações numa tabela (insert/update/delete) de forma eficiente, útil para cargas incrementais e auditoria. Este guia mostra como activar CDC, ler as alterações com funções fn_cdc e gerir retenção e desligamento.

Pré-requisitos

• SQL Server 2016 SP1 ou superior (CDC disponível nesta e versões mais recentes).
• Permissão de sysadmin ou db_owner na base de dados alvo.
• SQL Server Agent em execução (CDC usa jobs do Agent).
• Uma tabela com chave primária (exemplo usa dbo.Products).

Passo 1: Activar CDC na base de dados

Primeiro, activar CDC a nível de base de dados. Isto cria as tabelas de controlo e permite que capture instances sejam registadas.

USE YourDatabaseName;
GO
EXEC sys.sp_cdc_enable_db;
GO

Passo 2: Criar tabela de exemplo (se necessário) e activar CDC na tabela

A tabela precisa de uma chave primária. Se já tiver tabela, pule para a activação. Depois, registe a tabela para captura.

-- Exemplo de criação de tabela
CREATE TABLE dbo.Products (
  ProductID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(100),
  Price DECIMAL(10,2)
);
GO

-- Activar CDC na tabela
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Products',
  @role_name     = NULL; -- NULL permite leitura a qualquer utilizador com permissões
GO

Passo 3: Gerar alterações na tabela (insert / update / delete)

Inserir algumas linhas e fazer updates/deletes para que existam alterações a serem capturadas.

INSERT INTO dbo.Products (Name, Price) VALUES ('Caneta', 1.20);
INSERT INTO dbo.Products (Name, Price) VALUES ('Caderno', 3.45);

UPDATE dbo.Products SET Price = 1.50 WHERE Name = 'Caneta';
DELETE FROM dbo.Products WHERE Name = 'Caderno';
GO

Passo 4: Consultar as alterações com fn_cdc_get_all_changes

Para ler alterações use as funções de sistema de CDC. Obtenha as LSNs mínimas e máximas e consulte cdc.fn_cdc_get_all_changes_. O capture_instance por defeito é schema_tablename (ex.: dbo_Products).

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Products');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT 
  __$start_lsn,
  __$seqval,
  CASE __$operation
    WHEN 1 THEN 'delete'
    WHEN 2 THEN 'insert'
    WHEN 3 THEN 'update_before'
    WHEN 4 THEN 'update_after'
  END AS operation,
  ProductID, Name, Price
FROM cdc.fn_cdc_get_all_changes_dbo_Products(@from_lsn, @to_lsn, 'all');
GO

Interpretação rápida: __$operation = 1 (delete), 2 (insert), 3 (update antes), 4 (update depois). Use a linha _before_ e _after_ para detectar alterações de valores.

Passo 5: Gerir retenção e jobs do CDC

CDC cria jobs do SQL Server Agent para captura e limpeza. A retenção por defeito é 3 dias (em minutos: 4320). Pode ajustar o job de cleanup com sp_cdc_change_job.

-- Alterar retenção para 7 dias (10080 minutos)
EXEC sys.sp_cdc_change_job
  @job_type = N'cleanup',
  @retention = 10080;
GO

Verificar jobs associados com sp_help_job ou no SQL Server Agent. Para desactivar a captura numa tabela ou na base de dados use:

-- Desactivar CDC na tabela
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'Products', @capture_instance = N'dbo_Products';
GO

-- Desactivar CDC na base de dados
EXEC sys.sp_cdc_disable_db;
GO

Verificar o resultado

Confirme que as change tables existem e que há registos:

-- Ver change tables activas na base de dados
SELECT * FROM cdc.change_tables;

-- Ler dados capturados (exemplo já dado no Passo 4)
-- Se obtiver linhas com __$operation consegue verificar as alterações efectuadas.

Se não aparecerem alterações verifique: SQL Server Agent em execução, permissões (sysadmin/db_owner), e se a tabela tem chave primária. Erro comum: "The specified table does not have a primary key" — adicione PK à tabela antes de activar CDC.

Conclusão

Depois de activar e testar Change Data Capture em SQL Server, pode usar os dados para cargas incrementais em ETL, auditoria ou replicação personalizada. Próximos passos sugeridos: integrar a leitura CDC num processo SSIS/ADF, ou usar a função fn_cdc_get_net_changes para obter apenas o estado final. Dica: verifique e ajuste a retenção conforme o volume de alterações para evitar crescimento excessivo das change tables — pretende experimentar com um volume de teste maior?