CDC no SQL Server 2012 na prática…

Com a chegada do SQL Server 2012 melhorias foram realizadas e novidades implementadas e é uma dessas novidades que apresentarei.

No SSIS alguns novos componentes foram implementados para facilitar a utilização do CDC (Change Data Capture):
Esse componente se encontra no Control Flow e ele que irá realizar todo o controle necessário acessar as informações através do Log Sequence Number (LSN) . Mais informação sobre o CDC Contral Task acesse o link.
http://msdn.microsoft.com/en-us/library/hh758674.aspx
Esse componente se encontra dentro do Data Flow e ele irá realizar a leitura dos seus dados. Mais informação sobre o CDC Source acesse o link.
http://msdn.microsoft.com/en-us/library/hh758686.aspx
Esse componente se encontra dentro do Data Flow e ele irá separar seus dados de acordo com as opções escolhidas dentro do CDC Source. Mais informações sobre o CDC Splitter acesso o link.
http://msdn.microsoft.com/en-us/library/hh758656.aspx

Preparação do Ambiente

O CDC tem inúmeras utilidades, mas uma delas é ajudar no processo de carga de um ambiente de Data Warehouse e esse foi o cenário que escolhi.

1º Passo – Criação dos databases

Irei utilizar 3 databases simulando um ambiente de carregamento de dados em um DW.

 1: CREATE DATABASE EXEMPLO_CDC

 2: GO

 3: CREATE DATABASE STAGING

 4: GO

 5: CREATE DATABASE DW_EXEMPLO_DCD

 6: GO

O database EXEMPLO_CDC ira servir como databases OLTP, onde todos os dados transacionais serão mantidos e é nele que temos que habilitar o CDC.

 1: USE EXEMPLO_CDC

 2: GO

 3: EXECUTE sys.sp_cdc_enable_db

 4: GO

Para mais informações sobre a stored procedure de sistema acesse o link http://msdn.microsoft.com/en-us/library/bb510486
Agora crie uma tabela e habilite o CDC para esse objeto.

 1: CREATE TABLE [dbo].[CATEGORIA]

 2: (ID INT NOT NULL IDENTITY PRIMARY KEY,

 3: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 4:

 5: EXECUTE sys.sp_cdc_enable_table

 6: @source_schema = N'dbo',

 7: @source_name = N'CATEGORIA',

 8: @role_name = N'cdc_admin',

 9: @supports_net_changes = 1

 10: GO

 11:

Para mais informaçõe sobre a stored procedure de sistema acesso o link
http://msdn.microsoft.com/en-us/library/bb522475.aspx

 1: INSERT INTO [dbo].[CATEGORIA] VALUES

 2:  ('CATEGORIA 1')

 3: ,('CATEGORIA 2')

 4: ,('CATEGORIA 3')

 5: GO

 6:

 7: USE DW_EXEMPLO_CDC

 8: GO

 9: CREATE TABLE [dbo].[CATEGORIA_DESTINO]

 10: (ID INT NOT NULL PRIMARY KEY,

 11: NOME_CATEGORIA VARCHAR(100) NOT NULL)

 12:

Criando Pacote

Para esse exemplo irei criar dois pacotes, o primeiro de carga inicial ou carga completa e o segundo de carga incremental.

Pacote Inicial

Nesse pacote iremos criar uma carga completa dos dados. A primeira tarefa a se realizar é utilizar o componente CDC Control Task.
Algumas configurações importantes devem ser feitas nesse componentes.

• CDC control operation: Utilize a opção Mark Initial load start. Esta operação é usada no começo de um pacote da carga inicial para registrar o LSN atual no banco de dados de origem antes de o pacote da carga inicial começar a ler as tabelas de origem.
• Crie uma variável, User::CDC_State, para armazenar o estado da operação do CDC. O botão New faz isso automaticamente.
• Utilize a opção Automatically store in a database table para persistir o estado do CDC.
• Crie uma nova tabela para armazenar o estado do CDC. Clique em New e o assistente irá te fornecer um código para ser executado. Esta tabela será usada para rastrear as informações de carga CDC, de modo que você só pegar as novas alterações a cada vez que o pacote de carga incremental é executado
• State Name: O nome associado ao estado persistente. Esse nome é usado para verificar a linha de estado na tabela de estado.

Figura 1
Proxima tarefa é iniciar seu Data Flow que nesse exemplo é bem simples.

Figura 2
Despois de realizar todos as manipulações de dados temos que novamente colocar o componente de CDC Control Task porem com uma modificação. Altere a opção CDC control operation para Mark initial load end

Figura 3
Seu pacote deverá parecer como a figura 4.

Figura 4

Pacote Incremental

Nesse momento irei utilizar o banco de dado STAGING para criar duas tabelas: CATEGORIA_DESTINO_DELTE e CATEGORIA_DESTINO_UPDATE. O motivo dessas tabelas é realizar as operações de UPDATE e DELETE de uma forma performática.

 1: USE STAGING

 2: GO

 3:

 4: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_DELETE]') AND type in (N'U'))

 5: BEGIN

 6:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_DELETE]

 7:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 8: END

 9:

 10: IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CATEGORIA_DESTINO_UPDATE]') AND type in (N'U'))

 11: BEGIN

 12:     SELECT TOP 0 * INTO [dbo].[CATEGORIA_DESTINO_UPDATE]

 13:     FROM [DW_EXEMPLO_DCD].[dbo].[CATEGORIA_DESTINO]

 14: END

