CDC – Parte 03

Nesta etapa da série será possível entender os objetos criados na Parte 02 do conjunto de posts através da habilitação do Change Data Capture na base de dados.


Após a execução das procedures sys.sp_cdc_enable_dbsys.sp_cdc_enable_table (uma que habilita o CDC na base de dados e outra que cria o traking da(s) tabela(s) selecionadas respectivamente), serão criados alguns objetos dentro da base de dados – tabelas de sistema e jobs. As tabelas serão responsáveis por armazenar metadados do CDC e os dados históricos capturados e os Jobs serão responsáveis pela captura e pela deleção dos dados históricos de tempos em tempos.


As tabelas de sistema que foram criadas para a base de dados AdventureWorks2014 são:

-- ================================
-- ------- system tables ----------
-- ================================

--Check the columns with cdc on
SELECT * FROM [cdc].[captured_columns]

--Check the tables with cdc on
SELECT * FROM [cdc].[change_tables]

--Check in tables the historic of the DDL statements
SELECT * FROM [cdc].[ddl_history]

--Check in tables the indexed columns
SELECT * FROM [cdc].[index_columns]

--Mapping all transactions done in tables with cdc
SELECT * FROM [cdc].[lsn_time_mapping]

--cdc enabled table
SELECT * FROM [cdc].[Person_Person_CT] 

[cdc].[captured_columns] – Armazena as colunas onde existe CDC habilitado;

[cdc].[change_tables] – Armazena as tabelas onde existe CDC habilitado;

[cdc].[ddl_history] – Armazena o histórico de alterações DDL que ocorreram na tabela em cujo CDC está habilitado;

[cdc].[index_columns] – Armazena as colunas indexada da tabela em cujo CDC está habilitado. Caso você tenha optado por usar uma coluna NOT NULL com uma constraint UNIQUE através do parâmetro @index_name (veja mais sobre na Parte 02 do conjunto de posts), essa coluna será armazenada como indexed column nessa tabela;

[cdc].[lsn_time_mapping] – Armazena todo o mapeamento de alterações ocorridas nas tabelas de CDC através dos LSNs (Log Serial Number) de inicio e de fim;

[dbo].[systranschemas] – Como o CDC utiliza a mesma engine da replicação transacional, essa tabela também é criada ao habilitar o cdc. Para saber mais sobre ela, leia no Books Online;

[cdc].[Person_Person_CT] – Tabela que irá armazenar os dados históricos das alterações (INSERTs, UPDATEs e DELETEs) que ocorrem na tabela [Person].[Person] do banco de dados AdventureWorks2014. Essa tabela é gerada através do [Nome do Schema] + _ + [Nome da Tabela] + _ + [Sufixo CT]. Uma tabela igual é criada para cada tabela do sistema transacional em cujo o CDC for habilitado.


Dentro de cada uma das tabelas de sistema responsáveis pelo armazenamento dos dados históricos das tabelas originais do sistema transacional existem, além das colunas da tabela (todas ou somente as selecionadas através do parâmetro @captured_column_list) o CDC cria mais 5 colunas de sistema. São elas:

--cdc enabled table
SELECT [__$start_lsn] 
 /* lsn in log before the DML statement */

 ,[__$end_lsn]
 /* lsn in log after the DML statement */
 
 ,[__$seqval] 
 /* used to order the row changes within a transaction */

 ,[__$operation] 
 /* Identifies the data manipulation language (DML) operation associated with the change. Can be one of the following:
 1 = delete;
 2 = insert;
 3 = update (old values) Column data has row values before executing the update statement;
 4 = update (new values) Column data has row values after executing the update statement. */

 ,[__$update_mask] 
 /* The column _$update_mask shows, via a bitmap, which columns were updated in the 
 DML operation that was specified by _$operation. If this was a DELETE or INSERT operation, 
 all columns are updated and so the mask contains value which has all 1’s in it. 
 This mask is contains value which is formed with Bit values. */
 
 ,[BusinessEntityID]
 ,[PersonType]
 ,[NameStyle]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
 ,[Suffix]
 ,[EmailPromotion]
 ,[rowguid]
 ,[ModifiedDate]
FROM [cdc].[Person_Person_CT] 

[__$start_lsn] – Armazena o Log do início da transação;

[__$end_lsn] – Armazena o Log do fim da transação;

[__$seqval] – ID responsável por ordenar as linhas adicionadas ou alteradas dentro de cada transação;

[__$operation] – Indica qual DML ocorreu para cada linha de transação. Os códigos são: 1 para DELETE, 2 para INSERT, 3 para valor anterior ao UPDATE e 4 para valor posterior ao UPDATE;

[__$update_mask] – Mostra, via bitmap, qual(is) coluna(s) foi(rão) alterada(s) na operação DML. Se INSERT ou DELETE todas as colunas serão mostradas.


Além das tabelas de sistema de CDC, para o funcionamento da engine do CDC é necessária a criação de dois jobs dentro do serviço do AGENT do SQL Server. São eles:

[cdc.AdventureWorks2014_capture] – Responsável pela captura dos dados alterados dentro do Log de transação;

[cdc.AdventureWorks2014_cleanup] – Responsável pela deleção dos armazenados dentro das tabelas de histórico do CDC de tempos em tempos;

Por padrão, esses dois jobs são configurados com valores default, porém, usando a procedure de sistema sys.sp_cdc_change_job é possível alterar os valores default e configurá-los de maneira mais assertiva para cada ambiente e/ou situação.


Você aprenderá mais sobre a procedure sys.sp_cdc_change_job responsáveis por alterar os jobs do CDC e cada um de seus parâmetros de configuração no próximo post da série.

Um comentário sobre “CDC – Parte 03

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

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