O que é a CDA (captura de dados de alterações)?
Aplica-se a: 
SQL Server Instância Gerenciada de SQL do Azure
Neste artigo, saiba mais sobre o CDC (captura de dados de alterações), que registra a atividade em um banco de dados quando tabelas e linhas foram modificadas.
Este artigo explica como a CDA funciona com o SQL Server e a Instância Gerenciada de SQL do Azure. Para o Banco de Dados SQL do Azure, confira CDA com Banco de Dados SQL do Azure.
Visão geral
A captura de dados de alterações utiliza o SQL Server Agent para registrar em log as inserções, as atualizações e as exclusões que ocorrem em uma tabela. Assim, ela torna essas alterações de dados acessíveis para serem facilmente consumidas usando um formato relacional. Os dados da coluna e os metadados essenciais necessários para aplicar esses dados de alterações a um ambiente de destino são capturados para as linhas modificadas e armazenados em tabelas de alteração que espelham a estrutura da coluna das tabelas de origem rastreadas. Além disso, funções com valor de tabela estão disponíveis para acesso sistemático a esses dados de alterações pelos consumidores.
Um bom exemplo de um consumidor de dados que esta tecnologia busca é um aplicativo ETL (extração, transformação e carregamento). Um aplicativo de ETL carrega de forma incremental os dados de alterações de tabelas de origem do SQL Server em um data warehouse ou data mart. Embora a representação das tabelas de origem no data warehouse deva refletir as alterações nas tabelas de origem, uma tecnologia de ponta a ponta que atualiza uma réplica da origem não é adequada. Em vez disso, é necessário um fluxo seguro de dados de alteração, estruturado de forma que consumidores possam aplicá-lo às representações dos dados de destino. A captura de dados de alterações do SQL Server fornece essa tecnologia.
Fluxo de dados
A ilustração seguinte mostra o fluxo de dados principal para a captura de dados de alteração.
A origem de dados de alterações para a captura de dados de alterações é o log de transações do SQL Server . Como inserções, atualizações e exclusões são aplicadas às tabelas de origem rastreadas, as entradas que descrevem essas alterações são adicionadas ao log. O log atua como entrada para o processo de captura. Em seguida, ele lê o log e adiciona informações sobre alterações à tabela de alterações associada à tabela rastreada. As funções são fornecidas para enumerar as alterações exibidas nas tabelas de alteração em um intervalo especificado, retornando informações em um conjunto de resultados filtrados. O conjunto de resultados filtrados é normalmente usado por um processo de aplicativo para atualizar uma representação da origem em algum ambiente externo.
Instância de captura
Antes do rastreamento das alterações em qualquer tabela individual dentro de um banco de dados, a captura de dados de alterações deve ser explicitamente habilitada para o banco de dados. Isso é feito usando o procedimento armazenado sys.sp_cdc_enable_db. Quando o banco de dados está habilitado, é possível identificar as tabelas de origem como tabelas controladas usando o procedimento armazenado sys.sp_cdc_enable_table. Quando uma tabela está habilitada para Change Data Capture, uma instância de captura associada é criada para dar suporte à disseminação dos dados de alteração na tabela de origem. A instância de captura consiste em uma tabela de alteração e até duas funções de consulta. Os metadados que descrevem os detalhes de configuração da instância de captura são mantidos nas tabelas de metadados da captura de dados de alterações cdc.change_tables, cdc.index_columnse cdc.captured_columns. Estas informações podem ser recuperadas usando o procedimento armazenado sys.sp_cdc_help_change_data_capture.
Todos os objetos associados a uma instância de captura são criados no esquema de captura de dados de alteração do banco de dados habilitado. Os requisitos para o nome da instância de captura são um nome de objeto válido e exclusivo entre as instâncias de captura de banco de dados. Por padrão, o nome é <nome do esquema_nome da tabela> da tabela de origem. Sua tabela de alteração associada é nomeada acrescentando _CT ao nome da instância de captura. A função usada para consultar todas as alterações é nomeada acrescentando fn_cdc_get_all_changes_ ao nome da instância de captura. Se a instância de captura estiver configurada para oferecer suporte a alterações de rede, a função de consulta net_changes também será criada e nomeada acrescentando-se fn_cdc_get_net_changes_ ao nome da instância de captura.
Importante
O número de máximo de instâncias de captura que podem ser associadas simultaneamente a uma única tabela de origem é dois.
Tabela de alteração
As primeiras cinco colunas de uma tabela de alteração de captura de dados de alterações são colunas de metadados. Elas fornecem informações adicionais pertinentes à alteração registrada. As colunas restantes espelham as colunas capturadas identificadas da tabela de origem em nome e, geralmente, em tipo. Estas colunas mantêm os dados de coluna capturados que são juntados na tabela de origem.
Cada operação de inserção ou exclusão que é aplicada a uma tabela de origem aparece como uma única linha dentro da tabela de alteração. As colunas de dados da linha, que é o resultado de uma operação de inserção, contêm os valores de coluna depois da inserção. As colunas de dados da linha, que é o resultado uma operação de exclusão, contêm valores de coluna antes da exclusão. Uma operação de atualização requer uma entrada de linha para identificar os valores da coluna antes da atualização e uma segunda entrada para identificar os valores da coluna depois da atualização.
Cada linha em uma tabela de alterações também contém outros metadados para permitir a interpretação da atividade de alteração. A coluna __$start_lsn identifica o número da sequência do log de confirmação (LSN) que foi atribuído à alteração. O LSN de confirmação não só identifica alterações que foram confirmadas dentro da mesma transação, mas também ordena essas transações. A coluna __$seqval pode ser usada para ordenar mais alterações que ocorrem na mesma transação. A coluna __$operation registra a operação associada à alteração: 1 = excluir, 2 = inserir, 3 = atualizar (antes da imagem) e 4 = atualizar (depois da imagem). A coluna __$update_mask é uma máscara de bits variável com um bit definido para cada coluna capturada. Para entradas de inserção e exclusão, a máscara de atualização tem todos os bits definidos. No entanto, as linhas de atualização terão definidos os bits que correspondam às colunas alteradas.
Intervalo de validade
O intervalo de validade da captura de dados de alteração para um banco de dados é o tempo durante o qual os dados de alteração estão disponíveis para as instâncias de captura. O intervalo de validade começa quando a primeira instância de captura é criada para uma tabela de banco de dados e continua no momento atual.
Backup de banco de dados
Os dados que são depositados em tabelas de alteração crescerão de forma incontrolável se você não os podar de forma periódica e sistemática. O processo de limpeza da captura de dados de alterações é responsável pela aplicação da política de limpeza com base em retenção. Primeiro, ele move o ponto de extremidade inferior do intervalo de validade para atender à restrição de hora. Então, ele remove as entradas da tabela de alteração expiradas. Por padrão, são retidos três dias de dados.
No alto nível, conforme o processo de captura confirma cada novo lote de dados de alteração, novas entradas são adicionadas ao cdc.lsn_time_mapping para cada transação que tem entradas da tabela de alteração. Na tabela de mapeamento, um número da sequência do log de confirmação (LSN) e um tempo de confirmação da transação (colunas start_lsn e tran_end_time, respectivamente) são mantidos. O valor máximo de LSN que é encontrado em cdc.lsn_time_mapping representa a marca d’água superior da janela de validade do banco de dados. Seu tempo de confirmação correspondente é usado como a base da qual a limpeza baseada em retenção calcula uma nova marca d'água inferior.
Como o processo de captura extrai dados de alterações do log de transações, há uma latência interna entre o momento em que uma alteração é confirmada em uma tabela de origem e o momento em que a alteração aparece na tabela de alterações associada. Embora essa latência seja normalmente pequena, nunca é demais lembrar que os dados de alterações não ficam disponíveis até que o processo de captura processe as entradas de log relacionadas.
Instância de captura
Embora seja comum que o intervalo de validade do banco de dados e o intervalo de validade da instância de captura individual coincidam, isso nem sempre é verdade. O intervalo de validade da instância de captura começa quando o processo de captura reconhece a instância de captura e inicia o registro de alterações associadas a sua tabela de alterações. Como resultado, se forem criadas instâncias de captura em horas diferentes, cada uma terá um ponto de extremidade inferior diferente. A coluna start_lsn do conjunto de resultados que é retornado por sys.sp_cdc_help_change_data_capture mostra o ponto de extremidade inferior atual de cada instância de captura definida. Quando um processo de limpeza limpa as entradas da tabela de alterações, ele ajusta os valores de start_lsn para todas as instâncias de captura para refletir a nova marca d'água inferior em dados de alteração disponíveis. Somente essas instâncias de captura que têm valores start_lsn que sejam atualmente menores que a nova marca d'água inferior são ajustadas. Com o tempo, se nenhuma nova instância de captura for criada, os intervalos de validade para todas as instâncias individuais tenderão a coincidir com o intervalo de validade do banco de dados.
O intervalo de validade é importante para os consumidores de dados de alteração, porque o intervalo de extração para um pedido deve ser totalmente coberto pelo intervalo de validade da captura de dados de alteração atual para a instância de captura. Se o ponto de extremidade inferior do intervalo de extração estiver à esquerda do ponto de extremidade inferior do intervalo de validade, pode ser que dados de alteração estejam faltando devido a uma limpeza intensa. Se o ponto de extremidade alto do intervalo de extração está à direita do ponto de extremidade alto do intervalo de validade, isso indica que o processo de captura ainda não foi processado durante o tempo representado pelo intervalo de extração, e também pode haver dados de alterações ausentes.
A função sys.fn_cdc_get_min_lsn é usada para recuperar o LSN mínimo atual de uma instância de captura, enquanto sys.fn_cdc_get_max_lsn é usada para recuperar o valor máximo atual do LSN. Quando você consultar os dados de modificação, se o intervalo de LSN especificado não estiver dentro desses dois valores de LSN, as funções de consulta de captura de dados de alterações falharão.
Tratando alterações na tabela de origem
Acomodar as alterações de coluna nas tabelas de origem que estão sendo rastreadas é um problema difícil para os consumidores downstream. Embora a habilitação da captura de dados de alterações em uma tabela de origem não impeça que essas alterações de DDL ocorram, a captura de dados de alterações atenua o efeito sobre os consumidores, preservando os conjuntos de resultados retornados por meio da API, mesmo que a estrutura de colunas da tabela de origem subjacente seja alterada. Essa estrutura de coluna fixa também é refletida na tabela de alteração subjacente que as funções de consulta definidas acessam.
O processo de captura responsável por preencher a tabela de alterações acomoda uma tabela de alterações com estrutura de coluna fixa, ignorando quaisquer novas colunas não identificadas para captura quando a tabela de origem foi habilitada para a captura de dados de alterações. Se uma coluna rastreada for descartada, serão fornecidos valores nulos para a coluna nas entradas de alterações subsequentes. No entanto, se uma coluna existente sofrer uma alteração em seu tipo de dados, essa alteração será propagada para a tabela de alterações para garantir que o mecanismo de captura não introduza perda de dados nas colunas rastreadas. O processo de captura também envia qualquer alteração detectada para a estrutura de coluna de tabelas rastreadas para a tabela cdc.ddl_history. Os consumidores que desejam ser alertados sobre ajustes que tenham de ser feitos em aplicativos downstream usam o procedimento armazenado sys.sp_cdc_get_ddl_history.
Normalmente, a instância de captura atual continua a manter sua forma quando as alterações DDL são aplicadas à tabela de origem associada. No entanto, é possível criar uma segunda instância de captura para a tabela que reflita a nova estrutura de colunas. Essa opção permite que o processo de captura faça alterações na mesma tabela de origem em duas tabelas de alterações distintas com duas estruturas de colunas diferentes. Portanto, enquanto uma tabela de alteração pode continuar a alimentar os programas em operação atualmente, a segunda pode orientar um ambiente de desenvolvimento que está tentando inserir dados à nova coluna Permitir que o mecanismo de captura preencha ambas as tabelas de alteração em tandem significa que uma transição de uma para a outra pode ser cumprida sem a perda de dados de alteração. Isso pode acontecer quando as duas linhas de tempo da captura de dados de alterações se sobrepuserem. Quando a transição é efetuada, a instância de captura obsoleta pode ser removida.
Importante
O número de máximo de instâncias de captura que podem ser associadas simultaneamente a uma única tabela de origem é dois.
Relação com o agente leitor de logs
A lógica do processo de captura de dados de alterações é inserida ao procedimento armazenado sp_replcmds, uma função de servidor interna criada como parte de sqlservr.exe e também usada pela replicação transacional para coletar alterações do log de transações. No SQL Server e na Instância Gerenciada de SQL do Azure, quando somente a captura de dados de alterações está habilitada para um banco de dados, você cria o trabalho de captura do SQL Server Agent da captura de dados de alterações como o veículo para invocar sp_replcmds. Quando a replicação também está presente, o leitor de registro transacional sozinho é usado para satisfazer as necessidades de dados de alterações de ambos os consumidores. Essa estratégia reduz significativamente a contenção de log quando são habilitadas a replicação e a captura de dados de alterações para o mesmo banco de dados.
A alternância entre esses dois modos operacionais de captura de dados de alterações ocorre automaticamente sempre que há uma alteração no status de replicação de um banco de dados habilitado para captura de dados de alterações.
Observação
No SQL Server e na Instância Gerenciada de SQL do Azure, as duas instâncias da lógica de captura exigem que o SQL Server Agent seja executado para que o processo seja executado.
A principal tarefa do processo de captura é verificar o log e gravar dados da coluna e informações relacionadas à transação nas tabelas de alteração do Change Data Capture. Para garantir um limite consistente transacionalmente entre todas as tabelas de alteração de captura de dados de alteração preenchidas, o processo de captura abre e confirma sua própria transação em cada ciclo de verificação. Ele detecta quando tabelas são recém-habilitadas para a captura de dados de alteração e automaticamente os inclui no conjunto de tabelas ativamente monitoradas para as entradas de alteração no log. De modo semelhante, a desabilitação da captura de dados de alteração também será detectadas, fazendo com que a tabela de origem seja removida do conjunto de tabelas monitoradas ativamente para dados de alteração. Quando o processamento para uma seção de log é concluído, o processo de captura emite um sinal para a lógica de truncamento do log de servidor, que usa essa informação para identificar entradas de log qualificadas para truncamento.
Importante
Quando um banco de dados está habilitado para captura de dados de alteração, mesmo que o modo de recuperação esteja definido como recuperação simples, o ponto de truncamento do log não avançará até que todas as alterações marcadas para captura tenham sido coletadas pelo processo de captura. Se o processo de captura não estiver sendo executado e houver alterações a serem coletadas, a execução de CHECKPOINT não truncará o log.
O processo de captura também é usado para manter o histórico nas alterações de DDL para tabelas rastreadas. As instruções DDL que estão associadas ao Change Data Capture criam entradas no log de transações do banco de dados sempre que um banco de dados ou uma tabela habilitados para o Change Data Capture são descartados ou que colunas de uma tabela habilitada para o Change Data Capture são adicionadas, modificadas ou descartadas. Essas entradas de log são processadas pelo processo de captura, que então envia os eventos de DDL associados para a tabela cdc.ddl_history. Você pode obter informações sobre eventos de DDL que afetam tabelas controladas usando o procedimento armazenado sys.sp_cdc_get_ddl_history.
Aviso
- O MaxCmdsInTran não foi criado para estar sempre ativado. Ele existe para contornar casos em que alguém acidentalmente executou um grande número de operações DML em uma única transação (causando um atraso na distribuição de comandos até que toda a transação esteja no banco de dados de distribuição, bloqueios sendo mantidos etc.). Se você costuma enfrentar essa situação, analise a lógica de aplicativo para descobrir maneiras de reduzir o tamanho da transação.
- Não haverá suporte para MaxCmdsInTran se o banco de dados de publicação fornecido tiver tanto CDA quanto replicação habilitadas. O uso de MaxCmdsInTran nessa configuração pode levar à perda de dados nas tabelas de alterações da CDA. Isso também pode causar erros de PK se o parâmetro MaxCmdsInTran for adicionado e removido durante a replicação de uma transação grande.
Trabalhos do agente
Normalmente, dois trabalhos do SQL Server Agent estão associados a um banco de dados habilitado para captura de dados de alterações: um que é usado para preencher as tabelas de alterações do banco de dados e outro que é responsável pela limpeza da tabela de alterações. Ambos os trabalhos consistem em uma única etapa que executa um comando Transact-SQL. O comando Transact-SQL chamado é um procedimento armazenado definido para a captura de dados de alterações que implementa a lógica do trabalho. Os trabalhos são criados quando a primeira tabela do banco de dados é habilitada para captura de dados de alterações. O Trabalho de Limpeza sempre é criado. O trabalho de captura só será criado se não existir uma publicação transacional definida para o banco de dados. Um trabalho de captura é criado também quando a captura de dados de alterações e a replicação transacional são habilitadas para um banco de dados e o trabalho do leitor de logs transacionais é removido porque o banco de dados não definiu mais as publicações.
Ambos os trabalhos de captura e limpeza são criados usando parâmetros padrão. O trabalho de captura é iniciado imediatamente. Ele é executado continuamente, processando um máximo de 1000 transações por ciclo de verificação com uma espera de cinco segundos entre os ciclos. O trabalho de limpeza é executado diariamente às 2h da manhã. Ele retém as entradas da tabela de alterações por 4320 minutos ou 3 dias, removendo no máximo 5000 entradas com um único comando de exclusão.
Os trabalhos de agente de captura de dados de alterações são removidos quando a captura de dados de alterações é desabilitada para um banco de dados. O trabalho de captura também pode ser removido quando a primeira publicação é adicionada a um banco de dados e tanto a captura de dados de alteração quanto a replicação transacional estão habilitadas.
Internamente, os trabalhos de agente da captura de dados de alterações são criados e removidos usando os procedimentos armazenados sys.sp_cdc_add_job e sys.sp_cdc_drop_job, respectivamente. Esses procedimentos armazenados também são expostos de forma que administradores possam controlar a criação e remoção desses trabalhos.
Um administrador não tem nenhum controle explícito sobre a configuração padrão dos trabalhos de agente de captura de dados de alterações. O procedimento armazenado sys.sp_cdc_change_job é fornecido para permitir que os parâmetros de configuração padrão sejam modificados. Além disso, o procedimento armazenado sys.sp_cdc_help_jobs permite exibir os parâmetros de configuração atuais. Os trabalhos de captura e de limpeza extraem os parâmetros de configuração da tabela msdb.dbo.cdc_jobs na inicialização. As alterações feitas nesses valores usando sys.sp_cdc_change_job não terão efeito até que o trabalho seja interrompido e reiniciado.
Dois outros procedimentos armazenados são fornecidos para permitir que os trabalhos do agente de captura de dados de alterações sejam iniciados e interrompidos: sys.sp_cdc_start_job e sys.sp_cdc_stop_job.
Observação
Iniciar e interromper o trabalho de captura não resulta em uma perda de dados de alteração. Isso impede apenas o processo de captura de verificar ativamente o log quanto a entradas de alteração a serem colocadas nas tabelas de alteração. Uma estratégia razoável para impedir que a verificação do log adicione carga durante os períodos de pico de demanda é interromper o trabalho de captura e reiniciá-lo quando houver redução de demanda.
Os dois trabalhos do SQL Server Agent foram projetados para serem suficientemente flexíveis e configuráveis para atender às necessidades básicas dos ambientes de captura de dados de alterações. Nos dois casos, entretanto, os procedimentos armazenados subjacentes que fornecem funcionalidade principal têm sido expostos para que seja possível a personalização posterior.
A captura de dados de alterações não pode funcionar corretamente quando o serviço do Mecanismo de Banco de Dados ou o serviço do SQL Server Agent está sendo executado na conta NETWORK SERVICE. Isso pode resultar no erro 22832.
Interoperabilidade com outros recursos
A captura de dados de alterações tem algumas limitações ao trabalhar com outros recursos do SQL Server. Analise Interoperabilidade para saber mais.
Problemas conhecidos
Para problemas conhecidos e erros associados à captura de dados de alterações, analise Problemas conhecidos com CDA.