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

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

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.

image

Following is High level overview for CDC processing

image

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

image

image

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

image

select * from cdc.change_tables

image

select * from cdc.lsn_time_mapping

image

exec sys.sp_cdc_help_change_data_capture

image

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

image

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

Advertisements
Categories: SQL

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

%d bloggers like this: