SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)

Exemplo:

Exemplo:

Exemplo de uso:

Result:

Uma vez ativado o CDC na instância, vocês podem verificar que um schema “cdc” será criado no database em questão:

Além disso, algumas tabelas de sistema foram criadas utilizando o esquema “cdc”:

As tabelas criadas pelo CDC são:

• cdc.captured_columns: Essa tabela de sistema vai listar todas as colunas das tabelas que estão com o CDC ativado. Essas informações também podem ser consultadas utilizando a SP de sistema sys.sp_cdc_get_source_columns.

  

  Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-captured-columns-transact-sql

• cdc.change_tables: Essa tabela de sistema vai listar todas as tabelas que estão com o CDC ativado. Essas informações também podem ser consultadas utilizando a SP de sistema sys.sp_cdc_help_change_data_capture.

  

  Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-change-tables-transact-sql

• cdc.ddl_history: Essa tabela de sistema vai armazenar todas as alterações de DDL realizadas nas tabelas que estão com o CDC ativado. Essas informações também podem ser consultadas utilizando a SP de sistema sys.sp_cdc_get_ddl_history.

  

  Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-ddl-history-transact-sql

• cdc.index_columns: Essa tabela armazena as informações sobre os índices associados às tabelas que estão com o CDC ativado. Essas informações também podem ser consultadas utilizando a SP de sistema sys.sp_cdc_help_change_data_capture.

  

  Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-index-columns-transact-sql

• cdc.lsn_time_mapping: Retorna uma linha para cada transação da tabela com o CDC ativado. Essa tabela é utilizada para mapear os valores commitados entre o Log Sequence Number (LSN) e a hora que a transação é commitada. Essas informações também podem ser recuperadas com as funções sys.fn_cdc_map_lsn_to_time e sys.fn_cdc_map_time_to_lsn.

  

  Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-lsn-time-mapping-transact-sql

Exemplo de uso:

Result:

Após executar a SP acima, o monitoramento do Change Data Capture (CDC) foi ativado na tabela desejada. Com isso, uma nova tabela, no formato cdc.schema_tabela_CT, será criada com as mesmas colunas da tabela originais e mais algumas tabelas de metadados para controle da alteração.

Caso você queira monitorar as alterações em colunas específicas, e não em todas as colunas da tabela, você pode utilizar essa sintaxe:

Agora, vamos fazer algumas alterações na tabela para ver como o CDC se comporta ?

Inserção de dados

Atualização de dados

Remoção de dados

Truncando dados da tabela
Como vocês podem observar no print abaixo, uma vez ativado o CDC numa tabela, você não poderá truncar os dados de uma tabela.

Alteração de estrutura da tabela

Como vocês puderam observar, a coluna __$operation nos permite identificar qual o tipo de operação realizadas na tabela, nos quais os valores possíveis são:

• 1: DELETE
• 2: INSERT
• 3: Valor ANTES do UPDATE
• 4: Valor APÓS o UPDATE

Lembre-se de habilitar o CDC a nível de database antes de tentar habilitar o CDC numa tabela. Caso você não faça isso, irá se deparar com essa mensagem de erro:

E esses 2 jobs tem a seguinte finalidade:

• cdc.dirceuresende_capture: Job que é executado sempre que o SQL Server Agent é iniciado e executa a SP de sistema sys.sp_MScdc_capture_job, que por sua vez, executa a SP sys.sp_cdc_scan, iniciando o monitoramento da tabela.

  Referência: https://technet.microsoft.com/en-us/library/cc645591(v=sql.105).aspx

• cdc.dirceuresende_cleanup: Job que é executado diariamente às 02:00 e tem a finalidade de controlar o tamanho das tabelas de controle do CDC, para evitar que elas cresçam descontroladamente. Esse job executa a SP de sistema sys.sp_MScdc_cleanup_job,
  que por sua vez, executa a SP sys.sp_cdc_cleanup_job_internal.

  Referência: https://technet.microsoft.com/en-us/library/cc645885(v=sql.105).aspx

Para essa pergunta, a resposta é NÃO. Por padrão, os dados vão crescer até atingir o limite padrão do SQL Server (4320 minutos = 3 dias), o que pode ser muito ou pouco, dependendo da sua necessidade.

Caso você queira alterar o tempo de retenção, você pode utilizar a stored procedure sys.sp_cdc_change_job:

Referência: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-change-job-transact-sql

O valor máximo para esse parâmetro de retenção é 52494800 minutos (100 anos), mas se você quiser desativar esse limite, basta desativar o job de cleanup (cdc.dirceuresende_cleanup)

Para visualizar os parâmetros atuais de retenção, utilize a consulta abaixo:

Result:

Exemplo:

Result:

Vale lembrar que ao desativar o CDC a nível de database, TODOS os monitoramentos ativos do CDC a nível de tabela também serão desativados e os dados de histórico serão todos perdidos também (e você NÃO será alterado sobre a existência desses monitoramentos ativos a nível de tabela).

Vale lembrar que é possível desativar o CDC a nível de database, mesmo que existam monitoramentos ativos a nível de tabela (e você NÃO será alertado sobre a existência disso). No final desse tópico eu deixei alguns alertas sobre o que acontece quando você faz isso.. Leia até o final!

Identificando o nome da instância de captura do CDC:

Result:

Uma vez que identificamos o nome da instância (dbo_Clientes), agora podemos executar a sys.sp_cdc_disable_table para efetivamente desativar o CDC nesta tabela:

Result:

Após desativar o CDC na tabela, vocês podem observar que a tabela de monitoramento foi excluída automaticamente. MUITO CUIDADO com isso, para não perder os valores gravados e perder o seu histórico. Caso você queira desativar o CDC, mas não tem a intenção de perder o histórico, copie os dados da tabela de histórico para outra tabela antes de desativar o CDC na tabela.

Vale lembrar que ao desativar o CDC a nível de database, TODOS os monitoramentos ativos do CDC a nível de tabela também serão desativados e os dados de histórico serão todos perdidos também.

Restaurando o mesmo database, na mesma instância
Nessa situação, o restore será feito normalmente e o CDC continuá ativo e funcionando após a base ser restaurada. Nada muda.

Restaurando o backup na mesma instância, mas com outro nome de database ou em outra instância
Nesses dois casos, o CDC será desativado e as informações de metadados gravadas serão perdidas, o que seria algo bem ruim. Para que isso não aconteça, você deverá utilizar o parâmetro keep_cdc no comando de restore.

Exemplo:

Após o restore, você precisará executar os comandos abaixo para recriar os jobs do CDC: