Trabalhar com dados de mudanças (SQL Server)

Os dados de alteração são disponibilizados para alterar os consumidores de captura de dados por meio de TVFs (funções com valor de tabela). Todas as consultas dessas funções exigem dois parâmetros para definir o intervalo de LSNs (Números de Sequência de Log) que são elegíveis para consideração ao desenvolver o conjunto de resultados retornado. Os valores LSN superior e inferior que associam o intervalo são considerados incluídos dentro do intervalo.

Várias funções são fornecidas para ajudar a determinar os valores LSN apropriados para uso na consulta de um TVF. A função sys.fn_cdc_get_min_lsn retorna o menor LSN associado a um intervalo de validade da instância de captura. O intervalo de validade é o intervalo de tempo para o qual os dados de alteração estão disponíveis atualmente para suas instâncias de captura. A função sys.fn_cdc_get_max_lsn retorna o maior LSN no intervalo de validade. As funções sys.fn_cdc_map_time_to_lsn e sys.fn_cdc_map_lsn_to_time estão disponíveis para ajudar a colocar valores LSN em uma linha do tempo convencional. Como a captura de dados de alteração usa intervalos de consulta fechados, às vezes é necessário gerar o próximo valor LSN em uma sequência para garantir que as alterações não sejam duplicadas em janelas de consulta consecutivas. As funções sys.fn_cdc_increment_lsn e sys.fn_cdc_decrement_lsn são úteis quando um ajuste incremental a um valor LSN é necessário.

Validando limites de LSN

É recomendável validar os limites LSN que devem ser usados em uma consulta TVF antes de seu uso. Pontos de extremidade nulos ou pontos de extremidade que estão fora do intervalo de validade de uma instância de captura forçarão um erro a ser retornado por uma TVF de captura de dados de alteração.

Por exemplo, o erro a seguir é retornado para uma consulta para todas as alterações quando um parâmetro usado para definir o intervalo de consulta não é válido ou está fora do intervalo ou a opção de filtro de linha é inválida.

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes_ ...

O erro correspondente retornado para uma net changes consulta é o seguinte:

Msg 313, Level 16, State 3, Line 1

An insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_net_changes_ ...

As falhas de autorização retornarão falhas ao consultar todas as alterações, conforme mostrado:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'fn_cdc_get_all_changes_...', database 'MyDB', schema 'cdc'.

O mesmo é verdadeiro ao consultar alterações líquidas.

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object fn_cdc_get_net_changes_...', database 'MyDB', schema 'cdc'.

Consulte o modelo Enumerar Alterações na Rede usando TRY CATCH para obter uma demonstração de como interceptar esses erros de TVF conhecidos e retornar informações mais significativas sobre a falha.

Funções de consulta

Dependendo das características da tabela de origem que está sendo rastreada e da maneira como sua instância de captura é configurada, um ou dois TVFs para consultar dados de alteração são gerados.

• A função cdc.fn_cdc_get_all_changes_<capture_instance> retorna todas as alterações que ocorreram para o intervalo especificado. Essa função sempre é gerada. As entradas sempre são retornadas classificadas, primeiro pelo LSN de confirmação de transação da alteração e, em seguida, por um valor que sequencia a alteração dentro de sua transação. Dependendo da opção de filtro de linha escolhida, a linha final é retornada na atualização (opção de filtro de linha "all") ou os valores novos e antigos são retornados na atualização (opção de filtro de linha "todas as atualizações antigas").

• A função cdc.fn_cdc_get_net_changes_<capture_instance> é gerada quando o parâmetro @supports_net_changes é definido como 1 quando a tabela de origem está habilitada.

  Observação

  Essa opção só terá suporte se a tabela de origem tiver uma chave primária definida ou se o parâmetro @index_name tiver sido usado para identificar um índice exclusivo.

  A função netchanges retorna uma alteração por linha de tabela de origem modificada. Se mais de uma alteração for registrada para a linha durante o intervalo especificado, os valores de coluna refletirão o conteúdo final da linha. Para identificar corretamente a operação necessária para atualizar o ambiente de destino, o TVF deve considerar a operação inicial na linha durante o intervalo e a operação final na linha. Quando a opção de filtro de linha 'all' for especificada, as operações retornadas por uma net changes consulta serão inseridas, excluídas ou atualizadas (novos valores). Essa opção sempre retorna a máscara de atualização como nula porque há um custo associado à computação de uma máscara de agregação. Se você precisar de uma máscara de agregação que reflita todas as alterações em uma linha, use a opção "tudo com máscara". Se o processamento downstream não exigir que inserções e atualizações sejam distinguidas, use a opção "todos com mesclagem". Nesse caso, o valor da operação assumirá apenas dois valores: 1 para exclusão e 5 para uma operação que pode ser uma inserção ou uma atualização. Essa opção elimina o processamento adicional necessário para determinar se a operação derivada deve ser uma inserção ou uma atualização e pode melhorar o desempenho da consulta quando essa diferenciação não for necessária.

