Change Data Capture
- by Ricardo Peres
There's an hidden gem in SQL Server 2008: Change Data Capture (CDC). Using CDC we get full audit capabilities with absolutely no implementation code: we can see all changes made to a specific table, including the old and new values! You can only use CDC in SQL Server 2008 Standard or Enterprise, Express edition is not supported. Here are the steps you need to take, just remember SQL Agent must be running:
use SomeDatabase;
-- first create a table
CREATE TABLE Author
(
ID INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
Name NVARCHAR(20) NOT NULL,
EMail NVARCHAR(50) NOT NULL,
Birthday DATE NOT NULL
)
-- enable CDC at the DB level
EXEC sys.sp_cdc_enable_db
-- check CDC is enabled for the current DB
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = 'SomeDatabase'
-- enable CDC for table Author, all columns
exec sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Author', @role_name = null
-- insert values into table Author
insert into Author (Name, EMail, Birthday, Username)
values ('Bla', 'bla@bla', 1990-10-10, 'bla')
-- check CDC data for table Author
-- __$operation: 1 = DELETE, 2 = INSERT, 3 = BEFORE UPDATE 4 = AFTER UPDATE
-- __$start_lsn: operation timestamp
select *
from cdc.dbo_author_CT
-- update table Author
update Author
set EMail = '[email protected]'
where Name = 'Bla'
-- check CDC data for table Author
select *
from cdc.dbo_author_CT
-- delete from table Author
delete from Author
-- check CDC data for table Author
select *
from cdc.dbo_author_CT
-- disable CDC for table Author
-- this removes all CDC data, so be carefull
exec sys.sp_cdc_disable_table @source_schema = 'dbo', @source_name = 'Author', @capture_instance = 'dbo_Author'
-- disable CDC for the entire DB
-- this removes all CDC data, so be carefull
exec sys.sp_cdc_disable_db
SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf';
SyntaxHighlighter.all();
