13 Agosto 2018

CDC – Change Data Capture

Neste artigo iremos abordar uma solução apresentada no SQL Server 2008, o Change Data Capture (CDC), que permite obter dados de arquivo/log, sem necessidade de programação adicional.

Introdução

Existem duas formas de inserir/atualizar dados num Data Warehouse. A primeira abordagem consiste em inserir todo o universo de dados sempre que se queira atualizar o Data Warehouse. A segunda abordagem consiste em efetuar uma primeira inserção de todos os dados, seguida de inserções apenas com as atualizações.

A primeira opção, ainda que realizada de forma parcial em algumas tabelas, não é prática de executar, nomeadamente devido ao elevado volume de dados que poderia implicar.

Na inserção incremental, poderá ser necessário criar um mecanismo para monitorizar as alterações de dados realizadas nas tabelas fonte, para que apenas esses dados sejam considerados e inseridos no Data Warehouse.

Noutros casos, nomeadamente aplicações, por vezes é necessário que fiquem registadas as alterações a dados de uma base de dados, seja devido aos requisitos, ou apenas por uma questão de auditoria. Ou seja, é necessário guardar todo o histórico de alterações de determinadas tabelas.

Para implementar esse registo de alterações, é frequente implementar-se uma variedade de soluções como sejam os triggers, colunas de “timestamp” e queries e stored procedures complicadas, para auditar os dados.

Com o SQL Server 2005, surgiram novas funcionalidades com triggers “after update”, “after insert” e “after delete”, que ajudaram a resolver o problema de registo de alterações nos dados.

Uma solução melhor foi apresentada no SQL Server 2008, o Change Data Capture (CDC). O CDC permite obter dados de arquivo/log, sem necessidade de programação adicional. Com o CDC, é possível obter as alterações de dados ocorridas nas tabelas e guardar em tabelas relacionais, onde são facilmente acessíveis utilizando scripts T-SQL.

Quando se aplica a funcionalidade de Change Data Capture a uma tabela, uma cópia da tabela é criada com a mesma estrutura da tabela original, mas com mais algumas colunas que incluem os metadados para identificar a alteração realizada e os dados alvo dessa mesma alteração.

Ativar o Change Data Capture

1.1. ATIVAR O CDC NUMA BASE DE DADOS

Antes de iniciar a utilização do CDC, é necessário ativar a funcionalidade na respetiva base de dados.
O query seguinte devolve a listagem de bases de dados, com a respetiva indicação se o CDC está ativado, ou não.

change data capture

 

Para ativar o CDC numa base de dados, será necessário executar o seguinte script na respetiva base de dados. Por exemplo, para a base de dados AdventureWorks2008R2.

change data capture

 

1.2. OBJETOS CRIADOS COM A ATIVAÇÃO DO CDC NA BASE DE DADOS
Adicionalmente, e de forma automática, na base de dados AdventureWorks2008R2, serão criados alguns objetos:

1.1.1. SCHEMAS
Automaticamente é criado o schema cdc.

change data capture

 

1.1.2. USERS
Automaticamente é criado o user cdc.

change data capture

 

1.1.3. SYSTEM TABLES
São criadas as seguintes tabelas:

change data capture

 

• cdc.captured_columns – Esta tabela tem um registo por cada coluna da tabela visada. Por defeito, todas as colunas das tabelas afetadas pelo CDC deverão ficar registadas, no entanto, algumas colunas poderão ser incluídas ou excluídas quando a tabela fonte é ativada para o CDC e apenas algumas colunas são especificadas.
• cdc.change_tables – Esta tabela tem um registo por cada tabela ativada pelo CDC, na base de dados.
• cdc.ddl_history – Esta tabela tem um registo por cada comando (Data Definition Languange - DDL) executado sobre as tabelas afetadas pelo CDC. Esta tabela poderá ser usada para identificar que alterações foram realizadas sobre a tabela fonte e em que momento.
• cdc.index_columns – Esta tabela tem um registo por cada coluna indexada na tabela TCDC. As colunas indexadas são utilizadas pelo CDC para identificar registos na tabela fonte. Por defeito, as colunas com chave primária são incluídas. No entanto, se for criado um unique index na tabela fonte, quando o CDC está ativado, as colunas desse índice passarão a ser utilizadas. Será necessária a existência de uma primary key ou de um unique index, se a opção net change tracking for ativada.
• cdc.lsn_time_mapping – Esta tabela tem um registo por cada transação com registos na tabela TCDC. Esta tabela é usada para mapear entre os valores registados no log sequence number (LSN), tabela cdc._CT, e a data/hora da transação. Os registos podem também ser utilizados para verificar ausências de novas transações na tabela. Permite à tabela registar o término de processamentos LSN em períodos de pouca ou nenhuma atividade.