A máscara de atualização retornada de uma função de consulta é uma representação compacta que identifica todas as colunas que foram alteradas em uma linha de dados modificados. Normalmente, essas informações são necessárias apenas para um pequeno subconjunto das colunas capturadas. As funções estão disponíveis para auxiliar na extração de informações da máscara em um formulário que é mais utilizável diretamente por aplicativos. A função sys.fn_cdc_get_column_ordinal retorna a posição ordinal de uma coluna nomeada para uma determinada instância de captura, enquanto a função sys.fn_cdc_is_bit_set retorna a paridade do bit na máscara fornecida com base no ordinal que foi passado na chamada de função. Juntas, essas duas funções permitem que as informações da máscara de atualização sejam extraídas e retornadas com eficiência com a solicitação de dados de alteração. Consulte o modelo Enumerar Alterações da Rede usando Todas com Máscara para obter uma demonstração de como essas funções são usadas.

Cenários de função de consulta

As seções a seguir descrevem cenários comuns para consultar dados de captura de dados de alteração usando as funções de consulta cdc.fn_cdc_get_all_changes_<capture_instance> e cdc.fn_cdc_get_net_changes_<capture_instance>.

Consultar todas as alterações no intervalo de validade da instância de captura

A solicitação mais simples para dados de alteração é aquela que retorna todos os dados de alteração atuais no intervalo de validade de uma instância de captura. Para fazer essa solicitação, primeiro determine os limites LSN inferior e superior do intervalo de validade. Em seguida, use esses valores para identificar os parâmetros @from_lsn e @to_lsn passados para a função de consulta cdc.fn_cdc_get_all_changes_<capture_instance> ou cdc.fn_cdc_get_net_changes_<capture_instance>. Use a função sys.fn_cdc_get_min_lsn para obter o limite inferior e sys.fn_cdc_get_max_lsn para obter o limite superior. Consulte o modelo Enumerar Todas as Alterações para o Intervalo Válido para ver um código de exemplo que consulta todas as alterações válidas atuais usando a função de consulta cdc.fn_cdc_get_all_changes_<capture_instance>. Consulte o modelo Enumerar Alterações Líquidas para o Intervalo Válido para obter um exemplo semelhante de como usar a função cdc.fn_cdc_get_net_changes_<capture_instance>.

Consultando todas as novas alterações desde o último conjunto de alterações

Para aplicativos típicos, a consulta de dados de alteração será um processo contínuo, fazendo solicitações periódicas para todas as alterações que ocorreram desde a última solicitação. Para essas consultas, você pode usar a função sys.fn_cdc_increment_lsn para derivar o limite inferior da consulta atual do limite superior da consulta anterior. Esse método garante que nenhuma linha seja repetida porque o intervalo de consulta é sempre tratado como um intervalo fechado em que ambos os pontos de extremidade são incluídos no intervalo. Em seguida, use a função sys.fn_cdc_get_max_lsn para obter o ponto de extremidade alto para o novo intervalo de solicitação. Consulte o modelo Enumerar Todas as Alterações Desde a Solicitação Anterior de código de exemplo para mover sistematicamente a janela de consulta para obter todas as alterações desde a última solicitação.

Consultando todas as novas alterações até agora

Uma restrição típica que é colocada nas alterações retornadas por uma função de consulta é incluir apenas as alterações que ocorreram entre a solicitação anterior até a data e a hora atuais. Para essa consulta, aplique a função sys.fn_cdc_increment_lsn ao @from_lsn valor usado na solicitação anterior para determinar o limite inferior. Como o limite superior no intervalo de tempo é expresso como um ponto específico no tempo, ele deve ser convertido em um valor LSN antes de ser usado por uma função de consulta. Antes que o valor de datetime possa ser convertido em um valor LSN correspondente, você deve garantir que o processo de captura tenha processado todas as alterações que foram confirmadas até o limite superior especificado. Isso é necessário para garantir que todas as alterações qualificadas tenham sido propagadas para a tabela de alterações. Uma maneira de fazer isso é estruturar um loop de espera que verifica periodicamente se a confirmação máxima atual lsn registrada para qualquer tabela de alteração de banco de dados excede a hora de término desejada do intervalo de solicitação.

