Habilitar e desabilitar a captura de dados de alterações

  • Artigo

Aplica-se a: SQL Server Instância Gerenciada de SQL do Azure

Este artigo descreve como habilitar e desabilitar a captura de dados de alterações (CDA) para um banco de dados e uma tabela do SQL Server e da Instância Gerenciada de SQL do Azure. Para o Banco de Dados SQL do Azure, confira CDA com Banco de Dados SQL do Azure.

Permissões

As permissões sysadmin são necessárias para habilitar ou desabilitar a captura de dados de alteração no SQL Server e na Instância Gerenciada de SQL do Azure.

Habilitar para um banco de dados

Antes de criar uma instância de captura para tabelas individuais, você deve habilitar a captura de dados de alterações para o banco de dados.

Para habilitar a captura de dados de alterações, execute o procedimento armazenado sys.sp_cdc_enable_db (Transact-SQL) no contexto do banco de dados. Para determinar se um banco de dados já tem a CDA habilitada, consulte a coluna is_cdc_enabled na exibição de catálogo sys.databases.

Quando um banco de dados tem captura de dados de alterações habilitada, o esquema cdc, o usuário da cdc, as tabelas de metadados e outros objetos de sistema são criados para o banco de dados. O esquema cdc contém as tabelas de metadados da captura de dados de alterações e, depois que as tabelas de origem são habilitadas para a captura de dados de alterações, as tabelas de alterações individuais atuam como repositório para os dados de alterações. O esquema cdc também contém funções de sistema associadas usadas para consultar dados de alterações.

A captura de dados de alterações requer o uso exclusivo do esquema cdc e do usuário da cdc. Se houver um esquema ou usuário de banco de dados chamado cdc em um banco de dados, a captura de dados de alterações não poderá ser habilitada para o banco de até que o esquema e/ou o usuário sejam descartados ou renomeados.

-- ====
-- Enable Database for CDC
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO

Observação

Para localizar modelos relacionados a CDA no SQL Server Management Studio, vá para Exibir, selecione Explorador de Modelos e Modelos do SQL Server. A captura de dados de alterações é uma subpasta que contém os modelos

Desabilitar para um banco de dados

Use sys.sp_cdc_disable_db (Transact-SQL) no contexto do banco de dados para desabilitar a captura de dados de alterações em um banco de dados. Não é necessário desabilitar a CDA para tabelas individuais antes de desabilitar a CDA para o banco de dados. A desabilitação da CDA para o banco de dados removerá todos os metadados de captura de dados de alterações associados, inclusive o usuário cdc, o esquema e os trabalhos da captura de dados de alterações. No entanto, qualquer função associada criada pela CDC não será removida automaticamente e deverá ser excluída explicitamente. Para determinar se um banco de dados tem a CDA habilitada, consulte a coluna is_cdc_enabled na visualização do catálogo sys.databases.

Se um banco de dados habilitado para CDA for descartado, os trabalhos de captura de dados de alterações serão removidos automaticamente.

-- Disable Database for change data capture
USE MyDB
GO
EXEC sys.sp_cdc_disable_db
GO

Habilitar para uma tabela

Depois da habilitação de um banco de dados para captura de dados de alterações, os membros da função de banco de dados fixa db_owner poderão criar uma instância de captura para tabelas de origem individuais usando a exibição do catalogo do procedimento armazenado sys.sp_cdc_enable_table. Para determinar se uma tabela de origem já foi habilitada para Change Data Capture, examine a coluna is_tracked_by_cdc na exibição do catálogo sys.tables.

Importante

Para obter mais informações sobre os argumentos do procedimento armazenado sys.sp_cdc_enable_table, confira sys.sp_cdc_enable_table (Transact-SQL).

As seguintes opções podem ser especificadas durante a criação de uma instância de captura:

Colunas na tabela de origem a serem capturadas.

Por padrão, todas as colunas na tabela de origem são identificadas como colunas capturadas. Se apenas um subconjunto de colunas precisar ser rastreado, por exemplo, por motivos de privacidade ou desempenho, use o parâmetro @captured_column_list para especificar o subconjunto de colunas.

Um grupo de arquivos para conter a tabela de alteração.

