Feeds:
Posts
Comments

Archive for the ‘Audit’ Category

SQL SERVER – Working with Change Data Capture – Part 4

Posted in Audit, SQL SERVER, tagged , , , , , , on May 5, 2014| 1 Comment »

In my earlier articles, I discussed & demonstrated all basic activities in Change Data Capture (CDC) in any SQL Server database. However, I did not discuss about the retention period of CDC data in the database. In most cases, we need to modify the retention period due to our business requirements. I came across this query many times, if we can modify retention period, if Yes, how ?

BY DEFAULT, CDC configure the data retention period of 3 days. In other words, CDC keeps all the data changes history for 3 days ONLY and the rest will be cleaned (deleted). In most of my clients, I configured CDC for few tables with the retention period of 10 days and few of them leave it as a default (3 days).

Let me explain how to view / modify the retention period of CDC from in few easy steps.

Step 1 – View the existing RETENTION period in CDC:
First of all, you should check the existing retention period of CDC. It is also important to know that CDC keeps the retention period in minutes. Given below script will show the retention period in minutes as well as days by using dbo.cdc_jobs (a change data capture system table).

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part1

Step 2 – Modify the RETENTION period in CDC :
As you can see in the above result that the retention period is 3 days (4320 minutes). Lets modify it to 10 days by using sys.sp_cdc_change_job (a system stored procedure of CDC).

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
DECLARE @New_retention_period_in_minutes AS SMALLINT
DECLARE @New_retention_period_in_days AS TINYINT

--Set the retention period for 10 days
SET @New_retention_period_in_days = 10

--Convert 10 days into minutes
SET @New_retention_period_in_minutes= @New_retention_period_in_days*60*24

--Select the total number of minutes in 10 days to check.
SELECT @New_retention_period_in_minutes
As [Retention period in minutes]

--Update minutes in the CDC job
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = @New_retention_period_in_minutes;
GO
--OUTPUT

Change data capture.4.4_part2

Step 3 – Verify the RETENTION period in CDC:
You need to execute the same script as Step 1 but the output would be different this time, as we have successfully updated the retention period to 10 days in Step 2.

--This script is compatible with SQL Server 2008 and above.
USE msdb
GO
SELECT [retention] As [Retention period in minutes]
,[retention]/60/24 As [Retention period in days]
FROM
dbo.cdc_jobs
WHERE job_type ='cleanup'
GO
--OUTPUT

Change data capture.4.4_part33

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

SQL SERVER – Working with Change Data Capture – Part 3

Posted in Audit, SQL SERVER, tagged , , , , , , , on May 4, 2014| 1 Comment »

In my earlier two articles, I demonstrated how to enable and utilize the Change Data Capture (CDC) in any SQL Server database with few simple steps. However if you enable this feature for testing purpose in the test server, or sometimes you enable it on the wrong database / table  by mistake, so you need to disable it by following few steps as shown below. Before proceeding with the disability of CDC feature in the database, you must make sure that you DO NOT need the changes recorded by CDC because once you disable it, the CDC data will no longer be available.

Let me explain how to disable the CDC from any respective table & database in few easy steps.

Step 1 – Validating SQL Server Agent:
First of all you must make sure that your SQL Server Agent is UP and RUNNING as shown below.

Change data capture.3.1_part3

Step 2 – Find capture instance name :
The next step is to find the name of the capture instance (audit table) of any particular table that you need to disable for CDC. In order to achieve it, you need to execute sys.sp_cdc_help_change_data_capture (system stored procedure) to get all the list of CDC enabled objects along with its capture instance name as shown below.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
sys.sp_cdc_help_change_data_capture
GO
--OUTPUT

Change data capture.3.2_part3

Step 3 – Disable CDC for tables:
Once you have the list, you need to note the capture instance name and execute the given below script with schema, table & capture instance name.
This script will disable the CDC feature for the particular table and you will lose all the CDC data for the given below table.

