CDC – Parte 02

Neste post da série Dados Históricos com Change Data Capture será possível aprender a habilitar o CDC em sua base de dados e tabela(s), bem como a verificar os devidos status de habilitação usando as tabelas de sistema do SQL Server.


Antes de iniciar o assunto sobre o conjunto de comandos que são usados para habilitar o Change Data Capture em uma base de dados, primeiramente aponto para a necessidade de permissão de administrador do Servidor e/ou de owner da base de dados. Sem isso, não será possível realizar a habilitação da feature.

Dentro da tabela de sistema sys.databases é possível verificar através da coluna is_cdc_enabled se o Change Data Capture está ou não habilitado para aquele banco de dados.

Para habilitá-lo, é necessário execução da procedure sys.sp_cdc_enable_db conforme script abaixo:

-- =========================================
-- ------- Enable CDC on Database ----------
-- =========================================
--checking CDC feature enabled
SELECT name,
database_id,
is_cdc_enabled
FROM sys.databases
WHERE name = 'AdventureWorks2014'
GO

--enabling CDC on database
USE AdventureWorks2014
GO
EXEC sys.sp_cdc_enable_db
GO

Se executado o primeiro comando antes da execução da stored procedure, o resultado da coluna is_cdc_enabled será 0. Caso executado novamente (após habilitada a feature através da proc sys.sp_cdc_enable_db) o resultado do atributo será 1.

Feito isso, você irá verificar ao executar o comando abaixo (dentro da tabela de sistema sys.tables, na coluna is_tracked_by_cdc), que nenhuma tabela da base de dados possuirá o CDC habilitado:


--checking tables with CDC enabled
SELECT t.[name], is_tracked_by_cdc
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'Person'
GO

Para habilitar o tracking na tabela da base de dados onde você habilitou o CDC é necessário executar a procedure de sistema sys.sp_cdc_enable_table. Essa sp possui um conjunto de parâmetros que precisam ser preenchidos para que o Change Data Capture seja habilitado na(s) tabela(s):

@source_schema = Nome do schema da tabela em cujo CDC será habilitado. Esse parâmetro é obrigatório;

@source_name = Nome da tabela em cujo CDC será habilitado. Esse parâmetro é obrigatório;

@capture_instance = Nome que será dada para a tabela de CDC + o sufixo _CT. Se esse parâmetro não for determinado, a tabela de CDC terá o nome schema + tabela + o sufixo _CT;

@captured_column_list = Lista de colunas da tabela que irão sofrer traking. Caso esse parâmetro não seja determinado, todas as colunas serão cobertas;

@filegroup_name = File Group onde a tabela de CDC será armazenada. Caso o parâmetro não seja fornecido, a tabela ficará armazenada no file PRIMARY por default.

@supports_net_changes = Parâmetro que habilita ou não o suporte a Net Changes. Veremos o que é o suporte a Net Changes no Post 04 da série. Caso esse parâmetro não seja fornecido, por default esse atributo é cadastrado como zero (desabilitado);

@role_name = O propósito da Role é controlar o acesso aos dados de alteração. Se a Role especificada ainda não existir, ela será criada automaticamente. Os membros da função sysadmin e/ou db_owner têm acesso completo aos dados nas tabelas de alterações. Todos os outros usuários devem ter a permissão SELECT em todas as colunas capturadas da tabela de origem. Além disso, quando uma função é especificada, os usuários que não são membros da função sysadmin ou db_owner também devem ser membros da função especificada. Este parâmetro é obrigatório, porém, pode receber o valor NULL;

@index_name = Parâmetro usado para fornecer o nome de um indice UNIQUE existente na tabela onde o CDC está sendo habilitado. Caso a tabela seja HEAP, essa é uma alternativa pois retira a necessidade da criação de um index cluster. Se não fornecido, a tabela obrigatoriamente necessitará ter um indice primario e este será usado por default;

@allow_partition_switch = Indica se a execução do comando SWITCH PARTITION é permitida para as tabelas de CDC. Por default, caso não fornecido, o parâmetro recebe o valor 1.

Segue o exemplo que será usado no conjunto de posts abaixo:

