Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.
Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC inSQLHub database
–this willcreate “cdc” schema in SQLhub database too
–along with”cdc” schema, it will create some system table
–in”cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDCfor our table created above.
–when you willenable cdc for ChangeDataCapture table
–it will createtwo job under SQL Server Agent
–which willread data from transaction whenever you will make any change in data
–and stores itin CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you haveorginal table, named “ChangeDataCapture”
–another tableto keep all changes “ChangeDataCapture”,
–which has beencreated in step above
select * fromChangeDataCapture
–followingtable will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let usmake some DML operation in ChangeDataCapture table and
–observe howdoes it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 representINSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofUPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 representvalue before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofDELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 representDELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Change Data Capture (CDC) in SQL Server 2008

Recently I was working on one project and client required toaudit the data, means, insert / update / delete should be tracked on someimportant table. There are many ways you can capture these information (AuditTrail), I have written quite a few articles on this subject too but all thoseneeds additional code to be written. Fortunately my client is using SQL Server2008 R2 version so I don’t even need to write down any specific code to capturechanged data as there is facility of CDC (Change Data capture) in SQL Server2008.
Even before we look at CDC in details, make sure you haveyour SQL Server Agent is running as CDC in SQL Server 2008 will use SQL ServerAgent to make audit trail for you. If your SQL Server Agent is not running atthe moment, start it from control Panel->Administrative Tools-> Services.You can find “SQL Server Agent (YourInstanceName)”, just start this service andthen you will be able to work on CDC.
create database SQLHub
GO
use SQLHub
go
Create Table ChangeDataCapture
(
      ID INT Identity(1,1)
      ,Name varchar(20)
)
GO
–enable CDC inSQLHub database
–this willcreate “cdc” schema in SQLhub database too
–along with”cdc” schema, it will create some system table
–in”cdc” schema
USE SQLHub
GO
EXEC sys.sp_cdc_enable_db
GO
–now enable CDCfor our table created above.
–when you willenable cdc for ChangeDataCapture table
–it will createtwo job under SQL Server Agent
–which willread data from transaction whenever you will make any change in data
–and stores itin CDC table
USE SQLHub
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N’dbo’,
@source_name   = N’ChangeDataCapture’,
@role_name     = NULL
GO
–now you haveorginal table, named “ChangeDataCapture”
–another tableto keep all changes “ChangeDataCapture”,
–which has beencreated in step above
select * fromChangeDataCapture
–followingtable will be there under category of “System Tables” folder.
select * from cdc.dbo_ChangeDataCapture_CT
–now let usmake some DML operation in ChangeDataCapture table and
–observe howdoes it stores data in cdc.dbo_ChangeDataCapture_CT
INSERT INTO ChangeDataCapture
SELECT ‘Ritesh Shah’ UNION ALL
SELECT ‘Rajan Shah’ UNION ALL
SELECT ‘Teerth Shah’
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.
–2 representINSERT.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofUPDATE now.
UPDATEChangeDataCapture
SET Name=‘Rajan Jain’ WHERE Name=‘Rajan Shah’
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.
–3 representvalue before UPdate and 4 represent new value after update.
select * from cdc.dbo_ChangeDataCapture_CT
–see effect ofDELETE now
Delete From ChangeDataCapture WHEREID=2
GO
–see the datain both tables
select * fromChangeDataCapture
–incdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.
–1 representDELETE operation
select * from cdc.dbo_ChangeDataCapture_CT
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Article on Audit facility in SQL Server 2008 in Experts-Exchange

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server.

In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace which is not the most intuitive tools. However in the latest editions, we are now given quite a lot of power for auditing with some purpose built tools. The SQL 2008 Audit is meant to be replacing SQL Trace, it is faster, more granular and easier to interact with from SQL Server Management Studio, and with code. In this article, I am using a T-SQL code approach.

Since this is one of the big topics in SQL Server 2008 (and bound to become more popular), it is not possible to cover it completely here. However, I will try to give you one detailed but simple example as an introduction to SQL Audit.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Auditing in SQL Server 2008 white paper.