Please note, if you need to disable CDC for database ONLY kindly skip this step.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_table
@source_schema = N'HumanResources',
@source_name   = N'Department',
@capture_instance = N'HumanResources_Department'
GO
--OUTPUT

Change data capture.3.3_part3

Step 4 – Disable CDC for database:
In this step, we will disable the CDC feature from any database. Given below is the script that will NOT only disable CDC for any database but also will disable the CDC for all tables in that database. So be careful while executing this statement.

--This script is compatible with SQL Server 2008 and above.

USE AdventureWorks2012
GO

EXEC sys.sp_cdc_disable_db
GO
--OUTPUT

Change data capture.3.4_part3

Let me know if you enabled CDC in your SQL Server and its feedback.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

SQL SERVER – Working with Change Data Capture – Part 2

Posted in Audit, SQL SERVER, tagged , , , , on April 29, 2014| 1 Comment »

In my earlier article, I have explained how to enable Change Data Capture (CDC) features for any database and its tables in few easy steps. It seems to me that it is very simple and straight forward activity. Just few lines of codes can give you a kick start. The next step is to avail the benefits of CDC, when you perform a series of DML (Data Manipulation Language) statements and CDC records, it automatically?? without a single line of code.

Let me execute INSERT / UPDATE / DELETE statements against the particular tables, which we have enabled for CDC in the earlier article to demonstrate how CDC works.

Please NOTE that before proceeding with CDC you MUST make sure that your SQL SERVER AGENT IS UP AND RUNNING.

  • Impact of INSERT STATEMENT in CDC:

Let me pass an INSERT statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Insert a record in [HumanResources].[Department]
INSERT INTO [HumanResources].[Department]
([Name]
,[GroupName]
,[ModifiedDate])
VALUES
('IT'
,'R & D'
,GETDATE())
GO

Once you insert a new record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.1_part2

As you can observe from the above result set, it has one new record in [HumanResources].[Department] as usual. In addition, one record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 2 and it means that it has been inserted by INSERT statement.

  • Impact of UPDATE STATEMENT in CDC:

Let me pass an UPDATE statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Update a record in [HumanResources].[Department]
USE [AdventureWorks2012]
GO
UPDATE [HumanResources].[Department]
SET [Name] = 'IT Support'
WHERE [DepartmentID]=17
GO

Once you UPDATE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.2_part2

As you can observe from the above result set, it has one updated record in [HumanResources].[Department] as usual. In addition, two records have been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 3 & 4 and it means that it has been inserted by UPDATE statement. But what do you mean by 3 & 4. In fact 3 means the data before the update statement and 4 means the data after the update statement.

  • Impact of DELETE STATEMENT in CDC:

Let me pass a DELETE statement and observe how CDC records its changes in the audit tables as shown below.

--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Update a record in [HumanResources].[Department]
USE [AdventureWorks2012]
GO
DELETE from [HumanResources].[Department] WHERE [DepartmentID]=17
GO

Once you DELETE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT * FROM HumanResources.Department
GO
SELECT * FROM cdc.HumanResources_Department_CT
GO
--OUTPUT

Change data capture.2.3_part2

As you can observe from the above result set, it has DELETED one record in [HumanResources].[Department] as usual. In addition, ONE record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 1 and it means that it has been inserted by DELETE statement.

In my upcoming article, I will write how to DISABLE the CDC feature.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

SQL SERVER – Working with Change Data Capture – Part 1

Posted in Audit, SQL SERVER, tagged , , , , , , , , on April 14, 2014| 3 Comments »

In general, if your application having an audit trial feature (to records any add, edit & delete activity performed by any end user in each table), it would be value addition to your application or it may be the best selling point of your application. However, when it comes to the development of this feature, it really become a nightmare. The reason for being problematic is that you need to integrate this feature everywhere in your application. In addition, you need to develop so many audit tables, stored procedures & triggers in order to achieve it. Sometimes the development and testing of this feature takes long time.
In SQL Server 2008, this problem has been solved by shipping a new feature namely Change data capture. It is one of the biggest and remarkable features in SQL Server 2008. In fact, it records and maintains all the changes in any table (if enabled) with very minimal amount of efforts and the good news is that the developers do not need to write even single a line of code to capture all these changes :).