1.3. ATIVAR O CDC NUMA TABELA DA BASE DE DADOS
A funcionalidade de CDC pode ser aplicada ao nível das tabelas, em qualquer base de dados que tenha a funcionalidade CDC ativada, conforme indicado acima. A funcionalidade terá que ser ativada para as tabelas que se pretendam monitorizar.

O query seguinte devolve a listagem de tabelas da base de dados, com a respetiva indicação se o CDC está ativado, ou não.

change data capture

 

Pode-se executar o seguinte Stored Procedure para ativar o Change Data Capture (CDC). No entanto, é preciso ter em atenção que o SQL Server Agent deverá estar ativado. Se o SQL Server Agent não estiver ativado, o Stored Procedure não é executado e é apresentada a seguinte mensagem:

change data capture

 

Com o SQL Server Agent ativado, a mensagem será a seguinte:

change data capture

 

Para a execução da Stored Procedure, utilizámos os seguintes parâmetros, no entanto existem mais alguns, opcionais:

• @source_schema – é o schema a que pertence a tabela. Não poderá ser NULL.
• @source_name – é o nome da tabela fonte, onde se ativará o CDC. Não poderá ser NULL.
• @role_name – é o nome da role da base de dados, para permitir acesso aos dados modificados. O @role_name deverá ser sempre especificado, mesmo que seja NULL. Se for NULL, não será atribuída nenhuma role, e não haverá restrições no acesso aos dados.
• @captured_column_list – não foi usado no script acima, pois é opcional. Permite identificar as colunas a serem monitorizadas pelo CDC. É uma lista do tipo nvarchar(max), com os nomes das colunas separados por vírgulas. Se tiver o valor NULL, todas as colunas da tabela serão incluídas.

Como estamos a utilizar a base de dados AdventureWorks2008R2, são criados dois jobs, com os seguintes nomes:

change data capture

 

• cdc.AdventureWorks2008R2_capture – quando o job é executado, corre o stored procedure sys.sp_MScdc_capture_job, o qual internamente executa o stored procedure sys.sp_cdc_scan. Este stored procedure não pode ser executado explicitamente quando a operação de CDC já está ativa ou quando a base de dados esteja ativada para replicação. Este stored procedure, ativa o SQL Server Agent o qual ativa a funcionalidade de CDC.
• cdc.AdventureWorks2008R2_cleanup – quando o job é executado, corre o stored procedure sys.sp_MScdc_cleanup_job. Este stored procedure de sistema, limpa as tabelas de alterações da base de dados.

Após terminar com sucesso, verifica-se que foi criada uma nova tabela de sistema, com o nome cdc.HumanResources_Shift_CT. Esta tabela irá receber todas as alterações a dados da tabela HumanResources.Shift.

Se analisarmos esta tabela, encontraremos cinco colunas adicionais, relativamente à tabela fonte:

• __$start_lsn – Log Sequence Number (LSN) associado à transação da alteração de dados. Todas as alterações efetuadas no mesmo lote (commit), terão o mesmo LSN. Por exemplo, se for executado um delete de dois registos, em simultâneo, a tabela terá duas linhas, ambas com o mesmo LSN.
• __$end_lsn – coluna não utilizada, a partir do SQL 2012 tem sempre o valor NULL. Não é garantida compatibilidade futura, pois poderá ser excluída em futuras versões.
• __$seqval – valor sequencial utilizado para ordenar os registos inseridos, que pertencem a uma mesma transação e, portanto, que partilham o mesmo LSN.
• __$operation – identifica a operação realizada (DML – Data Manipulation Language) e associada à alteração dos dados originais.

