BISQL # 27 : Audit Trail Part : III-Implementation of Audit Trailing with CDC
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
Copyright © 2011 – 2012 Vishal Pawar
-
July 23, 2011 at 12:29 pmBISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail « SQL Server Mentalist
-
July 23, 2011 at 12:47 pmBISQL # 28 : Audit Trail Part : IV–Comparing Entity Framework and SQL CDC for Audit Trail - Vishal Pawar's Blog
-
July 31, 2011 at 10:11 pmList of monthly post of MS BI and SQL blog–July 2011 « SQL Server Mentalist
-
July 31, 2011 at 10:19 pmList of monthly post of MS BI and SQL blog–July 2011 « (B)usiness (I)ntelligence Mentalist
-
July 31, 2011 at 10:30 pmList of monthly post of MS BI and SQL blog–July 2011 « Dactylonomy of Web Resource
-
August 27, 2011 at 2:26 pmLink Resource # 20 : Aug 26 – Aug 28 « Dactylonomy of Web Resource