BI and Analytics Week & SQL Saturday #580 – Joinville

Pessoal, tenho estado em uma grande correria com as obrigações profissionais, porém, não poderia deixar passar a oportunidade de compartilhar o material relacionado às duas últimas palestras que fiz pela comunidade.

Realizei no dia 31/03 realizei para o BI and Analytics Summit uma sessão com o tema Por Detrás dos Relatórios de BI onde expliquei conceitos relacionados à arquitetura de um projeto de Data Warehouse e o funcionamento de um sistema de Business Intelligence do início até a criação dos relatórios, Key Performance Indicators, Scorecards e Dashboards. Ela pode ser assistida por qualquer profissional de BI ou simpatizantes com a área pois não relacionei o conteúdo com nenhuma ferramenta.

Agradeço de todo coração ao Rafael Piton pelo convite para fazer parte do grande time do evento. Caso queiram, segue abaixo o vídeo no meu canal do Youtube e o slide usado na apresentação. Have Fun!


O segundo evento do qual fiz parte foi o primeiro SQL Saturday que ocorreu no ano de 2017 no Brasil. O local foi Joinville em Santa Catarina. Foram mais de 300 pessoas participantes que puderam ouvir do grande time da comunidade sobre improvements e features relacionadas ao mundo de Data prataform e Analytics da Microsoft

Falei um pouco sobre uma nova feature do SQL Server 2016 denominada Temporal Tables. Realizei também uma comparação com a Feature Change Data Capture que vem no produto desde a release 2008.

Se interessados, é possível realizar o download do material (Slides e Scripts) clicando na imagem abaixo.

Agradeço de todo coração ao MVP em Data Plataform Rodrigo Dornel e a todo o time organizador do evento pela oportunidade de poder mais uma vez contribuir com a comunidade com informações sobre os produtos de Data Insights da Microsoft. Great Job!

Aproveito o post para convidá-los/intimá-los a participar dos próximos eventos que irão ocorrer gratuitamente na comunidade | Brasil:

  1. Dia 22/04 – Global Azure Bootcamp. Evento Gratuito que acontecerá simuntaneamente em todas as principais cidades do mundo sobre Cloud Computing.
  2. Dias 19 e 20/05 – The Amazing Data. Esse evento contará com participação do Benjamin Neverez. Ele é especialista em tuning e otimização de consulta e uma das maiores referências técnicas em SQL Server do mundo. Autor de três livros sobre o assunto:
    1. High Performance SQL Server”;
    2. SQL Server 2014 Query Tuning & Optimization”;
    3. Inside the SQL Server Query Optimizer”;
    4. Alem de ser também, co-autor do livro SQL Server 2012 Internals”.

    Para mais informações sobre o evento veja o link.

  3. Dia 24/06 – SQL Saturday | Caxias do Sul – RS.
  4. Dia 19/08 – SQL Saturday | Brasília – DF.
  5. Dia 30/09 – SQL Saturday | São Paulo – SP.
  6. Dia 21/10 – SQL Saturday | Rio de Janeiro – RJ.
  7. Dia 18/11 – SQL Saturday | Salvador – BA.

Espero poder vê-los ou revê-los em alguns deles!

Critical Update & Cumulative Update 2 – Resolução de Problemas no SQL Server 2016

Pessoal, ao realizar alguns testes com o SQL Server 2016 encontrei alguns erros na versão que foram corrigidos com alguns hotfix de correção e gostaria de compartilhar com vocês para ajudá-los, caso tenham o mesmo problema.


Ao habilitar o Change Data Capture em uma tabela no SQL Server é possível que você selecione as colunas dessa tabela que irão ser tracked (receberão versionamento de dados).

Porém, ao tentar habilitar o CDC em uma tabela do SQL Server 2016, recebi o erro abaixo:

error_change_data_capture_sql_2016

Para resolução do problema, a Microsoft disponibilizou o Critical Update que pode ser baixado através do seguinte Link.


Ao tentar realizar o Pin dos Dashboards do Reporting Services 2016 em um Painel no Cloud (Power BI) recebi a seguinte mensagem de erro:

“You don’t have any dashboards. You need at least one dashboard that you have permissions to pin to.”

Após muito pesquisar verifiquei em um link do suporte da Microsoft que a resolução do problema fora realizada no Cumulative Update #1 da ferramenta. Para Download e mais informações sobre demais correções dos Cumulative Updates #1 e #2 acesse o Link Link respectivamente.

Espero poder ajudá-los!

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.

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.