啟用和停用異動資料擷取

發行項
03/08/2024

本文說明如何針對 SQL Server 和 Azure SQL 受控執行個體的資料庫和資料表啟用和停用異動數據擷取 (CDC)。如需 Azure SQL 資料庫，請參閱使用 Azure SQL 資料庫 CDC。

權限

需要 sysadmin 權限，才能啟用或停用 SQL Server 和 Azure SQL 受控執行個體中的異動資料擷取。

為資料庫啟用

若要為個別資料表建立擷取執行個體，您必須先啟用資料庫的異動資料擷取。

若要啟用異動資料擷取，請在資料庫內容中執行 sys.sp_cdc_enable_db (Transact-SQL) 預存程序。若要判斷資料庫是否已啟用 CDC，請查詢 sys.databases 目錄檢視中的 is_cdc_enabled 資料行。

資料庫啟用異動資料擷取時，也會針對資料庫建立 cdc 結構描述、cdc 使用者、中繼資料表，以及其他系統物件。 cdc 結構描述包含異動資料擷取中繼資料表，以及啟用異動資料擷取的來源資料表後，當做變更資料儲存機制使用的個別變更資料表。 cdc 結構描述也包含用來查詢變更資料的相關聯系統函數。

異動資料擷取需要獨佔使用 cdc 結構描述與 cdc 使用者。如果名稱為 cdc 的結構描述或資料庫使用者目前存在於資料庫中，就無法啟用資料庫的異動資料擷取，直到卸除或重新命名結構描述和/或使用者為止。

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

注意

若要在 SQL Server Management Studio 中找出 CDC 相關範本，請移至 [檢視]，選取 [範本總管]，然後選取 [SQL Server 範本]。 異動資料擷取是包含範本的子資料夾

為資料庫停用

在資料庫內容中使用 sys.sp_cdc_disable_db (Transact-SQL) 停用資料庫的異動資料擷取。在您停用資料庫的 CDC 之前，不需要停用個別資料表的 CDC。停用資料庫的 CDC 會移除所有相關的異動資料擷取中繼資料，包括 cdc 使用者、結構描述以及異動資料擷取作業。不過，CDC 所建立的任何控制角色都不會自動移除，而且必須明確刪除。若要判斷資料庫是否已啟用 CDC，請在 sys.databases 目錄檢視中查詢 is_cdc_enabled 資料行。

如果啟用異動資料擷取的資料庫已卸除，系統會自動移除異動資料擷取作業。

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

為資料表啟用

啟用資料庫的異動資料擷取之後，db_owner 固定資料庫角色的成員可以使用預存程序 sys.sp_cdc_enable_table 建立個別來源資料表的擷取實例。若要判斷來源資料表是否已啟用異動資料擷取，請檢查 sys.tables 目錄檢視中的 is_tracked_by_cdc 資料行。

重要

如需 sys.sp_cdc_enable_table 預存程序引數的詳細資訊，請參閱 sys.sp_cdc_enable_table (Transact-SQL)。

您可以在建立擷取執行個體時指定下列選項：

來源資料表中要擷取的資料行。

根據預設，系統會將來源資料表中的所有資料行識別為擷取資料行。如果只需要追蹤部分資料行 (例如，基於隱私或效能原因)，請使用「@captured_column_list」參數指定這部分的資料行。

要包含變更資料表的檔案群組。

根據預設，變更資料表位於資料庫的預設檔案群組中。想要控制個別變更資料表位置的資料庫擁有者可以使用 @filegroup_name 參數，指定與擷取執行個體相關聯之變更資料表的特定檔案群組。此指定的檔案群組必須已存在。一般而言，建議將變更資料表放在與來源資料表不同的檔案群組中。如需示範 @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

用於控制變更資料表存取權的角色。

指定角色的目的是要控制變更資料的存取權。指定的角色可以是現有的固定伺服器角色或資料庫角色。如果指定的角色不存在，則會自動建立該名稱的資料庫角色。使用者對於來源資料表的所有擷取資料行必須具備 SELECT 權限。此外，指定角色時，不屬於系統管理員或 db_owner 角色之成員的使用者也必須是指定角色的成員。

如果您不想要使用控制角色，請將 @role_name 參數明確設定為 NULL。如需在不使用控制角色的情況下啟用資料表的範例，請參閱 在不使用控制角色的情況下啟用資料表 範本。

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

用以查詢變更淨值的函數。

擷取執行個體一定會包含資料表值函式 (TVF)，以便傳回在定義間隔內發生的所有變更資料表項目。這個函數的命名方式是將擷取執行個體名稱附加至 `cdc.fn_cdc_get_all_changes_``。如需詳細資訊，請參閱 cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)。

如果 @supports_net_changes 參數設為 1，擷取執行個體也會產生淨變更函數。此函數僅會針對在呼叫中指定之間隔內變更的每個不同資料列，傳回一個變更。如需詳細資訊，請參閱 cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)。

若要支援淨變更查詢，來源資料表必須具有主索引鍵或唯一的索引才能唯一識別資料列。如果使用了唯一的索引，就必須使用 @index_name 參數來指定該索引的名稱。在主索引鍵或唯一索引中定義的資料行必須包含在要擷取之來源資料行的清單中。

如需示範建立含有兩個查詢函數之擷取執行個體的範例，請參閱 針對所有和淨變更查詢啟用資料表 範本。

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

注意

如果在含有現有主索引鍵的資料表上啟用 [異動資料擷取]，而且並未使用 @index_name 參數來識別替代的唯一索引鍵，異動資料擷取功能就會使用此主索引鍵。如果沒有先針對資料表停用異動資料擷取，系統就不允許對主索引鍵進行後續變更。不論設定異動資料擷取時是否要求淨變更查詢的支援，都是如此。如果啟用異動資料擷取時，資料表沒有任何主索引鍵，則異動資料擷取就會忽略後續加入主索引鍵的作業。由於異動資料擷取不會使用啟用資料表之後所建立的主索引鍵，因此您可以移除此索引鍵和索引鍵資料行，而且沒有任何限制。

為資料表停用

db_owner 固定資料庫角色的成員可以使用預存程序 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 sys.tables` 目錄檢視，移除個別來源資料表的擷取執行個體。如果進行停用之後，資料庫中沒有任何資料表啟用，系統也會移除異動資料擷取作業。

如果啟用異動資料擷取的資料表已卸除，系統就會自動移除與此資料表相關聯的異動資料擷取中繼資料。

如需停用資料表的範例，請參閱「停用資料表的擷取執行個體」範本。

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

啟用和停用異動資料擷取

權限

為資料庫啟用

為資料庫停用

為資料表啟用

為資料表停用

另請參閱

意見反應

意見反應

其他資源