Trabalhar com os dados de alterações

Aplica-se a:SQL ServerInstância Gerenciada de SQL do Azure

Os dados de alterações são disponibilizados aos consumidores da captura de dados de alterações (CDC) 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 LSNs (números de sequência de log) qualificados para serem considerados no desenvolvimento do conjunto de dados retornado. Tanto o valor superior quanto o valor inferior de LSN que delimitam o intervalo são considerados incluídos no intervalo.

Muitas funções são fornecidas para ajudar a determinar os valores LSN apropriados para serem usados em uma consulta a uma 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 durante o qual os dados de alteração ficam disponíveis para as 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 fechados, algumas vezes é necessário gerar o próximo valor LSN em uma sequência para garantir que as alterações não serão 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 em um valor LSN.

Validar limites de LSN

Antes de utilizar os limites de LSN que devem ser usados em uma consulta de TVF, é recomendável validá-los. Extremidades nulas ou extremidades que estejam fora do intervalo de validade de uma instância de captura farão com que uma TVF de captura de dados de alterações retorne um erro.

Por exemplo, o erro abaixo é retornado para uma consulta de todas as alterações quando um parâmetro que é utilizado para definir o intervalo de consultas não é válido ou está fora do intervalo válido ou quando a opção de filtro de linhas é 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_ ...

Falhas de autorização resultarão em falha ao consultar todas as alterações, conforme mostrado abaixo:

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

No SQL Server Management Studio, 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 estiver sendo rastreada e da configuração da instância de captura, serão geradas uma ou duas TVFs para consultar dados de alteração.

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

• 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 é habilitada.

  Observação

  Esta 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 netchanges função retorna uma alteração por linha de tabela de origem modificada. Se forem registradas mais de uma alteração 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, a TVF deverá considerar tanto a operação inicial na linha durante o intervalo quanto a operação final na linha. Quando a opção de filtro de linha “all” for especificada, as operações retornadas por uma consulta net changes serão insert, delete ou update (novos valores). Esta opção sempre retorna a máscara de atualização como nula, pois há um custo associado ao cálculo de uma máscara de agregação. Caso queira uma máscara de agregação que reflita todas as alterações em uma linha, use a opção 'all with mask'. Se o processamento posterior não exigir que inserções e atualizações sejam diferenciadas, use a opção 'tudo com mesclagem'. Nesse caso, a operação aceitará somente dois valores: 1 para exclusão e 5 para uma operação que pode ser uma inserção ou uma atualização. Esta opção elimina o processamento adicional necessário para determinar se a operação derivada deveria 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 por uma função de consulta é uma representação compacta que identifica todas as colunas alteradas em uma linha de dados de alteração. Normalmente, essas informações são necessárias apenas para um pequeno subconjunto de colunas capturadas. Há funções disponíveis para ajudar a extrair informações da máscara em uma forma que seja mais diretamente utilizável por aplicativos. A função sys.fn_cdc_get_column_ordinal retorna a posição ordinal de uma coluna nomeada relativa a uma dada instância de captura, ao passo que a função sys.fn_cdc_is_bit_set retorna a paridade do bit da máscara fornecida com base no ordinal passado na chamada de função. Juntas, essas duas funções permitem extrair com eficiência as informações da máscara de atualização e retorná-las com a solicitação de dados de alteração. No SQL Server Management Studio, consulte o modelo Enumerar Alterações na Rede usando Todas com Máscara para uma demonstração de como essas funções são usadas.

Cenários de funções de consulta

As seções a seguir descrevem cenários comuns para consultar dados de captura de dados de alterações usando as funções de consulta cdc.fn_cdc_get_all_changes_<capture_instance> 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 simples 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 de 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. No SQL Server Management Studio, consulte o modelo Enumerar Todas as Alterações para o Intervalo Válido para obter um código de exemplo para consultar todas as alterações atualmente válidas usando a função de consulta cdc.fn_cdc_get_all_changes_<capture_instance>. No SQL Server Management Studio, consulte o modelo Enumerar Alterações na Rede para o Intervalo Válido para obter um exemplo semelhante de como usar a função cdc.fn_cdc_get_net_changes_<capture_instance>.

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

