Controle de alterações de dados (SQL Server)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

O SQL Server oferece dois recursos que rastreiam as alterações nos dados em um banco de dados: captura de dados de alterações e Controle de Alterações. Esses recursos permitem que os aplicativos determinem as alterações de DML (operações de inserção, atualização e exclusão) que foram feitas em tabelas de usuários em um banco de dados. A captura de dados de alteração e o controle de alterações podem ser habilitados no mesmo banco de dados; nenhuma consideração especial necessária. Para conhecer as edições do SQL Server que são compatíveis com a captura de dados de alterações e o controle de alterações, confira Edições e recursos compatíveis com o SQL Server 2022.

Benefícios do uso da captura de dados de alterações ou do controle de alterações

Um requisito importante para a eficiência de alguns aplicativos é a capacidade de consultar os dados que foram alterados em um banco de dados. Normalmente, para determinar as alterações de dados, os desenvolvedores de aplicativos precisam implementar um método de rastreamento personalizado em seus aplicativos usando uma combinação de gatilhos, colunas de carimbo de data/hora e tabelas adicionais. A criação desses aplicativos costumava envolve uma implementação muito trabalhosa, leva a atualizações de esquema e frequentemente acarreta uma sobrecarga de alto desempenho.

Usar a captura de dados de alterações ou o controle de alterações em aplicativos para rastrear alterações em um banco de dados em vez de desenvolver uma solução personalizada tem os seguintes benefícios:

  • Há tempo de desenvolvimento reduzido. Como a funcionalidade está disponível no SQL Server, você não precisa desenvolver uma solução personalizada.

  • Não são necessárias alterações no esquema. Não será necessário adicionar colunas, gatilhos ou criar uma tabela lateral para rastrear linhas excluídas ou armazenar informações de controle de alterações se as colunas não puderem ser adicionadas às tabelas de usuários.

  • Há um mecanismo de limpeza interno. A limpeza do controle de alterações é executada automaticamente em segundo plano. Não é necessária a limpeza personalizada dos dados armazenados em uma tabela lateral.

  • Funções são fornecidas para obter informações de alterações.

  • Há pouca sobrecarga em operações DML. O controle de alterações síncrono sempre terá alguma sobrecarga. No entanto, o uso do controle de alterações pode ajudar a minimizar a sobrecarga. A sobrecarga será frequentemente menor do que ao usar soluções alternativas, principalmente soluções que precisam usar gatilhos.

  • O controle de alterações é baseado em transações confirmadas. A ordem das alterações é baseada na hora da confirmação da transação. Isso permite que resultados confiáveis sejam obtidos ao executar transações demoradas e sobrepostas. As soluções personalizadas que usam valores de carimbo de data/hora devem ser projetadas para lidar com esses cenários.

  • Ferramentas padrão estão disponíveis para que você possa usar para configurar e gerenciar. O SQL Server fornece instruções DDL padrão, o SQL Server Management Studio, exibições de catálogo e permissões de segurança.

Diferenças de recursos entre a captura de dados de alterações e o controle de alterações

A tabela a seguir lista as diferenças de recursos entre a captura de dados de alterações e o controle de alterações. O mecanismo de controle na captura de dados de alteração envolve uma captura assíncrona de alterações no log de transação, de forma que as alterações fiquem disponíveis depois da operação de DML. No controle de alteração, o mecanismo de controle envolve o controle síncrono de alterações embutidas nas operações de DML de forma que as informações de alteração fiquem disponíveis imediatamente.

Recurso captura de dados de alterações controle de alterações
Alterações controladas
Alterações de DML Sim Yes
Informações controladas
Dados históricos Sim Não
Se a coluna foi alterada Sim Yes
Tipo de DML Sim Yes

captura de dados de alterações

A captura de dados de alteração fornece informações de alteração de histórico para uma tabela de usuário, capturando o motivo pelo qual as alterações de DML foram feitas e os dados reais que foram alterados. As alterações são capturadas por meio de um processo assíncrono que lê o log de transações e tem baixo impacto no sistema.

Como mostrado na ilustração a seguir, as alterações feitas para tabelas de usuários são capturadas nas tabelas de alteração correspondentes. Essas tabelas de alterações fornecem uma visão histórica das alterações ao longo do tempo. As funções de captura de dados de alterações fornecidas pelo SQL Server permitem que os dados de alterações sejam consumidos de forma fácil e sistemática.

Diagram showing the concept of change data capture.

Modelo de segurança

Esta seção descreve o modelo de segurança da captura de dados de alterações.

Configuração e administração

Para habilitar ou desabilitar a captura de dados de alterações para um banco de dados, o chamador de sys.sp_cdc_enable_db (Transact-SQL) ou sys.sp_cdc_disable_db (Transact-SQL) deve ser membro da função de servidor fixa sysadmin. Habilitar e desabilitar a captura de dados de alterações no nível de tabela exige que o chamador de sys.sp_cdc_enable_table (Transact-SQL) e sys.sp_cdc_disable_table (Transact-SQL) seja membro da função sysadmin ou membro da função database db_owner do banco de dados.