In this article, I will demonstrate how to enable Change Data Capture (CDC) for any database and tables step by step.

Step 1 :
Before enabling CDC for any database, you must check whether it is already enabled or not. Given below is the script that shows all the databases along with its CDC enabled status.

--This script is compatible with SQL Server 2008 and above.
USE master
GO
SELECT
database_id,
name,
is_cdc_enabled
FROM sys.databases
GO
--OUTPUT

Change data capture.1.1_part1

Step 2 :
In step 1, as you observed that all the is_cdc_enabled columns of sys.database (system view) is 0. It means that none of the databases has been enabled for CDC.
Lets enable CDC for AdventureWorks2012 database in this step. Given below is the script.

--This script is compatible with SQL Server 2008 and above.
--DONOT forget to change the database name below.
USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_db
GO

Step 3 :
Once you enabled the CDC, you need to repeat step 1, in order to check whether CDC has been enabled for AdventureWorks2012 database or NOT.
Given below is the same script but the output would be different.

--This script is compatible with SQL Server 2008 and above.
USE master
GO
SELECT
database_id,
name,
is_cdc_enabled
FROM sys.databases
GO
--OUTPUT

Change data capture.1.2_part1

As you can see that is_cdc_enabled column value is 1 for AdventureWorks2012 database and it is a confirmation that CDC is enabled for AdventureWorks2012 database.

Step 4 :
Once you enable the CDC in the database lets enable the CDC in a table in order to capture the changes in that particular table.
But hang on a minute, before activating this feature on any table, you must check whether it is already enabled for that particular table or not. Given below is the script that will list down all the tables available in that particular database along with the CDC status.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT
object_id,
SCHEMA_NAME(Schema_id) As [Schema Name],
name As [Table Name],
is_tracked_by_cdc
FROM sys.tables
GO
--OUTPUT

Change data capture.1.4_part1

Step 5 :
In step 5, as you noticed that all the is_cdc_enabled columns of sys.database (system view) is 0. It means that none of the databases has been enabled for CDC.
Lets enable CDC for table namely HumanResources.Department in AdventureWorks2012 database in this step. Given below is the script.

--This script is compatible with SQL Server 2008 and above.
--DONOT forget to change the database name below.
USE AdventureWorks2012
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'HumanResources',
@source_name = N'Department',
@role_name = NULL
GO

As you can see above one of the parameters @role_name is NULL. I deliberately made it NULL, so all the users belonging to any role can view the changes in that particular table. However, you can pass any role to restrict the access from other users & roles accordingly.

Step 6 :
In this step, we need to check again if CDC has been enabled for that particular table or not. In order to do it, we need to repeat step 3 and this time the result set would be different. Given below is the same script as Step 4.

--This script is compatible with SQL Server 2008 and above.
USE AdventureWorks2012
GO
SELECT
object_id,
SCHEMA_NAME(Schema_id) As [Schema Name],
name As [Table Name],
is_tracked_by_cdc
FROM sys.tables
GO
--OUTPUT

Change data capture.1.5_part1

Step 7 :
Now, we assured that CDC has been enabled for that particular table, it means that CDC has created an audit table for that particular table (Same way we used to manually create in earlier version of SQL Server to record audit for any table). But where is that particular audit table ? You will find that particular table in CDC schema as shown below.

Change data capture.1.6_part1

In my upcoming article, I will write how to manipulate the CDC feature and how we can view those changes in order to really enjoy the audit trial feature with zero customization.

An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.

Read Full Post »

SQL Server 2012 – Audit Enhancements – Part II

Posted in Audit, SQL SERVER, tagged , , , on June 20, 2013| Leave a Comment »

In my earlier article, I discussed a lot of different enhancements of Audit features in SQL Server 2012. Today we are going to implement these features step by step.

