Abilitare e disabilitare Change Data Capture

Articolo
03/13/2024

Si applica a:SQL Server Istanza gestita di SQL di Azure

Questo articolo descrive come abilitare e disabilitare Change Data Capture (CDC) per un database e una tabella per SQL Server e Istanza gestita di SQL di Azure. Per il database di Azure per MySQL, vedere CDC con Database SQL di Azure.

Autorizzazioni

Le autorizzazioni sysadmin sono necessarie per abilitare o disabilitare Change Data Capture in SQL Server e Istanza gestita di SQL di Azure.

Abilitare per un database

Prima di poter creare un'istanza di acquisizione per singole tabelle, è necessario abilitare Change Data Capture per il database.

Per abilitare Change Data Capture, eseguire la stored procedure sys.sp_cdc_enable_db (Transact-SQL) nel contesto del database. Per determinare se un database è già abilitato, eseguire una query sulla colonna is_cdc_enabled nella vista del catalogo sys.databases.

Quando un database è abilitato per Change Data Capture, per il database vengono creati lo schema cdc , l'utente cdc , le tabelle dei metadati e altri oggetti di sistema. Lo schema cdc contiene le tabelle di metadati di Change Data Capture e, dopo l'abilitazione della funzionalità delle tabelle di origine, le singole tabelle delle modifiche fungono da repository per i dati delle modifiche. Lo schema cdc contiene anche le funzioni di sistema associate usate per eseguire query sui dati delle modifiche.

Change Data Capture richiede l'uso esclusivo dello schema cdc e dell'utente cdc . Se in un database è attualmente presente uno schema o un utente di database denominato cdc, il Change Data Capture non può essere abilitato per il database finché lo schema e/o l'utente non vengono eliminati o rinominati.

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

Nota

Per trovare modelli correlati a CDC in SQL Server Management Studio, passare a Visualizza, selezionare Esplora modelli e quindi selezionare Modelli di SQL Server. Change Data Capture è una sottocartella che contiene i modelli

Disabilitare per un database

Usare sys.sp_cdc_disable_db (Transact-SQL) nel contesto del database per disabilitare Change Data Capture per un database. Non è necessario disabilitare CDC per le singole tabelle prima di disabilitare CDC per il database. La disabilitazione di CDC per il database rimuove tutti i metadati di Change Data Capture associati, inclusi l'utente cdc, lo schema e i processi di Change Data Capture. Eventuali ruoli di controllo creati da CDC, tuttavia, non verranno rimossi automaticamente e devono essere eliminati in modo esplicito. Per determinare se un database è abilitato, eseguire una query sulla colonna is_cdc_enabled nella vista del catalogo sys.databases.

Se viene eliminato un database abilitato per CDC, i processi Change Data Capture vengono automaticamente rimossi.

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

Abilitare per una tabella

Dopo avere abilitato un database per Change Data Capture, i membri del ruolo predefinito del database db_owner possono creare un'istanza di acquisizione per singole tabelle di origine utilizzando la stored procedure sys.sp_cdc_enable_table. Per determinare se una tabella di origine è già stata abilitata per Change Data Capture, esaminare la colonna is_tracked_by_cdc nella vista del catalogo sys.tables.

Importante

Per altre informazioni sugli argomenti della stored procedure sys.sp_cdc_enable_table, vedere sys.sp_cdc_enable_table (Transact-SQL).

Quando si crea un'istanza di acquisizione, è possibile specificare le opzioni seguenti:

Colonne nella tabella di origine da acquisire.

Per impostazione predefinita, tutte le colonne della tabella di origine vengono identificate come colonne acquisite. Se è necessario rilevare solo un subset di colonne, ad esempio per motivi di privacy o di prestazioni, usare il parametro @captured_column_list per specificare il subset di colonne.

Un filegroup per contenere la tabella delle modifiche.

