SQL Server Log Tool for Capturing Data Changes by building Change Data Capture Structure for MS SQL Server 2005

If you are using Microsoft SQL Server 2005 as your relational database and you neeed to log data changes in sql server databases in order to log changes or for recovery data, you can use the logging changes solution described here in this article built like Change Data Capture structures for MS SQL Server 2005 databases.

Either you are a SQL Server programmer or a deal with SQL Server administration, you might know that MS SQL Server 2008 has a few very nice enhancements like Change Data Capture (CDC) for sql server log tool in order to capture altered data columns in SQL Server databases.

Change Data Capture (CDC) is actually not built only for logging changed data in MS SQL Server databases data. Its main reason for its existency is its use in ETL (Extract, Transform, Load) for Data Warehousing and Data Mining applications. But using Changed Data Capture (CDC) for logging only changed database table columns is a nice feature especially for sql server developers and sql server database administrators.

Unfortunately, MS SQL Server 2005 does not have such a SQL Server log feature for only logging data changes in SQL Server databases.

But as a SQL Server programmer we can develop one database software or a tool for Microsoft SQL Server 2005 especially using the improvements introduced with SQL2005 in xml database logging for changes in data.

Step-by-step Guide for Installing SQL Server Database Changed Data Logger Modude

Create a new sql server database in MS SQL Server 2005 database instance with name "Cosmos_Logs".
Cosmos_Logs database name is being used in the change data capture module codes so if you want to use an other name, you can replace Cosmos_Logs in the required T-SQL code blocks (T-SQL stored procedures and T-SQL trigger sql codes).

Restore database from sql backup file using below T-SQL restore command or using Microsoft SQL Server Management Studio.

Using SQL Server Management Studio connect to the SQL Server 2005 database instance you have just created Cosmos_Logs logging database.
Run the following command: EXEC SP_InitialValues which will initialize the initial value that will be used by COSMOS_LOG_PARSER SQL Server database job.

In this step, we will create a job which will parse collected data changes and store changes in Cosmos_Logs database. So before start, first be sure that the SQL Server Agent is started and running.
In order to complete this step, you can create a SQL Server job manually which will run the "EXEC SP_LogParser" command in any interval you wish.
Or you can run the below t-sql script after editing it for required changes on the SQL Server query editor in Management Studio.
Open the Stored Procedures menu node in the Cosmos_Logs database, and edit the SP_Create_Job sql server stored procedure which we will use for creating the COSMOS_LOG_PARSER sql job.
Open the SP_Create_Job stored procedure in the edit mode and uncomment the commented lines. If you plan to use "sa" user as the @owner_login_name for the job you can run the uncommented code block. Or you can create a SQL Server login and use that user as the @owner_login_name of the MS SQL Server job.
Here in this job creation t-sql code, you can change the job name, job schedule, etc.
If you plan to change the sql logging database name, you can edit the @database_name parameter and replace Cosmos_Logs with the sql database name you desire.

And the last step for activating the logging of changed data in MS SQL Server 2005 database tables is just inserting the database and the table name which you want to track for data changes.
For example if you want to capture data changes and log only changed data column values in Countries table in LogSample sql database, you can run a similar t-sql insert statement like below on LoggedTables table.

You can realize that inserting a new database and a new table name into the dbo.LoggedTables will trigger the code block in TX_LoggedTables SQL Server trigger. And this trigger will create another trigger on the target table in the target database. This second sql table trigger will catch and log all the row inserted and/or deleted column values in XML format into the dbo.Logs table in Cosmos_Logs database. And the sql job COSMOS_LOG_PARSER will execute SP_LogParser in the Cosmos_Logs database which will parse the logged changes in XML into dbo.LogDetail table.

Before you download and install this change data capture module for MS SQL Server 2005, you should be aware of a few limitation which can be resolved by some additional work if necessary.

Limitations:

First, target table which you want to track data changes on should have a primary key column. Two columns forming a primary key is not currently supported.

Also, database collations is an other limitation. I believe reading database and table column collations and adding them into the t-sql code blocks will resolve the collation conflict errors but not yet included within the sample t-sql codes provided with the download. Default database collation for Cosmos_Logs logging database is "Latin1_General_CI_AS". If you use another collation, you should better change the database collation and table column collations before getting into details. You should use the SQL Server 2005 instance collation for Cosmos_Logs database.

You can download the Cosmos_Logs database in compressed .rar file format and use this logging SQL Server database for your solution to capturing changed data in your sql database tables after restoring the Cosmos_Logs database in a Microsoft SQL Server 2005 database instance.
Download compressed database back-up file: Change Data Capture Structure for MS SQL Server 2005

Another method can be creating the required database objects using the following sql script files and following the below instructions:

1.

Run the "CREATE DATABASE" scripts.(create-database-Cosmos_Logs-script.sql)
You can alter the file paths and file names on the scripts.
On the other side, changing the database name requires additional editing in the following scripts.


2.

Run the scripts for database objects.(create-database-objects-script.sql)


3.

Run the [TX_TriggerTable] create trigger script
Run the [TX_LoggedTables] create trigger script
(create-trigger-scripts.sql)


4.

Run the Cosmos_Log_Parser job create script.(create-sql-job-Cosmos_Log_Parser.sql)
Run this script on msdb database.

I hope you will like this SQL Server development in order to implement change data capture feature in Microsoft SQL Server 2005. SQL Server programmers and administrators can use SQL Server Log for changed data for SQL Server auditing as well as SQL Server recovery data alter or deleted in SQL Server databases.