No pacote inclua o componente CDC Control Task com as seguintes configurações:

• Crie ou configure uma conexao para o database de origem.
• Configure o CDC Control Operation para Get processing range
• Crie uma nova varial de estado (CDC_state)
• Crie ou configure uma conexao para o database de destino
• Selecion a tabela de estado que foi crianda anteriormente pelo pacote inicial ([dbo].[cdc_states])
• Configure State name – Este nome deve ser igual ao que foi usado no pacote inicial (CDC_State)

Figura 5
Volte ao Control Flow e inclua um Data Flow e ligue-o no CDC Control Task;
Adicione um componente de CDC Source:

• Configure a conexão para o database de origem;
• Selecione a tabela [dbo].[CATEGORIA]. Nessa combo box irão aparecer apenas tabelas que estão habilitadas para o uso do CDC;
• Configure o CDC processing mode para Net. Opção que busca apenas as alterações realizadas na tabela de origem;
• Selecione a variável CDC_State;
• Verifique a aba Columns para ter certeza que os dados estão sendo mapiados corretamente;

Figura 6
OBS: O CDC processing mode é a configuração mais importante aqui, pois cada modo de processamento terá um comportamento diferente que espera uma manipulação especifica.
Adicione o componete CDC Splitter.
Agora adicione 3 componetes ADO.NET Destination, um para nova linhas (Insert), outro para linhas atualizadas(UPDATE) e o ultimo para linhas exluidas (DELETE).
O componente para novas linhas (INSERT) você deve configura-lo para carregar diretamente a tabela [dbo].[CATEGORIA_DESTINO].
Os outros componentes devem ser mapeados para inserirem linhas nas tabelas [dbo].[CATEGORIA_DESTINO_DELETE] para linhas exluidas e [dbo].[CATEGORIA_DESTINO_UPDATE] para linhas atualizadas.
Agora o seu Data Flow deve estar parecido com a figura 7

Figura 7
Volte para o Control Flow e adicione dois componentes de Execute SQL Task. Um para realizar o UPDATE na tabela final e outro para realizar o DELETE na tabela final. Dessa maneira temos uma lote sendo executadas em cima de um conjunto de dados e não uma única linha sendo executada em um lote.
Abaixo segue os comandos de UPDATE e DELETE que utilizei.

 1: -- Update

 2:

 3: UPDATE dest

 4: SET

 5: dest.NOME_CATEGORIA = stg.NOME_CATEGORIA

 6: FROM

 7: [dbo].[CATEGORIA_DESTINO] dest

 8: INNER JOIN [STAGING].[dbo].[CATEGORIA_DESTINO_UPDATE] STG

 9: ON STG.ID = dest.ID

 10:

 11: -- Delete 

 12:

 13: DELETE FROM [CATEGORIA_DESTINO]

 14: WHERE ID IN

 15: (

 16: SELECT ID

 17: FROM [STAGING].[dbo].[CATEGORIA_DESTINO_DELETE]

 18: )

Por ultimo temos que atualizar nosso controle do CDC. Mais uma vez utilize o CDC Control Task para realizar essa tarefa e altera a opção CDC control operation para Mark processed range.

Figura 8
Inclui também um componente para limpar as tabelas do STAGING realizando um TRUNCATE nas tabelas.
O seu pacote deve estar parecido com a figura 9

Figura 9

Executando os Pacotes

Como já temos uma carga inicial com 3 registros vamos executar o pacote Inicial para realizar a carga.

Figura 10
Depois de executar o pacote Inicial podemos observar que a tabela cdc_states contem a seguinte informação.

Figura 11
Agora se executarmos o pacote incremental nesse momento não teremos nenhuma informação nova. Por isso vamos manipular alguns dados.

 1: USE EXEMPLO_CDC

 2: GO

 3: INSERT INTO dbo.CATEGORIA VALUES

 4: ('CATEGORIA 4')

 5: ,('CATEGORIA 5')

 6: ,('CATEGORIA 6')

 7: GO

 8: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 1'

 9: WHERE ID = 1

 10: UPDATE dbo.CATEGORIA set NOME_CATEGORIA = 'CATEGORIA ALTERACAO 2'

 11: WHERE ID = 2

 12: DELETE FROM dbo.CATEGORIA WHERE ID = 3

Depois de executar os comandos acima, vamos executar o pacote incrementar.

Figura 12
Fazendo uma consulta no seu destino podemos ver que os dados estao corretamente carregados de uma forma simples e eficiente.

Figura 13
Espero poder ajudar assim como o post original me ajudou. Eu literalmente traduzi e incrementei algumas coisas, mas os créditos são do Matt Masson. Para ver o post original acesse http://www.mattmasson.com/index.php/2011/12/cdc-in-ssis-for-sql-server-2012-2/
Obrigado!

Tiago Balabuch

I'm enthusiast, fascinated, passionate, fanatic by SQL Server and in the off-hours Cloud Data Administrator who loves what does