Depois que o loop de atraso verificar se o processo de captura já processou todas as entradas de log relevantes, use a função sys.fn_cdc_map_time_to_lsn para determinar o novo ponto limite superior expresso como um valor LSN. Para garantir que todas as entradas confirmadas por meio do tempo especificado sejam recuperadas, chame a função sys.fn_cdc_map_time_to_lsn e use a opção "menor ou igual".

O modelo enumera todas as alterações até agora demonstra como usar a estratégia anterior para consultar dados de alteração.

Adicionando um tempo de confirmação a um conjunto de resultados para todas as alterações

O tempo de confirmação de cada transação com uma entrada associada em uma tabela de alteração de banco de dados está disponível na tabela cdc.lsn_time_mapping. Ao combinar o valor __$start_lsn retornado em uma solicitação para todas as alterações com o valor start_lsn de uma entrada da tabela cdc.lsn_time_mapping, você pode retornar o tran_end_time juntamente com os dados de alteração para marcar a alteração com o horário de confirmação da transação na origem. O modelo Acrescentar o Tempo de Confirmação a Todas as Alterações no conjunto de resultados demonstra como executar essa junção.

Junção de dados de alteração com outros dados da mesma transação

Ocasionalmente, é útil unir dados de mudança com outras informações coletadas sobre a transação quando ela é confirmada na fonte. A coluna tran_begin_lsn na tabela cdc.lsn_time_mapping fornece as informações necessárias para executar essa junção. Quando a atualização da origem ocorre, o valor para database_transaction_begin_lsn do modo de exibição dinâmico do sistema sys.dm_tran_database_transactions deve ser salvo juntamente com qualquer outra informação a ser unida aos dados de alteração. Use a função fn_convertnumericlsntobinary para comparar os valores de database_transaction_begin_lsn e tran_begin_lsn. O código para criar essa função está disponível no modelo Criar Função fn_convertnumericlsntobinary. O modelo Retornar Todas as Alterações com um determinado tran_begin_lsn demonstra como efetuar a junção.

Consultando usando funções encapsuladoras de datetime

Um cenário típico de aplicação para consultar dados de alteração é solicitar dados periodicamente usando uma janela deslizante delimitada por valores de data e hora. Para essa classe de consumidores, o recurso de captura de dados de mudança oferece o procedimento armazenado sys.sp_cdc_generate_wrapper_function, que gera scripts para criar funções de wrapper personalizadas para as funções de consulta de captura de dados de mudança. Esses wrappers personalizados permitem que o intervalo de consulta seja expresso como um par de datas e horários.

As opções de chamada para o procedimento armazenado permitem que os wrappers sejam gerados para todas as instâncias de captura às quais o chamador tem acesso ou apenas uma instância de captura especificada. As opções com suporte também incluem a capacidade de especificar se o ponto de extremidade superior do intervalo de captura deve ser aberto ou fechado, qual das colunas capturadas disponíveis deve ser incluída no conjunto de resultados e quais das colunas incluídas devem ter sinalizadores de atualização associados. O procedimento retorna um conjunto de resultados com duas colunas: o nome da função gerada, que é derivado do nome da instância de captura, e a instrução create para o procedimento armazenado do wrapper. A função para encapsular todas as alterações da consulta é sempre gerada. Se o @supports_net_changes parâmetro tiver sido definido quando a instância de captura foi criada, a função para encapsular a função de alterações líquidas também será gerada.

É responsabilidade do designer de aplicativos chamar o procedimento armazenado de geração de script para gerar as instruções de criação para os procedimentos armazenados do wrapper e executar os scripts de criação resultantes para criar as funções. Isso não ocorre automaticamente quando uma instância de captura é criada.

Os wrappers datetime pertencem ao usuário e não são criados no esquema padrão do chamador. A função gerada é adequada sem modificação para a maioria dos usuários. No entanto, uma personalização adicional sempre pode ser aplicada ao script gerado antes de criar a função.

O nome da função para encapsular a consulta de todas as alterações é fn_all_changes_ seguido pelo nome da instância de captura. O prefixo usado para o wrapper de alterações líquidas é fn_net_changes_. Ambas as funções têm três argumentos, assim como suas TVFs de captura de dados de alteração associadas. No entanto, o intervalo de consulta para os wrappers é limitado por dois valores de datetime em vez de dois valores LSN. O @row_filter_option parâmetro para ambos os conjuntos de funções é o mesmo.

