[SQL server Database] Keeping track of ‘what,how,who’ of changed data in SQL server 2008

Standard

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!

Paras Doshi

Video: Did you know?

Standard

The video is worth watching. i say this because, for me, it was a very humbling experience. The Kind of life we live, we seldom get time to think about the macro picture. After watching the video, i realized the importance of continuous learning attitude. Since most of the technology becomes obsolete after couple of years, it’s important to keep oneself updated about latest technologies. If you reflect on the video, i am sure you will realize the technological advancement of our age is exponential. The statistics shown in the video gives you a whole new insight. After seeing the video, you will have a new perspective for looking at situations. here’s the video:

here’s a similar video that i had shared earlier: http://parasdoshi.com/2010/09/26/i-stumbled-upon-a-video-on-youtube-the-title-is-is-information-management-just-another-hype-its-a-paradigm-shifter/

Paras Doshi