SQL Server Mentalist

Home | Pages | Archives

BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC

July 23, 2011 12:00 pm

Hi Friends,

If you do not have prior knowledge for CDC Concept of SQL Server please read the following CDC blog post

• BISQL#15:Concept of Change Data Capture (CDC) in SQL Server 2008 Part – III
• BISQL#14:Concept of Change Data Capture (CDC) in SQL Server 2008 Part – II
• BISQL#13:Concept of Change Data Capture (CDC) in SQL Server 2008 Part – I

Now lets understand implementation of Audit Trailing with CDC Concept :

CDC will facilitate user to enable/disable the Audit Trailing on the Database Level. There will not be direct visible impact on the UI by CDC functionality.

Following is High level overview for CDC processing

CDC Audit Trailing requirements

• To track any modification performed on the database and to track all the changes in Database at every level, Audit Trailing needs to be implemented.
• As per Database requirement Auditing is most important aspect to track change details and easy to maintenance
• Edition Supported : Enterprise, Developer

High level implementation solution

What is CDC and why it is needed?

• CDC is acronym for Change Data Capture
• CDC helps us to provide information about DML changes on a table and a database
• Alternative to Audit trailing using trigger solution.
• It is inbuilt solution of SQL Server so internally fast if we compare with customized solution.

Configurable Audit Trailing

• The following script will enable CDC in database

USE <Your DB>

GO

EXEC sys.sp_cdc_enable_db

GO

• Following script will enable CDC on table on which we want audit trailing

USE <Your DB>

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'<Table Name>’,

@source_name = N'<CDC Table Name>’,

@role_name = NULL

GO

All changes and modification of the table can be viewed using following script which stored as separate system table

USE <Your DB>

GO

SELECT *

FROM cdc.<CDC Table Name>_CT

GO

• Disabling Change Data Capture on a table on audited table

USE <Your DB>;

GO

EXECUTE sys.sp_cdc_disable_table

@source_schema = N'<Table Name>’,

@source_name = N'<CDC Table Name>’,

@capture_instance = <Table Name>_<CDC Table Name>’;

GO

Disable Change Data Capture Feature on Database

USE <Your DB>

GO

EXEC sys.sp_cdc_disable_db

GO

• System will give option to turn Audit Trail ON/OFF as per requirement for any particular table.

Implementation Details

1. Database Table Implementation

For every table there will be separate audit table which is used to track every change

Following is structure of each table

2. Database Stored Procedure Implementation

Following Stored Proc Which will implement auditing for required Table,

· Stored Proc Name: CreateAudit

· Parameters to Stored Proc and its use

1.@TableName: Name of the table to be auditing is required

2.@AuditType: We have 3 Types of auditing (Insert, Update, and Delete) i.e. value (I, U & D)

1. Audit Table Screen shot and CDC metadata tables

select * from cdc.captured_columns

select * from cdc.change_tables

select * from cdc.lsn_time_mapping

exec sys.sp_cdc_help_change_data_capture

Mechanism of CDC

• Internally implemented as sp_replcmds so reduces log contention
• 2 agent jobs: capture and clean up
• Column data type changed: still works
• Column dropped: null
• New column: ignored
• Table dropped: change table dropped

Optimization of CDC

· Stop the capture job during load

· When applying changes to target, it is ideal to use merge

CDC functions for Retrieval

Pros of proposed solution

• Easy for maintenance
• No Triggers in this system
• Easy to implement and reuse
• User can Set Audit for Specific table
• Do Not consider auditing Staging table

Cons of proposed solution

• One Daily batch Job needs to be created for Archival of audit table for better performance
• Two table for each auditing table (one For CDC Change and other is for Archival )
• Table size may increase significantly during bulk insertion.
• Lots of change tables and functions
• Bad for big changes e.g. truncate & reload

For more interesting information on SQL we can also look into similar topics such as

· BISQL # 28: Audit Trail Part : IV–Comparing Entity Framework and SQL CDC

· BISQL # 29 : Analysis and Documentation Query

· BISQL # 30 : How Should I Find Information about Data Type

· BISQL # 31 : How To Create Folder from SSMS

· BISQL # 32 : How To find Parent Child Relationship Between Tables

Hope you will like this post on Audit Trail Part III.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog: Link Resource Website

                For More information related to BI World visit my Mentalist Blog

                         Link Resource Blog >> Daily Interesting links

                         SQL Server Mentalist >> SQL Learning Blog

                         Business Intelligence Mentalist >> BI World

                                         Connect With me on

              | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                     Copyright © 2011 – 2012 Vishal Pawar

Posted by Vishal Pawar

Categories: SQL

Tags:

« Older Newer »

6 Responses to “BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC”

1. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By BISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail « SQL Server Mentalist on July 23, 2011 at 12:29 pm

2. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By BISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail - Vishal Pawar's Blog on July 23, 2011 at 12:47 pm

3. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By List of monthly post of MS BI and SQL blog–July 2011 « SQL Server Mentalist on July 31, 2011 at 10:11 pm

4. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By List of monthly post of MS BI and SQL blog–July 2011 « (B)usiness (I)ntelligence Mentalist on July 31, 2011 at 10:19 pm

5. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By List of monthly post of MS BI and SQL blog–July 2011 « Dactylonomy of Web Resource on July 31, 2011 at 10:30 pm

6. […] BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC […]

   By Link Resource # 20 : Aug 26 – Aug 28 « Dactylonomy of Web Resource on August 27, 2011 at 2:26 pm

Leave a Reply

Mobile Site | Full Site

Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.