SQL Server 更改跟踪（Chang Tracking）监控表数据

2023-10-09 10:30
文章标签 sql 数据 监控 server 更改 跟踪 database tracking chang

本文主要是介绍SQL Server 更改跟踪（Chang Tracking）监控表数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

原文: SQL Server 更改跟踪（Chang Tracking）监控表数据

一.本文所涉及的内容（Contents）

  1. 本文所涉及的内容（Contents）
  2. 背景（Contexts）
  3. 主要区别与对比（Compare）
  4. 实现监控表数据步骤（Process）
  5. 参考文献（References）

二.背景（Contexts）

　　在SQL Server 2008以上版本中，对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）除了：SQL Server 变更数据捕获（CDC）监控表数据之外，还有一个新增功能，那就是：更改跟踪（Chang Tracking），它跟CDC有什么不同呢？使用场景有什么区别呢？

三.主要区别与对比（Compare）

1. SQL Server 2008 引入了两项跟踪功能：变更数据捕获和更改跟踪，以使应用程序能够确定对数据库中的用户表所做的 DML 更改（插入、更新和删除操作）；

2. 如果应用程序需要有关所有所做更改的信息以及所更改数据的中间值，则可能适合使用变更数据捕获，而不适合使用更改跟踪。有关详细信息，请参阅比较变更数据捕获和更改跟踪和变更数据捕获。

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

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

更改跟踪可以根据外部传入的应用程序上下文，来完成更细颗粒度的更改处理，参考：WITH CHANGE_TRACKING_CONTEXT

四.实现监控表数据步骤（Process）

/******* Step1:创建示例数据库*******/
USE MASTER
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'CT_DB')
DROP DATABASE CT_DB
GO
CREATE DATABASE CT_DB
GO

 

(二) 开启数据库更改跟踪Chang Tracking，通过下面的SQL脚本可以查询开启了更改跟踪的数据库列表；

/******* Step2:开启数据库更改跟踪Chang Tracking *******/
--启用更改跟踪（Chang Tracking），天清理一次（HOURS）
ALTER DATABASE CT_DB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS,
AUTO_CLEANUP = ON)--查看数据库是否启用更改跟踪
SELECT DB_NAME(database_id) DataBaseName,is_auto_cleanup_on,
retention_period,retention_period_units_desc
FROM sys.change_tracking_databases

wps_clip_image-12450

（Figure1：查看数据库是否启用更改跟踪）

 

(三) 开启数据库更改跟踪Chang Tracking，通过下面的SQL脚本可以查询开启了更改跟踪的数据库列表；

