CDC – Parte 06

Nesta etapa da série será possível aprender acerca das melhores práticas e pontos importantes para habilitação e manutenção do Change Data Capture em ambientes de Produção.


  1. Uma ótima prática de utilização do Change Data Capture é evitar habilitar a feature em colunas de uma tabela cujo não existe importância em armazenar o histórico de seus dados. Tabelas com grandes quantidades de colunas tendem a aumentar de maneira significativa a latência nas capturas das alterações;
  2. Somente habilite o suporte a Net Changes caso realmente seja necessário o seu uso. O trabalho que o CDC executa por detrás para realizar o controle dessas mudanças também prejudica a performance na utilização da feature;
  3. O Job de Cleanup dos dados históricos não possui um desempenho favorável quando executado em paralelo com o workload. Se possível, o job de Cleanup dos dados de alteração deve ser programado para horários com baixa ou nenhuma carga de trabalho;
  4. Uma das coisas mais importantes a que deve-se ter atenção no que diz respeito à captura de dados de alteração é o subsistema de log de transações. O arquivo de Log cresce significativamente quando o Change Data Capture é habilitado em um banco de dados. Além disso, registros de log permanecem ativos até o CDC processá-los. Isto significa que, especialmente em ambientes onde uma grande latência se acumula, o arquivo de Log pode crescer significativamente, porque o espaço de log não pode ser reutilizado até que o CDC execute o trabalho de carga dos dados ainda processados dentro do Log, mesmo no Recovery Mode Simple, ou mesmo depois de um backup de log no Recovery Mode Full;
  5. O Change Data Capture funciona em todos os Recovery Modes. Mas quando o CDC é habilitado, as operações que normalmente seriam minimamente logadas nos modos de recuperação Simple ou Bulk-logged são totalmente logadas para permitir ao CDC a capturar de todas as alterações;
  6. Esteja ciente que quando o disco de Log ficar cheio, você não poderá reduzí-lo fazendo um backup de log manualmente ou mesmo realizando um Shrink no arquivo até que o CDC tenha processado todas as transações;
  7. O Change Data Capture não pode processar as transações quando o disco de Log está cheio, porque a captura de dados de alteração escreve no arquivo de Log o que é realizado nas tabelas de histórico. Neste caso, a maneira mais fácil de resolver essa situação é adicionar temporariamente outro arquivo de Log em um disco diferente e deletá-lo após o CDC concluir a carga das tabelas de histórico;
  8. Planeje o crescimento do arquivo de Log. Esteja ciente que o Log File pode crescer de 200-300% a partir do tamanho original quando o Change Data Capture está habilitado na base de dados;
  9. Uma boa prática para manter o filegroup PRIMARY pequeno e para que possa-se ter uma clara distinção do que são dados transacionais e o que são dados históricos, é habilitar o CDC em um filegroup diferente;

No próximo post da série você começará a aprender sobre os componentes do MS Integration Services que são utilizados para trabalhar com o Change Data Capture, utilizando dessa maneira os dados históricos como fonte para cargas de Data Warehouses (DWs) e Operational Data Stores (ODSs).

Referência para o post – Tuning the Performance of Change Data Capture in SQL Server 2008.

Anúncios

CDC – Parte 05

Nesta etapa da série será possível entender o funcionamento das TVFs (Table Valued Function) denominadas [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…] e também da IF (Inline Table-Valued Function) denominada [sys].[fn_cdc_map_time_to_lsn] usada para transformar o conteúdo de data em LSN (Log Serial Number). Essas funções são usadas para manipulação dos dados contidos nas tabelas de CDC.

Um outro ponto importante com relação ao entendimento dessas funções é que elas são usadas internamente pelo Integration Services dentro do componente de origem de dados do CDC. Ou seja, seus conceitos são um ponto muito importante mais tarde, quando estivermos gerando as cargas de ETL.


Como foi explanado na Parte 3 do conjunto de posts, o CDC cria dentro das tabelas de histórico algumas colunas onde, através delas, é possível realizar o controle de versão dos registros com base nos LSNs do Log de Transação.

Trabalhar com base no log é complexo, dessa forma, é possível que realizemos a conversão de LSN em data através da IF [sys].[fn_cdc_map_time_to_lsn]. Essa função captura a data desejada com base em algums parâmetros e transforma internamento esse range de período no LSN correto a ser interpretado pela função [cdc].[fn_cdc_get_all_changes_…] ou [cdc].[fn_cdc_get_net_changes_…].