--checking tables with CDC enabled
SELECT t.[name], is_tracked_by_cdc 
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'Person'
GO 

--enabling CDC on tables
EXEC sys.sp_cdc_enable_table
@source_schema = N'Person', -- schema
@source_name = N'Person', -- table
@captured_column_list = N'[BusinessEntityID],
[PersonType],[NameStyle],[Title],[FirstName],
[MiddleName],[LastName],[Suffix],[EmailPromotion],
[rowguid],[ModifiedDate]', -- columns
@role_name = NULL, -- role
@supports_net_changes = 1 --enable net changes support
GO

Caso você necessite habilitar o Change Data Capture em várias tabelas dentro da sua base dados, ficaria bastante trabalhosa a construção dos scripts para habilitação do CDC nas mesmas.

No intuito de facilitar esse processo, criei a query do script abaixo, como exemplo, que gerá uma saída de script de execução da procedure sys.sp_cdc_enable_table default para cada uma das tabelas do schema Person da base de dados AdventureWorks2014:

-- generate queryes for enabling CDC on tables
SELECT 
'EXEC sys.sp_cdc_enable_table
@source_schema = N''' + s.name + ''',
@source_name = N''' + t.name + ''',
--@captured_column_list = <columns name>,
@role_name = NULL,
@supports_net_changes = 1
GO

'
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE s.name = 'Person'

Junto com a criação da primeira tabela de controle de registro histórico, são também criados dois jobs dentro do serviço do Agent. Um ponto MUITO IMPORTANTE acerca da Feature é que para ser possível a captura dos das alterações através do Log de Transação, OBRIGATORIAMENTE o Serviço do Agent necessita estar HABILITADO. O SQL Server usa o mesmo sistema interno da replicação transacional para isso. Será possível entender melhor sobre esses dois Jobs na Parte 05 do conjunto de posts.

Depois de habilitado o Change Data Capture e criados os objetos responsáveis pelos metadados da feature e o armazenamento dos dados históricos de alteração, caso haja a necessidade de desabilitá-lo na base de dados, basta a execução do comando abaixo:

-- =============================================
-- --------- Desable CDC on Database -----------
-- =============================================

--Disable CDC in database
USE AdventureWorks2014
GO

EXEC sys.sp_cdc_disable_db
GO

Feito isso, todas as tabelas e jobs serão automaticamente deletados do banco de dados.


No próximo post da série será possível você entender mais sobre os objetos criados para controle do Change Data Capture, bem como sobre as colunas de controle de versionamento de registros criadas dentro de cada uma das tabelas de CDC.

4 comentários sobre “CDC – Parte 02

  1. Pingback: CDC – Parte 03 – Arthur Luz | Data's Light

  2. Pingback: CDC – Parte 05 – Arthur Luz | Data's Light

  3. Renato

    Arthur, legal seu post.

    Tenho uma dúvida:

    Como faço para auditar somente as tabelas para um range de logins.
    supondo que queira auditar somente logins que não façam parte do range do sistema, por exemplo: auditar logins que tenham acesso ao banco conectados por fora da aplicação, nesse caso preciso saber o que eles estão fazendo, sejam esses logins autenticados pelo AD ou por autenticação SQLServer.

    Curtido por 1 pessoa

    1. Olá, Renato!

      Desculpe a demora para te responder, mas realmente andei um pouco por fora do blog essas últimas semanas. 🙂

      Quanto a tua dúvida, vamos lá:

      Existem várias formas de auditoria em uma base de dados. Uma delas (a que é possível realizar com o CDC) está relacionada à alterações de dados.

      Já a auditoria que você pretende realizar está relacionada à quem alterou e de que lugar o fez. 🙂 Isso não é possível realizar com o CDC.

      Para realizar isso, você pode usar duas features do SQL Server. Uma delas é o Audit. Com esse cara é possível que você descubra qual o login realizou aquela alteração.

      Outra feature que você pode utilizar para negar o acesso fora da aplicação é o Police Management.

      Qualquer outra dúvida estou a disposição e, novamente, desculpe a demora para responder!

      Grande abraço!

      Arthur Luz

      Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s