In this video
will try to understand
what exactly is
CDC
CDC stands for
Change Data Capture
Many times in projects we would like to go and maintain audit trail for a table
On the screen you know I have a simple sales table here
I have two records here Shiv and Raju
Somebody changes shiv to shiva
Then I would like to maintain some kind of audit trail table where I can get the old values and the new values
CDC helps us to capture
insert, update and delete activities on table data
Now enabling CDC
is a two step process
the first thing is
we need to go enable CDC on the
database level
and then we need to go and define specific tables on which we need to go enable CDC
In order to enable CDC
In order to enable CDC on the database level
we need to go and fire this stored procedure here you can see on the screen
sp_cdc_enable_db
Let me just go and fire this
stored procedure
Once you fire the stored procedure you can see that on screen it is saying me
command has completed successfully that means
Now CDC has been enabled on the database level
Once you have enabled CDC
it creates couple of tables for himself
At the left hand side it creates couple of
tables in the system tables folder
like captured columns what kind
of columns will be
captured for old values a new values
which are the tables for which CDC are enable that is
change_tables etc
it has created couple of tables I will come to this thing later on
also it has create couple of stored procedures not couple of stored procedures many stored procedures actually
If you go down below you can see this
_CDC stored procedures here all of
these are created for CDC
so once you enable CDC it creates stored procedures and some tables so that it can
manage the CDC functionality properly
Now we have enable CDC on the databse level
We need to enable CDC on the table level
We have the simple table here called as sales
Go ahead enable CDC on the sales table
To enable CDC on a table level
we need to use
sp_cdc_enable_table
In this stored procedure we need to go and pass the table name
on which we want the CDC to be enable so you can see here
I have written here the sales table
and I am the passing the sales table name
to this stored procedure
If I now execute this stored procedure
so there it is executing
When I go to enable CDC on the table level
He is saying that your SQL Server agent is not running so in order to
implement CDC you need to ensure that
the SQL Server agent is running
Go ahead to our services.msc
and let’s enable
SQL Server agent
so I have clicked on start run and I have type services.msc
so there my
services
I have opened. Let’s go ahead and look into
SQL Server
agent
there it is
You can see that in my PC I have
lots of version of SQL Server installed like SQL Server 2012 and this is 2008. currently we are working on 2008. let me go and run this agent
I am going to start this
There my SQL server agent starting
now
until that agent starts there at the background
let’s go ahead and switch
to the SQL Server management studio
We have enabled CDC on the table level
it will create one more table
This is some table folder here
and this table name is dbo_sale_CT
_CT tables are those tables
which actually captures insert update and delete activities for a particular table
This table here dbo_sales_CT table
will capture all the activities of
insert update and delete for the sales table
Let's say I had a customer table here and if I enable CDC on it
a table with name dbo-customers_CT would have been created here
The CT table actually captures
insert, update and delete activities for a particular table
The CDC has been enable on the sales table
If I go and do some modification on the sales table data
Change this Shiv to Shiva
I am going to change that to Shiva
now if I go and open my dbo_sales_ CT table
In this table there are lots of
fields here
Look at the last two fields
Its telling that the old value was Shiv
and then later it was changed to Shiva
The sales amount was unchanged
In the same way if I go and change Raju
to Raja
The old value was Raju and the new value is Raja. In the same way if I go and delete Raja
I will get
one entry here saying that
Raja was deleted, this 1 stands for delete
By using CDC now we can monitor
insert
update and delete activities
on a table
There is one more important field in this table here called as the operation field
This operation field actually helps you to track
what kind of activities has happened when this transaction occurs
Was it a insert operation, was it delete operation
was it update operation what exactly
was a operation
on the data
The operation field
has four possible values
The first one is delete so basically
When any kind of operation has a delete
activity then it will be marked as 1
Second one is insert which is marked as 2
In case it is update it is marked by 3 and 4 respectively, 3 indicates
that it’s a old value
and the 4 indicates that it is a new value
1 for delete 2 for insert
and 3 or 4 to markup
update activity
There are couple of more tables here and probably these table can be interesting
The first table which is very interesting is captured_columns table
This keep the record of
what kind of column it is tracking
Currently we have enabled
CDC on the sales table we are tracking the salesman
and the sales amount
It has one more table here called as a change_tables
It track basically which table has been enabled
for CDC
The most important table is the _CT table where he will get the old values and new values with the operation field saying what kind of activities was
conducted on the record
Second we have the captured_columns table
Where we can know that what kind of columns are
enabled for CDC and then finally we have the change_tables table
which shows on which tables
the CDC was enabled
I hope that you enjoyed this video. In this video we were trying to understand
how we can use CDC to track
insert, update and delete activities on the table
Whatever video you have seen is
just a glimpse of what we have done so
In case you are interested in our video package
You can go to our site that is www.questpon.com
You can call on this number and you can ask
the complete DVD package what we have. In this DVD package what we have done is basically we have covered
almost everything what a .NET developer wants so right from basics of ASP.NET, Object oriented programming, SQL Server to new technologies
like WCF, Silverlight, Linq, Azure, Entity framework
We also have UML, Architecture estimation project management
There is a complete invoicing create a project end to end which is covered
You can get a better feel of how to actually projects in a systematic manner
We have covered server products both for Sharepoint 2007
as well as 2010
We have lot of best practices videos on SQL Server etc
This complete package you can get from www.questpond.com if you are interested and you can
call on this number
and you can ask for the rates it’s a very
decent rate what we have
In the same way
as compare to the videos we also have one more products with us that is our interview question books
We have different kinds of interview question books right from
.NET interview questions to SQL Servers interview questions
sharepoint interview questions BizTalk interview questions etc
In case you are interested in the books part
you can call on these numbers as per your location
Keep enjoying the videos you keep
seeing our site
and I hope that you gain more knowledge
Thank you very much