Change Tracking – Overview

Uma alternativa para quem quer monitorar INSERT/UPDATE/DELETE em tabelas do SQL Server 2008, até mesmo na edição EXPRESS, é o Change Tracking. Mas o que difere ele das outras features, como SQL Server Audit e Change Data Capture? Além do fato dele funcionar até na edição EXPRESS, ele monitora especificamente a “versão”, a chave primária e a ação executada (U – UPDATE, D – DELETE, I – INSERT), possibilitando também identificar qual coluna foi atualizada no caso de um UPDATE.

O Change Tracking é comumente utilizado em combinação com o Sync Framework (Sync Service for ADO.NET), visto que com o Sync Framework se torna bastante prático criar um modelo para sincronizar bancos de dados (SQL Server + outra solução como o SQL Server CE). Mas é possível criar uma solução sem o Sync Framework para sincronizar bancos de dados com outras plataformas ou Data Warehouse, visto que o Change Tracking possui funções que facilitam bastante este processo.

Ao se configurar esta feature, o primeiro passo é habilitar o Change Tracking no banco de dados, informando quanto tempo os dados históricos ficarão armazenados (CHANGE_RETENTION) e se estes dados serão removidos automaticamente do banco de dados (AUTO_CLEANUP).

--Habilitando o Change Tracking no banco de dados CT_Demo

ALTER DATABASE CT_Demo
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
GO

Em seguida, habilitar o Change Tracking nas tabelas a serem monitoradas. Nesta passo existe a possibilidade de configurar que sejam armazenadas informações de quais colunas que foram atualizadas ou não (TRACK_COLUMNS_UPDATED).

--Habilitando o Change Tracking na tabela Salarios

ALTER TABLE Salarios
ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO

A função CHANGE_TRACKING_CURRENT_VERSION() poder ser utilizada para identificar a atual versão do Change Tracking no banco de dados, iniciando de 0 e sendo incrementada a cada INSERT/UPDATE/DELETE nas tabelas monitoradas.

--Identfiicando a versão atual do Change Tracking no banco de dados

SELECT CHANGE_TRACKING_CURRENT_VERSION();
GO

A função CHANGETABLE(CHANGES NomeTabela, Versão), permite relacionar todas as alterações realizadas na tabela informada a partir de uma determinada versão.

--Identificando as alterações realizadas na tabela Salarios a partir da versão 0

DECLARE @SINC BIGINT
SET @SINC = 0

SELECT *
FROM CHANGETABLE(CHANGES Salarios, @SINC) AS CT
GO

A função CHANGE_TRACKING_IS_COLUMN_IN_MASK, permite identificar qual coluna foi atualizada numa operação de UPDATE.

--Identificando se a coluna Salario ou coluna Empregado foi atualizada numa operação de UPDATE na tabela Salarios

DECLARE @SINC BIGINT
SET @SINC = 0

DECLARE @SalarioColumnId INT
SET @SalarioColumnId = COLUMNPROPERTY(
		OBJECT_ID('Salarios'),
		'Salario',
		'ColumnId'
	)

DECLARE @EmpregadoColumnId INT
SET @EmpregadoColumnId = COLUMNPROPERTY(
		OBJECT_ID('Salarios'),
		'Empregado',
		'ColumnId'
	)

SELECT
SalarioAlterado = CHANGE_TRACKING_IS_COLUMN_IN_MASK(@SalarioColumnId, CT.SYS_CHANGE_COLUMNS),
EmpregadoAlterado = CHANGE_TRACKING_IS_COLUMN_IN_MASK(@EmpregadoColumnId, CT.SYS_CHANGE_COLUMNS),
CT.*
FROM CHANGETABLE(CHANGES Salarios, @SINC) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'
GO

Podem aguardar que em um próximo artigo apresentarei um cenário completo com o Change Tracking.

Anúncios

7 pensamentos sobre “Change Tracking – Overview

  1. Bom Dia Paulo.

    Eu tentei usar o CHANGE_TRACKING mas obtive o erro abaixo:

    Comando usado:
    ALTER DATABASE Cor
    SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
    GO

    Msg Erro:
    Mensagem 5011, Nível 14, Estado 5, Linha 1
    O usuário não tem permissão para alterar o banco de dados ‘Cor’, o banco de dados não existe ou o banco de dados não está em um estado que permita o acesso às verificações.
    Mensagem 5069, Nível 16, Estado 1, Linha 1
    Falha na instrução ALTER DATABASE.

  2. Pingback: Change Tracking – Demo « SQL From Hell.com

  3. Pingback: Centro de Treinamento SQL Server 2008 R2 « SQL From Hell.com

  4. Boas. Uma pergunta, há existe alguma forma de activar o change_tracking de todas as tabelas de uma base de dados de uma só vez ou tem que ser uma a uma?
    obrigado e cumprimentos

    • Bom dia Diogo, você pode optar por criar um script usando a “sys.tables” para relacionar as tabelas do banco de dados e habilitar o change tracking de cada tabela de uma forma mais prática.

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 )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s