探究SQL SERVER 更改跟踪

合集 - SQL Server数据库运维(59)

1.SQL SERVER日常运维巡检系列之-日志2024-12-302.关于SQL Server镜像的一个小误区2016-07-023.一切从“简”，解放IT运维人员2016-12-164.CPU问题定位与解决2017-02-075.数据库优化案例——————某市中心医院HIS系统2017-01-186.系统隐形杀手——阻塞与等待2017-01-117.SQL Server常见问题介绍及快速解决建议2016-12-208.tempdb日志文件暴增分析2022-11-099.数据库服务器CPU不能全部利用原因分析2022-11-0710.如何快速定位TempDB产生问题2017-02-2811.索引的重要性2017-02-1612.SQL语句的优化建议2017-02-1413.通过分析等待类型解决系统产生问题2017-02-1314.磁盘问题定位与解决2017-02-0915.内存问题定位与解决2017-02-0816.写给数据库运维的兄弟2023-02-2717.为什么带NOLOCK的查询语句还会造成阻塞2023-02-2318.链接服务器查询导致的阻塞2023-02-2119.CXPACKET等待类型分析2023-02-2020.为什么sleeping的会话会造成阻塞(2)2023-02-1721.为什么sleeping的会话会造成阻塞2023-02-1422.为SQL Server配置连接加密2023-02-1423.SQL SERVER——高可用技术概述2023-02-0424.数据库服务器CPU不能全部利用原因分析2023-01-1525.管理工具造成的阻塞2023-01-0926.自动增长配置不合理导致的性能抖动2023-01-0527.Profiler导致的严重性能问题2023-01-0228.CPU持续100%分析并解决分析2022-12-2629.tempdb大量闩锁等待问题分析2022-11-29

30.探究SQL SERVER 更改跟踪2022-11-15

31.tempdb数据文件暴增分析2022-11-1032.SQL SERVER 2016 AlwaysOn 无域集群+负载均衡搭建与简测2024-12-1633.Moebius兼容AlwaysOn2024-12-1634.Moebius for SQL Server2024-12-1635.SQL SERVER日常运维巡检系列——数据库备份2024-12-0936.SQL SERVER日常运维巡检系列——结构设计2024-11-2637.Moebius for SQL Server2024-11-2038.SQL SERVER日常运维巡检系列之-数据库2024-10-0939.【能力提升】SQL Server常见问题介绍及快速解决建议2024-06-1940.CDC作业历史记录无法删除问题2024-03-0441.为Oracle链接服务器使用分布式事务2023-11-2142.大事务导致数据库恢复时间长2023-10-2543.SQL Server关于AlwaysOn的理解-读写分离的误区(一)2023-09-1444.链接服务器导致SQL Server停止响应2023-09-0545.简单配置Sql专家云2023-07-3146.SQL专家云回溯某时间段内的阻塞2023-07-0447.SQL专家云快速解决阻塞2023-06-1948.SQL专家云汇总低效的SQL语句2023-06-0549.数据库优化案例—某市中心医院HIS系统2023-05-1950.细说数据库协作运维2023-05-0851.医院核心数据库一体化建设实践2023-04-2452.【能力提升】SQL Server常见问题介绍及快速解决建议2023-04-1753.Moebius数据库多活集群2023-04-1054.30分钟带你熟练性能优化的那点儿事儿（案例说明）2023-04-0355.SQL Server底层架构技术对比2023-03-2856.Moebius兼容AlwaysOn2023-03-2357.透过等待看数据库2023-03-2158.数据库自动收缩造成的阻塞2023-03-0959.CPU持续100%分析并解决2023-03-02

收起

1.介绍

SQL SERVER在2008以上的版本提供两个用于数据库中跟踪数据更改的功能：变更数据捕获（CDC）与更改跟踪（CT）。这两个功能使应用程序能够确定对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）

 

变更数据捕获（CDC）

