Trabalhar com dados de alterações (SQL Server)

Os dados de alteração ficam disponíveis para consumidores de Change Data Capture através das 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) qualificados para serem considerados no desenvolvimento do conjunto de dados retornado. Tanto o valor superior quanto o valor inferior do LSN indica que o limite do intervalo é considerado ao ser incluído 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.

Neste tópico

• Validando limites de LSN

• Funções de consulta

• Cenários de funções de consulta

Validando limites de LSN

Antes de utilizar os limites de LSN que devem ser usados em uma consulta de TVF, é recomendável validá-los. Os pontos de extremidades nulos ou pontos de extremidade que ficam fora do intervalo de validade da instância de captura forçará um erro que será retornado por uma TVF de captura de dados de alteração.

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

Observação

É sabido que a mensagem Msg 313 é equivocada e não informa a causa real da falha. Esse uso inadequado é originário da falta de habilidade para elevar um erro explícito de dentro de uma TVF. No entanto, o valor de retorno de um erro reconhecido, mesmo inexato, foi considerado preferível ao retorno de apenas um resultado vazio. Um conjunto de resultados vazio não seria perceptível a partir de uma consulta válida que não retorna nenhuma alteração.

Falhas de autorização retornarão falhas ao consultar todas as alterações, como a seguir:

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 ocorre ao realizar consultas em 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 Enumerate Net Changes Using TRY CATCH para ver uma demonstração de como interceptar esses erros de TVF conhecidos e retornar informações mais significativas sobre a falha.

Observação
Para localizar modelos de Change Data Capture no SQL Server Management Studio, no menu Exibir, clique em Gerenciador de Modelos, expanda Modelos do SQL Server e expanda a pasta Change Data Capture.

[Início]

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_<instância_de_captura> retorna todas as alterações ocorridas no 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 e a tabela de origem está habilitada.

  Observação
  Essa opção terá suporte apenas 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 net changes retorna uma alteração por linha modificada da tabela de origem. 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 inserção, exclusão ou atualização (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 downstream não exigir que inserções e atualizações sejam diferenciadas, use a opção 'all with merge'. 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 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 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 que as informações da coluna atualizada sejam extraídas de maneira eficaz e retornadas com a solicitação de dados de alteração. Consulte o modelo Enumerate Net Changes Using All With Mask para ver uma demonstração de como essas funções são usadas.

[Início]

Cenários de funções de consulta

As seções seguintes 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>.

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

A solicitação mais objetiva de dados de alteração é a que retorna todos os dados de alteração atuais em um intervalo de validade da 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. Consulte o modelo Enumerate All Changes for the Valid Range a fim de ver o código de exemplo para consultar todas as alterações válidas atuais usando a função de consulta cdc.fn_cdc_get_all_changes_<capture_instance>. Consulte o modelo Enumerate Net Changes for the Valid Range 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 feitas 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 ponto de extremidade superior relativo ao intervalo de solicitação. Consulte o modelo Enumerate All Changes Since Previous Request a fim de ver o código de exemplo para mover sistematicamente a janela de consulta e obter todas as alterações feitas desde a última solicitação.

Consultando todas as novas alterações feitas 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 valor @from\_lsn que foi 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. Para que o valor de data e hora seja convertido em um valor LSN correspondente, você deve assegurar que o processo de captura processou todas as alterações confirmadas através do limite superior especificado. Isso é necessário para garantir que todas as alterações qualificadas foram propagadas na tabela de alteração. 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 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 de extremidade superior expresso como um valor LSN. Para garantir que todas as entradas que foram confirmadas pela hora especificada sejam recuperadas, chame a função sys.fn_cdc_map_time_to_lsn e use a opção 'maior menor que ou igual'.

Observação
Em períodos de inatividade, uma entrada fictícia é adicionada à tabela cdc.lsn_time_mapping para indicar que o processo de captura processou as alterações até uma determinada hora de confirmação. Isso impede que se tenha a impressão de que o processo de captura não foi executado quando simplesmente não existem alterações a serem processadas.

O modelo Enumerate All Changes Up Until Now demonstra como usar a estratégia anterior para consultar dados de alteração.

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

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

De vez em quando é útil unir dados de alteração com outras informações coletadas sobre a transação quando confirmada na origem. A coluna tran_begin_lsn da tabela cdc.lsn_time_mapping fornece as informações necessárias para fazer tal 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 juntamente 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 Create Function fn_convertnumericlsntobinary. O modelo Return All Changes with a Given tran_begin_lsn demonstra como efetuar a junção.

Consultando com funções de wrapper de data e hora

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.

Chamar opções do procedimento armazenado permite 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 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 de função gerado que é derivado do nome da instância de captura, e a instrução de criação para o procedimento armazenado do wrapper. A função usada para incluir a consulta de todas as alterações sempre é gerada. Se o parâmetro @supports\_net\_changes foi definido durante a criação da instância de captura, a função usada para incluir as alterações líquidas também será 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 usada 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 usam três argumentos, assim como suas TVFs associadas do Change Data Capture. 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 oferecem suporte à convenção a seguir para percorrer sistematicamente a linha do tempo do Change Data Capture: 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 que têm uma hora de confirmação 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 fornecido 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 nulo for passado como o ponto de extremidade inferior da janela de consulta para o wrapper de data e hora, o ponto de extremidade inferior do intervalo de validade da instância de captura será usado na instrução SELECT subjacente aplicada à TVF da 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 de wrapper inclui todas as colunas solicitadas seguidas por uma coluna de operação, gravada como um ou dois caracteres que identificam a operação associada à linha. Caso os sinalizadores de atualização tenham sido solicitados, eles 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 Instantiate a Wrapper TVF With Update Flag mostra como personalizar uma função de wrapper gerada para acrescentar um sinalizador de atualização de uma coluna especificada ao conjunto de resultados retornado por uma consulta de alterações líquidas. O modelo Instantiate CDC Wrapper TVFs for a Schema mostra como instanciar as TVFs Datetime Wrappers for the Query 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 de data e hora para consultar dados de alteração, consulte o modelo Get Net Changes Using Wrapper With Update Flags. Este modelo demonstra como consultar alterações delta com uma função de wrapper quando o wrapper está configurado para retornar sinalizadores de atualização. Observe que a opção de filtro de linhas 'all with mask' é 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 e hora inferior e superior para indicar que a função deve usar o ponto de extremidade inferior e o ponto de 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.

Usando as funções de wrapper de data e hora para transição entre 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 deste recurso é acomodar uma transição entre várias instâncias de captura quando alterações de DDL na tabela de origem expandem o conjunto de colunas disponíveis para controle. 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.

[Início]

Consulte também

Conceitos

Controle de 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)