A key part of any data security strategy is the ability to track who has accessed, or attempted to access, your data. This provides the ability to detect unauthorized access attempts or, if necessary, to piece together the actions of malicious insiders who misused their legitimate access. Furthermore, a rich and robust tracking capability can provide oversight of sensitive configuration changes made by administrators.
Such considerations are ever more relevant in today’s information economy. Data is collected, stored, used, and misused at an ever increasing rate. Governments and private sector organizations around the world are responding to this by establishing various compliance regimes to improve the stewardship of data by those who hold it. A few of the most widely known examples include:
  • European Union Data Protection Directive, a strict data protection policy with implications across the EU and the global economy.
  • HIPAA, or Health Insurance Portability and Accountability Act, part of United States law
  • Sarbanes-Oxley, part of United States law governing corporations.
  • Payment Card Industry Data Security Standard, mandated by major credit card companies, with worldwide implications.
These formal regulations affect organizations of all sizes, in all industries, around the world. They place significant pressure on organizations to ensure their IT platforms and practices are compliant. And ultimately, these requirements land at the feet of the DBAs, developers, and IT professionals who manage the data.
It is important that a data management platform provide the means to meet these requirements, and do so efficiently. To address these needs, SQL Server 2008 introduces a rich and deeply integrated auditing capability that offers major improvements over previous versions of the Microsoft® SQL Server® database software.
This paper will review the new audit features of SQL Server 2008, compare them to past versions, and walk through some implementation examples.
You can read full while paper by clicking here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Audit in SQL Server 2008

Audit is really one of the interesting, useful yet difficult to maintain topic in history of SQL Server. I have already written few articles on how to maintain Audit trail in SQL Server. You can refer those articles from the below given links, once you will go through those articles and then read ahead this one, you will come to know how much it is easy in SQL Server 2008.

Earlier people had very few options for auditing in SQL Server but in latest technology, you are having quite a good power on auditing stuff. Since this is one of the big topics in SQL Server, it is not possible to cover it completely here, though I will try to give you one detailed example herewith below.

First of all when you want to use power of Audit in SQL Server 2008, you have to create AUDIT object. AUDIT object is nothing more than just a container of Audit specification on Server level as well as on database level. You can store log in Application Event Log, Security Event Log and Filesystem.

I am going to create one Audit object which will act as a container of one of the database level audit specification which will keep an eye on one Schema for any DML statement executed on that schema.

I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.

–Select MASTER Database
USE master
GO
–create SERVER Audit
CREATE SERVER AUDIT [FirstAudit]
TO FILE
(
      FILEPATH=N’D:\AUDIT\’
      ,MAXSIZE=10 MB
      ,MAX_ROLLOVER_FILES=100
      ,RESERVE_DISK_SPACE=ON
)
WITH
(
      QUEUE_DELAY=1000
      ,ON_FAILURE=SHUTDOWN
      ,AUDIT_GUID=‘2EB5EF64-1B15-4AFF-B248-6F39D423E2E7’
)
GO
–Alter server audit object for
–making it enable
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO
–creating one test database,
–if it is exists, droping it first
if exists(SELECT 1 FROM SYS.DATABASES WHERE name=‘AuditTest’)
BEGIN
      USE master
      drop database AuditTest
END
CREATE DATABASE AuditTest
GO
–select newly created database
USE AuditTest
GO
–create two schema AUD1 and AUD2
CREATE SCHEMA Aud1
GO
CREATE SCHEMA Aud2
GO
–create two table
–one with each schema
if OBJECT_ID(‘Aud1.Table1’) is not null drop table Aud1.Table1
CREATE TABLE Aud1.Table1 (id int)
GO
if OBJECT_ID(‘Aud2.Table1’) is not null drop table Aud2.Table1
CREATE TABLE Aud2.Table1 (id int)
GO
–create database level AUDIT SPECIFICATION
–for our server audit created above
–which will keep watch on schema AUD1
–for DML statements but won’t watch for AUD2 schema
CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]
FOR SERVER AUDIT [FirstAudit]
ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])
WITH (STATE=ON)
GO
–making some DML actions in both table
–of both schema
INSERT INTO Aud1.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
INSERT INTO Aud2.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
UPDATE Aud1.Table1 set id=6 where ID=5
GO
UPDATE Aud2.Table1 set id=6 where ID=5
GO
–looking at what we have received in our audit file.
–you will know that you got data for AUD1 schema only
SELECT * FROM sys.fn_get_audit_file(‘D:\Audit\*’,DEFAULT,DEFAULT)
go
You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Audit in SQL Server 2008