Por padrão, a tabela de alteração está localizada no grupo de arquivos padrão do banco de dados. Os proprietários de banco de dados que desejam controlar o posicionamento de tabelas de alterações individuais podem usar o parâmetro @filegroup_name para especificar determinado grupo de arquivos para a tabela de alterações associada à instância de captura. O grupo de arquivos nomeado já deve existir. Geralmente, é recomendável que tabelas de alterações sejam colocadas em um grupo de arquivos separado das tabelas de origem. Confira o modelo Habilitar uma tabela especificando a opção de grupo de arquivos para obter um exemplo que mostra o uso do parâmetro @filegroup_name .

-- Enable CDC for a table specifying filegroup
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @filegroup_name = N'MyDB_CT',
    @supports_net_changes = 1
GO

Uma função para controlar o acesso a uma tabela de alteração.

O propósito da função nomeada é controlar o acesso aos dados de alteração. A função especificada pode ser uma função de servidor fixa existente ou uma função de banco de dados. Se a função especificada ainda não existir, uma função de banco de dados com esse nome será criada automaticamente. Os usuários devem ter permissão SELECT em todas as colunas capturadas da tabela de origem. Além disso, quando uma função é especificada, os usuários que não são membros da função sysadmin ou db_owner também devem ser membros da função especificada.

Se não quiser usar uma função de restrição, defina explicitamente o parâmetro @role_name como NULL. Confira o modelo Habilitar uma tabela sem uma função associada para obter um exemplo de como habilitar uma tabela sem uma função associada.

-- Enable CDC for a table using a gating role option
USE MyDB
GO
    EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = NULL,
    @supports_net_changes = 1
GO

Uma função para consultar alterações líquidas.

Uma instância de captura inclui uma função com valor de tabela (TVF) para retornar todas as entradas de tabela de alterações que ocorreram dentro de um intervalo definido. Essa função é denominada com acrescentando o nome da instância de captura a `cdc.fn_cdc_get_all_changes_``. Para obter mais informações, confira cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Se o parâmetro @supports_net_changes for definido como 1, uma função de alterações líquidas também será gerada para a instância de captura. Essa função retorna apenas uma alteração para cada linha distinta alterada no intervalo especificado na chamada. Para obter mais informações, confira cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Para dar suporte às consultas de entradas, a tabela de origem deve ter uma chave primária ou índice exclusivo para identificar linhas. Se um índice exclusivo for usado, o nome do índice deverá ser especificado com o uso do parâmetro @index_name . As colunas definidas na chave primária ou índice exclusivo deverão ser incluídas na lista de colunas de origem a serem capturadas.

Confira o modelo Habilitar uma tabela para tudo e consultas de alterações líquidas para obter um exemplo que demonstra a criação de uma instância de captura com ambas as funções de consulta.

-- Enable CDC for a table for all and net changes queries
USE MyDB
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @role_name     = N'MyRole',
    @supports_net_changes = 1
GO

Observação

Se a captura de dados de alterações for habilitada em uma tabela com a chave primária existente e o parâmetro @index_name não for usado para identificar um índice exclusivo alternativo, o recurso de captura de dados de alterações usará a chave primária. Alterações subsequentes à chave primária não são permitidas sem primeiro desabilitar a captura de dados de alterações para a tabela. Isso é verdadeiro quer o suporte para consultas de alterações globais tenha sido solicitado ou não durante a configuração do Change Data Capture. Se não houver nenhuma chave primária em uma tabela quando ela for habilitada para o Change Data Capture, a adição subsequente de uma chave primária será ignorada pelo Change Data Capture. Como o Change Data Capture não usará uma chave primária criada após a habilitação da tabela, a chave e as colunas de chave poderão ser removidas sem restrições.

Desabilitar para uma tabela

Os membros da função de banco de dados fixa db_owner podem remover uma instância de captura para tabelas de origem individuais usando o procedimento armazenado sys.sp_cdc_disable_table. Para determinar se uma tabela de origem está habilitada atualmente para Change Data Capture, examine a coluna is_tracked_by_cdc na exibição de catálogo sys.tables. Se não houver tabelas habilitadas para o banco de dados após a desabilitação, os trabalhos do Change Data Capture também serão removidos.

Se uma tabela habilitado do Change Data Capture for descartada, os metadados associados à tabela de Change Data Capture serão removidos automaticamente.

Consulte o modelo Desabilitar uma Instância de Captura para uma Tabela para obter um exemplo de como desabilitar uma tabela.

-- Disable a Capture Instance for a table
USE MyDB
GO
    EXEC sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name   = N'MyTable',
    @capture_instance = N'dbo_MyTable'
GO

Confira também