一、相關文檔老規矩，為了避免我的解釋誤導大家，請大家務必通過官網瞭解一波SQL SERVER的相關功能。文檔地址：整體介紹文檔：https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-t... ...
一、相關文檔
老規矩，為了避免我的解釋誤導大家，請大家務必通過官網瞭解一波SQL SERVER的相關功能。
文檔地址：
Change Data Capture:https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017
Change Tracking:https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server?view=sql-server-2017
英文差的朋友可以把URL中的en-us改成zh-cn來看中文的文檔
二、功能介紹
SQL SERVER內置提供了兩種抓取數據變更的機制，一種叫Change Data Capture（下文簡稱CDC）,另外一種叫Change Tracking(下文簡稱CT)。這兩個功能能夠在用戶執行DML操作（插入、更新、刪除）時，記錄數據的變更。
他們的工作原理是，當對數據表進行操作時，SQL SERVER會記錄事務日誌，如果你啟用了以上兩個功能中的任意一個，SQL SERVER會使用SQL SERVER代理（一個獨立的程式）來抓取這些日誌，並記錄到特定的表中（所以該方案會有額外的存儲空間和伺服器性能的開銷），最終SQL SERVER提供了一系列的函數，來幫助使用者解析這些變更記錄表，當然也有辦法可以直接去讀取這些變更記錄。
需要註意的是，這些功能在2014及以下的版本中，需要企業版或者開發版中才會有這個功能，在SQL SERVER 2016 以上的版本中，標準版也內置了這個功能。
優勢：
- 系統內置，無需自定義解決方案
- 數據表結構不需要調整，不需要添加標識列之類的東西
- CDC有內置的數據清除機制，對於過期後的LOG不需要自定義清除機制
- 該方案是非同步的，雖說會對伺服器性能有影響，但畢竟進程是獨立的，這種影響比直接使用觸發器之類的影響要小（不知道有沒有方案把SQL SERVER代理部署到單獨的機器上，知道的大佬可以說下）
- 更改是基於事務的提交，更改的順序就是事務提交的時間，該方案獲取的變更順序一定是可靠的。
- SQL SERVER提供了可配置和管理的一些工具
工作原理：
這裡主要是實戰為主，所以只放官網的兩張圖片大家自行感受~
區別：
這兩個功能的主要區別在於記錄數據的格式，CDC更為詳細一些，他會記錄每條記錄的每一次變更的詳細內容，即變更前後，數據的每個欄位的值。而CT則只是記錄，這條記錄發生了變更，具體的變更前後的內容不會被記錄。
具體記錄的內容下麵會給大家做詳細的介紹,請稍安勿躁~
三、準備工作
開啟相關功能
除了本文外，可以參考博客園其他人寫的文章：
https://www.cnblogs.com/maikucha/p/9039205.html
https://www.cnblogs.com/chenmh/p/4408825.html
1.添加專用文件組
在需要記錄數據變更的數據上右鍵->屬性->文件組，點擊添加文件組，添加一個名為TDC的文件組。
2.添加資料庫文件
切換到文件Tab頁，然後點擊添加按鈕，新建一個文件，文件類型選擇行數據，文件組選擇剛纔創建好的TDC文件組。
這一步是我從別的博主那邊學到的，官方文檔中沒有前兩步，當然你也可以忽略這兩步，不過我個人的理解是這兩步是為了避免和SQL SERVER主進程搶占mdf文件資源，如果和主進程使用同一個文件，可能會導致性能問題和併發的一些問題，具體可以在正式上PRD之前，多做一些測試。
3.啟用SQL SERVER代理
在windows 服務裡面找到SQL Server 代理服務，點擊啟動（必要的話設置成開機自動啟動），最後在MSSQL連上資料庫之後顯示效果如下：
4.資料庫級別啟用相關功能
這些數據變更追蹤功能預設都是關閉狀態的，在使用這些功能的時候，首先需要在資料庫級別啟用這些功能。
啟用資料庫的CDC功能需要執行以下SQL：
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO啟用資料庫的CT功能需要執行以下SQL:
ALTER DATABASE JaxTest（資料庫名稱） SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
也可以在資料庫上右鍵->屬性->更改跟蹤頁面中配置：
5.表級別啟用相關功能
資料庫級別啟用完成後，還需要在表級別也啟用相關功能，啟用過程如下：
我們先創建一張表：
CREATE TABLE Person ( Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, Name NVARCHAR(32) NOT NULL, Age INT NOT NULL, Remark NVARCHAR(512) NULL )
啟用CDC需要執行下麵的SQL:
exec sys.sp_cdc_enable_table [ @source_schema = ] 'source_schema', ---表所屬的架構名，一般是dbo [ @source_name = ] 'source_name' ,----表名 [ @role_name = ] 'role_name'---是用於控制更改數據訪問的資料庫角色的名稱。 [,[ @capture_instance = ] 'capture_instance' ]--是用於命名變更數據捕獲對象的捕獲實例的名稱，這個名稱在後面的存儲過程和函數中需要經常用到。 [,[ @supports_net_changes = ] supports_net_changes ]---指示是否對此捕獲實例啟用凈更改查詢支持如果此表有主鍵，或者有已使用 @index_name 參數進行標識的唯一索引，則此參數的預設值為 1。否則，此參數預設為 0。 [,[ @index_name = ] 'index_name' ]--用於唯一標識源表中的行的唯一索引的名稱。index_name 為 sysname，並且可以為 NULL。如果指定，則 index_name 必須是源表的唯一有效索引。如果指定 index_name，則標識的索引列優先於任何定義的主鍵列，就像表的唯一行標識符一樣。 [,[ @captured_column_list = ] 'captured_column_list' ]--需要對哪些列進行捕獲。captured_column_list 的數據類型為 nvarchar(max)，並且可以為 NULL。如果為 NULL，則所有列都將包括在更改表中。 [,[ @filegroup_name = ] 'filegroup_name' ]--是要用於為捕獲實例創建的更改表的文件組。 [,[ @partition_switch = ] 'partition_switch' ]--指示是否可以對啟用了變更數據捕獲的表執行 ALTER TABLE 的 SWITCH PARTITION 命令。allow_partition_switch 為 bit，預設值為 1。
上面的內容可能有點啰嗦，舉個實際例子吧，比如我要對Person這張表啟用CDC，則執行的SQL如下：
EXEC sys.sp_cdc_enable_table @source_name = 'Person', @source_schema = 'dbo', @capture_instance = 'dbo_Personal', @filegroup_name = 'TDC', @supports_net_changes = 1, @role_name = NULL
啟用CT需要執行下麵的SQL:
ALTER TABLE dbo.Person（表名） ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
當然，也可以在數據表上右鍵->屬性->變更跟蹤 Tab頁中進行啟用。
到這裡為止，就已經啟用了資料庫的CDC和CT兩個功能，當然，實際大部分情況下，只需要根據需要，選擇其中一種即可，這裡只是都做一個說明。你可以只挑一個來進行實踐。
使用CDC和CT功能進行變更抓取
1.使用CDC進行變更抓取
在我們先向表中插入一些數據，然後再修改、刪除插入的這些數據，再使用SQL SERVER提供的相關SP來抓取這些變更。
本文中的數據變化過程如下：
首先新增三條數據：
然後修改成下麵這樣子：
最後再把第二條刪掉：
此時，我們先使用CDC的相關腳本來查詢所有變更：
DECLARE @from_lsn binary(10), @to_lsn binary(10); SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Personal'); SET @to_lsn = sys.fn_cdc_get_max_lsn(); SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Personal (@from_lsn, @to_lsn, N'all update old'); GO
這段腳本中有兩個地方用到了dbo_Personal這個名字，這個名字其實是在上面啟用CDC的時候，指定的@capture_instance = 'dbo_Personal', 這個參數，如果你已經忘記了，可以翻到博客的上面回顧一下~
如果你已經忘記你執行的時候指定的這個參數名字，可以在DB的Function列表中找到它，都是以cdc.fn_cdc_get_all_changes開頭的。
執行腳本後，會得到如下結果：
調用這個Function時候的參數含義和返回的每一列的含義可以參考微軟官方文檔：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql?view=sql-server-2017，下麵也給懶人朋友們截個圖。
從這個LOG中，其實我們已經可以獲得非常詳細的我們每一次對Person這張表的操作了，而且可以發現，微軟的這個順序也已經是按照我們執行的SQL語句的順序進行排列了，每一個欄位每次的變更前後也記錄的非常的清楚了。
此外，對於CDC，也可以抓取凈變更記錄，即再一段時間內，數據差異，並且把反覆修改的中間過程會過濾掉，比如把某條記錄的某個欄位從A改成B，又從B改成A，這時候就會被忽略掉這個修改：
我們可以執行下麵的SQL來抓取凈變更：
DECLARE @from_lsn binary(10), @to_lsn binary(10); SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Personal'); SET @to_lsn = sys.fn_cdc_get_max_lsn(); SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Personal (@from_lsn, @to_lsn, N'all '); GO
最終得到的結果如下：
可以看到，對於Id為2的那條數據，是沒有體現在這裡的，因為他在這個過程中，是從新增變為了刪除，相當於是沒有變化的，所以這個函數獲取出來就沒有那條記錄~
這個函數的相關參數以及返回列的含義請參考：https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-net-changes-capture-instance-transact-sql?view=sql-server-2017
2.使用CT進行變更抓取
使用CT進行變更抓取需要執行以下SQL:
SELECT * FROM CHANGETABLE(CHANGES dbo.Person,0) AS CT
對於上面的操作記錄來說，最終會得到以下結果：
可以看到CT記錄的結果很簡單，他只會記錄哪些ID發生了變化，至於變更的內容是什麼，他不會記錄，但他會告訴你，你如果想同步這種變更到另外一個地方，需要使用的操作是Insert,Delete還是Update（SYS_CHANGE_OPERATION列），當然還有很多高級的用法，需要大家繼續探索。
小結
本文主要講瞭如何使用CDC的功能來抓取數據的變化，其實整體說的也比較淺，一是我自己對這個的認識也沒用那麼深，另外一方面是文章篇幅所限，本文的重點也不是將這些東西的各種用法講清楚，我們的目的只有一個，就是將SQL SERVER中的數據同步到ES中。所以，下篇文章我們將直接使用今天說到的這些功能，結合一些其他的函數，來將數據嘗試導入到ES中。
天色已晚，上床睡覺保頭髮~