Audit is really one of the interesting, useful yet difficult to maintain topic in history of SQL Server. I have already written few articles on how to maintain Audit trail in SQL Server. You can refer those articles from the below given links, once you will go through those articles and then read ahead this one, you will come to know how much it is easy in SQL Server 2008.

Earlier people had very few options for auditing in SQL Server but in latest technology, you are having quite a good power on auditing stuff. Since this is one of the big topics in SQL Server, it is not possible to cover it completely here, though I will try to give you one detailed example herewith below.

First of all when you want to use power of Audit in SQL Server 2008, you have to create AUDIT object. AUDIT object is nothing more than just a container of Audit specification on Server level as well as on database level. You can store log in Application Event Log, Security Event Log and Filesystem.

I am going to create one Audit object which will act as a container of one of the database level audit specification which will keep an eye on one Schema for any DML statement executed on that schema.

I will store log in D:\Audit in my server, you can change the path in given script if you wish, do create the folder and give reference in script below.

–Select MASTER Database
USE master
GO
–create SERVER Audit
CREATE SERVER AUDIT [FirstAudit]
TO FILE
(
      FILEPATH=N’D:\AUDIT\’
      ,MAXSIZE=10 MB
      ,MAX_ROLLOVER_FILES=100
      ,RESERVE_DISK_SPACE=ON
)
WITH
(
      QUEUE_DELAY=1000
      ,ON_FAILURE=SHUTDOWN
      ,AUDIT_GUID=‘2EB5EF64-1B15-4AFF-B248-6F39D423E2E7’
)
GO
–Alter server audit object for
–making it enable
ALTER SERVER AUDIT [FirstAudit]
WITH (STATE=ON)
GO
–creating one test database,
–if it is exists, droping it first
if exists(SELECT 1 FROM SYS.DATABASES WHERE name=‘AuditTest’)
BEGIN
      USE master
      drop database AuditTest
END
CREATE DATABASE AuditTest
GO
–select newly created database
USE AuditTest
GO
–create two schema AUD1 and AUD2
CREATE SCHEMA Aud1
GO
CREATE SCHEMA Aud2
GO
–create two table
–one with each schema
if OBJECT_ID(‘Aud1.Table1’) is not null drop table Aud1.Table1
CREATE TABLE Aud1.Table1 (id int)
GO
if OBJECT_ID(‘Aud2.Table1’) is not null drop table Aud2.Table1
CREATE TABLE Aud2.Table1 (id int)
GO
–create database level AUDIT SPECIFICATION
–for our server audit created above
–which will keep watch on schema AUD1
–for DML statements but won’t watch for AUD2 schema
CREATE DATABASE AUDIT SPECIFICATION [AuditTestSpec]
FOR SERVER AUDIT [FirstAudit]
ADD (SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[Aud1] by [PUBLIC])
WITH (STATE=ON)
GO
–making some DML actions in both table
–of both schema
INSERT INTO Aud1.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
INSERT INTO Aud2.Table1
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5
go
UPDATE Aud1.Table1 set id=6 where ID=5
GO
UPDATE Aud2.Table1 set id=6 where ID=5
GO
–looking at what we have received in our audit file.
–you will know that you got data for AUD1 schema only
SELECT * FROM sys.fn_get_audit_file(‘D:\Audit\*’,DEFAULT,DEFAULT)
go
You can find detailed text on this topic from MSDN. Do look at all reference link given below the article in MSDN.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com