Please visit SQLVERSITY.COM for more details.
Thank you very much for reading my previous article Change Tracking (CT) and I hope you have enjoyed while reading the content of CT.
Microsoft has introduced this feature to overcome a small drawback in CT.
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\location\
.mdf file: It is called as main data file which contains the current data of that database.
--------------------------------------------------- -- Creating Database ChangeDataCaptureDB --------------------------------------------------- USE [master] GO CREATE DATABASE [ChangeDataCaptureDB] GO --------------------------------------------------- -- Creating Table [dbo].[MSCertificationCapturing] --------------------------------------------------- USE [ChangeDataCaptureDB] GO CREATE TABLE [dbo].[MSCertificationCapturing]( CertID INT PRIMARY KEY, CertName Varchar(30) NOT NULL, Eligibility Varchar(10), RegFee$ INT ) GO ---------------------------------------------------------------------- -- Insert 5 records into [dbo].[MSCertificationCapturing] table ---------------------------------------------------------------------- BEGIN TRANSACTION [CDC] BEGIN TRY INSERT [dbo].[MSCertificationCapturing] VALUES(70433,'DBDevelopment','Developers',80) INSERT [dbo].[MSCertificationCapturing] VALUES(70483,'Programming in C#','Developers',60) INSERT [dbo].[MSCertificationCapturing] VALUES(70462,'Administring SQLServer','Admin',100) INSERT [dbo].[MSCertificationCapturing] VALUES(70685,'W7 Enterprise Support','Technician',50) INSERT [dbo].[MSCertificationCapturing] VALUES(70563,'.NET Framework','Developers',80) COMMIT TRANSACTION [CDC] END TRY BEGIN CATCH ROLLBACK TRANSACTION [CDC] END CATCH GO ---------------------------------------------------------------------- SELECT * FROM [dbo].[MSCertificationCapturing]–- 5 rows ------------------------------------------------------------------------
Flow of CDC:
Let’s dig into this flow in detail.
1. Enable CDC on Database:
Before enabling CDC, let us check the properties of our database. As shown in the below screenshot, sys.databases table has a column called ‘is_cdc_enabled’ which represents whether CDC is enabled on that particular database or not. Possible values of this column are 0 or 1.
0 — indicates CDC is not enabled and
1 — indicates CDC is enabled on the database.
By default it is 0(disabled) when we create a database.
Please run the below query to check whether CDC is enabled on our database or not.
------------------------------------------------- USE [ChangeDataCaptureDB] GO EXEC sp_cdc_enable_db GO -------------------------------------------------
Let’s enable CDC on our database by executing the below procedure.
------------------------------------------------- USE [ChangeDataCaptureDB] GO EXEC sp_cdc_enable_db GO -------------------------------------------------
I do not know whether you have noticed or not, without knowing yourself you created the below.
When we enable CDC on a database, by default the following will be created. If they are not displaying in object explorer, please refresh your SQL Server instance and open.
Tables:
cdc.captured_columns: It holds list of captured column.
cdc.change_tables: It holds list of tables for which CDC is enabled.
cdc.ddl_history: It holds all DDL changes since CDC is enabled.
cdc.index_columns: It holds list of indexes which related to our CDC table.
cdc.lsn_time_mapping: It maps LSN number and time.
dbo.systranschemas: It is used to track schema changes.
No need to bother about these tables. So I am not going in detail 🙂
Users:
CDC
Schema:
CDC
2. Start SQL Server Agent service:
This service is required to schedule SQL Server agent jobs. Since we are going to use these jobs in future in this article, we need to start this service if it is not started automatically.
If it is not started, please follow below steps to start the service.
Start > Run > type Services.msc > Ok > SQL Server Agent (MSSQLSERVER) > Start > Ok.
Now check your SQL Server agent whether it is started or not.
3. Enable CDC on Table:
Please run the below query to check CDC is enabled on our table or not.
------------------------------------------- USE [ChangeDataCaptureDB] GO SELECT is_tracked_by_cdc FROM sys.tables WHERE name ='MSCertificationCapturing' GO -------------------------------------------
It is not enabled. Let’s enable CDC on it. Please execute the below procedure to enable CDC on [dbo].[MSCertificationCapturing] table.
------------------------------------------------ USE [ChangeDataCaptureDB] GO EXEC sys.sp_cdc_enable_table @source_schema ='dbo', @source_name ='MSCertificationCapturing', @role_name =NULL, @supports_net_changes = 1 GO ------------------------------------------------
Let’s have a discussion on the above parameters.
@source_schema:Schema name of the table that we want to enable CDC.
@source_name: Table name that we want to enable CDC.
@role_name: It is used to restrict the access to some users. If it is NULL, then it will be accessible to everyone.
@supports_net_changes: It can have 0 or 1.
0 — To track all changes.
1 — To track all changes and net changes.
Some more parameters are there in this procedure. But they are optional and not much important.
Now CDC is enabled. If we want we can check by running above query.
------------------------------------------- USE [ChangeDataCaptureDB] GO SELECT is_tracked_by_cdc FROM sys.tables WHERE name ='MSCertificationCapturing' GO -------------------------------------------
After enabling CDC on table, by default the following were created.
Table:
cdc.dbo_MSCertificationCapturing_CT – This is the main table which holds all the changed data since the CDC is enabled.
Below is the list of columns in this table.
- __$start_lsn
- __$end_lsn
- __$seqval
- __$operation
- __$update_mask
Out of the above 5 columns, __$operation column is much important for our data capturing. It can have 1, 2, 3 and 4 as values. Below table shows the meaning of these values.
Value | Meaning |
1 |
Delete |
2 |
Insert |
3 |
Value before update |
4 |
Value after update |
So, based on this column value, we are going to say which DML operation has done on the data.
Jobs:
cdc.ChangeDataCaptureDB_capture: It reads the data from .ldf file and stored all data in change table.
cdc.ChangeDataCaptureDB_cleanup: It cleans the old data. By default it cleans for 3 days.
Note: Not only above, some more procedures and functions also creates when we enable CDC.
4 & 5.Make changes and Track the data:
Before modifying data, let us see the records in tables.
---------------------------------------------------------------------- SELECT * FROM [dbo].[MSCertificationCapturing] -- 5 rows ---------------------------------------------------------------------- SELECT * FROM cdc.dbo_MSCertificationCapturing_CT -- 0 rows ----------------------------------------------------------------------
Now I am going to do modification in the existing data of [dbo].[MSCertificationCapturing] table.
Please run the below script.
---------------------------------------------------------------------- -- Making DML changes to [dbo].[MSCertificationCapturing] table ---------------------------------------------------------------------- BEGIN TRANSACTION [CDC_DML] BEGIN TRY INSERT [dbo].[MSCertificationCapturing] VALUES(70620,'MS Excel Macros','Developers',90) DELETE FROM [dbo].[MSCertificationCapturing] WHERE CertID = 70433 UPDATE [dbo].[MSCertificationCapturing] SET RegFee$ = 70 WHERE RegFee$ = 50 COMMIT TRANSACTION [CDC_DML] END TRY BEGIN CATCH ROLLBACK TRANSACTION [CDC_DML] END CATCH GO ----------------------------------------------------------------------
Now check the data.
---------------------------------------------------------------------- SELECT * FROM [dbo].[MSCertificationCapturing] -- 5 rows ---------------------------------------------------------------------- SELECT * FROM cdc.dbo_MSCertificationCapturing_CT -- 4 rows ----------------------------------------------------------------------
Even if we do not know about the DML script which we ran above, we can find the modifications by looking at the above screenshot.
6. Disable CDC on Table:
Yes..!!! We are done with data capture.
Now ultimately our next step will be disabling CDC. We should disable on database as well as table.
But we should first disable on table and then table. As you already gone through my Change Tracking article, I need not explain the reason why am I disabling fist on table not on database.
Please execute the below procedure to disable CDC on table.
------------------------------------------------ USE [ChangeDataCaptureDB] GO EXEC sys.sp_cdc_disable_table @source_schema ='dbo', @source_name ='MSCertificationCapturing', @Capture_Instance ='dbo_MSCertificationCapturing' GO ------------------------------------------------
7. Disable CDC on database:
Please execute the below procedure to disable CDC on table.
------------------------------------------------ USE [ChangeDataCaptureDB] GO EXEC sys.sp_cdc_disable_db GO ------------------------------------------------
Ufff…..!!! We are done with Change Data Capture :).
Please do not think that this article is complete reference for CDC 🙂. This article gives you basic understanding and I almost covered all concepts. If I discuss each and every topic in depth, then it will not be an article and becomes a complete reference of CDC. So I am concluding here.
If you guys have little confusion between CT and CDC, do not worry 🙂. Let’s have a great discussion in my next article ‘Comparison between Change Tracking and Change Data Capture’
Have a nice day…!!!
ALL THE BEST
Very interesting topic and useful to track/recover changes of data. Thank you
Thank you very much…
Nice work.. It helped alot.. Keep it up dude..
Thank you very much…
G8 work. Helped me alot….
Share some more dude…
Sure. Thank you…
very nice article with simple explanation for detailed understanding
Thank you…
[…] To overcome the above drawbacks, SQL Server 2008 introduced powerful and efficient tracking mechanisms called‘Change Tracking (CT)’ and Change data Capture (CDC) […]
Good Article….
Nice article!
But unfortunatelly I have Standard Edition, so I can’t use this feature.
I have to update several tables in my database and I must report users which columns are updated and show values before and after update. So, how can I do it without CTC?
Hi Igor,
Try with Change Tracking. It will help you till some extent.
It works in standard edition.
Please find my article below on Change Tracking.
http://www.codeproject.com/Articles/537649/SQL-Server-Change-Tracking-CT
Happy Learning
SQLVersity.
Thanks for sharing nice information about changes tracking. This article described about how captures the changed details. I have also found good information from http://www.sqlserveraudit.org/. This tool allows to track the SQL server changes as who, what, when and where changes and audit specific user activities. It provides data filter options that helps to unfold the minutest of information to the last level and allows to automate the report generation.