SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)

SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
4 (80%) 3 votos

Olá pessoal!
Nesse post, vou demonstrar para vocês como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC) no SQL Server. Eu já havia escrito o post SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria), que utiliza o recurso de triggers de tabela para realizar essa tarefa, mas gostaria de mostrar uma solução mais robusta (e menos manual), que está disponível desde o SQL Server 2008 (Enterprise, Developer e Trial – Valeu pela dica, Fabio Colli).

Muito utilizado em tabelas que sofrem alterações e que precisam de auditorias e logs de alteração, o CDC permite criar um histórico de alterações de dados realizadas numa tabela, tudo de forma automática e sem precisar de criar triggers e nem nada nesse sentido. Com esse excelente recurso, podemos identificar inserções de dados, alterações (dados antes e depois do update) e dados excluídos da tabela e também alterações de estruturas (DDL) realizadas nas tabelas monitoradas.

Uma vez que esse recurso é ativado em uma tabela, será criada uma nova tabela espelho, com as mesmas colunas da tabela original e mais algumas colunas de metadados para controle da alteração realizada na tabela original. Essa nova tabela, que será criada automaticamente, aceita consultas normais (SELECT) em seus dados. Vale ressaltar que o monitoramento continua ativo, mesmo após reiniciar o serviço.

Quais databases estão com o CDC ativo?

Visualizar conteúdo
Para verificar quais databases da sua instância estão com o Change Data Capture (CDC) ativo, você pode consultar as informações da view de sistema sys.databases.

Exemplo:

Quais tabelas estão sendo monitoradas com CDC?

Visualizar conteúdo
Para verificar quais tabelas de um determinado database estão com o recurso de CDC ativo, basta realizar uma consulta na view de sistema sys.tables, conforme exemplo abaixo:

Exemplo:

Como habilitar o CDC em um database (Nível 1)

Visualizar conteúdo
O controle do Change Data Capture (CDC) é feito a nível de database. Para ativar o CDC, você irá utilizar a Stored Procedure de sistema sys.sp_cdc_enable_db.

Exemplo de uso:

Resultado:

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:

Como ativar o CDC e monitorar alterações nas tabelas (Nível 2)

Visualizar conteúdo
Para iniciar o monitoramento de tabelas e começar a armazenar o histórico de alterações de dados (DML) e estrutura (DDL), você precisará utilizar a SP de sistema sys.sp_cdc_enable_table.

Exemplo de uso:

Resultado:

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:

Msg 22901, Level 16, State 1, Procedure sp_cdc_enable_table, Line 39 [Batch Start Line 2] The database ‘dirceuresende’ is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.

Que jobs criados pelo CDC são esses?

Visualizar conteúdo
Como vocês puderam perceber no tópico “Como ativar o CDC e monitorar alterações nas tabelas”, ao ativar o CDC em uma tabela, 2 jobs foram criados automaticamente (caso você ative o CDC em outras tabelas, os mesmos 2 jobs vão continuar sendo utilizados, ou seja, são criados 2 jobs por database monitorado e não por tabela):

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

Como desativar o CDC em um database (Nível 1)

Visualizar conteúdo
Caso você queira desativar o CDC de um database, basta utilizar a SP de sistema sys.sp_cdc_disable_db.

Exemplo:

Resultado:

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).

Como desativar o CDC em uma tabela (Nível 2)

Visualizar conteúdo
Para desativar o CDC de uma tabela específica, você precisará primeiro identificar o nome da instância de captura do CDC, utilizando a SP sys.sp_cdc_help_change_data_capture ou consultando a cdc.change_tables, para depois desativar o monitoramento com a SP sys.sp_cdc_disable_table.

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:

Resultado:

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:

Resultado:

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.

É isso aí, pessoal!
Espero que tenham gostado desse post e que ele possa lhes ser útil!

Um abraço e até a próxima!

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

2 Comments

Deixe uma resposta