Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

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

Post Views 12,924 views
Esse post é a parte 8 de 21 da série Security and Auditing
Reading time 9 minutes

Hey Guys!
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) e a partir do SQL Server 2016 SP1 passou a ser disponibilizada também na edição Standard. No Azure SQL Database, é suportado a partir do tier S3.

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.

O Change Data Capture (CDC) tem baixo impacto para o banco de dados, pois funciona através de um job do SQL Agent (um job para cada database que tenha o CDC ativado) que faz a leitura assíncrona da transaction log e armazena as alterações na tabela de histórico. Por este motivo, o recovery model do database é alterado automaticamente para FULL caso esteja no SIMPLE.

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:

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:

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:

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:

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 configurar a retenção de dados do CDC?

Visualizar conteúdo
Uma dúvida bem comum de quem quer implementar o CDC é em relação à retenção dos dados. Será que esses dados vão crescer infinitamente, sem limite?

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:

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:

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

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:

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.

Change Data Capture (CDC) e operações de Backup/Restore

Visualizar conteúdo
Como o CDC é um recurso interno do SQL Server e que cria metadados e jobs para realizar algumas atividades, operações como backup/restore podem não se comportar exatamente como o esperado em determinadas situações.

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:

And that's it, folks!
Espero que tenham gostado desse post e que ele possa lhes ser útil!

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