Step 1 :
In this step, we need to create an audit at server level and set the options as per the requirement.
Given below are the two different methods to create Audit.

(1) Using SQL Server Management Studio :
To create Audit you need to go to Object Explorer, expand the security node (folder) after that right click the Audits folder and select New Audit as shown in the picture below.

Audit1.1

Audit1.2

(2) Using Transact-SQL :
Given below is the script to create an audit on server.

USE [master]
GO

CREATE SERVER AUDIT [Audit_Sample]
TO FILE
( FILEPATH = N'C:\Audit\'
)

ALTER SERVER AUDIT [Audit_Sample] WITH (STATE = ON)
GO

Step 2 :
In this step, we need to create an audit specification on the specific database. Also, we can setup the criteria for audit as well.
Given below are the two methods to achieve it.

(1) Using SQL Server Management Studio :
To create Audit specification, you need to go to Object Explorer, expand the security node (folder) of that particular database and then right click the Database Audits Specifications folder and select New Database Audit Specification… as shown in the picture below.

Audit1.3

Audit1.4

(2) Using Transact-SQL :
Given below is the script to create an Audit Specification on Adventure database.

USE [AdventureWorks2012]
GO

CREATE DATABASE AUDIT SPECIFICATION [FilterFor_HumanResource_Data]
FOR SERVER AUDIT [Audit_Sample]
ADD (SELECT ON SCHEMA::[HumanResources] BY [public])
WITH (STATE = ON)
GO

Step 3 :
Once Audit and Audit Specifications are done, lets browse any table within the [HumanResources] Schema to show its reflection in audit report.

USE [AdventureWorks2012]
GO
SELECT * FROM HumanResources.Department
GO

Step 4 :

Lets view the audit report using fn_get_audit_file

USE [AdventureWorks2012]
GO
SELECT * FROM fn_get_audit_file
('C:\Audit\Audit_Sample_*.sqlaudit',default,default);
GO

Audit1.5

Conclusion :
I captured only few areas of Audit, but there is much more to do in it. Also, you can easily configure it in such a way that it can reduce your troubleshoot time.

Let me know if you used this feature in real world example along with its benefits.

Read Full Post »

SQL Server 2012 – Audit Enhancements – Part I

Posted in Audit, SQL SERVER, tagged , , , , on June 18, 2013| 1 Comment »

SQL Server 2012 came up with lots of new exciting features along with great enhancements as well. One of the enhancements available in SQL SERVER 2012 is its audit features.

Given below are the details of the enhancements:

  • Database level auditing is available in Developer, Enterprise & Evaluation editions but its support is available in all editions of SQL Server. So, now you can take the benefit of database audit in every edition.
  • Another major issue is resolved using this new enhancement. Lets say if you setup a target directory over the network and due to any reason (e.g network failure) the target is not available, you usually lose the data in the earlier version. But in SQL Server 2012, it will automatically recover once the connection is restored.
  • Now, you can restrict the number of files to be created for audit. This will stop creating unnecessary files in the audit folder. But there’s one more option to make it unlimited. (Note: be careful when you use this option, it requires proper maintenance).
  • This feature is basically helpful for the database developers because now it gives the additional information of audit log and you can even trace whether the SQL Query is passed through application directly or via stored procedures.
  • Now you can also write audited events in the audit log using a new stored procedure (sp_audit_write) introduced in SQL Server 2012.
  •  Using user-defined audit event, now applications can write custom audit information in the log as well.
  • To keep a track of user defined events, SQL Server has provided the new catalog views (sys.server_audits, sys.server_file_audits) .
  • It is also very easy to filter the audit events before it is written into the audit files. So you can customize the type of events you would like to capture. Otherwise you need to capture unnecessary events.
  • As we discussed contained database features in my earlier article, now you can monitor contained database users & its activity as well.
  • And the last but not the least, the dialogue boxes are now available in SQL Server management studio. So not only from script but also you can get much more control than earlier version through SSMS as well.

Read Full Post »