CDC - Change data capture é uma features responsável por monitorar e capturar todas as operações
DML (insert - update - delete) em uma ou mais tabelas de dados. Diferentemente do CT - Change tracking comum (Triggers - SQL Profile e entre outros) com o Change Data temos a possibilidade de registrar e armazenar todas as alterações de dados. O que custa a um
overhead maior do que o que o CT tradicional em relação a necessidade de armazenar os dados modificados.
Os dados capturados são registrados em uma tabela SQL Server, o que facilita as consultas e estudos de analise dos mesmos. E sim, é uma solução muito mais
elegante e eficiente do que utilizar Triggers sobre as tabelas.
Como realmente funciona :
O SQL Server Change Data Capture, monitor e captura as alterações diretamente do arquivo de log da database ldf. Ou seja, os processos DML que ocorrem na database não são condicionados ao CDC, como por exemplo 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 é (geralmente disponível ambientes mais robustos de alta disponibilidade).
Determinado momento apos leitura do log as respectivas informações modificadas são registradas nas 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 ( ): 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.
Perceba também que isso irá 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.
Neste exemplos iremos 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
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 & 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 semelhantes a original, 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 ( ): http://msdn.microsoft.com/en-us/library/bb500305.aspx
Como obter os retornos:
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 ( ): 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 queriamos compartilhar com vocês hoje. Espero que seja útil no seu dia-a-dia! Grande abraços