O uso dos procedimentos armazenados para fornecer suporte aos trabalhos de captura de dados de alteração é restrito a membros da função sysadmin do servidor e a membros da função database db_owner .

Enumeração de alterações e consultas de metadados

Para obter acesso aos dados de alterações associados a uma instância de captura, o usuário deve ter acesso SELECT a todas as colunas capturadas da tabela de origem associada. Além disso, se uma função de controle for especificada quando a instância de captura for criada, o chamador também deverá ser membro da função de controle especificada e o esquema de captura de dados de alterações (cdc) deverá ter acesso SELECT à função de controle.

Outras funções gerais de captura de dados de alterações para acessar os metadados estarão acessíveis a todos os usuários do banco de dados por meio da função pública, embora o acesso aos metadados retornados também seja tipicamente bloqueado pelo uso do acesso SELECT às tabelas de origem subjacentes e pela associação a quaisquer funções de bloqueio definidas.

Operações DDL para alterar tabelas de origem habilitadas para captura de dados

Quando uma tabela é habilitada para a captura de dados de alteração, as operações de DDL podem ser aplicadas à tabela somente por um membro de função fixa de servidor sysadmin, um membro de database role db_ownerou um membro de database role db_ddladmin. Os usuários que têm acesso explícito para executar as operações de DDL na tabela receberão o erro 22914 se tentarem essas operações.

Considerações sobre o tipo de dados para a captura de dados de alterações

Todos os tipos de coluna base são suportados pela captura de dados de alteração. A tabela a seguir lista o comportamento e as limitações de vários tipos de coluna.

Tipo de coluna Alterações capturadas em tabelas de alteração Limitações
Colunas esparsas Sim Não é compatível com a captura de alterações ao usar um conjunto de colunas.
Colunas computadas Não As alterações nas colunas computadas não são rastreadas. A coluna aparecerá na tabela de alterações com o tipo apropriado, mas terá um valor de NULL.
XML Sim As alterações em elementos XML individuais não são controladas.
Carimbo de data/hora Sim O tipo de dados na tabela de alterações é convertido em binário.
Tipos de dados BLOB Sim A imagem anterior da coluna BLOB somente será armazenada se a própria coluna for alterada.

Captura de dados de alterações e outros recursos do SQL Server

Esta seção descreve como os seguintes recursos interagem com a captura de dados de alteração:

  • Espelhamento de banco de dados
  • Replicação transacional
  • Restauração ou anexação de banco de dados

Espelhamento de banco de dados

Um banco de dados que é habilitado para captura de dados de alteração pode ser espelhado. Para assegurar que a captura e a limpeza ocorram automaticamente no espelho, siga estas etapas:

  1. Certifique-se de que o SQL Server Agent esteja em execução no espelho.

  2. Crie os trabalhos de captura e de limpeza no espelho depois que a entidade fizer failover no espelho. Para criar os trabalhos, use o procedimento armazenado sys.sp_cdc_add_job (Transact-SQL).

Para obter mais informações sobre o espelhamento de banco de dados, consulte Espelhamento de banco de dados (SQL Server).

Replicação transacional

A captura de dados de alteração e a replicação transacional podem coexistir no mesmo banco de dados, mas a população das tabelas de alteração ocorre de modo diferente quando os dois recursos estão habilitados. A captura de dados de alteração e a replicação transacional sempre usam o mesmo procedimento, sp_replcmds, para ler alterações no log de transações. Quando a captura de dados de alterações é habilitada por conta própria, um trabalho do SQL Server Agent chama sp_replcmds. Quando os dois recursos estão habilitados no mesmo banco de dados, o Agente de Leitor de Log chama sp_replcmds. Esse agente preenche as tabelas de alterações e as tabelas do banco de dados distribution. Para obter mais informações, consulte Replication Log Reader Agent.

Considere um cenário em que a captura de dados de alteração está habilitada no banco de dados AdventureWorks2022 e há duas tabelas habilitadas para captura. Para preencher as tabelas de alterações, o trabalho de captura chama sp_replcmds. O banco de dados está habilitado para replicação transacional, e é criada uma publicação. Agora, o Agente de Leitor de Log é criado para o banco de dados, e o trabalho de captura é excluído. O Agente de Leitor de Log continua a examinar o log do último número de sequência de log confirmado na tabela de alteração. Isso assegura a consistência de dados nas tabelas de alteração. Se a replicação transacional estiver desabilitada nesse banco de dados, o Agente de Leitor de Log será removido e o trabalho de captura, recriado.

Observação

Quando o Agente de Leitor de Log é usado para captura de dados de alterações e replicação transacional, as alterações replicadas são gravadas primeiro no banco de dados distribution. Em seguida, as alterações capturadas são gravadas nas tabelas de alteração. As duas operações são confirmadas ao mesmo tempo. Se houver alguma latência na gravação no banco de dados distribution, haverá uma latência correspondente antes que as alterações apareçam nas tabelas de alterações.