变更数据捕获通过获取进行 DML 更改的方面和更改的实际数据，提供用户表的历史更改信息。更改是使用异步进程捕获的，此进程读取事务日志，并且对系统造成的影响很小。

                

 

 

更改跟踪（CT）

 

更改跟踪捕获更改了表行这一事实，但不会捕获更改的数据。因此，与变更数据捕获相比，更改跟踪可以解答的历史问题比较有限。但是，对于不需要历史信息的那些应用程序，更改跟踪产生的存储开销要小得多，因为它不需要捕获更改的数据。它使用同步跟踪机制来跟踪更改。此功能旨在最大限度地减少 DML 操作开销。

 

 

2. 测试变更数据捕获（CDC）功能

场景与优势

 

    为了满足数据迁移和数据抽取的业务需要，使得有机会在数据库层面上直接实现增量抽取功能，ORACLE综合性能和场景需要，在数据库引擎层面直接集成了CDC功能，由于提供了类似API的功能接口，变更数据捕获和更改跟踪均不要求在源中进行任何架构更改或使用触发器，所以比第三方工具具有一定的优势。利用CDC捕获变更有以下特点：

 

① 性能影响小。使用异步进程捕获，通过进程读取事务日志，对系统造成的影响很小，不对业务系统造成太大的压力，影响现有业务。

 

② 监控范围大。对该表的所有DML和DDL操作都会被记录，有助于跟踪表的变化，实现表操作的追根溯源。

 

③ 操作简单 。CDC是在数据库引擎中添加的功能，封装在数据库中，类似于API接口调用，不需要复杂的业务处理逻辑就可以实现DML和DDL的操作监控。

 

④ 有一定时延性。由于捕获进程从事务日志中提取更改数据，因此，向源表提交更改的时间与更改出现在其关联更改表中的时间之间存在内在的延迟。虽然这种延迟通常很小，但务必记住，在捕获进程处理相关日志项之前无法使用更改数据。

 

开启CDC的必要条件

• sqlserver 2008 以上版本

• 需要开启代理服务（作业）

• 磁盘要有足够的空间，保存日志文件

• 表必须要有主键或者是唯一索引

 

准备测试条件

 1、首先创建测试数据库和表：

use  master

go

create  database test

go

use  test

go

CREATE  TABLE [dbo].[test](

    [id] [int] NOT NULL,

    [name] [varchar](20) NULL,

 CONSTRAINT [id] PRIMARY KEY CLUSTERED(

    [id] ASC

)  ON [PRIMARY])

go

2、然后开启数据库的CDC功能

USE  test

GO 

EXEC  sys.sp_cdc_enable_db 

GO 

3、添加次要数据文件组及文件

 

 

 

 

 4、执行脚本开启表的CDC功能

EXEC sys.sp_cdc_enable_table

        @source_schema  = 'dbo', -- 源表所属的架构的名称

        @source_name  = 'test', -- 要启用的表名称

        @capture_instance  = NULL, -- capture_instance

        @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  = 'CDC' -- 要用于创建捕获实例的更改表的文件组

执行结果：

 

 

 查询CDC开启状态

SELECT  name ,

        is_tracked_by_cdc  ,

        CASE WHEN is_tracked_by_cdc  = 0 THEN 'CDC功能禁用'

             ELSE  'CDC功能启用'

        END 描述

FROM    sys.tables

WHERE   OBJECT_ID= OBJECT_ID('test')

（有需要查看更详细参数以及语法说明，可查看微软官方资料：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-table-transact-sql?view=sql-server-2017）

 

开启成功后，CDC会在数据库中生成多个系统表，视图，系统存储过程，函数，作业等。

 

 

 

详细的介绍可查看微软官方文档：

函数：（https://docs.microsoft.com/zh-cn/sql/relational-databases/system-functions/change-data-capture-functions-transact-sql?view=sql-server-2017）

存储过程：（https://docs.microsoft.com/zh-cn/sql/relational-databases/system-stored-procedures/change-data-capture-stored-procedures-transact-sql?view=sql-server-2017）