Poderá ter um dos seguintes valores:
1 – Delete
2 – Insert
3 – Update (valor original, antes do Update)
4 – Update (novo valor, após o Update)

• __$update_mask – é um bit mask que identifica as colunas que sofreram alteração.

Exemplo de Change Data Capture

Vamos testar esta funcionalidade executando os comandos SQL de INSERT, UPDATE e DELETE na tabela acima ativada para o CDC: HumanResources.Shift.

Antes de iniciar os testes, vamos só verificar o conteúdo das tabelas em causa.

change data capture

 

A tabela original, HumanResources.Shift, tem três registos, enquanto que a tabela cdc.HumanResources_Shift_CT está vazia. Esta tabela só terá registos, após serem executadas operações na tabela fonte.

1.1. OPERAÇÃO INSERT
Vamos executar uma operação de INSERT na tabela HumanResources.Shift.

 

Após a execução do INSERT, vamos novamente verificar o conteúdo das tabelas acima:

change data capture

 

Foi inserido um novo registo na tabela fonte HumanResources.Shift.

Entretanto, a tabela do CDC, cdc.HumanResources_Shift_CT, registou a inserção acima com o valor 2 na coluna __$operation, que significa que o registo apresentado nas colunas seguintes foi inserido.

1.2. OPERAÇÃO UPDATE
Para demonstrar os efeitos de um UPDATE na tabela HumanResources.Shift, vamos atualizar o novo registo, inserido acima.

change data capture

 

Mais uma vez, vamos validar as tabelas source, HumanResources.Shift, e do CDC, cdc.HumanResources_Shift_CT.

change data capture

 

As operações de UPDATE resultam sempre em duas entradas diferentes na tabela de registo do CDC.

Uma entrada indica o valor antigo, do registo, antes do UPDATE ter sido executado: __$operation = 3.

A outra entrada indica o novo valor do registo, depois do UPDATE ter sido executado: __$operation = 4.

De notar que a coluna __$start_lsn tem o mesmo valor para os dois registos, pois correspondem ao mesmo batch.

Neste caso, e porque se trata de um UPDATE, também a coluna __$seqval tem o mesmo valor para os dois registos, pois trata-se de uma só operação sobre o mesmo registo, mas que se decompõe em dois registos, o antes e o depois.

O mecanismo Change Data Capture, captura sempre todas as colunas da tabela, exceto quando é definido explicitamente quais as colunas a capturar.

1.3. OPERAÇÃO DELETE
Para demonstrar os efeitos de um DELETE na tabela HumanResources.Shift, vamos eliminar um registo, inserido acima.

change data capture

 

Eliminaremos o registo com ShiftID=5:

change data capture

 

De seguida, poderemos validar as tabelas:

change data capture

 

O SQLServer Agent não se encontrava ativado e por essa razão, não foi efetuado qualquer registo do comando acima: DELETE.

change data capture

 

Vamos então ativar o SQLServer Agent, e inserir um novo registo, para depois o eliminarmos e verificar o CDC:

change data capture

 

 

 

 

 

Neste momento, temos o ShiftID=6, o qual iremos eliminar e verificar o que ficou registado no CDC:

change data capture

 

 

 

Conforme se verifica acima, foi inserido um novo registo para o ShiftID=6, com __$operation = 1, que como foi referido anteriormente, corresponde à eliminação de registos das tabelas.

Conclusão

> Ativando a funcionalidade Change Data Capture, CDC, é possível registar um histórico das transações realizadas sobre tabelas e/ou colunas para as quais tenha sido ativado o CDC.

> Esta foi a apresentação do Change Data Capture, CDC, utilizando T-SQL (SSMS), em breve apresentaremos como utilizar o CDC nos packages de Integration Services (SSIS).

change data capture

 

 

 

 

change data capture