SQL Tips by Namwar Rizvi

August 30, 2007

Capture every data operation in SQL Server 2008

Filed under: Information,New Features,SQL Server 2008 — namwar @ 7:45 PM

Logging all the data related activities to maintain an Audit Trail is always a prime requirement of sensitive applications. People either use Triggers to manage Audit Trails or use middle tiers to maintain the log by themselves.
SQL Server 2008 has introduced a completely new framework targeted for Audit Trail maintenance. This framework consists of in-built change tracking, change log tables and associated table value functions to access the data of change log. Following is a quick summary of how change tracking works in SQL Server 2008 as provided by BOL:

  1. A member of sysadmin user enables the data capture for database by sys.sp_cdc_enable_db_change_data_capture stored procedure.
  2. After enabling, a new schema called cdc will be created which will contain the change log tables.
  3. Similarly a new user called cdc will be created.
  4. Make sure SQL Server Agent is running.
  5. Run sys.sp_cdc_enable_table_change_data_capture and supply the name of the table for which you want to capture the changes.
  6. A new table will be created in cdc schema which will be the copy of source table along with additional column to describe the type of changes.
  7. Change tracking has been setup. To query the changes, following two functions can be used
  • cdc.fn_cdc_get_all_changes_[instance name] (All changes happened to the source table in the given interval. [instance name] will be replaced by the instance name specified during enabling of data capture.)
  • cdc.fn_cdc_get_net_changes_[instance name] (Only final change happened to the source table in the given interval. [instance name] will be replaced by the instance name specified during enabling of data capture.)

This information is gathered from Microsoft Books Online and I will highly recommend every reader to read the topic Configuring Change Data Capture in Books Online of SQL Server 2008.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: