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

Artigo
11/19/2013

O SQL Server 2012 fornece dois recursos que controlam alterações em dados de bancos 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 as edições do SQL Server com suporte para a captura de dados de alterações e o controle de alterações, consulte Recursos compatíveis com as edições do SQL Server 2012.

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. Em geral, para determinar alterações de dados, os desenvolvedores de aplicativos devem implementar um método personalizado de controle nos aplicativos usando uma combinação de gatilhos, colunas de carimbo de data e 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:

O tempo de desenvolvimento é reduzido. Como a funcionalidade está disponível no SQL Server 2012, não é necessário desenvolver uma solução personalizada.
Alterações de esquema não são necessárias. Você não precisar adicionar colunas, gatilhos nem criar tabelas laterais nas quais rastrear linhas excluídas ou armazenar informações do controle de alterações se não for possível adicionar colunas a tabelas de usuário.
Há um mecanismo de limpeza interno. A limpeza do controle de alterações é executada automaticamente em segundo plano. A limpeza personalizada de dados armazenados em uma tabela lateral não é necessária.
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 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. Soluções personalizadas que usem valores de timestamp devem ser criadas especificamente para tratar desses cenários.
Ferramentas padrão estão disponíveis para que você possa usar para configurar e gerenciar. O SQL Server 2012 fornece instruções DDL padrão, SQL Server Management Studio, exibições do catálogo e permissões de segurança.

Diferenças de recurso entre captura de dados de alterações e 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	Change Data Capture	Controle de alterações
Alterações controladas
Alterações de DML	Sim	Sim
Informações controladas
Dados do histórico	Sim	Não
Se a coluna foi alterada	Sim	Sim
Tipo de DML	Sim	Sim

Change Data Capture

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ção fornecem uma exibição histórica das alterações com o passar do tempo. As funções da captura de dados de alterações que o SQL Server fornece habilita os dados de alteração a serem consumidos fácil e sistematicamente.

Ilustração conceitual do 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ção para um banco de dados, o chamador de sys.sp_cdc_enable_db (Transact-SQL) ou de sys.sp_cdc_disable_db (Transact-SQL) deve ser um membro da função de servidor fixo sysadmin. Habilitar e desabilitar a captura de dados de alteração no nível de tabela exige que o chamador de sys.sp_cdc_enable_table (Transact-SQL) e de sys.sp_cdc_disable_table (Transact-SQL) seja um membro da função sysadmin ou um 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.
Consultas de metadados e enumeração de alteração
Para obter acesso aos dados de alteração associados a uma instância de captura, o usuário deve ter acesso de seleção a todas as colunas capturadas da tabela de origem associada. Além disso, se uma função gating for especificada quando a instância de captura for criada, o chamador também deverá ser um membro da função gating especificada. Outras funções gerais de captura de dados de alteração para o acesso aos metadados estarão acessíveis a todos os usuários de banco de dados por meio da função pública, embora o acesso aos metadados retornados será sempre concedido pelo uso do acesso de seleção às tabelas de origem subjacentes e pela associação em qualquer função gating definida.
Operações de DDL para tabelas de origem habilitadas para captura de dados de alterações
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_owner ou 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 tipos 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 de vários tipos de coluna.

Tipo de coluna	Alterações capturadas em tabelas de alteração	Limitações
Colunas esparsas	Sim	Não dão suporte à captura de alterações durante o uso de columnset.
Colunas computadas.	Não	As alterações feitas a 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 a elementos XML individuais não são rastreadas.
Timestamp	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ção 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:

Verifique se o SQL Server Agent está sendo executado no espelho.
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 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ção é habilitada por iniciativa 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ção e do banco de dados de distribuição. Para obter mais informações, consulte Replication Agente de Leitor de Log.

Considere um cenário em que a captura de dados de alteração está habilitada no banco de dados AdventureWorks2012 e há duas tabelas habilitadas para captura. Para preencher as tabelas de alteração, o trabalho de captura chama sp_replcmds. O banco de dados está habilitado para replicação transacional, e é criada uma publicação. Agora, o Log Reader Agent é criado para o banco de dados, e o trabalho de captura é excluído. O Log Reader Agent continua a examinar o log do número de sequência do último 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 Log Reader Agent será removido e o trabalho de captura, recriado.

Observação
Quando o Log Reader Agent for usado para captura de dados e replicação transacional, as alterações replicadas serão gravadas primeiro no banco de dados de distribuição. 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 uma latência na gravação no banco de dados de distribuição, haverá uma latência correspondente antes de as alterações aparecerem nas tabelas de alteração.

Quando o Log Reader Agent for usado para captura de dados e replicação transacional, as alterações replicadas serão gravadas primeiro no banco de dados de distribuição. 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 uma latência na gravação no banco de dados de distribuição, haverá uma latência correspondente antes de as alterações aparecerem nas tabelas de alteração.

Restaurando ou anexando 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 deve permanecer habilitada depois que um banco de dados for 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 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 para qualquer edição diferente da Enterprise, a operação será bloqueada, pois a captura de dados de alteração exige 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.

Ícone de seta usado com o link Voltar ao Início [Início]

Controle de alterações

O controle de alteração captura o motivo pelo qual as linhas em uma tabela foram alteradas, mas não captura o que foi alterado. 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. Entretanto, para aqueles aplicativos que não exigem informações de histórico, há menos sobrecarga de armazenamento pois os dados alterados não são 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 na 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.

Ilustração conceitual do rastreamento de alteração

Controle de alterações e serviços de sincronização 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 pode ser usada para criar aplicativos que têm como destino cenários offline e de colaboração. Sync Services for ADO.NET fornece uma API para sincronizar alterações, mas não controla de fato as alterações no servidor ou banco de dados 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 as alterações em um servidor ou banco de dados par, recomendamos usar o controle de alteração no SQL Server 2012, pois sua configuração é fácil e ele oferece 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 sobre 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.
Microsoft Sync Framework Developer Center

Fornece a documentação completa para Sync Framework e Sync Services. Na documentação para Sync Services, o tópico "Como usar o controle de alterações do SQL Server" contém informações detalhadas e exemplos de código.

Ícone de seta usado com o link Voltar ao Início [Início]

Tarefas relacionadas (necessárias)

Tarefa	Tópico
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 tópico abrange a validação de limites de LSN, as funções de consulta e 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 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 tópico 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)

Consulte também

Compartilhar via