Parece ser bastante difícil encontrar comparações entre tabelas temporais com versão do sistema e as opções mais antigas, como gatilhos de banco de dados e CDC. No momento, não tenho tempo para escrever um teste estendido no SQL Server 2016, então pensei em perguntar sobre isso aqui.
Basicamente, a vantagem típica dos gatilhos é que eles são mais fáceis de gerenciar em ambientes autônomos e em cluster/alwaysOn, são sincronizados em tempo real e têm acesso aos dados da sessão, como o ID do usuário.
O CDC, por outro lado, embora exija um pouco mais de gerenciamento e seja assíncrono, é muito mais leve e, portanto, tem um desempenho muito melhor. Portanto, se houver alguma dúvida de que o gargalo causado pelos gatilhos pode se tornar um problema, o CDC será basicamente a solução superior. Em termos de requisitos de hardware, há um requisito de espaço extra insignificante pelo CDC devido ao uso de logs e tabelas de auditoria do CDC para rastrear as alterações.
A pergunta: como as tabelas temporais se comparam às duas anteriores? Em termos de velocidade, desempenho, uso de espaço de armazenamento. QUANDO devo usar tabelas temporais em vez de gatilhos ou CDC? Quando não devo?
Entendo que algo potencialmente complexo como os requisitos de negócios e as limitações técnicas por trás da auditoria de banco de dados não terá uma resposta fácil, pois depende muito dos requisitos e do escopo do projeto. Mas qualquer coisa para lançar mais luz sobre as questões acima seria apreciada. Obrigado!
(Observação, voltei a isso em 2021 e acrescentei o que havia aprendido como resposta à pergunta original. Você a encontrará abaixo.)
Depende do seu caso de negócios. As tabelas temporais e a captura de dados alterados oferecem funcionalidades diferentes.
As tabelas temporais são usadas para fornecer uma versão da sua tabela em um determinado momento. Um caso de uso pode ser uma dimensão que muda lentamente na qual você deseja rastrear as mudanças nos atributos da dimensão e relatá-las a qualquer momento.
A captura de dados alterados pode ser usada em uma tabela OLTP para facilitar a exportação para um data mart. Ele registra todas as alterações em uma tabela separada, para que você possa visualizar facilmente as linhas alteradas desde o último ponto LSN de exportação.
Como tem havido algum interesse contínuo nisso e anos depois eu me familiarizei muito bem com tudo o que foi dito acima, aqui está um breve resumo em termos de desempenho: Fiz um teste em alguma versão do SQL Server 2016 envolvendo inserção, atualização e exclusão 10.000 linhas de 40 tipos diferentes de tabelas, uma a uma, e mapeou o tempo total gasto, informações básicas de bloqueio etc. sobre cada uma. O resumo simples é que onde os gatilhos adicionaram em média 500-1000% mais atraso às operações, com tabelas temporais e CDC foi mais próximo de 10% de atraso extra por operação. Ajudaria se eu tivesse os resultados exatos, mas não me lembro mais deles. O processo de gatilho foi muito simplificado, mas inseriu uma linha por coluna alterada, em vez de temporal / cdc, que inseriu uma linha, independentemente de quantas colunas nela foram alteradas. Neste sentido, algumas alterações podem ter feito os gatilhos parecerem mais lentos do que eram, devido à contenção de chave de várias linhas sendo inseridas ao mesmo tempo. No entanto, era óbvio que os gatilhos eram a ferramenta menos adequada para auditoria simples. Então, aqui está um resumo técnico simples das diferenças que eu estava tentando entender quando criei este post:
Os gatilhos só são bons se você realmente precisar de alguma lógica personalizada incorporada ao banco de dados, para observar as alterações DML, modificar dados específicos, capturar o ID do usuário em instâncias específicas, etc. Mas tente evitá-los como a praga. Eles são horríveis para o desempenho. E se você precisar de auditoria ou registro, eles são o último lugar que você deve procurar.
Tabelas temporais são muito fáceis de gerenciar quando você as executa, especialmente em HADR como Always On. Como eles suportam compactação e refletem a maioria das alterações de esquema do pai para a tabela de histórico, eles exigem muito pouca manutenção. Especialmente com novas versões do SQL Server, você pode definir o período de retenção para remover dados com mais de x anos de qualquer maneira, de modo que as considerações de armazenamento e limpeza também sejam insignificantes. Eles são tão disparados e esquecidos quanto as coisas acontecem, exceto algumas atualizações exóticas nas tabelas pai, nas quais você precisa alterar os dados; nesse caso, é necessário desvincular, modificar a tabela pai e o histórico e vinculá-los novamente. Mas estes são raros e relativamente fáceis de fazer. O pacote de tabela temporal é robusto e lida bem com erros, então você achará difícil quebrá-lo acidentalmente.
O CDC pode ser ótimo para serviços de relatórios ou cenários semelhantes em que você não se importa com dados assíncronos, mas precisa analisar alterações, por exemplo, em lotes noturnos. Você pode definir a configuração de retenção para manter apenas x dias de dados para manter os custos de armazenamento no mínimo. Dito isto, o CDC é, pela minha experiência, meticuloso e não muito estável. Os DMLs podem "quebrá-lo" às vezes sem aviso, portanto, você pode precisar de gatilhos DDL no nível do banco de dados para avisá-lo sobre alterações nos objetos rastreados pelo CDC. Você também pode precisar definir watchjobs customizados para HADR, pois ele não lida nativamente com eventos de failover. E o CDC tem uma propensão muito desagradável para não reiniciar depois de ser desativado, algo sobre o estado dele não ser atualizado corretamente usando os próprios trabalhos do MS. Isso significa que ocasionalmente será necessário trabalho manual para garantir que os trabalhos de limpeza e captura e suas referências sejam removidos corretamente. Dito isso, o SSIS/RS se integra muito bem e facilita o uso do CDC para eles.