CDC - Change Data Capture

• Download project - 576 B

Introduction

In a previous article, I wrote about Postgres and audits. Audits are a large part of enterprise applications. I try and read as many articles on enterprise applications as I can. When I see a new technology, I want to see how it might fit into these type of applications. I started reading about CDC and decided to write this article.

Using the Technology

To start, I created a database that we can use to test the feature.

CREATE Database MinAudit
GO

USE  MinAudit
GO
/*
/ Enable CDC
*/
EXEC sys.sp_cdc_enable_db
GO

IF OBJECT_ID('MinUser') IS NOT NULL
DROP TABLE MinUser
GO

/*
/ The main data table
*/
CREATE TABLE MinUser (
    User_Id             UNIQUEIDENTIFIER       not NULL PRIMARY KEY,
    Mod_User            varchar (50) NOT NULL,
    User_Currency       int not null,
    User_Name           varchar (50) NOT NULL,
    User_Password       varchar (50) NOT NULL,
    UNIQUE (User_Name)
)

After creating the database, you should enable the CDC. If the table exists, then drop it before we create it. This is just a sample table but the sequence will be the same for any other table or tables.

This is what it should now look like:

The next step is to create the audits in CDC.

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MinUser',
@role_name     = NULL,
@supports_net_changes = 1
GO

To check what this procedure has done, check the system tables under the database you just created. You should see something like what is shown below. There should be a table with the schema name, the source name, and the _CT to describe that the table is used for change.

One thing to check before you do this is to make sure the SQL agent is running. CDC uses this to do the changes. If you forget, you will get an error. One time, I forgot I received the following error, but I deleted the table and re-ran the SQL.

SQLServerAgent is not currently running so it cannot be notified of this action. 

The next step is to check the audits and see what they do. First let's do an insert just to see what happens, and then an update.

INSERT INTO [MinUser](User_Id, Mod_User, User_Currency, User_Name, User_Password)
          VALUES ('1DC422DB-7A82-410c-9465-98BBB692D1C8', 'hazel', 2, 'don', 'password')

Now do a select on each of the tables to see what is in each.

Update [MinUser] set User_Currency = 3 where _
	User_Id = '1DC422DB-7A82-410c-9465-98BBB692D1C8'

Now do a select on each of the tables again to see what is in each, and see what has changed.

The table shows the operations that have occurred on the main table along with the data of the main record. The operation column shows what operation has taken place on the data. The operations are listed as:

• 1 = delete
• 2 = insert
• 3 = update (captured column values are those before the update operation). This value applies only when the row filter option 'all update old' is specified
• 4 = update (captured column values are those after the update operation)

Therefore, as you can see, the first time we have an operation of 2 which tells us an insert happened. The second image shows that we have a 3 (before operation) and a 4 (after operation). There are many more CDC operations that can be used. A brief list of the additional stored procedures are:

Points of Interest

This is a brief introduction to CDC. The main reason I wanted to try this was to learn its capabilities. I wanted to see if this would help us in doing Audits. The CDC feature as of this writing is only available in the enterprise and developer versions of MSSQL, Which I find disappointing. The CDC feature has many benefits, not only is it good at doing audits but Microsoft has additional plans for this, one being Sync. Sync can be used to synchronize two disconnected databases. It does this using the CDC to keep track of the changes that have occurred. I hope that you liked this introduction and are interested in learning more. The main problem of adoption will be that it is only available on the enterprise and developer editions.

History

• 12^th September 2009 - Initial release