/******* Step3:对表启用更改跟踪*******/
--创建测试表
USE CT_DB
GO
CREATE TABLE [dbo].[Department]([DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](200) NULL,[GroupName] [nvarchar](50) NOT NULL,[ModifiedDate] [datetime] NOT NULL,[AddName] [nvarchar](120) NULL,CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED 
([DepartmentID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO--对表启用更改跟踪
ALTER TABLE  [dbo].[Department]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)--查看表是否启用更改跟踪
SELECT OBJECT_NAME(object_id) TableName,is_track_columns_updated_on
FROM sys.change_tracking_tables

wps_clip_image-20661

（Figure2：查看表是否启用更改跟踪）

 

(四) 测试对Department表进行DML操作：分两次把数据插入到表，并使用下面的SQL脚本查看变更跟踪信息，从中可以发现，两次不同的插入会生成两个版本，这可以证明变更跟踪是跟DML操作是同步的；

/******* Step4:测试DML变更跟踪*******/
--测试插入数据（版本将变成1）
INSERT  INTO dbo.Department(Name ,GroupName ,ModifiedDate
)VALUES('Marketing','Sales and Marketing',GETDATE())--再次测试插入数据（版本将变成2）
INSERT  INTO dbo.Department(Name ,GroupName ,ModifiedDate
)VALUES
('Production','Manufacturing',GETDATE()),
('Purchasing','Inventory Management',GETDATE())--表记录
SELECT * FROM dbo.Department
--当前版本
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
AS CURRENT_VERSION
--最小版本
SELECT CHANGE_TRACKING_MIN_VALID_VERSION
(OBJECT_ID('dbo.Department')) AS MIN_VERSION
--使用Changes关键字查看更改信息
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION
FROM CHANGETABLE(CHANGES dbo.Department, 0) AS CT

wps_clip_image-13828

（Figure3：更改跟踪信息）

 

(五) 继续测试对Department表进行DML操作：做一次Update操作、做一次Delete操作；

--测试更新数据（版本将变成3）
UPDATE dbo.Department SET Name = 'Marketing Group',ModifiedDate = GETDATE()
WHERE Name = 'Marketing'
--测试删除数据（版本将变成4）
DELETE FROM dbo.Department WHERE Name='Production'--表记录
SELECT * FROM dbo.Department
--当前版本
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
AS CURRENT_VERSION
--最小版本
SELECT CHANGE_TRACKING_MIN_VALID_VERSION
(OBJECT_ID('dbo.Department')) AS MIN_VERSION
--查看版本2之后的更改
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,SYS_CHANGE_COLUMNS
FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT

wps_clip_image-7154

（Figure4：更改跟踪信息）

 

(六) 查看列变更说明；

--返回哪些列被修改，1为真,0为假
SELECT DepartmentID,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'),'Name', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变Name',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'),'GroupName', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变GroupName',
CHANGE_TRACKING_IS_COLUMN_IN_MASK(
COLUMNPROPERTY(
OBJECT_ID('dbo.Department'), 'ModifiedDate', 'ColumnId') ,
SYS_CHANGE_COLUMNS) '是否改变ModifiedDate'
FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT
WHERE SYS_CHANGE_OPERATION = 'U'

wps_clip_image-13637

（Figure5：列变更说明）

 

(七) 使用Version关键字查看更改信息；

--使用Version关键字查看更改信息
SELECT *
FROM dbo.Department d
CROSS APPLY CHANGETABLE(VERSION dbo.Department, (DepartmentID), (d.DepartmentID)) AS ct

wps_clip_image-8676

（Figure6：Version关键字查看更改信息）

 

(八) 通过在外部应用程序中的上下文信息判断这个DML是由哪个应用产生的；

--设置跟踪外部程序上下文信息
DECLARE @context VARBINARY(128) = CAST('我要插入记录' AS VARBINARY(128));
WITH CHANGE_TRACKING_CONTEXT (@context)
--测试插入数据（版本将变成）
INSERT  INTO dbo.Department(Name ,GroupName ,ModifiedDate
)VALUES('Document Control','Quality Assurance',GETDATE())--查询Context更改
SELECT DepartmentID,SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,
CAST(SYS_CHANGE_CONTEXT AS VARCHAR) ApplicationContext
FROM CHANGETABLE(CHANGES dbo.Department, 4) AS CT

wps_clip_image-9332

（Figure7：上下文信息）

 

(九) 获取更改跟踪版本2之后的表数据；

--获取更改跟踪版本2之后的表数据
SELECT SYS_CHANGE_OPERATION,SYS_CHANGE_VERSION,SYS_CHANGE_COLUMNS,D.*
FROM CHANGETABLE(CHANGES dbo.Department, 2) AS CT
LEFT JOIN dbo.Department AS D
ON CT.DepartmentID = D.DepartmentID

wps_clip_image-11505

（Figure8：更改表记录）

 

(十) 总结：在更改跟踪的记录中包括了表Department 的唯一编号，还有DML的操作符字段SYS_CHANGE_OPERATION，枚举这些值（I=Insert、U=Update、D=Delete），还有DML操作的版本号：SYS_CHANGE_VERSION，它是每进行一次DML，都会递增一个版本号，所以你可以针对I=Insert、U=Update、D=Delete不同的类型加上版本号过滤，就可以找到那些数据进行了更新；

五.参考文献（References）

SQL Server 2008中新增的变更数据捕获（CDC）和更改跟踪

跟踪数据更改

Oracle 一样能够Flashback

这篇关于SQL Server 更改跟踪（Chang Tracking）监控表数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/172219。

相关文章

SQL注入漏洞扫描之sqlmap详解

SQL注入漏洞扫描之sqlmap详解

《SQL注入漏洞扫描之sqlmap详解》SQLMap是一款自动执行SQL注入的审计工具,支持多种SQL注入技术,包括布尔型盲注、时间型盲注、报错型注入、联合查询注入和堆叠查询注入... 目录what支持类型how---less-1为例1.检测网站是否存在sql注入漏洞的注入点2.列举可用数据库3.列举数据库
阅读更多...
Python将大量遥感数据的值缩放指定倍数的方法(推荐)

Python将大量遥感数据的值缩放指定倍数的方法(推荐)

《Python将大量遥感数据的值缩放指定倍数的方法(推荐)》本文介绍基于Python中的gdal模块,批量读取大量多波段遥感影像文件,分别对各波段数据加以数值处理,并将所得处理后数据保存为新的遥感影像... 本文介绍基于python中的gdal模块，批量读取大量多波段遥感影像文件，分别对各波段数据加以数值处
阅读更多...
Mysql虚拟列的使用场景

Mysql虚拟列的使用场景

《Mysql虚拟列的使用场景》MySQL虚拟列是一种在查询时动态生成的特殊列,它不占用存储空间,可以提高查询效率和数据处理便利性,本文给大家介绍Mysql虚拟列的相关知识,感兴趣的朋友一起看看吧... 目录1. 介绍mysql虚拟列1.1 定义和作用1.2 虚拟列与普通列的区别2. MySQL虚拟列的类型2
阅读更多...
Window Server创建2台服务器的故障转移群集的图文教程

Window Server创建2台服务器的故障转移群集的图文教程

《WindowServer创建2台服务器的故障转移群集的图文教程》本文主要介绍了在WindowsServer系统上创建一个包含两台成员服务器的故障转移群集,文中通过图文示例介绍的非常详细,对大家的... 目录一、 准备条件二、在ServerB安装故障转移群集三、在ServerC安装故障转移群集，操作与Ser
阅读更多...
使用MongoDB进行数据存储的操作流程

使用MongoDB进行数据存储的操作流程

《使用MongoDB进行数据存储的操作流程》在现代应用开发中,数据存储是一个至关重要的部分,随着数据量的增大和复杂性的增加,传统的关系型数据库有时难以应对高并发和大数据量的处理需求,MongoDB作为... 目录什么是MongoDB？MongoDB的优势使用MongoDB进行数据存储1. 安装MongoDB
阅读更多...
mysql数据库分区的使用

mysql数据库分区的使用

《mysql数据库分区的使用》MySQL分区技术通过将大表分割成多个较小片段,提高查询性能、管理效率和数据存储效率,本文就来介绍一下mysql数据库分区的使用,感兴趣的可以了解一下... 目录【一】分区的基本概念【1】物理存储与逻辑分割【2】查询性能提升【3】数据管理与维护【4】扩展性与并行处理【二】分区的
阅读更多...
MySQL中时区参数time_zone解读

MySQL中时区参数time_zone解读

《MySQL中时区参数time_zone解读》MySQL时区参数time_zone用于控制系统函数和字段的DEFAULTCURRENT_TIMESTAMP属性,修改时区可能会影响timestamp类型... 目录前言1.时区参数影响2.如何设置3.字段类型选择总结前言mysql 时区参数 time_zon
阅读更多...
Python MySQL如何通过Binlog获取变更记录恢复数据

Python MySQL如何通过Binlog获取变更记录恢复数据

《PythonMySQL如何通过Binlog获取变更记录恢复数据》本文介绍了如何使用Python和pymysqlreplication库通过MySQL的二进制日志（Binlog）获取数据库的变更记录... 目录python mysql通过Binlog获取变更记录恢复数据1.安装pymysqlreplicat
阅读更多...
Linux使用dd命令来复制和转换数据的操作方法

Linux使用dd命令来复制和转换数据的操作方法

《Linux使用dd命令来复制和转换数据的操作方法》Linux中的dd命令是一个功能强大的数据复制和转换实用程序,它以较低级别运行,通常用于创建可启动的USB驱动器、克隆磁盘和生成随机数据等任务,本文... 目录简介功能和能力语法常用选项示例用法基础用法创建可启动www.chinasem.cn的 USB 驱动
阅读更多...
使用SQL语言查询多个Excel表格的操作方法

使用SQL语言查询多个Excel表格的操作方法

《使用SQL语言查询多个Excel表格的操作方法》本文介绍了如何使用SQL语言查询多个Excel表格,通过将所有Excel表格放入一个.xlsx文件中,并使用pandas和pandasql库进行读取和... 目录如何用SQL语言查询多个Excel表格如何使用sql查询excel内容1. 简介2. 实现思路3
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2