Restaurar ou anexar um banco de dados habilitado para a captura de dados de alterações

O SQL Server usa a seguinte lógica para determinar se a captura de dados de alterações permanece habilitada depois que um banco de dados é restaurado ou anexado:

  • Se um banco de dados for restaurado para o mesmo servidor com o mesmo nome de banco de dados, a captura de dados de alteração permanecerá habilitada.

  • Se um banco de dados for restaurado para outro servidor, por padrão a captura de dados de alterações será desabilitada, e todos os metadados relacionados serão excluídos.

    Para manter a captura de dados de alteração, use a opção KEEP_CDC quando restaurar o banco de dados. Para obter mais informações sobre essa opção, consulte RESTORE.

  • Se um banco de dados for desanexado e anexado ao mesmo servidor ou a outro servidor, a captura de dados de alteração permanecerá habilitada.

  • Se um banco de dados for anexado ou restaurado com a opção KEEP_CDC em qualquer edição que não seja Standard ou Enterprise, a operação será bloqueada porque a captura de dados de alterações requer as edições Standard ou Enterprise do SQL Server. A mensagem de erro 932 é exibida:

    SQL Server cannot load database '%.*ls' because change data capture is enabled. The currently installed edition of SQL Server does not support change data capture. Either disable change data capture in the database by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

Você pode usar sys.sp_cdc_disable_db para remover a captura de dados de alterações de um banco de dados restaurado ou anexado.

controle de alterações

O controle de alterações captura o fato de que as linhas de uma tabela foram alteradas, mas não captura os dados que foram alterados. Isso permite que os aplicativos determinem as linhas que foram alteradas com os últimos dados de linha obtidos diretamente das tabelas de usuários. Então, o controle de alterações é mais limitado nas perguntas de histórico do que pode responder comparado à captura de dados de alterações. No entanto, para os aplicativos que não exigem informações históricas, a sobrecarga de armazenamento é muito menor porque os dados alterados não estão sendo capturados. Um mecanismo de controle síncrono é usado para controlar as alterações. Isso foi projetado para ter o mínimo de sobrecarga nas operações de DML.

A ilustração a seguir mostra um cenário de sincronização que se beneficiaria com o uso do controle de alterações. No cenário, um aplicativo requer as seguintes informações: todas as linhas da tabela que foram alteradas desde a última sincronização da tabela e apenas os dados da linha atual. Como um mecanismo síncrono é usado para controlar as alterações, um aplicativo pode executar a sincronização de duas vias e detectar de modo confiável qualquer conflito que possa ocorrer.

Diagram showing the concept of change tracking.

Controle de alterações e o Sync Services para ADO.NET

O Sync Services para ADO.NET permite a sincronização entre bancos de dados, fornecendo uma API intuitiva e flexível que lhe permite criar aplicativos voltados para cenários offline e de colaboração. O Sync Services para ADO.NET fornece uma API para sincronizar alterações, mas não rastreia de fato as alterações no banco de dados do servidor ou do par. Você pode criar um sistema de controle de alterações personalizado, mas geralmente esse procedimento apresenta uma complexidade significativa e sobrecarga de desempenho. Para controlar alterações em um banco de dados de servidor ou par, recomendamos que você use o controle de alterações no SQL Server, pois ele é fácil de configurar e oferece acompanhamento de alto desempenho.

Para obter mais informações sobre o controle de alterações e o Sync Services para ADO.NET, use os links a seguir:

Próximas etapas

Tarefa Artigo
Fornece uma visão geral da captura de dados de alterações. Sobre o change data capture (SQL Server)
Descreve como habilitar e desabilitar a captura de dados de alterações em um banco de dados ou uma tabela. Habilitar e desabilitar o Change Data Capture (SQL Server)
Descreve como administrar e monitorar a captura de dados de alterações. Administrar e monitorar a captura de dados de alteração (SQL Server)
Descreve como trabalhar com os dados de alteração que estão disponíveis para consumidores de captura de dados de alterações. Este artigo abrange a validação de limites de LSN, as funções de consulta e os cenários de função de consulta. Trabalhar com dados de alterações (SQL Server)
Fornece uma visão geral do controle de alterações. Sobre o controle de alterações (SQL Server)
Descreve como habilitar e desabilitar o controle de alterações em um banco de dados ou uma tabela. Habilitar e desabilitar o controle de alterações (SQL Server)
Descreve como gerenciar o controle de alterações, configurar a segurança e identificar os efeitos sobre o armazenamento e o desempenho quando você usa o controle de alterações. Gerenciar o controle de alterações (SQL Server)
Descreve como os aplicativos que usam o controle de alterações podem obter as alterações controladas, aplicá-las a outro repositório de dados e atualizar o banco de dados de origem. Este artigo também descreve a função que o controle de alterações desempenha quando ocorre um failover e um banco de dados precisa ser restaurado de um backup. Trabalhar com o controle de alterações (SQL Server)

Confira também