o rastreamento de mudanças para o servidor SQL é uma tecnologia flexível e fácil de usar para tabelas de monitoramento para inserções, atualizações e apagamentos. Neste post, eu discutirei começar com o rastreamento de mudanças no servidor SQL, e mostrarei um exemplo de como começar com ele.
Change Tracking in SQL Server
Change tracking is a lightweight mechanism for tracking which rows have been inserted, updated, and deleted in tables monitored by change tracking. O rastreamento de mudanças apareceu pela primeira vez no SQL Server 2008 e tem estado em todas as versões desde então. Ainda melhor, o monitoramento de mudanças está disponível em todas as edições do servidor SQL, até mesmo a edição free express.Em poucas palavras, é assim que funciona .: Em tabelas que têm o rastreamento de mudanças ativado, a mudança líquida para cada linha é rastreada internamente e é acessível através de funções de rastreamento de mudanças. Cada alteração tem um ID de versão anexado a ele, e haverá um ID de versão nova para cada linha que é inserida, atualizada ou apagada. A versão de rastreamento de mudanças é um inteiro de 8 bytes (BIGINT) que reflete o ID de mudança mais recente naquele banco de dados. É importante notar que a versão de acompanhamento de alterações não é específica da tabela – uma operação DML em qualquer tabela rastreada em cada banco de dados irá aumentar o número da versão. O número da versão será sempre sequencial, mas não será necessariamente contíguo dentro de uma única tabela se houver mais de uma tabela ativada para rastreamento de mudanças.
o acompanhamento das alterações está activo ao nível da base de dados. Depois disso, cada tabela que será monitorada deve ser alistada individualmente no rastreamento de mudança. Cada tabela a ser monitorada pelo rastreamento de mudanças deve ter uma chave primária, uma vez que este é o identificador de nível de linha usado para relatar as operações DML dentro do rastreamento de mudanças. Ao activar o seguimento de alterações ao nível da tabela, pode optar por seguir A(S) coluna (s) alterada (s) na actualização mais recente, o que lhe dará maior visibilidade no que foi alterado.
uma vez configurado, o uso de rastreamento de mudanças é um processo relativamente simples. Existem algumas funções-mais notavelmente, CHANGE_TRACKING_ CURRENT_VERSION () e CHANGETABLE (), que podem ser usadas para verificar o carimbo da versão atual no acompanhamento de alterações e obter a lista de alterações recentes. Vou demonstrar as duas funções em breve.
o acompanhamento de alterações não é o registo de auditorias
vou ter o cuidado de não usar as palavras auditoria ou registo para descrever o acompanhamento de alterações. Permitam-me que seja claro: este não é um mecanismo de registo completo. O histórico de mudanças não é rastreado em todo-o rastreamento de mudanças apenas relata o fato de que uma mudança ocorreu, mas não retém o histórico de versões. Considere o caso de uma linha de dados com um ID de 1234. Essa linha é inserida, então atualizada 5 vezes, e então apagada. Alterar o seguimento não iria mostrar o histórico de inserir, actualizar e apagar; em vez disso, reportaria apenas a alteração líquida, que a linha ID 1234 foi suprimida. Se o seu processo de carga requer histórico de registro detalhado para cada mudança (em vez de apenas o delta de todas as alterações), você teria que usar algo como captura de dados de mudança.
configurar o seguimento de alterações no servidor SQL
permitir o seguimento de alterações ao nível da tabela é um processo em duas fases. Primeiro, deve ser ativado na base de dados. Isto pode ser feito através da IU nas propriedades do banco de dados, na página de acompanhamento de alterações.
como mostrado, não há muito a configurar ao activar o seguimento de alterações numa base de dados. Basta definir o valor de rastreamento de mudança para True para configurar o rastreamento de mudança para esse banco de dados. Opcionalmente, o valor do período de retenção também pode ser alterado. O valor padrão é de 2 dias, que eu tenho substituído neste exemplo para usar 14 dias em vez disso. Tal como acontece com a maioria das operações UI, existe um comando T-SQL para fazer a mesma coisa. O comando para configurar o seguimento de alterações nesta base de dados está em baixo.
após este passo, o rastreamento de mudança é ativado, mas ainda não está rastreando nada. Ele ainda tem que ser habilitado para que cada tabela seja rastreada. A interface de Propriedades da tabela torna isto muito fácil.
como mostrado, simplesmente mudando o valor de rastreamento de mudanças para verdadeiro permite o rastreamento de mudanças para esta tabela. Neste exemplo, eu também optei por rastrear as colunas alteradas durante as atualizações (mais sobre isso em um pouco).
o último passo acima seria repetido para cada tabela a ser rastreado no rastreamento de mudanças. Uma vez que a mudança de rastreamento está ativada, quaisquer alterações (inserções, atualizações ou apagamentos) a essa tabela serão armazenados na cache de rastreamento de mudança.Para o exemplo acima, vou inserir, atualizar e excluir alguns dados para demonstrar como acessar os dados de rastreamento de mudanças gerados para essas operações DML. Para referência, aqui está a estrutura da tabela.
I shower how to enable change tracking for a single table using the UI. Eu prefiro usar T-SQL para esta tarefa, uma vez que é mais fácil de repetir. Activar o seguimento de alterações para a tabela i criada acima pode ser feito como mostrado aqui:
lembre-se que mencionei anteriormente que o seguimento de alterações usa um ID de versão para seguir a versão actual das tabelas seguidas. Esse ID de versão é o nosso marcador cronológico para detectar mudanças. Para obter esse valor, existe uma função muito simples: CHANGE_TRACKING_ CURRENT_VERSION(). É usado como mostrado abaixo.
no meu sistema de teste, este valor é 470 (desde que eu fiz vários testes antes desta escrita). Este é o ponto de partida, e quaisquer alterações feitas a partir deste ponto em diante desencadeariam um novo número de versão. Vou fazer uma nota desse valor, e agora vou fazer algumas mudanças na tabela descrita acima. Vou inserir um punhado de linhas para mostrar como mudar as inserções de rastreamento.
depois de inserir estas seis linhas, verifico o valor CHANGE_TRACKING_ CURRENT_VERSION() de novo e descubro que o valor é agora 476. Foi aumentado em 6-1 por linha inserida, que é o que eu esperava.
usando funções de rastreamento de mudanças
em seguida, vamos usar a função de rastreamento de mudanças CHANGETABLE() para mostrar as mudanças líquidas nesta tabela.
:
- CHANGETABLE é a função do sistema de valores de tabela que irá devolver a lista de alterações armazenadas no seguimento de alterações
- as alterações indicam que estou à procura das alterações, uma vez que a versão especificada
- @ver é a variável que eu configurei para armazenar o número da versão. CHANGETABLE retornará todos os resultados refletindo as mudanças desde esta versão. Note que você pode usar uma variável como eu fiz, ou apenas passar em um número escalar (usando o literal 470 aqui teria realizado a mesma coisa)
Quando eu executar o código acima, eu recebo o seguinte conjunto de resultados.
Isso me diz a versão da inserção e/ou atualização, a operação (I, U ou D para insert, update, ou delete, respectivamente), a coluna de máscara para as operações de atualização (mais sobre isso em instantes), e a chave primária da linha afetado por essa mudança. Como CHANGETABLE () retorna uma tabela, eu poderia facilmente juntar este resultado de volta para a tabela original para ver a operação de mudança junto com os dados atuais nessa tabela.
isto vai parecer um pouco diferente para uma operação de atualização. Em seguida, vou executar uma declaração de atualização, mas primeiro, eu vou notar a versão atual de rastreamento de mudança (que ainda é 476).
agora a instrução de atualização, que irá atualizar duas linhas na tabela:
Agora, quando eu executar CHANGETABLE() código acima, usando o mais recente alterar controle de versão (476) como um ponto de partida, eu obter um conjunto de resultados diferentes:
Este é o metadados para todas as mudanças desde a versão 476, que inclui apenas as duas linhas atualizados a partir da declaração de ATUALIZAÇÃO acima. Observe que a versão de criação é nula, porque esta mudança foi uma atualização, não uma inserção. Além disso, o valor do SYS_CHANGE_COLUMNS é agora preenchido, embora o valor não nos mostre realmente o que mudou (ainda). Este é um bom momento para falar sobre a função change_tracking_ IS_COLUMN_ IN_ máscara(). Esta função irá verificar se a coluna indicada foi actualizada desde a versão mais recente. Sua sintaxe é um pouco peculiar, mas para verificar se o MiddleName foi atualizado, a consulta teria esta aparência:
Honestamente, eu não sei o que eu já usou o CHANGE_TRACKING_IS_COLUMN_IN_MASK função. É um pouco doloroso porque você tem que correr isso para cada coluna que você quer verificar. A maioria do meu trabalho é em armazenamento de dados, e eu encontrei alguns casos onde eu preciso saber exatamente quais colunas foram atualizadas – eu só quero saber se a linha foi atualizada. No entanto, para outros cenários (especialmente em OLTP), eu posso ver a necessidade para isso.
demonstrei inserções e atualizações. Vamos ver como seria um delete. Mais uma vez, vou anotar a versão atual número 478 para a próxima operação. Agora vou apagar uma linha de dados:
depois de ter apagado uma linha, vou executar CHANGETABLE () novamente para ver que alterações de relatórios de rastreamento para esta operação.
encontro a linha que eliminei na última operação, com o SYS_CHANGE_OPERATION definido como D (apagar):
agora, lembre-se que o número da versão faz a diferença aqui! O número da versão passou para CHANGETABLE() é o ponto de partida para quaisquer alterações retornadas por essa função. Através deste exercício, tenho verificado os resultados de acompanhamento de alterações após cada operação DML. No entanto, eu posso definir o número de versão inicial para qualquer número de versão válido, ou simplesmente usar NULL para obter todos os resultados de rastreamento de alterações disponíveis para essa tabela. Para demonstrar, vou ajustar o valor de volta para a versão 470 – o ponto de partida antes de quaisquer atualizações-para mostrar como o histórico completo seria. Quando eu reunir CHANGETABLE () usando a nossa versão de rastreamento de mudança original, Eu recebo o seguinte:
existem algumas nuances previsíveis aqui. Em primeiro lugar, a linha que mostra o ID de registro de 1 (que foi o registro Phoebe Buffay que eu excluí) aparece simplesmente como uma operação de delete, mesmo que esta linha foi inserida e posteriormente apagada desde o número da versão inicial. Lembre – se, é o delta que será mostrado-cada operação contra essa linha não é retida no rastreamento de mudanças. Para os IDs numerados 2 e 4 – que foram as duas linhas que introduzi e subsequentemente actualizadas-o SYS_CHANGE_OPERATION mostra uma sequência inserida, apesar de termos actualizado ambos os registos após a inserção. A opção é que o SYS_CHANGE_VERSION e o SYS_CHANGE_CREATION_VERSION nestas linhas não correspondem, indicando que a alteração mais recente não foi a sequência inserida.
Conclusion
Change tracking is a simple and lightweight means of change detection in SQL Server. O uso de rastreamento de mudanças permite fácil identificação de dados novos, alterados e apagados, eliminando a necessidade de comparações de Força bruta. No meu próximo post, vou olhar para isso a partir de uma perspectiva ETL, integrando o rastreamento de mudanças em um processo de carga de ponta a ponta.