系统表：（https://docs.microsoft.com/zh-cn/sql/relational-databases/system-tables/change-data-capture-tables-transact-sql?view=sql-server-2017）

 

开始测试CDC功能

 执行对表数据增删改的脚本以及修改表结构的脚本

use  test

insert  into test values(1,'one'),(2,'two'),(3,'three')

update  test set id=4,name='four' where id =2

delete  from test where id = 1

ALTER  TABLE test ADD name2 varchar(20)

通过之前开启CDC系统自动生成的系统表进行数据变更查询

 

 

 

可以看到相关的数据变更信息，在这些系统表里都有数据变更的记录。

 

查询表结构变更记录

 

 

 

可以看到相关的数据变更信息，在这些系统表里都有表结构变更的记录

 3    测试更改跟踪（CT）功能

场景与优势

 

对于不需要历史信息的那些应用程序,更改跟踪产生的存储开销要小得多,因为它不

需要捕获更改的数据(不需要触发器和表时间戳).它使用同步跟踪机制来

跟踪更改.此功能旨在最大限度地减少DML 操作开销.

准备测试条件

1、开启更改跟踪功能

必须先在数据库级别启用更改跟踪，然后才能使用更改跟踪。

 

 开启数据库级别更改跟踪脚本如下：

ALTER  DATABASE test 

SET  CHANGE_TRACKING =  ON  --开启更改跟踪功能

(CHANGE_RETENTION =  35 DAYS, AUTO_CLEANUP = ON) --设置保持期与是否自动清除

开启成功后，创建测试表。（注意，表必须要有主键）

CREATE  TABLE [dbo].[test](

    [c1] [int] IDENTITY(1,1) NOT NULL,

    [c2] [varchar](20) NOT NULL,

    [c3] [varchar](max) NULL

CONSTRAINT  [PK_test] PRIMARY  KEY CLUSTERED  

(

    [c1] ASC

)ON [PRIMARY])

插入数据：

insert  into test values('test1','one'),('test2','two'),('test3','three')

 

开启表更改跟踪：

 

 

开启表更改跟踪功能脚本如下：

ALTER  TABLE test 

ENABLE  CHANGE_TRACKING  --开启更改跟踪功能

WITH  (TRACK_COLUMNS_UPDATED  = ON)  --开启跟踪已更新的列

开始测试CT功能

 对表做增删改操作

insert  into test values('test4','four')

 

update  test set c2 = 't2' where c1 <3

 

delete  from test where c2='test3'

上边的脚本每执行一次，都要对应执行一次下面的脚本，查看版本号以及跟踪信息。

SELECT  CHANGE_TRACKING_CURRENT_VERSION()

AS当前版本号

 

SELECT

SYS_CHANGE_OPERATION  as 操作类型,

SYS_CHANGE_VERSION  as 版本号,

SYS_CHANGE_CONTEXT  as 语句,

SYS_CHANGE_COLUMNS  as 发生更改的列,

c1  --跟踪表主键值

FROM  CHANGETABLE(CHANGES dbo.test, 0) AS CT

结果如下：

          

 

 

  从中可以发现，三次不同的DML操作会生成三个版本，这可以证明变更跟踪是跟DML操作是同步的。并且一次操作影响两行数据，更改跟踪记录会有两条数据，证明更改跟踪的记录是按照跟踪表主键来记录的。

 4总结

变更数据捕获中的跟踪机制涉及从事务日志中异步捕获更改，因此，可以在执行 DML 操作后获得更改信息。更改跟踪中的跟踪机制涉及在执行 DML 操作的同时同步跟踪更改，因此，可以立即获得更改信息。

两种机制的功能差异如下图：

 

 

1.变更数据捕获使用异步进程捕获，此进程读取事务日志；更改跟踪是与DML操作同步的，不需要读取事务日志；

2.变更数据捕获包含了变更的历史记录，更改跟踪只保存行，但不会捕获更改的数据。

 

希望通过本文章，大家对SQL SERVER更改跟踪功能有所了解。