Sobre a Captura de Dados de Alteração (SQL Server)

Alterar a atividade de inserção, atualização e exclusão de registros de captura de dados aplicada a uma tabela do SQL Server. Isso disponibiliza os detalhes das alterações em um formato relacional facilmente consumido. As informações de coluna e os metadados necessários para aplicar as 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 de colunas das tabelas de origem controladas. As funções com valor de tabela são fornecidas para permitir o acesso sistemático aos dados de alteração pelos consumidores.

Um bom exemplo de um consumidor de dados que é direcionado por essa tecnologia é 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 dentro do data warehouse deva refletir alterações nas tabelas de origem, uma tecnologia de ponta a ponta que atualiza uma réplica da origem não é apropriada. 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.

Alterar fluxo de dados de captura de dados

A ilustração a seguir mostra o fluxo de dados principal para a captura de dados de alteração.

A fonte de dados de alteração para captura de dados de alteração é 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 serve como entrada para o processo de captura. Este processo lê o log e adiciona informações sobre mudanças à tabela de alterações associada à tabela controlada. 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.

Noções básicas sobre a Captura de Dados de Alteração e a Instância de Captura

Antes que as alterações em tabelas individuais em 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á habilitado, as tabelas de origem podem ser identificadas como tabelas rastreadas 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. Metadados que descrevem os detalhes de configuração da instância de captura são mantidos nas tabelas de metadados de captura de dados de mudança, 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 é que ele seja um nome de objeto válido e que ele seja 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 anexando-se fn_cdc_get_all_changes_ ao nome da instância de captura. Se a instância de captura estiver configurada para dar suporte net changes, a net_changes função de consulta também será criada e nomeada anexando fn_cdc_get_net_changes_ ao nome da instância de captura.

Alterar Tabela

As cinco primeiras colunas de uma tabela de alteração de captura de dados são colunas de metadados. Elas fornecem informações adicionais relevantes para a alteração registrada. As colunas restantes espelham as colunas capturadas e identificadas da tabela de origem por nome e, normalmente, por tipo. Essas colunas contêm os dados de coluna capturados 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, 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 de coluna antes da atualização e uma segunda entrada de linha para identificar os valores da coluna após a atualização.

Cada linha em uma tabela de alterações também contém metadados adicionais para permitir a interpretação da atividade de alteração. A coluna __$start_lsn identifica o LSN (número de sequência de log de confirmação) atribuído à alteração. O LSN de confirmação não apenas identifica as alterações que foram confirmadas na 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 inserir e excluir entradas, a máscara de atualização sempre terá todos os bits definidos. No entanto, as linhas de atualização terão apenas os bits definidos que correspondem às colunas alteradas.

Alterar o intervalo de validade da captura de dados para um banco de dados

O intervalo de validade de 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 atual.

Os dados depositados nas tabelas de alteração aumentam de modo não gerenciável se você não diminuir periódica e sistematicamente os dados. O processo de limpeza de captura de mudança de dados é responsável por impor a política de limpeza baseada em retenção. Primeiro, ele move o ponto de extremidade baixo do intervalo de validade para atender à restrição de tempo. Em seguida, ele remove entradas de tabela de alterações expiradas. Por padrão, três dias de dados são retidos.

No nível superior, à medida que o processo de captura confirma cada novo lote de dados de alteração, novas entradas são adicionadas para cdc.lsn_time_mapping para cada transação com entradas na tabela de alteração. Dentro da tabela de mapeamento, são mantidos tanto um LSN (número de sequência de log de commit) quanto um tempo de confirmação de transação, nas colunas start_lsn e tran_end_time, respectivamente. O valor máximo de LSN encontrado cdc.lsn_time_mapping representa o limite máximo da janela de validade do banco de dados. O tempo de confirmação correspondente é utilizado como base para que a limpeza baseada em retenção calcule um novo limite inferior.

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 a hora em que a alteração aparece em sua tabela de alterações associada. Embora essa latência seja normalmente pequena, no entanto, é importante lembrar que os dados de alteração não estão disponíveis até que o processo de captura tenha processado as entradas de log relacionadas.

Alterar o intervalo de validade da captura de dados para uma 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 é iniciado quando o processo de captura reconhece a instância de captura e começa a registrar em log as alterações associadas à tabela de alterações. Como resultado, se as instâncias de captura forem criadas em momentos diferentes, cada uma terá inicialmente um ponto de extremidade baixo diferente. A coluna start_lsn do conjunto de resultados retornado pelo sys.sp_cdc_help_change_data_capture mostra o ponto de extremidade inferior atual para cada instância de captura definida. Quando o processo de limpeza remove as entradas de tabela de alterações, ele ajusta os valores de start_lsn para todas as instâncias de captura, refletindo o novo patamar mínimo para os dados de alteração disponíveis. Somente as instâncias de captura que têm valores start_lsn que atualmente são menores do que a nova marca d'água baixa são ajustadas. Ao longo do tempo, se nenhuma nova instância de captura for criada, os intervalos de validade para todas as instâncias individuais tendem 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 o ponto de extremidade baixo do intervalo de extração for à esquerda do ponto de extremidade baixo do intervalo de validade, poderá haver dados de alteração ausentes devido à limpeza agressiva. Se o ponto de extremidade alto do intervalo de extração estiver à direita do ponto de extremidade alto do intervalo de validade, o processo de captura ainda não foi processado durante o período de tempo representado pelo intervalo de extração e os dados de alteração também poderão estar 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 é usado para recuperar o valor LSN máximo atual. Ao consultar 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.

