SQL数据同步到ELK（四）- 利用SQL SERVER Track Data相关功能同步数据（上）

一、相关文档

老规矩，为了避免我的解释误导大家，请大家务必通过官网了解一波sql server的相关功能。

文档地址：

整体介绍文档：

change data capture:

change tracking:

英文差的朋友可以把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 以上的版本中，标准版也内置了这个功能。

优势：

1. 系统内置，无需自定义解决方案
2. 数据表结构不需要调整，不需要添加标识列之类的东西
3. cdc有内置的数据清除机制，对于过期后的log不需要自定义清除机制
4. 该方案是异步的，虽说会对服务器性能有影响，但毕竟进程是独立的，这种影响比直接使用触发器之类的影响要小（不知道有没有方案把sql server代理部署到单独的机器上，知道的大佬可以说下）
5. 更改是基于事务的提交，更改的顺序就是事务提交的时间，该方案获取的变更顺序一定是可靠的。
6. sql server提供了可配置和管理的一些工具

工作原理：

这里主要是实战为主，所以只放官网的两张图片大家自行感受~

区别：

这两个功能的主要区别在于记录数据的格式，cdc更为详细一些，他会记录每条记录的每一次变更的详细内容，即变更前后，数据的每个字段的值。而ct则只是记录，这条记录发生了变更，具体的变更前后的内容不会被记录。

具体记录的内容下面会给大家做详细的介绍,请稍安勿躁~

三、准备工作

开启相关功能

除了本文外，可以参考博客园其他人写的文章：

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时候的参数含义和返回的每一列的含义可以参考微软官方文档：，下面也给懒人朋友们截个图。

从这个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的那条数据，是没有体现在这里的，因为他在这个过程中，是从新增变为了删除，相当于是没有变化的，所以这个函数获取出来就没有那条记录~

这个函数的相关参数以及返回列的含义请参考：

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

天色已晚，上床睡觉保头发~