Trabalhar com dados de alteração

Aplica-se a:SQL ServerAzure SQL Managed Instance

Os dados de alteração são disponibilizados para alterar os consumidores de captura de dados por meio de funções com valor de tabela (TVFs). Todas as consultas dessas funções exigem dois parâmetros para definir o intervalo de números de sequência de log (LSNs) que são elegíveis para consideração ao desenvolver o conjunto de resultados retornado. Os valores de LSN superior e inferior que ligam 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 de instância de captura. O intervalo de validade é o intervalo de tempo para o qual os dados de alteração estão atualmente disponíveis 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 os 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 é necessário um ajuste incremental para um valor LSN.

Validar limites LSN

Recomendamos 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 para uma instância de captura forçarão um erro a ser retornado por um TVF de captura de dados de alteração.

Por exemplo, o seguinte erro é 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 consulta net changes é 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 de rede:

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'.

No SQL Server Management Studio, consulte o modelo Enumerar alterações de rede usando TRY CATCH para obter uma demonstração de como intercetar esses erros 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. Esta função é sempre gerada. As entradas são sempre retornadas classificadas, primeiro pela transação confirmar LSN 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 "todos") 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á ativada.

  Observação

  Esta opção só é suportada 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 netchanges função retorna uma alteração por linha da tabela de origem modificada. Se mais de uma alteração for registrada para a linha durante o intervalo especificado, os valores da 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' é especificada, as operações retornadas por uma consulta net changes 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 ao cálculo de uma máscara agregada. Se você precisar de uma máscara agregada que reflita todas as alterações em uma linha, use a opção 'tudo com máscara'. Se o processamento a jusante não exigir que as inserções e atualizações sejam distinguidas, use a opção 'tudo com mesclagem'. Nesse caso, o valor da operação assumirá apenas dois valores: 1 para excluir 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 é 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 de alteração. Normalmente, essas informações são necessárias apenas para um pequeno subconjunto das colunas capturadas. Funções estão disponíveis para ajudar na extração de informações da máscara em um formato que é mais diretamente utilizável pelos 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 de forma eficiente com a solicitação de dados de alteração. No SQL Server Management Studio, consulte o modelo Enumerar alterações de rede usando tudo 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 cdc.fn_cdc_get_all_changes_<capture_instance> de consulta e cdc.fn_cdc_get_net_changes_<capture_instance>.

Consulta de todas as alterações dentro do intervalo de validade da instância de captura

A solicitação mais direta de 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 inferior e superior da LSN do intervalo de validade. Em seguida, use esses valores para identificar os parâmetros @from_lsn e @to_lsn passados para a função cdc.fn_cdc_get_all_changes_<capture_instance> de consulta 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. No SQL Server Management Studio, consulte o modelo Enumerar todas as alterações para o intervalo válido para obter o código de exemplo para consultar todas as alterações válidas atuais usando a função cdc.fn_cdc_get_all_changes_<capture_instance>de consulta . No SQL Server Management Studio, consulte o modelo Enumerar alterações de rede para o intervalo válido para obter um exemplo semelhante de uso da função cdc.fn_cdc_get_net_changes_<capture_instance>.

Consultar 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 onde ambos os pontos finais são incluídos no intervalo. Em seguida, use a função sys.fn_cdc_get_max_lsn para obter o ponto final alto para o novo intervalo de solicitação. No SQL Server Management Studio, consulte o modelo Enumerar todas as alterações desde a solicitação anterior para código de exemplo para mover sistematicamente a janela de consulta para obter todas as alterações desde a última solicitação.

Consultar 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 hora atuais. Para esta consulta, aplique a função sys.fn_cdc_increment_lsn ao @from_lsn valor que foi 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 poder ser usado por uma função de consulta. Antes que o valor datetime possa ser convertido em um valor LSN correspondente, você deve garantir que o processo de captura tenha processado todas as alterações confirmadas por meio do 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 o lsn máximo de confirmação atual registrado 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 final alto expresso como um valor LSN. Para garantir que todas as entradas que foram confirmadas durante o tempo especificado sejam recuperadas, chame a função sys.fn_cdc_map_time_to_lsne use a opção 'maior menor ou igual'.

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

Adicionar um tempo de confirmação a um conjunto de resultados de 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 juntar o valor __$start_lsn retornado em uma solicitação para todas as alterações com o valor start_lsn de uma cdc.lsn_time_mapping entrada de tabela, você pode retornar o tran_end_time juntamente com os dados de alteração para carimbar a alteração com o tempo de confirmação da transação na origem. O modelo Append Commit Time to All Changes Result set demonstra como executar essa junção.

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

