Controlar alterações de dados (SQL Server)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

O SQL Server fornece dois recursos que controlam alterações em 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 DML (operações de inserção, atualização e exclusão) feitas nas tabelas do usuário em um banco de dados. A captura e o controlo de dados de alterações podem ser ativados na mesma base de dados; Não são necessárias considerações especiais. Para as edições do SQL Server que oferecem suporte à captura de dados de alterações e ao controle de alterações, consulte Edições e recursos com suporte do SQL Server 2022.

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

A capacidade de consultar dados que foram alterados em um banco de dados é um requisito importante para que alguns aplicativos sejam eficientes. Normalmente, para determinar alterações de dados, os desenvolvedores de aplicativos devem implementar um método de controle personalizado em seus aplicativos usando uma combinação de gatilhos, colunas de carimbo de data/hora e tabelas adicionais. A criação desses aplicativos geralmente envolve muito trabalho para implementar, leva a atualizações de esquema e, muitas vezes, carrega uma sobrecarga de alto desempenho.

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

• O tempo de desenvolvimento é reduzido. Como a funcionalidade está disponível no SQL Server, não é necessário desenvolver uma solução personalizada.

• Não são necessárias alterações de esquema. Não é necessário adicionar colunas, adicionar gatilhos ou criar tabelas laterais para controlar linhas excluídas ou armazenar informações de controle de alterações se as colunas não puderem ser adicionadas às tabelas do usuário.

• Há um mecanismo de limpeza integrado. A limpeza para controle de alterações é executada automaticamente em segundo plano. A limpeza personalizada para dados armazenados em uma tabela lateral não é necessária.

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

• Há uma baixa sobrecarga nas operações DML. O controle síncrono de alterações 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 a utilização de soluções alternativas, especialmente soluções que requerem a utilização de gatilhos.

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

• Estão disponíveis ferramentas padrão que você pode usar para configurar e gerenciar. O SQL Server fornece instruções DDL padrã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ção e o controle de alterações. O mecanismo de rastreamento na captura de dados de alteração envolve uma captura assíncrona de alterações do log de transações para que as alterações estejam disponíveis após a operação DML. No controle de alterações, o mecanismo de rastreamento envolve o rastreamento síncrono de alterações de acordo com as operações DML para que as informações de alteração estejam disponíveis imediatamente.

Alterar a captura de dados

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

Conforme mostrado na ilustração a seguir, as alterações feitas nas tabelas do usuário são capturadas nas tabelas de alterações correspondentes. Estas tabelas de alterações fornecem uma visão histórica das mudanças ao longo do tempo. As funções Change Data Capture Functions que o SQL Server fornece permitem que os dados de alteração sejam consumidos de forma fácil e sistemática.

Modelo de segurança

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

Configuração e administração

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

O uso dos procedimentos armazenados para dar suporte à administração de trabalhos de captura de dados de alteração é restrito aos membros da função sysadmin do servidor e aos membros da função db_owner banco de dados .

Alterar consultas de enumeração e metadados

Para obter acesso aos dados de alteração 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 seleção for especificada quando a instância de captura for criada, o chamador também deverá ser membro da função de seleção especificada e o esquema de captura de dados de alteração (cdc) deverá ter SELECT acesso à função de bloqueio.

Outras funções gerais de captura de dados de alteração para acessar metadados serã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 normalmente fechado usando SELECT o acesso às tabelas de origem subjacentes e pela associação a quaisquer funções de vinculação definidas.

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

Quando uma tabela é habilitada para captura de dados de alteração, as operações DDL só podem ser aplicadas à tabela por um membro da função de servidor fixa sysadmin, um membro da função de banco de dados db_owner ou um membro da função de banco de dados db_ddladmin. Os usuários que têm concessões explícitas para executar operações DDL na tabela receberão o erro 22914 se tentarem essas operações.

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

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 para vários tipos de coluna.

Integração de 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 habilitado para captura de dados de alteração pode ser espelhado. Para garantir que a captura e a limpeza ocorram automaticamente no espelho, siga estas etapas:

1. Verifique se o SQL Server Agent está sendo executado no espelho.

2. Crie o trabalho de captura e limpeza no espelho depois que o responsável de segurança tiver feito failover para o espelho. Para criar os trabalhos, use o procedimento armazenado sys.sp_cdc_add_job.

Para obter mais informações sobre 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 o preenchimento das tabelas de alteração é tratado de forma diferente quando ambos os 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 as alterações do log de transações. Quando a captura de dados de alteração é habilitada por conta própria, um trabalho do SQL Server Agent chama sp_replcmds. Quando ambos os recursos estão habilitados no mesmo banco de dados, o Log Reader Agent chama sp_replcmds. Esse agente preenche as tabelas de alteração e as distribution tabelas de banco de dados. Para obter mais informações, consulte Replication Log Reader Agent.

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

Restaurar ou anexar um banco de dados habilitado para captura de dados de alteração

O SQL Server usa a seguinte lógica para determinar se a captura de dados de alteração 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ção será desabilitada e todos os metadados relacionados serão excluídos.

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

• Se um banco de dados for desanexado e conectado 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 para qualquer edição que não seja Standard ou Enterprise, a operação será bloqueada porque a captura de dados de alteração requer as KEEP_CDC 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ção de um banco de dados restaurado ou anexado.

Acompanhamento 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 dados de linha mais recentes sendo obtidos diretamente das tabelas do usuário. Portanto, o controle de alterações é mais limitado nas perguntas históricas que pode responder em comparação com a captura de dados de alteração. No entanto, para os aplicativos que não exigem as informações históricas, há muito menos sobrecarga de armazenamento devido aos dados alterados não serem capturados. Um mecanismo de rastreamento síncrono é usado para controlar as alterações. Isso foi projetado para ter uma sobrecarga mínima para as operações DML.

A ilustração a seguir mostra um cenário de sincronização que se beneficiaria usando o controle de alterações. No cenário, um aplicativo requer as seguintes informações: todas as linhas na tabela que foram alteradas desde a última vez que a tabela foi sincronizada e apenas os dados de linha atuais. Como um mecanismo síncrono é usado para controlar as alterações, um aplicativo pode executar a sincronização bidirecional e detetar de forma confiável quaisquer conflitos que possam ter ocorrido.

Controle de alterações e sincronização de serviços para ADO.NET

O Sync Services for ADO.NET permite a sincronização entre bancos de dados, fornecendo uma API intuitiva e flexível que permite criar aplicativos destinados a cenários offline e de colaboração. O Sync Services for ADO.NET fornece uma API para sincronizar alterações, mas não controla as alterações no servidor ou no banco de dados de mesmo nível. Você pode criar um sistema de controle de alterações personalizado, mas isso geralmente introduz complexidade significativa e sobrecarga de desempenho. Para controlar alterações em um servidor ou banco de dados de mesmo nível, recomendamos que você use o controle de alterações no SQL Server porque é fácil de configurar e fornece controle de alto desempenho.

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

• Sobre o controle de alterações (SQL Server)

  Descreve o controle de alterações, fornece uma visão geral de alto nível de como o controle de alterações funciona e descreve como o controle de alterações interage com outros recursos do Mecanismo de Banco de Dados do SQL Server.

• Centro de Desenvolvedores do Microsoft Sync Framework

  Fornece documentação completa para o Sync Framework e o Sync Services.

Conteúdo relacionado

• Alterar funções de captura de dados (Transact-SQL)
• Alterar procedimentos armazenados de captura de dados (Transact-SQL)
• Alterar tabelas de captura de dados (Transact-SQL)
• Visualizações de gerenciamento dinâmico do sistema
• 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
• Trabalhar com dados de alteração
• Funções de controlo de alterações (Transact-SQL)
• Sobre o controle de alterações (SQL Server)
• Habilitar e desabilitar o controle de alterações (SQL Server)
• Gerenciar o controle de alterações (SQL Server)
• Trabalhar com controle de alterações (SQL Server)