Controlar alterações de dados (SQL Server)

O SQL Server 2014 fornece dois recursos que rastreiam 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 DML (operações de inserção, atualização e exclusão) que foram feitas em tabelas de usuário em um banco de dados. A captura de dados de alterações e o controle de alterações podem ser habilitados no mesmo banco de dados; não são necessárias considerações especiais. Para ver as edições do SQL Server que dão suporte à captura de dados de alterações e ao controle de alterações, consulte recursos compatíveis com as edições do SQL Server 2014.

Benefícios de usar o Change Data Capture ou o 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 controlar as alterações em um banco de dados, em vez de desenvolver uma solução personalizada, tem os seguintes benefícios:

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

• Alterações de esquema não são necessárias. Você não precisa adicionar colunas, adicionar gatilhos ou criar tabela lateral na qual 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 interno. A limpeza para controle de alterações é executada automaticamente em segundo plano. A limpeza personalizada para dados armazenados em uma tabela auxiliar não é necessária.

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

• Há baixa sobrecarga nas operações DML. O controle de alterações síncrono sempre terá certa sobrecarga. No entanto, usar o controle de alterações pode ajudar a minimizar a sobrecarga. A sobrecarga será frequentemente menor do que a do uso de soluções alternativas, especialmente soluções que exigem os gatilhos de uso.

• 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 execução longa e sobrepostas. Soluções personalizadas que usam timestamp valores devem ser projetadas especificamente para lidar com esses cenários.

• As ferramentas padrão estão disponíveis que você pode usar para configurar e gerenciar. O SQL Server 2014 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ção e o controle de alterações

A tabela a seguir lista as diferenças entre os recursos de captura de alterações nos dados e controle de alterações. O mecanismo de acompanhamento 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 acompanhamento envolve o acompanhamento síncrono de alterações de acordo com as operações DML para que as informações de alteração fiquem disponíveis imediatamente.

Captura de dados de alterações

A captura de dados de alteração fornece informações de alteração históricas para uma tabela de usuário capturando tanto o fato de que as alterações de DML foram feitas quanto 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 de usuário são capturadas em tabelas de alteração correspondentes. Essas tabelas de alterações fornecem uma exibição histórica das alterações ao longo do tempo. As funções de captura de dados de alteraçãoque 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 mudanças.

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 (Transact-SQL) ou sys.sp_cdc_disable_db (Transact-SQL) deve ser um membro da função de servidor sysadmin fixa. Habilitar e desabilitar a captura de dados de alteração no nível da tabela requer 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 de banco de dados database db_owner .

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

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 receber acesso selecionado 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 deve ser membro da função de controle especificada. Outras funções gerais de captura de dados de alterações para acessar metadados estarão acessíveis a todos os usuários do banco de dados por meio da função pública. Contudo, o acesso aos metadados retornados geralmente será restrito através de permissões seletivas nas tabelas de origem subjacentes e pela associação em funções de controle especificamente definidas.

Operações de DDL para alterar tabelas de origem habilitadas para captura de dados
Quando uma tabela está 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 do database role db_owner ou um membro do database role db_ddladmin. Os usuários que tiverem concessões explícitas para executar operações DDL na tabela receberão o erro 22914 se tentarem essas operações.

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

Todos os tipos de coluna base são compatíveis com a captura de dados de modificações. A tabela a seguir lista o comportamento e as limitações para vários tipos de coluna.

Alterar a captura de dados 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 do 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á em execução no espelho.

2. Crie o trabalho de captura e o trabalho de limpeza no espelho depois que o principal for transferido para o espelho. Para criar os trabalhos, use o procedimento armazenado sys.sp_cdc_add_job (Transact-SQL).

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 a população das tabelas de alterações é tratada de forma diferente quando ambos os recursos sã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 os dois recursos estão ativados no mesmo banco de dados, o agente leitor de logs chama sp_replcmds. Esse agente preenche as tabelas de alteração e as tabelas de banco de dados de distribuição. Para obter mais informações, consulte Replication Log Reader Agent.

Considere um cenário no qual a captura de dados de alteração está habilitada no banco de dados AdventureWorks2012 e duas tabelas estão 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 uma publicação é criada. O agente de leitura de log é criado para o banco de dados e a tarefa de captura é excluída. O Agente de Leitor de Log continua verificando o log 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ções. Se a replicação transacional estiver desabilitada neste banco de dados, o Agente de Leitor de Log será removido e o trabalho de captura será recriado.

Restaurando ou anexando um banco de dados habilitado para a 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 opção KEEP_CDC ao restaurar o banco de dados. Para obter mais informações sobre essa opção, consulte RESTORE.

• Se um banco de dados estiver 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 para qualquer edição diferente da Enterprise, a operação será bloqueada porque a captura de dados de alteração exigirá o SQL Server Enterprise. 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.

Controle de Alterações

O controle de alterações captura o fato de que as linhas em 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 as informações históricas, há muito menos sobrecarga de armazenamento devido à alteração dos dados não capturados. Um mecanismo de acompanhamento síncrono é usado para controlar as alterações. Isso foi projetado para ter uma sobrecarga mínima nas 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 em 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 detectar de forma confiável quaisquer conflitos que possam ter ocorrido.

Serviços de Controle e Sincronização de Alterações para ADO.NET

Os Serviços de Sincronização para ADO.NET permitem 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 para ADO.NET fornece uma API para sincronizar alterações, mas não controla as alterações no servidor ou no banco de dados par. Você pode criar um sistema de controle de alterações personalizado, mas isso normalmente introduz complexidade significativa e sobrecarga de desempenho. Para controlar as alterações em um banco de dados de servidor ou par, recomendamos que você use o controle de alterações no SQL Server 2014 porque é fácil de configurar e fornece acompanhamento de alto desempenho.

Para obter mais informações sobre controle de alterações e Serviços de Sincronização para 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. Na documentação dos Serviços de Sincronização, o tópico "Como usar o controle de alterações do SQL Server" contém informações detalhadas e exemplos de código.

Tarefas relacionadas (obrigatório)

Consulte Também

Alterar funções de captura de dados (Transact-SQL)
Funções de Controle de Alterações (Transact-SQL)
Alterar procedimentos armazenados de captura de dados (Transact-SQL)
Alterar tabelas de captura de dados (Transact-SQL)
Visões de gerenciamento dinâmico relacionadas à captura de alteração de dados (Transact-SQL)