Per impostazione predefinita, la tabella delle modifiche si trova nel filegroup predefinito del database. I proprietari di database che desiderano controllare la posizione di singole tabelle delle modifiche possono usare il parametro @filegroup_name per specificare un determinato filegroup per la tabella delle modifiche associata all'istanza di acquisizione. È necessario che il filegroup specificato sia già presente. È in genere consigliabile inserire le tabelle delle modifiche in un filegroup distinto dalle tabelle di origine. Vedere il modello Abilitare una tabella specificando l'opzione filegroup per un esempio su come usare il parametro @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

Un ruolo per il controllo dell’accesso alla tabella delle modifiche.

Lo scopo del ruolo specificato consiste nel controllare l'accesso ai dati delle modifiche. Il ruolo specificato può essere un ruolo predefinito del server esistente o un ruolo del database. Se il ruolo specificato non è già presente, verrà automaticamente creato un ruolo del database con il nome indicato. Gli utenti devono disporre dell'autorizzazione SELECT per tutte le colonne acquisite della tabella di origine. Quando viene specificato un ruolo, inoltre, gli utenti che non sono membri del ruolo sysadmin o db_owner devono essere anche membri del ruolo specificato.

Se non si vuole usare un ruolo di controllo, impostare in modo esplicito il parametro @role_name su NULL. Vedere il modello Abilitare una tabella senza usare un ruolo di controllo per un esempio su come abilitare una tabella senza un ruolo di controllo.

-- 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

Una funzione per eseguire query per le modifiche delta.

Un'istanza di acquisizione include sempre una funzione con valori di tabella (TVF) per la restituzione di tutte le voci della tabella delle modifiche generate in un intervallo definito. Il nome di questa funzione viene creato aggiungendo il nome dell'istanza di acquisizione a "cdc.fn_cdc_get_all_changes_". Per altre informazioni, vedere cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL).

Se il parametro @supports_net_changes è impostato su 1, viene generata anche una funzione delle modifiche delta per l'istanza di acquisizione. Questa funzione restituisce solo una modifica per ogni riga distinta modificata nell'intervallo specificato nella chiamata. Per altre informazioni, vedere cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL).

Per supportare le query sulle modifiche delta, è necessario che la tabella di origine disponga di una chiave primaria o di un indice univoco per identificare le righe in modo univoco. Se viene usato un indice univoco, il nome dell'indice deve essere specificato con il parametro @index_name . Le colonne definite nella chiave primaria o nell'indice univoco devono essere incluse nell'elenco delle colonne di origine da acquisire.

Vedere il modello Abilitare una tabella per le query All e Net Changes per un esempio che descrive la creazione di un'istanza di acquisizione con entrambe le funzioni di query.

-- 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

Nota

Se Change Data Capture è abilitato in una tabella con una chiave primaria esistente e non viene usato il parametro @index_name per identificare un indice univoco alternativo, la funzionalità Change Data Capture userà la chiave primaria. Non sono consentite modifiche successive alla chiave primaria se prima non si disabilita Change Data Capture per la tabella. Questa regola è sempre valida, indipendentemente dal fatto che durante la configurazione di Change Data Capture sia stato o meno richiesto il supporto per le query sulle modifiche delta. Se in una tabella non è presente alcuna chiave primaria al momento dell'abilitazione di Change Data Capture, l'aggiunta successiva di una chiave primaria verrà ignorata da Change Data Capture. Poiché Change Data Capture non utilizzerà una chiave primaria creata in seguito all'abilitazione della tabella, la chiave e le colonne chiave possono essere rimosse senza restrizioni.

Disabilitare per una tabella

I membri del ruolo predefinito del database db_owner possono rimuovere un'istanza di acquisizione per singole tabelle di origine utilizzando la stored procedure sys.sp_cdc_disable_tablee. To determine whether a source table is currently enabled for change data capture, examine the **is_tracked_by_cdc** column in the viste del catalogo di sys.tables. Se in seguito alla disabilitazione non è presente alcuna tabella abilitata per il database, vengono rimossi anche i processi Change Data Capture.

Se viene eliminata una tabella abilitata per Change Data Capture, i metadati di Change Data Capture associati alla tabella vengono automaticamente rimossi.

Per un esempio di disabilitazione di una tabella, vedere il modello Disable a Capture Instance for a Table.

-- 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