Registrando modificações com o CDC
Posted in Tutorial, Vida Real on November 7th, 2010 by Silas Mendes – Be the first to commentA edição Enterprise do SQL Server 2008 veio com uma funcionalidade nova e bastante interessante conhecida como CDC, abreviação de Change Data Capture. Esta função permite que o DBA capture e registre todas as mudanças feitas numa determinada tabela, de forma bastante simples.
Em versões anteriores do SQL Server, poderíamos utilizar triggers DML para suprir necessidades similares, e para isso tínhamos que programar a trigger que seria disparada e que gravaria a alteração em outra tabela; no entanto, este processo demandava esforço e exigia um controle na manutenção das triggers e tabelas de apoio. Com o CDC, a partir do momento que o DBA habilita-o em determinada tabela, o SQL Server se responsabiliza por toda a estrutura necessária para capturar e registrar as mudanças.
Como funciona?
O CDC captura e registra alterações baseado no log do SQL Server; basicamente ele utiliza o mesmo mecanismo da replicação transacional: o CDC lê o log do SQL Server e registra as alterações em suas tabelas. Note que este processo é assíncrono, ou seja, um dado é alterado e a alteração fica registrada no log; periodicamente é feita uma leitura no log e as alterações são registradas nas tabelas do CDC (criadas pelo SQL Server no schema CDC).
“Mão na massa”
Agora vamos exemplificar um cenário simples utilizando o CDC, monitorando uma única tabela.
No primeiro passo iremos criar a tabela que será utilizada em nossos testes:
-- Cria tabelas para teste CREATE TABLE Funcionario ( codigoFuncionario INT NOT NULL, nome VARCHAR(60), idade INT, numeroCPF CHAR(11) ); GO ALTER TABLE Funcionario ADD CONSTRAINT PK_CodFunc PRIMARY KEY (codigoFuncionario);
Agora ativamos o CDC na base de dados e na tabela Funcionario:
-- Habilita CDC banco EXEC sys.sp_cdc_enable_db; GO -- Habilita CDC na tabela Funcionario EXECUTE sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Funcionario', @role_name = 'roleCDC';
Ao habilitar o CDC, o SQL Server cria jobs que serão executados pelo SQL Server Agent. Estes jobs serão os responsáveis pelo registro das alterações nas tabelas do CDC; logo, para o CDC funcionar corretamente, lembre-se que o SQL Server Agent deve estar em execução.
Com o CDC habilitado, vamos popular a tabela Funcionario:
-- Carga de dados INSERT INTO Funcionario (codigoFuncionario, nome, idade, numeroCPF) VALUES (1, 'Silas Mendes', 29, '08643238736'), (2, 'Antonio Robberto', 36, '04686398765'), (3, 'Joana Maxado', 45, '52341369844'), (4, 'Roberto',null, null), (5, 'Armando Filo', null, '41397601223');
Agora já podemos consultar a tabela do CDC que armazena as alterações na tabela Funcionario:
Observe que a tabela do CDC está no esquema CDC.
Lembre-se que este é um processo assíncrono, portanto ao executar o comando abaixo, pode ser que a tabela ainda não tenha sido atualizada; neste caso, repita a execução do comando.
SELECT * FROM cdc.dbo_Funcionario_CT;
No resultado são retornadas 9 colunas. Neste momento atente-se à coluna __$operation. Note que ela está com o valor: 2. O valor 2 significa que estes registros foram resultado de inserções.
Vamos continuar manipulando os dados para verificar os reflexos das alterações nas tabelas do CDC:
-- UPDATEs na tabela Funcionario UPDATE Funcionario SET Nome = 'Antonio Roberto' WHERE codigoFuncionario = 2; UPDATE Funcionario SET Nome = 'Joana Machado' WHERE codigoFuncionario = 3 ; UPDATE Funcionario SET Nome = 'Roberto Nespolitano', Idade = 18, NumeroCPF = '08468354173' WHERE codigoFuncionario = 3;
Observe o registro das alterações na tabela do CDC:
SELECT * FROM cdc.dbo_Funcionario_CT;
Agora observe que aumentou a quantidade de registros da tabela do CDC. Além das inserções (__$operation = 2) temos também o ANTES e DEPOIS nas operações de UPDATE. Observe agora que, onde o campo __$operation é igual a 3 o registro contém o estado antes do UPDATE, e onde o campo __$operation = 4 temos o estado do registro após o UPDATE.
Para finalizar nosso exemplo, vamos realizar uma exclusão e verificar como essa alteração é registrada:
DELETE FROM Funcionario WHERE codigoFuncionario IN (2,3);
Execute novamente a consulta na tabela do CDC:
SELECT * FROM cdc.dbo_Funcionario_CT;
Agora notamos: onde o campo __$operation = 1 temos os registros que foram excluídos da tabela Funcionario.
CONCLUSÃO
Como observamos, basicamente são necessários 4 passos para utilizar o CDC:
- Utilizar a versão Enterprise (ou superior) do SQL Server 2008;
- O serviço SQL Server Agent deve estar ativo;
- Ativar o CDC no banco de dados utilizando a procedure sp_cdc_enable_db;
- Ativar o CDC nas tabelas que serão monitoradas utilizando a procedure sp_cdc_enable_table.
Vimos também que na tabela de registro do CDC temos diversos valores para o campo __$operation, sendo:
| Comando DML | __$operation |
| DELETE | 1 |
| INSERT | 2 |
| Antes do UPDATE | 3 |
| Depois do UPDATE | 4 |
Hoje abordamos somente os processos básicos do CDC. Irei estender este assunto em outros posts, onde pretendo abordar alguns detalhes interessantes desta nova feature do SQL Server 2008.
Até +