Manipulando alterações em tabelas de origem

Lidar com as alterações de coluna nas tabelas de origem que estão sendo acompanhadas é um problema difícil para os consumidores finais. Embora a habilitação da captura de dados de alteração em uma tabela de origem não impeça que essas alterações de DDL ocorram, a captura de dados de alteração ajuda a atenuar o efeito sobre os consumidores, permitindo que os conjuntos de resultados entregues que são retornados por meio da API permaneçam inalterados, mesmo quando a estrutura de colunas da tabela de origem subjacente é alterada. Essa estrutura de coluna fixa também é refletida na tabela de alterações subjacente que as funções de consulta definidas acessam.

Para acomodar uma tabela de alteração de estrutura de coluna fixa, o processo de captura responsável por preencher a tabela de alterações ignorará as novas colunas que não sã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 registra quaisquer alterações detectadas na estrutura das colunas de tabelas rastreadas na tabela cdc.ddl_history. Os consumidores que desejam ser alertados sobre os ajustes que podem ter que ser feitos em aplicativos downstream, usam o procedimento armazenado sys.sp_cdc_get_ddl_history.

Normalmente, a instância de captura atual continuará a manter sua forma quando as alterações de DDL forem aplicadas à tabela de origem associada. No entanto, é possível criar uma segunda instância de captura para a tabela que reflete a nova estrutura de colunas. Isso 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 coluna diferentes. Assim, enquanto uma tabela de alterações pode continuar a alimentar os programas operacionais atuais, a segunda pode impulsionar um ambiente de desenvolvimento que está tentando incorporar os novos dados de 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 as duas linhas do tempo de captação de dados de mudança se sobrepõem. Quando a transição é efetivada, a instância de captura obsoleta pode ser removida.

Relação entre a Tarefa de Captura e o Leitor de Log de Replicação Transacional

A lógica do processo de captura de dados de alteração é inserida no procedimento armazenado sp_replcmds, 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. Quando a captura de dados de alteração estiver habilitada apenas para um banco de dados, você criará o trabalho de captura do SQL Server Agent como o veículo para invocar sp_replcmds. Quando a replicação também está presente, o leitor de logs transacional é usado solitariamente para atender às necessidades de dados de alteração de ambos os consumidores. Essa estratégia reduz significativamente a contenção de log quando a replicação e a captura de dados de alteração estão habilitadas para o mesmo banco de dados.

A opção 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.

A tarefa principal do processo de captura é escanear o log e gravar dados de coluna e informações relacionadas à transação nas tabelas de captura de dados de alteração. Para garantir um limite de consistência transacional em todas as tabelas de alterações 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 detecta quando as tabelas estão habilitadas recentemente para a captura de dados de alteração e as inclui automaticamente no conjunto de tabelas que são monitoradas ativamente para entradas de alteração no log. Da mesma forma, a desabilitação da captura de dados de alteração também será detectada, fazendo com que a tabela de origem seja removida do conjunto de tabelas monitoradas ativamente para alterar dados. Quando o processamento de uma seção do log é concluído, o processo de captura sinaliza a lógica de truncamento de log do servidor, que usa essas informações para identificar entradas de log qualificadas para truncamento.

O processo de captura também é usado para manter o histórico das alterações de DDL em tabelas rastreadas. As instruções DDL associadas à captura de dados de alteração fazem entradas no log de transações de 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, em seguida, posta os eventos DDL associados na tabela cdc.ddl_history. Você pode obter informações sobre eventos DDL que afetam tabelas controladas usando o procedimento armazenado sys.sp_cdc_get_ddl_history.

Trabalhos do Agente de Captura de Dados de Alteração

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 um 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 definido de captura de dados de alteração que implementa a lógica do trabalho. Os trabalhos são criados quando a primeira tabela do banco de dados está habilitada para captura de alterações de dados. 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. A tarefa de captura também é criada quando a captura de dados de alteração e a replicação transacional são habilitadas para um banco de dados, e o trabalho do logreader 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ã. Ele mantém as entradas de tabela de alteração por 4320 minutos ou 3 dias, removendo um máximo de 5.000 entradas com uma única instrução delete.

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 pode ser removido quando a primeira publicação é adicionada a um banco de dados, desde que a captura de dados de alteração e a replicação transacional estejam habilitadas.

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 exibidos. O trabalho de captura e o trabalho de limpeza extraem parâmetros de configuração da tabela msdb.dbo.cdc_jobs na inicialização. Todas as alterações feitas nesses valores usando sys.sp_cdc_change_job não entrarão em vigor até que o trabalho seja interrompido e reiniciado.

Dois procedimentos armazenados adicionais 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.

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 uma personalização adicional seja possível.

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 estiver em execução na conta NETWORK SERVICE. Isso pode resultar no erro 22832.

Consulte Também

Rastrear alterações de dados (SQL Server)
Habilitar e desabilitar o Change Data Capture (SQL Server)
Trabalhar com Dados de Mudança (SQL Server)
Administrar e monitorar a captura de dados de alteração (SQL Server)