Thiago Zavaschi R2 www.zavaschi.com

CDC – Change Data Capture – O que é? Como Usar?

Olá pessoal,

Publiquei um artigo sobre Change Tracking (CT) e comentei que existe um “irmão” chamado Change Data Capture (CDC). Ambos presentes desde o SQL Server 2008.

O CDC serve para fazer capturas das operações de INSERT, UPDATE e DELETE realizadas em uma tabela, porém, diferentemente do CT, ele consegue armazenar o dado. Isso leva a um overhead maior do que o que o CT causa, devido a necessidade de armazenar os dados modificados.

Os dados capturados são armazenados em uma tabela SQL Server, o que facilita o consumo dos mesmos. E sim, é uma solução muito mais elegante e eficiente do que utilizar TRIGGERs sobre as tabelas.

Já vi cenários onde o CDC é utilizado como mecanismo de auditoria, e, ainda que ele consiga cumprir com esse papel (dados algumas premissas), já vimos aqui no blog sobre o recurso que seria mais recomendado nestes casos: SQL Audit.

Vamos entender um pouco melhor como configurar e como este recurso de captura funciona.

Como Funciona

Olhe a seguinte imagem (extraída de http://msdn.microsoft.com/en-us/library/cc645937.aspx):

O SQL Server Change Data Capture, captura as alterações diretamente do arquivo de log da database. Ou seja, os processos DML que ocorrem na database não são condicionados ao CDC, como seriam no caso de triggers.

É importante ressaltar a diferença de metodologia do Change Data Capture para o Change Tracking. O CDC é assíncrono e captura as informações do log. Já o CT é um processo leve, porém síncrono (pois as informações das operações são armazenadas no momento da operação). Observe também que o CDC é uma feature enterprise e o CT não é (estando assim disponívél em outras SKUs).

Uma vez o log lido, as informações pertinentes às modificações são aplicadas a tabelas de modificações (que podem crescer rapidamente, de acordo com a alteração dos dados na tabela com o tracking ativo).

O CDC fornece funções que operam sobre estas tabelas para retornar os valores em um formato filtrado para atender em geral aos processos de ETL. Estes processos por sua vez se beneficiam muito pois não precisam comparar os dados da tabela com os dados de um DW por exemplo para determinar se houve mudança ou não. Isso pode baixar consideravelmente o tempo de carga nos seus pacotes de ETL.

Maiores informações (em inglês): http://msdn.microsoft.com/en-us/library/cc645937.aspx

Como Configurar

Para utilizar o CDC é necessário habilitá-lo para a database através da stored procedure: sys.sp_cdc_enable_db. E na sequência habilitar nas tabelas desejadas com sys.sp_cdc_enable_table.

Imagine que temos uma database chamada MonitorarCDC e que gostaríamos de monitorar algumas de suas tabelas. A começar com a tabela chamada Empregados.

Primeiramente habilitamos o CDC na database MonitorarCDC:

USE MonitorarCDC
GO
EXECUTE sys.sp_cdc_enable_db;

Se atente que o nome da databe não é passada como parâmetro, é utilizado o contexto atual.

Perceba também que isso irá criar na sua database um schema chamado cdc, bem como algumas tabelas de sistema para gerenciar seu CDC.

A função de cada uma destas tabelas você pode ver abaixo:

cdc.captured_columns – Retorna a lista de colunas capturadas.
cdc.change_tables – Armazena a lista de todas as tabelas habilitadas para captura.
cdc.ddl_history – Contém toda a modificação de estrutura (DDL) desde que a captura foi habilitada.
cdc.index_columns – Contém os índices associados às tabelas capturadas.
cdc.lsn_time_mapping – Esta tabela mapeia o número LSN e o tempo.

Agora vamos criar uma tabela para monitorar e vamos inserir alguns valores:

CREATE TABLE Empregados
( matricula int primary key,
  nome varchar(255) not null,
  nascimento datetime not null,
  cargo varchar(255) null,
  ativo bit not null
)

INSERT INTO Empregados
VALUES
(1000, 'Thiago', '1980-01-01', 'Gerente Geral', 1),
(1001, 'Maria', '1987-01-01', 'Auxiliar Administrativo', 1),
(1002, 'Pedro', '1980-01-01', 'Operação Fiscal', 1),
(1003, 'Jorge', '1980-01-01', 'Analista Financeiro', 1)

Agora vamos habilitar o CDC para a tabela Empregados. O exemplo não cobre, mas existem outros parâmetros na procedure sp_cdc_enable_table, como por exemplo a possibilidade de especificar o monitoramento de apenas algumas colunas. Para maiores informações desta procedure segue a documentação: http://technet.microsoft.com/en-us/library/bb522475(v=sql.120).aspx

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Empregados',
@role_name     = NULL
GO

O parametro “role_name” serve para atribuir acesso a esta tabela somente aos pertecentes a esta role passada, caso a role não exista ela é criada. Em caso de NULL, como no exemplo, todos possuem acesso a tabela de tracking.

Existem alguns erros que você pode receber caso tenha instalado o SQL Server e depois renomeado a maquina (servidor). No meu caso isso aconteceu e eu recebi o seguinte erro:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 645
Could not update the metadata that indicates table [dbo].[Empregados] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database MonitorarCDC to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

Como descobri isso?

O retorno da variável @@SERVERNAME foi “ZAVA-SP3” sendo que atualmente é “THIAGOZ-MS”. Isso significa que os metadados do SQL Server estão desatualizados.

Para resolver siga os passos presentes aqui:

http://msdn.microsoft.com/en-us/library/ms143799.aspx

Basicamente no meu caso eu precisei utilizar o seguinte comando:

sp_dropserver 'ZAVA-SP3';
GO
sp_addserver 'thiagoz-ms', local;
GO

Perceba que a sp_helpserver retorna o valor atualizado, porém o @@SERVERNAME me retorna o valor antigo. Para completar o procedimento é necessária a reinicialização da instância.

Agora ao executar a proc sys.sp_cdc_enable_table vemos que funciona e a mensagem de retorno é:

Job 'cdc.MonitorarCDC_capture' started successfully.
Job 'cdc.MonitorarCDC_cleanup' started successfully.

O CDC, como vimos, é assíncrono e (caso não tenha replicação) irá utilizar jobs para fazer a leitura do log de transações, cuja principal função é a execução das procedures sys.sp_MScdc_capture_job e sys.sp_MScdc_cleanup_job.

Quando há a replicação para não haver competição entre o capture e o transactional logreader da replicação, ambos passam a utilizar o transactional logreader.

Além destes jobs no SQL Agent, há a criação de uma tabela de sistema com o nome e estrutura parecidas, com o seguinte padrão de nomenclatura cdc.<schema>_<nome_da_tabela>_CT. No meu caso: cdc.dbo_Empregados_CT.

A estrutura da tabela é semelhante, porém com 5 colunas a mais. Veja na imagem abaixo:

__$start_lsn: LSN associado com o commit da transação de mudança. Todas as mudanças feitas em uma mesma transação compartilham o mesmo valor de __$start_lsn. Se houve deleção de 10 linhas, as 10 linhas possuirão o mesmo valor.

__$end_lsn: Para propósitos informacionais apenas. Não suportado e a compatibilidade futura não é garantida (atualmente insere o valor NULL para todos os valores).

__$seqval: Valor usado para ordenar as operações dentro de uma transação.

__$operation: Identifica a operação (DML) realizada. 1 – DELETE, 2 – INSERT, 3 - UPDATE (valores antigos), 4 – UPDATE (novos valores).

__$update_mask: Mostra os valores alterados no update (1 para as posições alteradas). No caso de DELETE e INSERT onde todas as colunas são afetadas, virá uma máscara com 1’s em todas as posições.

Maiores informações sobre esta tabela criada pode ser vista na documentação: http://msdn.microsoft.com/en-us/library/bb500305.aspx.

Como Obter os Valores

Bom, agora que já temos o CDC implementado e já conhecemos as estruturas criadas, vamos realizar algumas alterações e ver como consumir estas informações.

Vamos executar algumas operações:

INSERT INTO Empregados
VALUES
(1010, 'Mariano', '1975-01-01', 'Atendente', 1),
(1011, 'Paulo', '1990-01-01', 'Atendente', 1)

DELETE FROM Empregados WHERE matricula IN (1002, 1010)

UPDATE Empregados SET cargo = 'Diretor Financeiro' WHERE matricula = 1003

Vamos verificar o que há na tabela de tracking (as imagens correspondem às mesmas linhas):

Ok, consigo ver as operações que foram realizadas e os dados, mas como sei quando aconteceu?

Bom, o LSN é sequencial e corresponde a um tempo específico, esta informação fica armazenada na tabela cdc.lsn_time_mapping criada na database monitorada no momento em que você habilitou o CDC.

Para facilitar o consumo destas tabelas, sem termos que executar uma série de joins e análises, o CDC criou para nós algumas funções que nos auxiliam neste consumo, tais como: sys.fn_cdc_map_time_to_lsn e cdc.fn_cdc_get_all_changes_<schema>_<tabela>.

Veja abaixo um exemplo do consumo de dados utilizando estas funções no nosso exemplo para trazer as modificações das últimas 24 horas:

DECLARE
    @begin_time DATETIME,
    @end_time DATETIME,
    @begin_lsn BINARY(10),
    @end_lsn BINARY(10)

SELECT @begin_time = GETDATE()-1, @end_time = GETDATE();
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Empregados(@begin_lsn,@end_lsn,'all')
GO

A função fn_cdc_map_time_to_lsn pode receber um dos seguintes parâmetros (auto explicativos):

• largest less than
• largest less than or equal
• smallest greater than
• smallest greater than or equal

Essa tabela de tracking pode crescer muito?

Se as suas tabelas monitoradas são altamente transacionais, você pode esperar um rápido crescimento das mesmas. O job de cleanup é o responsável pela limpeza destas tabelas.

Por padrão há a limpeza a cada 3 dias, porém esse período de retenção é configurável. Na realidade este valor é configurado em minutos, neste caso: 4320 minutos.

Para alterar o período de retenção utilize a procedure abaixo. Há um limite de tempo máximo para a retenção que é de 52494800 minutos (100 anos), e caso seja especificado deve ser um valor inteiro e positivo. Maiores informações na documentação (em inglês): http://technet.microsoft.com/en-us/library/bb510626(v=sql.110).aspx.

sp_cdc_change_job @job_type='cleanup', @retention=minutes

Bom pessoal, e era isso o que eu queria mostrar hoje. Espero que seja útil!

Abraços,
Zava