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 are familiar with I have a simple marketings table now I have two records now Shiv and Raju Somebody converts shiv to shiva Then I would like to maintain some kind of audit trail table where I can get the old-time ethics and the brand-new appraises CDC helps us to capture insert, update and delete acts 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 placed 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 intends 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-time importances a brand-new qualities which are the tables for which CDC are enable that is change_tables etc it has generated couple of tables I will come to this thing later on also it has create got a couple of placed procedures not couple of stored procedures numerous accumulated procedures actually If you go down below you can see this _CDC collected procedures here all of these are created for CDC so formerly you enable CDC it creates stored procedures and some tables so that it can manage the CDC functionality properly Now “were having” 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 onward enable CDC on the sales table To enable CDC on a table level we need to use sp_cdc_enable_table In this placed procedure we need to go and pass the table epithet 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 refer to this placed procedure If I now implement this placed 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 lets enable SQL Server agent so I have sounded on start raced and I have type services.msc so there my works I have opened.Lets go ahead and conducted an investigation into SQL Server agent there it is You can see that in my PC I have lots of version of SQL Server positioned 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 negotiator starts there at the background gives go ahead and switch to the SQL Server control studio We have enabled CDC on the table level it will create one more table This is some table folder here and this counter figure is dbo_sale_CT _CT counters are those counters which actually captures position update and delete tasks for a particular table This counter now dbo_sales_CT table will captivate all the activities of insert update and delete for the sales table Let’s say I had a customer table now and if I enable CDC on it a table with honour dbo-customers_CT would have been created here The CT table actually captivates implant, update and delete acts for a particular table The CDC has been enable on the sales table If I is now going do some qualifying on the sales table data Change this Shiv to Shiva I am going to change that to Shiva now if I is now going open my dbo_sales_ CT table In this counter there are a lot of battlegrounds now Look at the last two battlefields Its telling that the aged price was Shiv and then later it was changed to Shiva The sales amount was unchanged In the same way if I go and mutate Raju to Raja The aged appraise was Raju and the brand-new quality is Raja.In the same way if I go and delete Raja I will get one entryway now saying that Raja was deleted, this 1 stands for delete By using CDC now we can monitor insert update and delete acts on a table There is one more important field in this table here called as the operation field This action arena actually helps you to track what kind of undertakings has happened when this transaction results Was it a position busines, was it delete operation was it update operation what exactly was a operation on the data The operation field has four possible qualities The first one is delete so basically When any kind of operation has a delete activity then it is likely to be celebrated as 1 Second one is insert which is differentiated as 2 In case it is update it is labelled by 3 and 4 respectively, 3 indicates that its a old-fashioned price and the 4 indicates that it is a brand-new appreciate 1 for delete 2 for insert and 3 or 4 to markup update activity There are couple of more counters 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 ii” tracking the salesman and the sales sum It has one more table here called as a change_tables It track mostly which table has been enabled for CDC The most important table is the _CT table where he will get the aged evaluates and new significances with the operation field saying what kind of works 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 depicts on which tables the CDC was enabled I hope that you experienced this video.In this video we were trying to understand how we can use CDC to track set, 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 box You can go to our site that is http://www.questpon.com You can call on this quantity and you can ask the ended DVD package what we have. In this DVD package what we have done is basically “were having” covered almost everything what a. Cyberspace make misses so right from elementaries of ASP.NET, Object familiarized 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 extremity which is covered You can get a better feel of how to actually projects in a systematic manner We have plastered server commodities both for Sharepoint 2007 as well as 2010 We have parcelled of best patterns videos on SQL Server etc This complete box you can get from http://www.questpond.com if you are interested and you can call on this numeral and you can ask for the rates its a awfully good charge 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 notebooks We have different kinds of interview question works right from. NET interrogation questions to SQL Servers interview questions sharepoint interview questions BizTalk interview questions etc In occurrence you are interested in the books part you can call on these counts as per your site Keep enjoying the videos you maintain insuring our site and I hope that you gain more knowledge Thank you much needed