Para trabalhar com a função [sys].[fn_cdc_map_time_to_lsn] é necessário entender acerca de sues parâmetros que são:

@relacional_operator – É usado para identificar um valor de LSN distinto dentro da tabela cdc.lsn_time_mapping com um tran_end_time associado que satisfaça a relação quando comparado ao valor @tracking_time (BOL). Dentro desse parâmetro é possível usar os seguintes valores:

‘smallest greater than’ ou ‘smallest greater than or equal’, onde:

smallest

‘largest less than’ ou ‘largest less than or equal’, onde:

largest

@tracking_time – É o valor de data e hora ao qual corresponder. tracking_time é datetime (BOL).


Agora que compreendida a Função [sys].[fn_cdc_map_time_to_lsn] você irá iniciar o entendimento dos parâmetros das funções [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…], onde cada uma delas possui os seguintes parâmetros:

@from_lsn – LSN de início da transação;

@to_lsn – LSN de fim da transação;

@row_filter_option – Rege o conteúdo das colunas de metadados, bem como as linhas retornadas no conjunto de resultados.

Para trabalhar com ambas as functions para manipulação de registros é necessário usar o código abaixo. Lembrando que, conforme foi dito na Parte 02 do conjunto de posts,  o suporte a Net Changes somente é habilitado caso o parâmetro @supports_net_changes seja preenchido como um na hora de habilitar o CDC para tabela. Caso não, a função [cdc].[fn_cdc_get_net_changes_…] não será criada.


-- ===========================
-- checking get_all funtion --
-- ===========================

USE AdventureWorks2014
GO

-- function all change - Used for capture all changes in tables with cdc excluding the last value in update modification
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all update old');
-- the parameter <row_filter_options> may have three values: ALL and ALL UPDATE OLD

-- ===========================
-- checking get_net funtion --
-- ===========================

USE AdventureWorks2014
GO
-- function net change - Used for capture the last value of the data, dispensing information of the update and delete statements
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE() -1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all with mask');
-- the parameter <row_filter_options> may have three values: ALL, ALL WITH MASK (return the final LSN of the row) and ALL WITH MERGE

Primeiramente execute os comandos abaixo que irão Inserir e Deletar alguns registros na tabela Person.Person e, também alguns Updates nos seus dados:

USE AdventureWorks2014
GO

-- ====================================
-- ----- insert operation test --------
-- ====================================

INSERT INTO [Person].[BusinessEntity]
 ([rowguid]
 ,[ModifiedDate])
VALUES ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()), 
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

INSERT INTO [Person].[Person]
 ([BusinessEntityID]
 ,[PersonType]
 ,[NameStyle]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
 ,[Suffix]
 ,[EmailPromotion]
 ,[AdditionalContactInfo]
 ,[Demographics]
 ,[rowguid]
 ,[ModifiedDate])
