微軟介紹了SQL Server 2008的一項新功能：捕獲資料變化。捕獲資料變化功能可以友善地監控到表的變化。本文的第一、二部分介紹了怎樣在資料庫及表上開啟捕獲資料變化功能。下面将介紹的是，如果表的結構發生變化，捕獲資料變化功能将有什麼樣的表現。
注：本文章基于SQL Server 200811月CTP。
第一步
用下面的代碼建立一個名為“CDCDB”的資料庫。
| USE [master] GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CDCDB') DROP DATABASE [CDCDB] GO USE [master] GO
CREATE DATABASE [CDCDB] GO |
第二步
建立一個名為Employee的表，代碼如：
| use [CDCDB] go
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
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
結論
為了更好的監控表結構的變化，我們可以在适當的時候關閉或開啟資料庫上的捕獲資料變化功能。