Em aplicativos típicos, consultar dados de alteração será um processo constante, fazendo solicitações periódicas de todas as alterações ocorridas desde a última solicitação. Para tais consultas, você pode usar a função sys.fn_cdc_increment_lsn para derivar o limite inferior da consulta atual com base no limite superior da consulta anterior. Este método assegura que nenhuma linha seja repetida porque o intervalo de consulta é sempre tratado como um intervalo fechado, onde os dois pontos de extremidade estão incluídos no intervalo. Em seguida, use a função sys.fn_cdc_get_max_lsn para obter o limite superior do intervalo da nova solicitação. No SQL Server Management Studio, 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.

Consultar todas as novas alterações até agora

Uma restrição típica imposta sobre as alterações retornadas por uma função de consulta é incluir apenas as alterações que ocorreram entre a solicitação anterior e 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 sobre o intervalo de tempo é expresso como um momento determinado, ele deve ser convertido em um valor LSN para que possa ser usado por uma função de consulta. Antes que o valor de data e hora possa ser convertido em um valor LSN correspondente, você deve garantir que o processo de captura tenha processado todas as alterações confirmadas até o limite superior especificado. Isso é necessário para garantir que todas as alterações aplicáveis tenham sido propagadas para a tabela de alterações. Uma forma de fazer isso é estruturar um loop de espera que verifique periodicamente se o lsn de confirmação máximo atual registrado para qualquer tabela de alteração do banco de dados ultrapassa a hora de término desejada do intervalo de solicitação.

Depois que o laço de espera 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 limite superior expresso em um valor de LSN. Para garantir que todas as entradas gravadas até o horário especificado sejam recuperadas, chame a função sys.fn_cdc_map_time_to_lsn e use a opção "o maior valor 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.

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

A hora de confirmação de cada transação com uma entrada associada de uma tabela de alterações do banco de dados fica disponível na tabela cdc.lsn_time_mapping. Ao associar o valor __$start_lsn retornado em uma solicitação de todas as alterações ao valor start_lsn de uma entrada da tabela cdc.lsn_time_mapping, você pode retornar o tran_end_time junto com os dados de alteração para registrar a alteração com a hora de confirmação da transação na origem. O modelo Adicionar o horário do commit ao conjunto de resultados All Changes demonstra como executar essa junção.

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

Ocasionalmente, é útil combinar dados de alteração com outras informações coletadas a respeito da transação no momento em que ela foi confirmada na origem. A tran_begin_lsn coluna na tabela cdc.lsn_time_mapping fornece as informações necessárias para executar essa junção. Quando ocorre a atualização da origem, o valor de database_transaction_begin_lsn da exibição dinâmica do sistema sys.dm_tran_database_transactions deve ser salvo junto com qualquer outra informação a ser unida aos dados de alteração. Use a função fn_convertnumericlsntobinary para comparar os valores 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 influenciar a junção.

Consulta com funções de encapsulamento de DateTime

Um cenário de aplicativo típico para consultar dados de alteração é solicitar dados de alteração periodicamente usando uma janela deslizante vinculada por valores de data e hora. Para esta classe de consumidores, o Change Data Capture 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 do Change Data Capture. Esses wrappers personalizados permitem que o intervalo de consulta seja expresso como um par de data/hora.

As opções de chamada do procedimento armazenado permitem que encapsulamentos 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 com suporte também incluem a capacidade de especificar se o ponto de extremidade superior do intervalo de captura deve ser aberto ou fechado, quais colunas capturadas disponíveis devem ser incluídas no conjunto de resultados e quais 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 pode ser derivado do nome da instância de captura, e a instrução de criação para o procedimento armazenado de encapsulamento. A função para encapsular a consulta de todas as alterações é sempre gerada. Se o parâmetro @supports_net_changes foi definido quando a instância de captura foi criada, a função para encapsular a função de alterações líquidas também é gerada.

