CDC is known as Changed Data Capture. This is a concept of SQL Server and has to be enabled manually. We enable CDC to track down the Insert, Update, Delete on a particular table or inside a particular database.
Now when any candidate comes in MSBI training following some simple steps are needed to be followed through which we can enable the CDC on Database as well as individual Table.
- Firstly what we have to do is that open a New Query editor window.
- Next we will write a stored procedure. First we will enable CDC on the Database.
EXEC sys.sp_cdc_enable_db
Then we execute the query.
In this way we have to enable CDC on a Database.
- Now after enabling CDC on the Database we will now do it for Table. In the same way we will open a new Query editor window
and write a stored Procedure in it.
EXEC sys.sp_cdc_enable_table
@source_schema=N'dbo',
@source_name=N'tblCustomer',
@role_name=NULL
After writing the stored procedure we then execute the query and then our Table has CDC enabled.
- Now to check whether the CDC on table as well as the database has been enabled we will go to our Object Explorer and see that there has been a folder named “System Tables”. When we expand that table we will see that there are tables created with CDC as prefix. We will also see the CDC enabled on a table as there is a table created with our table’s name on which our CDC was enabled followed by “_CT”. Example, cdc.dbo_tblCustomer_CT.
In this way MSBI training candidate are thought to enable CDC on a table as well as on a Database.