VALUES (20778,'IN',0,NULL,'Arthur','J','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()),
 (20779,'IN',0,NULL,'Heitor','D','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 (20780,'IN',0,NULL,'Ulisses','L','Silva Neto',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 (20781,'IN',0,NULL,'Raquel','F','Nascimento',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 (20782,'IN',0,NULL,'Miguel','C','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 (20783,'IN',0,NULL,'Rafael','M','Guedes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 (20784,'IN',0,NULL,'Marcelo','F','Moreira',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 (20785,'IN',0,NULL,'Ana','L','Dos Santos',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 (20786,'IN',0,NULL,'Leticia','B','Mendes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 (20787,'IN',0,NULL,'Lucas','A','Cruz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

-- ====================================
-- ----- delete operation test --------
-- ====================================

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (
 20785,
 20786,
 20787
)
GO

-- ====================================
-- ----- update operation test --------
-- ====================================

UPDATE [Person].[Person] 
SET [FirstName] = 'William',
 [LastName] = 'Golçalves',
 [MiddleName] = 'D'
WHERE [BusinessEntityID] = 20783
GO

UPDATE [Person].[Person] 
SET [Title] = 'Sr.'
WHERE [BusinessEntityID] = 20778
GO

UPDATE [Person].[Person] 
SET [PersonType] = 'SC'
WHERE [BusinessEntityID] = 20782
GO

UPDATE [Person].[Person]
SET [PersonType] = 'SC',
    [Title] = 'Sr.'
WHERE [BusinessEntityID] IN (1,2,3,4)
GO

Para facilitar o entendimento, para cada uma das opções de @row_filter_option de cada uma das TVFs, você usará um dos códigos abaixo (partindo do pressuposto de que o ambiente já foi criado seguindo os passos anteriores do conjunto de posts):

[fn_cdc_get_all_changes_…] – Value ALL

Usado para retornar todos registros da tabela de histórico do CDC eliminando os registros que possuem o valor 3 no campo [__$operation] pois estes registros são os valores anteriores ao valor atual do update (vide Parte 3 do conjunto de posts).

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

01

[fn_cdc_get_all_changes_…] – Value ALL UPDATE OLD

Usado para retornar todos registros da tabela de histórico do CDC incluindo os registros que possuem o valor 3 no campo [__$operation].

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all update old
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all update old'); 
GO

02


[fn_cdc_get_net_changes_…] – Value ALL

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico. Por exemplo, caso um registro tenha sido inserido, sofrido um update e depois deletado, a query abaixo irá mostrar apenas o registro deletado.

A coluna [__$update_mask] aparece com o valor NULL.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

03

[fn_cdc_get_net_changes_…] – Value ALL WITH MASK

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico assim como na situação anterior, porém, nesse caso, quando a coluna [__$operation] retornar o valor 4 (novo valor de um update), temos a coluna [__$update_mask] preenchida com a máscara da ultima atualização.

Você entenderá melhor sobre a utilização dessa coluna mais abaixo neste post.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all with mask
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with mask'); 
GO

04

[fn_cdc_get_net_changes_…] – Value ALL WITH MERGE

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico, porém realiza um merge entre os registros Inseridos e Atualizados mostrando, neste caso, para ambas as situações, a coluna [__$operation] com valor igual a 5. O valor igual a 1 é usado pra deleção.

USE AdventureWorks2014
GO

--delete registry existing before cdc initialization
ALTER TABLE [Person].[EmailAddress]
DROP CONSTRAINT [FK_EmailAddress_Person_BusinessEntityID]
GO

ALTER TABLE [HumanResources].[Employee]
DROP CONSTRAINT [FK_Employee_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[Password]
DROP CONSTRAINT [FK_Password_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[PersonPhone]
DROP CONSTRAINT [FK_PersonPhone_Person_BusinessEntityID]
GO

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (10,11,12)
GO
-- get_all_changes with row filter options equal all with merge
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with merge'); 
GO

Capture

Observer que para os registros de [BusinessEntityID] igual a 20785, 20786 e 20787 da tabela [Person].[Person] que foram inseridos hoje e também deletados hoje, ao realizar a operação de merge de acordo com o Range de data que foi passado (de ontem para hoje), o CDC desconsidera a operação de INSERT e nem o apresenta no retorno do SELECT. É como se esse registro não houvesse sido inserido.

Porém, para os registros 10, 11 e 12 que já faziam parte da tabela, ele mostra no select para a coluna [__$operation] o valor 1.


A coluna [__$update_mask] é responsável por “armazena uma máscara de bits com base nos ordinais de coluna da tabela de alteração que identificam as colunas que foram alteradas” (BOL).

Em outras palavras, através dela é possível descobrir quais colunas foram alteradas dentro de uma instrução de UPDATE. Caso a instrução seja de DELETE ou INSERT todas as colunas serão apresentadas.

No código será possível verificar quais registros, de acordo com o range de data fornecido, sofreram alterações de UPDATE ([__$operation] = 4) na coluna [Title] da tabela [Person].[Person]:

-- =======================================
-- understand the column __$update_mask --
-- =======================================

DECLARE @StartDate datetime = GETDATE()-1;
DECLARE @EndDate datetime = GETDATE();
DECLARE @begin_lsn BINARY(10); 
DECLARE @end_lsn BINARY(10);

-- Set the LSN values
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @StartDate);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @EndDate);

-- get all updated rows where Title was updated
SELECT *
FROM cdc.fn_cdc_get_all_changes_Person_Person(@begin_lsn, @end_lsn, 'all')
WHERE __$operation = 4 
 AND sys.fn_cdc_is_bit_set (
 sys.fn_cdc_get_column_ordinal ('Person_Person','Title'),__$update_mask
 ) = 1
GO

Capture2

Já usando o código abaixo será possivel identificar quais colunas foram alteradas para cada um dos registros da tabela de histórico do CDC capturando somente aqueles que possuem valor de [__$operation] = 4 (todos os valores de UPDATE).

-- =======================================
-- understand the column __$update_mask --
-- =======================================

-- capture all columns changed in each transaction
SELECT ( SELECT CC.column_name + ','
 FROM cdc.captured_columns CC
 INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
 WHERE capture_instance = 'Person_Person'
 AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
 PD.__$update_mask) = 1
 FOR
 XML PATH('')
 ) AS ChangedColumns,
 *
FROM cdc.Person_Person_CT PD
WHERE PD.__$operation = 4
GO

Capture3


No proximo post da série será possível aprender sobre as melhores práticas e observações para a utilização do Change Data Capture. Após isso, iniciaremos a etapa de construção de ETLs para consumo da feature em cargas de dados de Data Warehouse (DWs) e/ou Operational Data Stores (ODSs).

CDC – Parte 04

Neste post da série você aprenderá sobre a procedure de sistema  sys.sp_cdc_change_job e seus parâmetros usada para alterar os jobs de captura e limpeza dos dados nas tabelas de histórico do Change Data Capture.


A procedure sys.sp_cdc_change_job é usada para configurar os jobs [_capture] [_cleanup] que são responsáveis pelas rotinas de captura e cleanup das tabelas de CDC. essa sp possui alguns parâmetros. São eles:

@job_type – Identifica qual o job que será alterado/configurado. Parâmetro obrigatório;


Para o Job de Captura de dados existem os seguintes parâmetros de configuração:

@maxtrans – Fornece o número máximo de transações a processar em cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 500 transações por ciclo;

@maxscans – Fornece o número máximo de ciclos de scan a serem executados para captura de todos os registros do log de transação. Esse parâmetro só pode ser utilizado para o job de captura O valor inicial é de 10 ciclos de scan;

@pollinginterval Quantidade de segundos entre cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 5 segundos de espera entre cada ciclo;

@continuous – Indica se o job de captura de dados do Log terá execução continua ou não.

Quando definido como 1, o job examina o log e processa até (max_trans * max_scans) transações. Em seguida, aguarda o número de segundos especificados no polling_interval antes de começar um novo ciclo de verificações.

Se definido como 0, a execução do job ocorrerá (max_trans * max_scans) e depois sairá do job. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é 1;

A arquitetura do job de captura funciona conforme figura abaixo:

Capturar

É importante ao utilizar o Change Data Capture que você faça uma verificação, tanto de Hardware quanto de Volumetria de dados de alteração para poder otimizar o seu ambiente. A latência de captura das alterações e a quantidade de transações capturadas por segundo são dois pontos de suma importância a serem observados.

Sobre isso, deixo como abaixo um dos exemplos realizados Steffen Krause em um post escrito para o TechNet em 2008 onde uma comparação é realizada entre dois tipos de configuração do job de captura.

Imagem1
Padrão – @continuous = 1, @maxscans = 10, @maxtrans = 500 e @pollinginterval = 5
Imagem2
Modificado – @continuous = 1, @maxscans = 100, @maxtrans = 5000 e @pollinginterval = 5
Imagem3
Outras possibilidades de configuração e observações realizadas nos testes.

Para maiores informações sobre configurações avançadas do Change Data Capture, leia o post Tuning the Performance of Change Data Capture in SQL Server 2008 do Steffen Krause na íntegra. 

As informações demonstradas acima estão relacionadas ao hardware usado no artigo.


Para o Job de Cleanup existem os seguintes parâmetros de configuração:

@retention Número em minutos de armazenamento dos dados de alteração nas tabelas de histórico do CDC antes que a limpeza desses dados ocorra. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 4320 minutos (3 dias);

@threshold Número máximo de exclusões que podem ser efetuadas para cada operação de cleanup de dados. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 5000 deleções;


Para consultar as configurações atuais dos Jobs [_capture] e de [_cleanup] de dados use a tabela msdb.dbo.cdc_jobs.

Abaixo um exemplo de execução da procedure sys.sp_cdc_change_job:


-- ===================================
-- changes in the CDC configuration --
-- ===================================
USE AdventureWorks2014
GO

-- Used for to verify the current configuration inside the jobs
SELECT *
FROM msdb.dbo.cdc_jobs

/* Case the change data capture is enabled in database 
 with transactional replication, the jobs of the replication are used */

-- Jobs
EXEC sys.sp_cdc_change_job @job_type = 'capture'
 ,@maxtrans = 5000 -- number of transactions read between each cicle
 ,@maxscans = 10 -- number of cycles between each polling interval 
 ,@continuous = 1 -- enable the continuous execution of the work of the capture 
 ,@pollinginterval = 1 -- Interval in seconds between each cycle of the log read

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
 ,@retention = 10080 -- time in minutes of retention of the dates in cdc tables (default is 3 days)

No próximo post da série você aprenderá sobre as functions [_get_all_changes] e [_get_net_changes] para manipular os dados salvos dentro das tabelas de histórico do Change Data Capture.

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.

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.

CDC – Parte 01

“O Change Data Capture foi projetado para capturar inserções, atualizações e excluções aplicadas em tabelas do SQL Server e disponibilizar os detalhes das mudanças disponíveis em um formato relacional facilmente consumível.

As tabelas de alterações usadas pelo Change Data Capture contêm colunas que refletem a estrutura de colunas de uma tabela de origem controlada, junto com os metadados necessários para entender as mudanças ocorridas.” (Microsoft Books Online)

O Change Data Capture ou CDC atua nas tabelas selecionadas de um banco de dados específico como uma trigger before que dispara as inserções e/ou alterações realizadas no objeto para o qual ela está apontada em uma outra tabela.

A grande diferença é que a trigger usa a mesma transação realizada na tabela original para espelhar essa alteração em uma outra tabela (trabalha de forma Síncrona), podendo causar uma série de problemas para o sistema transacional (entre eles block/lock, overhead dentre outros). 

Neste ponto o Change Data Capture atua de maneira a não prejudicar o sistema transacional, realizando a captura das alterações da tabela através dos registros salvos dentro do arquivo de LOG do SQL Server após a transação original ser concluída (trabalha de maneira Assíncrona).

arquitetura_cdc

O CDC, conforme representado através da imagem que demonstra a arquitetura da feature, é geralmente utilizado para captura de dados históricos a serem usados para ambiente de Data Warehouse e/ou Operational Data Store (ODS)

Para conseguir exemplificar a sua utilização, imagine um sistema onde ocorrem várias alterações em um mesmo registro durante o dia, porém a carga de dados do DW e/ou ODS ocorre uma vez ao dia. O que ocorreria é que somente a ultima alteração desse registro seria levada para o Sistema histórico. 

O CDC atuaria, nesse caso, como um armazém de dados voláteis e momentâneos transacional utilizado para carga do armazém de dados não voláteis dimensional.


Antes de iniciarmos os testes com o Change Data Capture, será necessário que criemos o ambiente de teste.

Como base de dados relacional, usaremos dentro do SQL Server 2014 o banco de dados AdventureWorks2014

Como stage de dados (passo anterior à carga de um ODS ou DW) usaremos uma base de dados que deverá ser construída com o script abaixo:

-- =========================================
-- ------ Create Stage Enviromment ---------
-- =========================================

USE master
GO

CREATE DATABASE AdventureWorks_Stage
GO

USE AdventureWorks_Stage
GO

CREATE TABLE [dbo].[Person](
 [BusinessEntityID] INT NOT NULL,
 [PersonType] NCHAR(2) NOT NULL,
 [NameStyle] BIT NOT NULL CONSTRAINT [DF_Person_NameStyle] DEFAULT ((0)),
 [Title] NVARCHAR(8) NULL,
 [FirstName] NVARCHAR (50) NOT NULL,
 [MiddleName] NVARCHAR (50) NULL,
 [LastName] NVARCHAR (50) NOT NULL,
 [Suffix] NVARCHAR (10) NULL,
 [EmailPromotion] INT NOT NULL CONSTRAINT [DF_Person_EmailPromotion] DEFAULT ((0)),
 [rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL CONSTRAINT [DF_Person_rowguid] DEFAULT (newid()),
 [ModifiedDate] DATETIME NOT NULL CONSTRAINT [DF_Person_ModifiedDate] DEFAULT (getdate()),
 [ModifiedType] CHAR(1) NOT NULL

) ON [PRIMARY]
GO

No próximo post da série você aprenderá a criar, habilitar e/ou verificar sua existência em tabelas localizadas dentro de uma base de dados específica.