É de responsabilidade do designer do aplicativo chamar o procedimento armazenado de geração de script para gerar as instruções de criação para os procedimentos armazenados do wrapper, bem como 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 de data e hora pertencem ao usuário e não são criados no esquema padrão do chamador. A função gerada é adequada e não requer modificação para a maioria dos usuários. Todavia, antes de criar a função sempre é possível aplicar mais personalizações ao script gerado.

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 aceitam três argumentos, assim como as TVFs de captura de dados de alteração associadas. No entanto, o intervalo de consulta para os wrappers é vinculado por dois valores de data e hora, e não por dois valores LSN. O parâmetro @row_filter_option para os dois 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 da alteração de captura de dados: espera-se que o parâmetro @end_time do intervalo anterior seja usado como o parâmetro @start_time do intervalo subsequente. A função de wrapper mapeia os valores de data e hora para valores LSN e assegura que nenhum dado seja perdido ou repetido se esta convenção for seguida.

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

Embora as TVFs da consulta gerada falhem se for indicado um valor nulo para o valor @from_lsn ou @to_lsn, as funções de wrapper de data e hora usam nulo para permitir que esses wrappers retornem todas as alterações atuais. Ou seja, se NULL for passado como o limite inferior da janela de consulta para o wrapper de datetime, o limite inferior do intervalo de validade da instância de captura será usado na instrução SELECT subjacente aplicada à TVF de consulta. De maneira semelhante, se nulo for passado como o ponto de extremidade superior da janela de consulta, o ponto de extremidade superior do intervalo de validade da instância de captura será usado para fazer uma seleção na TVF da consulta.

O conjunto de resultados retornado por uma função encapsuladora 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. Caso sinalizadores de atualização tenham sido solicitados, serão exibidos como colunas de bit após o código da operação, na ordem especificada no parâmetro @update_flag_list. Para obter informações sobre as opções de chamada para personalizar os wrappers de data e hora gerados, consulte sys.sp_cdc_generate_wrapper_function (Transact-SQL).

O modelo Instanciar uma Função Wrapper TVF com Sinalizador de Atualização mostra como personalizar uma função wrapper gerada para anexar ao conjunto de resultados retornado por uma consulta de alterações líquidas um sinalizador de atualização para uma coluna especificada. O modelo Instantiate CDC Wrapper TVFs for a Schema mostra como instanciar os encapsuladores Datetime das 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 ver um exemplo que usa um wrapper de datetime para consultar dados de alterações, no SQL Server Management Studio, consulte o modelo Get Net Changes Using Wrapper With Update Flags. Este modelo demonstra como consultar mudanças líquidas com uma função wrapper quando a função estiver configurada para retornar flags 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 inferior e superior do intervalo de datetime para indicar à função que use a extremidade inferior e a extremidade superior do intervalo de validade da instância de captura ao executar a consulta subjacente baseada em LSN. A consulta retorna uma linha para cada modificação em uma linha de origem ocorrida no intervalo válido para a instância de captura.

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

O Change Data Capture dá suporte a até duas instâncias de captura para uma única tabela de origem rastreada. O principal uso desta capacidade é permitir uma transição entre múltiplas instâncias de captura quando alterações de DDL na tabela de origem expandem o conjunto de colunas disponíveis para rastreamento. Ao transitar para uma nova instância de captura, uma forma de proteger níveis de aplicativos superiores contra alterações nos nomes de funções de consulta subjacentes é usar uma função de wrapper para incluir a chamada subjacente. Em seguida, atente para que o nome da função de wrapper permaneça o mesmo. Quando a transição tiver de ser feita, a antiga função de wrapper poderá ser descartada, e uma nova com o mesmo nome poderá ser criada para fazer referência às novas funções de consulta. Se primeiro você modificar o script gerado para criar uma função de wrapper de mesmo nome, poderá fazer a transição para uma nova instância de captura sem afetar as camadas de aplicativos superiores.

Conteúdo relacionado

• Captura de dados de alteração (CDC) com Banco de Dados SQL do Azure
• Rastrear alterações de dados (SQL Server)
• O que é a CDA (captura de dados de alterações)?
• Habilitar e desabilitar a captura de dados de alteração
• Administrar e monitorar a captura de dados de alteração