Reference: This blogpost is based on the talk ‘Auditing made easy’ by Pinal dave at Virtual tech days session
He talked about tools available in SQL server 2008 (Enterprise, Developer and evaluation versions) for tracking data changes.
we are able to
1) Track what data changes were made using Change tracking (CT)
2) Track how data change took place using change data capture (CDC)
Difference between CT and CDC is that
– CDC keeps Historical data whereas CT does not have it.
– Apart from this difference, they are very much alike.
3) who changed the data using Audit – it’s very easy!
In Nutshell:
{
what – change tracking
how – change data capture
who – audit
}
Let’s dive in a little deeper.
1) Change Tracking.
– you can enable change tracking using
ALTER DATABASE [DBname]
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP=ON, CHANGE_RETENTION=8 hours)
GO
CHANGE_RETENTION refers to the time until which the data will be kept by CT. here, the data will be kept for 8 hours. you can change this value.
– The information collected by the CT is accessible through the DMV:
select * from sys.change_tracking_tables;
– you can disable it by using
ALTER DATABASE [DBname] SET CHANGE_TRACKING = OFF
Detailed Info on Database Journal
2) change data capture
To enable CDC at database level.
create database testcdc;
USE TestCdc
EXEC sys.sp_cdc_enable_db;here, you will be able to see six system tables.
you will also need to enable a database table for CDC.
then you can track changes
Detailed Info on Database Journal
3) Audit.
– you will need to enable audit.
Go to object explorer and follow the path.
security > Audits > new audit
Fill in appropriate details. refer MSDN for more details
DO NOT FORGET TO ENABLE IT AFTER CREATING AN AUDIT
Now, next step:
Go to a {DatabaseName} > Security > create database audit specification
Now, fill it with appropriate details.
Reminder: do not forget to enable it too.
voila! Audit has been enabled!
PS: this is for sherlock holmes in Database arena!
Thanks a lot, Pinal dave, for sharing this information!