SQL SEVER CDC 啟動和關閉操作說明

-Advertisement-

本文分享自華為雲社區《GaussDB資料庫SQL系列-層次遞歸查詢》，作者： Gauss松鼠會小助手2。一、前言層次遞歸查詢是一種常見的SQL查詢方式，特別是在一些層次化的數據存儲結構中經常用到。本文主要以GaussDB資料庫為實驗平臺，為大家講解其使用方法。二、GuassDB資料庫層次遞歸查 ...

什麼是變更數據捕獲 (CDC)？

變更數據捕獲使用 SQL Server 代理記錄表中發生的插入、更新及刪除。因此，它使得可以通過關係格式輕鬆使用這些數據更改。將為修改的行捕獲將這些更改數據應用到目標環境所需的列數據和基本元數據，並將其存儲在鏡像所跟蹤源表的列結構的更改表中。此外，表值函數可供使用者系統訪問此更改數據。

開啟CDC

1.前置條件

sqlsever 2008以上版本
需要開啟代理服務（作業）
表必須要有主鍵或者是唯一索引

2.開啟CDC

2.1 開啟資料庫CDC

-- Enable Database for CDC
EXEC sys.sp_cdc_enable_db

查詢CDC狀態

---dbname為資料庫名稱，返回結果1表示開啟
select is_cdc_enabled from sys.databases where name='dbname'

2.2開啟代理服務

--開啟SQL server agent服務（逐條執行）
sp_configure 'show advanced options', 1;
GO 
RECONFIGURE;
GO 
sp_configure 'Agent XPs', 1;
GO 
RECONFIGURE
GO

2.3添加CDC文件組和文件

---添加文件組
ALTER DATABASE dbname ADD FILEGROUP CDCGroup;
---向文件組添加文件
ALTER DATABASE dbname 
ADD FILE
(
NAME= 'HospitalInterfaceDb_CDC',
FILENAME = 'E:\SQLSERVER_DATAs\HospitalInterfaceDb_CDC.ndf'
)
TO FILEGROUP CDCGroup;
---查詢db的物理文件，不清楚物理存儲路徑的可以先查詢，特別說明，當刪除了物理文件，這個查詢仍會有記錄直到下一次DB進行備份才會更新
SELECT name, physical_name FROM sys.master_files WHERE database_id = DB_ID('dbname');

2.4開啟表CDC

IF EXISTS(SELECT 1 FROM sys.tables WHERE name='table_name' AND is_tracked_by_cdc = 0)
BEGIN
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo', 
@source_name = 'table_name', -- table_name
@capture_instance = NULL, -- capture_instance 可以為NULL
@supports_net_changes = 1, -- supports_net_changes
@role_name = NULL, -- role_name
@index_name = NULL, -- index_name
@captured_column_list = NULL, -- captured_column_list
@filegroup_name = 'CDCGroup' -- filegroup_name
END; -- 開啟表級別CDC
--查詢表CDC狀態
select name, is_tracked_by_cdc from sys.tables where object_id = OBJECT_ID('table_name')

2.5 CDC表格說明
開啟之後會在作業裡面生成對應的_capture和_cleanup作業，表值函數會新增實例計算函數，系統表會添加CDC相關表格

cdc.change_tables：表開啟cdc後會插入一條數據到這張表中，記錄表一些基本信息
cdc.captured_columns：開啟cdc後的表，會記錄它們的欄位信息到這張表中
[cdc].[dbo_ORTT_CT]： ORTT是table名，這裡就是捕獲的修改日誌
其中：

__$start_lsn列：保存其事務日誌的開始序列號（LSN）,可以通過函數sys.fn_cdc_map_lsn_to_time(__$start_lsn) 轉換為時間；
__$operation列：1 = 刪除、2= 插入、3= 更新（舊值）、4= 更新（新值）；

2.6 CDC 配置

--查看CDC 作業配置
sys.sp_cdc_help_jobs

maxtrans:捕獲作業每次迴圈時要處理的最大事務數
maxscans:每次迴圈數
continuous:1:連續運行，0：間隔運行
rerention:變更保留時長，單位是（分鐘）
可以通過執行語句調整時長、執行次數等參數:

EXECUTE sys.sp_cdc_change_job @job_type = N'',      -- nvarchar(20)
                              @maxtrans = 0,        -- int
                              @maxscans = 0,        -- int
                              @continuous = NULL,   -- bit
                              @pollinginterval = 0, -- bigint
                              @retention = 0,       -- bigint
                              @threshold = 0        -- bigint

關閉CDC

-- 關閉資料庫CDC,CDC 關閉後相關表會自行刪除
EXEC sys.sp_cdc_disable_db
--刪除文件和文件組
ALTER DATABASE dbname REMOVE FILE file_name
ALTER DATABASE dbname REMOVE FILEGROUP group_name

您的分享是我們最大的動力!