Ocasionalmente, é útil juntar dados de alteração com outras informações coletadas sobre a transação quando ela foi confirmada na fonte. A tran_begin_lsn coluna na tabela cdc.lsn_time_mapping fornece as informações necessárias para realizar essa junção. Quando ocorre a atualização da fonte, o valor para database_transaction_begin_lsn do sys.dm_tran_database_transactions de visualização dinâmica do sistema deve ser salvo junto com quaisquer outras informações a serem unidas aos dados de alteração. Use a função fn_convertnumericlsntobinary para comparar os database_transaction_begin_lsn valores e tran_begin_lsn . O código para criar esta função está disponível no modelo Create Function fn_convertnumericlsntobinary. O modelo Return All Changes with a Given tran_begin_lsn demonstra como afetar a junção.

Consulta usando funções de wrapper DateTime

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

As opções de chamada para o procedimento armazenado permitem que wrappers sejam gerados para todas as instâncias de captura às quais o chamador tem acesso ou apenas para uma instância de captura especificada. As opções suportadas também incluem a capacidade de especificar se o ponto final alto do intervalo de captura deve ser aberto ou fechado, quais das colunas capturadas disponíveis devem ser incluídas 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 é derivável do nome da instância de captura, e a instrução create para o procedimento armazenado do wrapper. A função para encapsular a consulta de todas as alterações é sempre gerada. Se o @supports_net_changes parâmetro foi definido quando a instância de captura foi criada, a função para encapsular a função net changes também é gerada.

É responsabilidade do designer de aplicativos chamar o procedimento armazenado de geração de script para gerar as instruções create 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, a 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 de rede é fn_net_changes_. Ambas as funções usam três argumentos, assim como seus TVFs de captura de dados de mudança associados. No entanto, o intervalo de consulta para os wrappers é limitado por dois valores datetime em vez de dois valores LSN. O @row_filter_option parâmetro para ambos os conjuntos de funções são os mesmos.

As funções de wrapper geradas suportam a 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 parâmetro @start_time do intervalo subsequente. A função wrapper cuida de mapear os valores 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 suportar um limite superior fechado ou 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.

Enquanto os TVFs de consulta gerados falham se for fornecido um valor nulo para o @from_lsn valor ou o @to_lsn valor, as funções de wrapper datetime usam null para permitir que os wrappers datetime retornem todas as alterações atuais. Ou seja, se null for passado como o ponto final baixo da janela de consulta para o wrapper datetime, o ponto de extremidade baixo do intervalo de validade da instância de captura será usado na instrução subjacente SELECT que é aplicada ao TVF de consulta. Da mesma forma, se null for passado como o ponto final alto da janela de consulta, o ponto final alto do intervalo de validade da instância de captura será usado ao selecionar a partir do TVF de 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 bits após o código da 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 datetime gerados, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

O modelo Instanciar um TVF de invólucro 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 net changes. O modelo Instanciate CDC Wrapper TVFs for a Schema mostra como instanciar os Datetime Wrappers 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 obter um exemplo que usa um wrapper datetime para consultar dados de alteração, no SQL Server Management Studio, consulte o modelo Get Net Changes Using Wrapper With Update Flags. Este modelo demonstra como consultar alterações de rede com uma função wrapper quando o wrapper está configurado para retornar sinalizadores de atualização. 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 data/hora inferior e superior para sinalizar a função para usar o ponto de extremidade baixo e o ponto de extremidade alto 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.

Use as funções de wrapper DateTime para fazer a transição entre instâncias de captura

A captura de dados de alteração suporta até duas instâncias de captura para uma única tabela de origem controlada. O principal uso desse recurso é acomodar uma transição entre várias instâncias de captura quando a linguagem de definição de dados (DDL) muda para a tabela de origem e expande o conjunto de colunas disponíveis para rastreamento. 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, certifique-se de que o nome da função wrapper permanece o mesmo. Quando a opção deve ocorrer, a função de wrapper antiga pode ser descartada e uma nova com o mesmo nome criada que faz referência às novas funções de consulta. Ao modificar primeiro o script gerado para criar uma função de wrapper com o mesmo nome, você pode alternar para uma nova instância de captura sem afetar as camadas superiores do aplicativo.

Conteúdo relacionado

• CDC com Banco de Dados SQL do Azure
• Controlar alterações de dados (SQL Server)
• O que é captura de dados de mudança (CDC)?
• Ativar e desativar a captura de dados de alteração
• Administrar e monitorar a captura de dados de alteração