As funções de wrapper geradas dão suporte à seguinte convenção para percorrer sistematicamente a linha do tempo de captura de dados de alteração: espera-se que o @end_time parâmetro do intervalo anterior seja usado como o @start_time parâmetro do intervalo subsequente. A função wrapper cuida de mapear os valores de datetime para valores LSN e garantir que nenhum dado seja perdido ou repetido se essa convenção for seguida.

Os wrappers podem ser gerados para dar suporte a um limite superior fechado ou a um limite superior aberto na janela de consulta especificada. Ou seja, o chamador pode especificar se as entradas com um tempo de confirmação igual ao limite superior do intervalo de extração devem ser incluídas dentro do intervalo. Por padrão, o limite superior é incluído.

Embora os TVFs de consulta gerados falhem se for fornecido um valor nulo para o valor @from_lsn ou @to_lsn, as funções de encapsulamento de data e hora usam nulo para permitir que essas funções retornem todas as alterações atuais. Ou seja, se nulo for passado como o ponto final inferior da janela de consulta para o encapsulador de data/hora, o ponto final inferior do intervalo de validade da instância de captura será usado na instrução SELECT subjacente que é aplicada à TVF de consulta. Da mesma forma, se nulo for passado como o limite superior da janela de consulta, o limite superior do intervalo de validade da instância de captura será usado ao selecionar a partir da TVF da consulta.

O conjunto de resultados retornado por uma função wrapper inclui todas as colunas solicitadas seguidas por uma coluna de operação, recodificada como um ou dois caracteres para identificar a operação associada à linha. Se os sinalizadores de atualização tiverem sido solicitados, eles aparecerão como colunas de bit após o código de operação, na ordem especificada no @update_flag_list parâmetro. Para obter informações sobre as opções de chamada para personalizar os wrappers de datetime gerados, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

O modelo instanciar um TVF wrapper com sinalizador de atualização mostra como personalizar uma função de wrapper gerada para acrescentar um sinalizador de atualização para uma coluna especificada ao conjunto de resultados retornado por uma consulta de alterações líquidas. O modelo instanciar TVFs de wrapper CDC para um esquema mostra como instanciar os Wrappers datetime para os TVFs de consulta para todas as instâncias de captura criadas para as tabelas de origem em um determinado esquema de banco de dados.

Para um exemplo que utiliza um wrapper de data e hora para consultar dados de alteração, consulte o modelo Obter Alterações Líquidas Usando Wrapper Com Flags de Atualização. Este modelo demonstra como consultar mudanças líquidas usando uma função encapsuladora quando esta está configurada para retornar indicadores de atualização. Observe que a opção de filtro de linha 'tudo com máscara' é necessária para que a função de consulta subjacente retorne uma máscara de atualização não nula na atualização. Valores nulos são passados para os limites de intervalo de datetime inferior e superior para sinalizar a função para usar o ponto de extremidade baixo e o ponto de extremidade superior do intervalo de validade para a instância de captura ao executar a consulta baseada em LSN subjacente. A consulta retorna uma linha para cada modificação em uma linha de origem que ocorreu dentro do intervalo válido para a instância de captura.

Usando as funções encapsuladoras de data e hora para transitar entre instâncias de captura

A captura de dados de alteração dá suporte a até duas instâncias de captura para uma única tabela de origem controlada. O uso principal desse recurso é acomodar uma transição entre várias instâncias de captura quando a linguagem de definição de dados (DDL) for alterada para a tabela de origem expandir o conjunto de colunas disponíveis para acompanhamento. Ao fazer a transição para uma nova instância de captura, uma maneira de proteger níveis de aplicativo mais altos contra alterações nos nomes das funções de consulta subjacentes é usar uma função wrapper para encapsular a chamada subjacente. Em seguida, verifique se o nome da função wrapper permanece o mesmo. Quando a mudança deve ocorrer, a função wrapper antiga pode ser descartada, e uma nova função com o mesmo nome criada, que faz referência às novas funções de consulta. Modificando primeiro o script gerado para criar uma função wrapper com o mesmo nome, você pode fazer a opção para uma nova instância de captura sem afetar camadas de aplicativo mais altas.

Consulte Também

Rastrear alterações de dados (SQL Server)
Sobre o change data capture (SQL Server)
Habilitar e desabilitar o Change Data Capture (SQL Server)
Administrar e monitorar a captura de dados de alteração (SQL Server)