SQL Server2008新功能 捕获数据变化

微软介绍了SQL Server 2008的一项新功能：捕获数据变化。捕获数据变化功能可以方便地监控到表的变化。本文的第一、二部分介绍了怎样在数据库及表上开启捕获数据变化功能。下面将介绍的是，如果表的结构发生变化，捕获数据变化功能将有什么样的表现。

　　注：本文章基于SQL Server 200811月CTP。

　　第一步

　　用下面的代码创建一个名为“CDCDB”的数据库。

USE [master] 　　GO 　　/*** Object: Database [CDCDB] Script Date: 01/07/2008 18:46:15 ***/ 　　IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CDCDB') 　　DROP DATABASE [CDCDB] 　　GO 　　USE [master] 　　GO 　　/*** Object: Database [CDCDB] Script Date: 01/07/2008 18:46:33 ***/ 　　CREATE DATABASE [CDCDB] 　　GO

　　第二步

　　创建一个名为Employee的表，代码如：

use [CDCDB] 　　go 　　/*** Object: Table [dbo].[Employee] Script Date: 01/07/2008 18:52:14 ***/ 　　IF EXISTS (SELECT * FROM sys.objects 　　WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') 　　AND type in (N'U')) 　　DROP TABLE [dbo].[Employee] 　　GO 　　use [CDCDB] 　　go 　　/*** Object: Table [dbo].[Employee] Script Date: 01/07/2008 18:52:26 ***/ 　　SET ANSI_NULLS ON 　　GO 　　SET QUOTED_IDENTIFIER ON 　　GO 　　SET ANSI_PADDING ON 　　GO 　　CREATE TABLE [dbo].[Employee]( 　　[ID] [int] NOT NULL, 　　[Name] [varchar](100) NULL, 　　CONSTRAINT [Employee_PK] PRIMARY KEY CLUSTERED 　　( 　　[ID] ASC 　　)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 　　IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 　　ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 　　) ON [PRIMARY] 　　GO 　　SET ANSI_PADDING OFF 　　GO

　　第三步

　　在数据库“CDCDB”上开启捕获数据变化功能，如下：

USE [CDCDB] 　　GO 　　EXEC sys.sp_cdc_enable_db_change_data_capture 　　GO

　　第四步

　　在表“Employee”上开启捕获数据变化功能，如下：

use [CDCDB] 　　go 　　GO 　　EXEC sys.sp_cdc_enable_table_change_data_capture 　　@source_schema = 'dbo', 　　@source_name = 'Employee', 　　@role_name = 'cdc_Employee' 　　GO

第五步

　　开启了捕获数据变化功能后，在表“Employee”上增加几列，如下：

use [CDCDB] 　　go 　　GO 　　Alter Table Employee add Address varchar(500) 　　GO 　　Alter Table Employee add Salary money 　　GO 　　Alter Table Employee add Bonus money 　　GO

　　第六步

　　向表中插入一些数据。

use [CDCDB] 　　go 　　select * from Employee 　　go 　　Insert into Employee values (1, 'Dancing Doll','221, West Broad st, 　　Greenbay, Wisconsin',60000,1000) 　　Insert into Employee values (2, 'Rainbow Dance','21, East st, 　　Denville, New Jersey',68000,1300) 　　Insert into Employee values (3, 'Water Dance','1, South Broad st, 　　Quincy, Massachusetts',76000,1600) 　　Insert into Employee values (4, 'Mickey Mouse','5, Main, 　　Greenbay, Wisconsin',120000,12000) 　　Insert into Employee values (5, 'Rat year','7, New road, 　　Danbury , Connecticut',45000,1600) 　　go 　　select * from Employee 　　go

　　执行结果

ID, Name, Address, Salary, Bonus 　　home/sql2008(HOME/MAK): (0 row(s) affected) 　　home/sql2008(HOME/MAK): (1 row(s) affected) 　　home/sql2008(HOME/MAK): (1 row(s) affected) 　　home/sql2008(HOME/MAK): (1 row(s) affected) 　　home/sql2008(HOME/MAK): (1 row(s) affected) 　　home/sql2008(HOME/MAK): (1 row(s) affected) 　　ID, Name, Address, Salary, Bonus 　　1, Dancing Doll, 221, West Broad st, 　　Greenbay, Wisconsin, 60000.0000, 1000.0000 　　2, Rainbow Dance, 21, East st, 　　Denville, New Jersey, 68000.0000, 1300.0000 　　3, Water Dance, 1, South Broad st, 　　Quincy, Massachusetts, 76000.0000, 1600.0000 　　4, Mickey Mouse, 5, Main, 　　Greenbay, Wisconsin, 120000.0000, 12000.0000 　　5, Rat year, 7, New road, 　　Danbury , Connecticut, 45000.0000, 1600.0000 　　home/sql2008(HOME/MAK): (5 row(s) affected)

　　第七步

　　更新及删除一些数据，如下：

use [CDCDB] 　　go 　　Update Employee set name='test' where id =5 　　go 　　Delete Employee where id in (3,4) 　　Go

　　第八步

　　通过以下查询语句查看DDL和DML的变化。[参考图 Fig 1.0 and Fig 1.1]

use [CDCDB] 　　go 　　select * from cdc.ddl_history 　　go

　　执行结果

　　source_object_id, object_id, required_column_update, ddl_command, ddl_lsn, ddl_time
　　565577053, 597577167, 0, Alter Table Employee add Address varchar(500)
　　, 0x000000360000006B0022, 2008-02-09 15:03:00.000
　　565577053, 597577167, 0, Alter Table Employee add Salary money
　　, 0x000000360000007A0018, 2008-02-09 15:03:00.000
　　565577053, 597577167, 0, Alter Table Employee add Bonus money
　　, 0x00000036000000800018, 2008-02-09 15:03:00.000
　　home/sql2008(HOME/MAK): (3 row(s) affected)

　　图Fig 1.0

use [CDCDB] 　　go 　　Select case __$operation when 1 then 'Deleting' 　　when 2 then 'Inserting' 　　when 3 then 'Value before Update' 　　when 4 then 'Value after Update' 　　when 5 then 'Merge' end ,__$update_mask,ID,Name 　　from cdc.dbo_Employee_CT 　　go

　　执行结果

, __$update_mask, ID, Name 　　Inserting, 0x03, 1, Dancing Doll 　　Inserting, 0x03, 2, Rainbow Dance 　　Inserting, 0x03, 3, Water Dance 　　Inserting, 0x03, 4, Mickey Mouse 　　Inserting, 0x03, 5, Rat year 　　Value before Update, 0x02, 5, Rat year 　　Value after Update, 0x02, 5, test 　　Deleting, 0x03, 3, Water Dance 　　Deleting, 0x03, 4, Mickey Mouse 　　home/sql2008(HOME/MAK): (9 row(s) affected)

　　图Fig 1.1

　　从以上的结果中，我们可以看出只有ID和Name这2列被监控到了，开启数据捕获功能之后增加的所有列都内有被监控到。

　　

　　第九步

　　现在我们对所有列进行数据捕获。先关闭当前的数据捕获功能，再打开数据捕获功能就可以了。使用下面的语句关闭当前功能：

use [CDCDB] 　　go 　　EXEC sys.sp_cdc_disable_table_change_data_capture 　　@source_schema = 'dbo', 　　@source_name = 'Employee', 　　@capture_instance = 'dbo_Employee' 　　Go

　　第十步

　　使用如下T-SQL语句在表“Employee”上开启捕获数据变化功能。这次我们想查看到哪些列被监控了。

use [CDCDB] 　　go 　　EXEC sys.sp_cdc_enable_table_change_data_capture 　　@source_schema = 'dbo', 　　@source_name = 'Employee', 　　@role_name = 'cdc_Employee', 　　@captured_column_list = N'ID, Name, Salary,Bonus' 　　GO

　　查询捕获数据变化模式下Empolyee表的变化，如下：

use [CDCDB] 　　go 　　select * from cdc.dbo_Employee_CT 　　go

　　执行结果显示，Empolyee表的所有列都被监控了。(参考图 Fig 1.2)

　　图 Fig 1.2

　　结论

　　为了更好的监控表结构的变化，我们可以在适当的时候关闭或开启数据库上的捕获数据变化功能。