Habilitando o Change Data Capture

Este tópico aborda como habilitar o Change Data Capture para um banco de dados e uma tabela.

Habilitando o Change Data Capture para um banco de dados

Para que uma instância de captura possa ser criada para tabelas individuais, um membro da função de servidor fixa sysadmin deve primeiro habilitar o banco de dados para o Change Data Capture. Isso é feito com a execução do procedimento armazenado sys.sp_cdc_enable_db (Transact-SQL) no contexto do banco de dados. Para determinar se um banco de dados já está habilitado, examine a coluna is_cdc_enabled na exibição do catálogo sys.databases.

Quando um banco de dados está habilitado para o Change Data Capture, o esquema cdc, o usuário 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 do Change Data Capture e, depois que as tabelas de origem são habilitadas para o Change Data Capture, as tabelas de alterações individuais atuam como repositório para os dados de alteração. O esquema cdc também contém funções de sistema associadas usadas para consultar dados de alteração.

O Change Data Capture requer o uso exclusivo do esquema cdc e do usuário cdc. Se um esquema ou usuário de banco de dados denominado cdc existir atualmente em um banco de dados, o banco de dados não poderá ser habilitado para Change Data Capture até que o esquema e/ou o usuário sejam descartados ou renomeados.

Consulte o modelo Habilitar Banco de dados para Change Data Capture para obter um exemplo de como habilitar um banco de dados.

Importante
Para localizar os modelos no SQL Server Management Studio, vá para Exibir, clique em Explorador de Modelos e selecione Modelos do SQL Server. Change Data Capture é uma subpasta. Nesta pasta, você encontrará todos os modelos referenciados neste tópico. Também há um ícone do Explorador de Modelos na barra de ferramentas SQL Server Management Studio.

-- ================================

-- Modelo Enable Database for CDC

-- ================================

USE MyDB
GO

EXEC sys.sp_cdc_enable_db
GO

Habilitando o Change Data Capture para uma tabela

Após a habilitação de um banco de dados para Change Data Capture, 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 o 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.

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

Columns in the source table to be captured.

Por padrão, todas as colunas na tabela de origem são identificadas como colunas capturadas. Se for necessário rastrear apenas um subconjunto das colunas, por questões de privacidade ou desempenho, use o parâmetro @captured_column_list para especificar o subconjunto de colunas.

A filegroup to contain the change table.

Por padrão, a tabela de alteração está localizada no grupo de arquivos padrão do banco de dados. Proprietários de banco de dados que queiram controlar o posicionamento de tabelas de alterações individuais podem usar o parâmetro @filegroup_name para especificar um 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. Consulte o modelo Enable a Table Specifying Filegroup Option para obter um exemplo que mostra uso do parâmetro @filegroup_name.

===================================================

-- Modelo Enable a Table Specifying Filegroup Option

-- ===================================================

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

A role for controlling access to a change table.

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 membros da função sysadmin ou db_owner têm acesso completo aos dados nas tabelas de alterações. Todos os outros usuários devem ter a 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 você não quiser usar uma função associada, defina explicitamente explicitamente o parâmetro @role_name como NULL. Consulte o modelo Enable a Table Without Using a Gating Role para obter um exemplo de como habilitar uma tabela sem uma função associada.

-- ===================================================

-- Modelo Enable a Table Without Using a Gating Role

-- ===================================================

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

A function to query for net changes.

Uma instância de captura sempre incluirá uma função com valor de tabela para retornar todas as entradas de tabela de alterações que ocorreram dentro de um intervalo definido. Essa função é denominada com a anexação do nome da instância de captura para "cdc.fn_cdc_get_all_changes_". Para obter mais informações, consulte 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 globais também é 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, consulte 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.

Consulte o modelo Enable a Table for All and Net Changes Queries para obter um exemplo que demonstra a criação de uma instância de captura com ambas as funções de consulta.

=======================================================

-- Modelo Enable 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 o Change Data Capture for habilitado 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 Change Data Capture usará a chave primária. Alterações subseqüentes à chave primária não serão permitidas sem a desabilitação primeiro do Change Data Capture 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 subseqüente 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.