Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a:
Servidor SQLInstância Gerenciada SQL do Azure
Neste artigo, saiba mais sobre a captura de dados de alteração (CDC), que registra a atividade em um banco de dados quando tabelas e linhas foram modificadas.
Este artigo explica como o CDC funciona com o SQL Server e a Instância Gerenciada SQL do Azure. Para a Base de Dados SQL do Azure, consulte CDC com a Base de Dados SQL do Azure.
Visão geral
A captura de dados de alteração utiliza o SQL Server Agent para registrar inserções, atualizações e exclusões que ocorrem em uma tabela. Assim, torna essas alterações de dados acessíveis para serem facilmente consumidas usando um formato relacional. Os dados de coluna e os metadados essenciais necessários para aplicar esses dados de alteração a um ambiente de destino são capturados para as linhas modificadas e armazenados em tabelas de alteração que espelham a estrutura de colunas das tabelas de origem controladas. Além disso, estão disponíveis funções com valor de tabela para acesso sistemático a estes dados de alteração por parte dos consumidores.
Um bom exemplo de consumidor de dados direcionado a essa tecnologia é um aplicativo de extração, transformação e carregamento (ETL). Um aplicativo ETL carrega incrementalmente dados de alteração de tabelas de origem do SQL Server para 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 atualize uma réplica da origem não é apropriada. Em vez disso, você precisa de um fluxo confiável de dados de alteração estruturados para que os consumidores possam aplicá-los a representações de destino diferentes dos dados. A captura de dados de alteração do SQL Server fornece essa tecnologia.
Fluxo de dados
A ilustração a seguir mostra o fluxo de dados principal para a captura de dados de alteração.
A origem dos dados de alteração para a captura de dados de alteração é o log de transações do SQL Server. À medida que inserções, atualizações e exclusões são aplicadas a tabelas de origem controladas, as entradas que descrevem essas alterações são adicionadas ao log. O log serve 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 controlada. As funções são fornecidas para enumerar as alterações que aparecem nas tabelas de alteração em um intervalo especificado, retornando as informações na forma de um conjunto de resultados filtrado. O conjunto de resultados filtrados é normalmente usado por um processo de aplicativo para atualizar uma representação da fonte em algum ambiente externo.
Instância de captura
Antes que as alterações em qualquer tabela individual dentro de um banco de dados possam ser controladas, a captura de dados de alteração 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á ativado, as tabelas de origem podem ser identificadas como tabelas controladas por rastreamento usando o procedimento armazenado sys.sp_cdc_enable_table. Quando uma tabela é habilitada para captura de dados de alteração, 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 de captura de alterações cdc.change_tables, cdc.index_columns e cdc.captured_columns. Essas 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 que seja um nome de objeto válido e que seja exclusivo nas instâncias de captura de banco de dados. Por padrão, o nome é <schema name_table name> da tabela de origem. Sua tabela de alterações associada é nomeada anexando _CT ao nome da instância de captura. A função usada para consultar todas as alterações é nomeada ao prefixar fn_cdc_get_all_changes_ ao nome da instância de captura. Se a instância de captura estiver configurada para suportar alterações líquidas, a função de consulta net_changes também será criada e nomeada por pré-adicionando fn_cdc_get_net_changes_ ao nome da instância de captura.
Importante
O número máximo de instâncias de captura que podem ser associadas simultaneamente a uma única tabela de origem é dois.
Alterar tabela
As cinco primeiras colunas de uma tabela de alteração de captura de dados são colunas de metadados. Estes fornecem informações adicionais que são relevantes para a alteração registada. As colunas restantes refletem as colunas capturadas identificadas da tabela de origem tanto no nome quanto, habitualmente, no tipo. Essas colunas contêm os dados da coluna capturados que são coletados da tabela de origem.
Cada operação de inserção ou exclusão aplicada a uma tabela de origem aparece como uma única linha dentro da tabela de alterações. As colunas de dados da linha resultante de uma operação de inserção contêm os valores das colunas após a inserção. As colunas de dados da linha que resulta de uma operação de exclusão contêm os valores de coluna antes da exclusão. Uma operação de atualização requer uma entrada de linha para identificar os valores de coluna antes da atualização e uma segunda entrada de linha para identificar os valores de coluna após a atualização.
Cada linha em uma tabela de alteração também contém outros metadados para permitir a interpretação da atividade de alteração. A coluna __$start_lsn identifica o número de sequência do log de confirmação (LSN) atribuído à alteração. A LSN de confirmação identifica as alterações que foram cometidas dentro da mesma transação e 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 (após a imagem). A coluna __$update_mask é uma máscara de bits variável com um bit definido para cada coluna capturada. Para inserir e excluir entradas, a máscara de atualização tem todos os bits definidos. As linhas de atualização, no entanto, terão os bits definidos que correspondem à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 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 até o momento.
Base de dados
Os dados depositados em tabelas de alterações crescem de forma incontrolável se você não remover os dados de forma periódica e sistemática. O processo de limpeza de captura de dados de alteração é responsável por aplicar a política de limpeza baseada em retenção. Primeiro, ele move o ponto final baixo do intervalo de validade para satisfazer a restrição de tempo. Em seguida, remove entradas de tabela de alteração expiradas. Por padrão, três dias de dados são retidos.
Na extremidade superior, à medida que 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 de tabela de alteração. Na tabela de mapeamento, um LSN (Número de Sequência de Log) de confirmação e um tempo de confirmação de transação (colunas start_lsn e tran_end_time, respectivamente) são mantidos. O valor máximo de LSN encontrado em cdc.lsn_time_mapping representa o ponto máximo da janela de validade da base de dados. Seu tempo de confirmação correspondente é usado como a base a partir da qual a limpeza baseada em retenção calcula uma nova marca d'água baixa.
Como o processo de captura extrai dados de alteração 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 em sua tabela de alterações associada. Embora essa latência seja normalmente pequena, é importante lembrar que os dados de alteração não estarão disponíveis até que o processo de captura tenha processado 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, no entanto, 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 começa a registrar as alterações associadas em sua tabela de alterações. Como resultado, se as instâncias de captura forem criadas em momentos diferentes, cada uma terá um ponto de extremidade baixo diferente. A coluna start_lsn do conjunto de resultados retornado por sys.sp_cdc_help_change_data_capture mostra o extremo inferior atual para cada instância de captura definida. Quando o 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 o novo limite inferior para os dados de alteração disponíveis. Apenas as instâncias de captura que têm valores de start_lsn que atualmente são inferiores ao novo limite 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 de uma solicitação deve ser totalmente coberto pelo intervalo de validade de captura de dados de alteração atual para a instância de captura. Se a extremidade inferior do intervalo de extração estiver à esquerda da extremidade inferior do intervalo de validade, possam faltar dados de alterações devido a uma limpeza agressiva. Se o ponto final alto do intervalo de extração estiver à direita do ponto final 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ção ausentes.
A função sys.fn_cdc_get_min_lsn é usada para recuperar o LSN mínimo atual para uma instância de captura, enquanto sys.fn_cdc_get_max_lsn é usada para recuperar o valor LSN máximo atual. Quando você consulta os dados de alteração, se o intervalo LSN especificado não estiver dentro desses dois valores LSN, as funções de consulta de captura de dados de alteração falharão.
Tratamento de alterações na tabela de origem
Acomodar alterações de coluna nas tabelas de origem que estão a ser rastreadas é uma questão difícil para os consumidores a jusante. Embora a habilitação da captura de dados de alteração em uma tabela de origem não impeça que essas alterações DDL ocorram, a captura de dados de alteração atenua o efeito sobre os consumidores, preservando os conjuntos de resultados entregues retornados por meio da API, mesmo quando a estrutura de colunas da tabela de origem subjacente muda. Essa estrutura de coluna fixa também é refletida na tabela de alterações 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ção de estrutura de coluna fixa ignorando quaisquer novas colunas não identificadas para captura quando a tabela de origem foi habilitada para captura de dados de alteração. Se uma coluna controlada for descartada, valores nulos serão fornecidos para a coluna nas entradas de alteração subsequentes. No entanto, se uma coluna existente sofrer uma alteração em seu tipo de dados, a alteração será propagada para a tabela de alterações para garantir que o mecanismo de captura não introduza perda de dados para colunas controladas. O processo de captura também regista quaisquer alterações detetadas na estrutura de colunas de tabelas de rastreio na tabela cdc.ddl_history. Os consumidores que desejem ser alertados para os ajustamentos que possam precisar ser feitos nas aplicações subsequentes utilizam 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 coluna. Esta opção permite que o processo de captura faça alterações na mesma tabela de origem em duas tabelas de alteração distintas com duas estruturas de coluna diferentes. Assim, enquanto uma tabela de alteração pode continuar a alimentar os programas operacionais atuais, a segunda pode conduzir um ambiente de desenvolvimento que está tentando incorporar os novos dados da coluna. Permitir que o mecanismo de captura preencha ambas as tabelas de alteração em conjunto significa que uma transição de uma para a outra pode ser realizada sem perda de dados de alteração. Isso pode acontecer a qualquer momento em que os dois cronogramas de captura de dados se sobreponham. Quando a transição é afetada, a instância de captura obsoleta pode ser removida.
Importante
O número 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 para o processo de captura de dados de alteração é incorporada na sp_replcmds, procedimento armazenado, uma função de servidor interna criada como parte do 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 Gerida SQL do Azure, quando apenas a captura de dados de alteração está habilitada para um banco de dados, cria-se o trabalho de captura de dados de alteração do SQL Server Agent como meio para invocar sp_replcmds. Quando a replicação também está presente, é usado apenas o leitor de log de transações para satisfazer as necessidades de dados de alteração de ambos os consumidores. Essa estratégia reduz significativamente a contenção de logs quando a replicação e a captura de dados de alteração estão habilitadas para o mesmo banco de dados.
A alternância entre esses dois modos operacionais para capturar dados de alteração 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ção.
Observação
No SQL Server e na Instância Gerenciada SQL do Azure, ambas as instâncias da lógica de captura exigem que o SQL Server Agent esteja em execução para que o processo seja executado.
A tarefa principal do processo de captura é escanear o log e gravar dados de coluna e informações relacionadas a transações nas tabelas de alteração de dados capturados. Para garantir um limite transacionalmente consistente em todas as tabelas de alteração de captura de dados que ele preenche, o processo de captura abre e confirma sua própria transação em cada ciclo de verificação. Ele deteta quando as tabelas são recém-habilitadas para captura de dados de alteração e as inclui automaticamente no conjunto de tabelas que são ativamente monitoradas para entradas de alteração no log. Da mesma forma, a desativação da captura de dados de alteração também será detetada, fazendo com que a tabela de origem seja removida do conjunto de tabelas ativamente monitoradas para dados de alteração. Quando o processamento de uma seção do log é concluído, o processo de captura sinaliza a lógica de truncamento do log do servidor, que usa essas informações para identificar entradas de log qualificadas para truncamento.
Importante
Quando um banco de dados é 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 de 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 em execução 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 das alterações DDL nas tabelas rastreadas. As instruções DDL associadas à captura de dados de alteração fazem entradas no log de transações do banco de dados sempre que um banco de dados ou tabela habilitado para captura de dados de alteração é descartado ou colunas de uma tabela habilitada para captura de dados de alteração são adicionadas, modificadas ou descartadas. Essas entradas de log são processadas pelo processo de captura, que então posta os eventos DDL associados na tabela cdc.ddl_history. Você pode obter informações sobre eventos DDL que afetam tabelas monitorizadas usando o procedimento armazenado sys.sp_cdc_get_ddl_history.
Advertência
- MaxCmdsInTran não foi projetado para estar sempre ligado. 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ê cair rotineiramente nessa situação, revise a lógica do aplicativo para encontrar maneiras de reduzir o tamanho da transação.
- MaxCmdsInTran não é suportado se a base de dados de publicação fornecida tiver o CDC e a replicação ativados. Usar MaxCmdsInTran nesta configuração pode levar à perda de dados nas tabelas de alteração CDC. Também pode causar erros PK se o parâmetro MaxCmdsInTran for adicionado e removido durante a replicação de uma transação grande.
Vagas de Agente
Dois trabalhos do SQL Server Agent normalmente são associados a um banco de dados habilitado para captura de dados de alteração: um que é usado para preencher as tabelas de alteração de 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 invocado é um procedimento armazenado de captura de dados de alteração que implementa a lógica do trabalho. Os jobs são criados quando a primeira tabela do banco de dados é habilitada para a captura de dados de alteração. O Trabalho de Limpeza é sempre criado. O trabalho de captura só será criado se não houver publicações transacionais definidas para o banco de dados. O trabalho de captura também é criado quando a captura de dados de alteração e a replicação transacional estão habilitadas para um banco de dados, e o trabalho de leitor de log transacional é removido porque o banco de dados não tem mais publicações definidas.
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 5 segundos entre os ciclos. O trabalho de limpeza é executado diariamente às 2 da manhã. Retém entradas de tabela de alteração por 4320 minutos ou 3 dias, removendo um máximo de 5000 entradas com uma única instrução de eliminação.
Os trabalhos do agente de captura de dados de alteração são removidos quando a captura de dados de alteração é desabilitada para um banco de dados. O trabalho de captura também pode ser removido quando a primeira publicação é adicionada a uma base de dados, desde que a captura de dados de alteração e a replicação transacional estejam ativas.
Internamente, os trabalhos do agente de captura de dados de alteração são criados e descartados usando os procedimentos armazenados sys.sp_cdc_add_job e sys.sp_cdc_drop_job, respectivamente. Esses procedimentos armazenados também são expostos para que os administradores possam controlar a criação e a remoção desses trabalhos.
Um administrador não tem controle explícito sobre a configuração padrão dos trabalhos do agente de captura de dados de alteração. 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 que os parâmetros de configuração atuais sejam consultados. Tanto o trabalho de captura quanto o trabalho de limpeza extraem parâmetros de configuração da tabela msdb.dbo.cdc_jobs na inicialização. Quaisquer 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ção sejam iniciados e interrompidos: sys.sp_cdc_start_job e sys.sp_cdc_stop_job.
Observação
Iniciar e parar o trabalho de captura não resulta em perda de dados de alteração. Isto apenas impede o processo de captura de verificar ativamente o log em busca de entradas de alteração para depositar nas tabelas de alteração. Uma estratégia razoável para evitar que a varredura de logs adicione carga durante períodos de pico de demanda é interromper o trabalho de captura e reiniciá-lo quando a demanda for reduzida.
Ambos os trabalhos do SQL Server Agent foram projetados para serem flexíveis o suficiente e suficientemente configuráveis para atender às necessidades básicas de ambientes de captura de dados de alteração. Em ambos os casos, no entanto, os procedimentos armazenados subjacentes que fornecem a funcionalidade principal foram expostos para que seja possível uma maior personalização.
A captura de dados de alteração não pode funcionar corretamente quando o serviço Mecanismo de Banco de Dados ou o serviço SQL Server Agent está sendo executado na conta SERVIÇO DE REDE. Isso pode resultar no erro 22832.
Interoperabilidade com outras funcionalidades
A captura de dados de alteração tem algumas limitações ao trabalhar com outros recursos do SQL Server. Consulte Interoperabilidade para saber mais.
Problemas conhecidos
Para problemas conhecidos e erros associados à captura de dados de alteração, consulte Problemas conhecidos com CDC.