A CDC Example

The idea of CDC is to capture the changes undergone by the data in a table in order to make use of those changes elsewhere. Very often this is to update a data warehouse with just the changes rather than completely refreshing it. This post is designed to lead you through a simple example so that you get a good feel of how to create and use CDC.

A word of warning before we start off - this is going to be a long one!

Create Your Environment

For this example we start by creating a new database and a new table.

1.     Create the database

create database NWM

2.     Make sure that the SQL Server Agent is running for the database instance

3.     Enable CDC for that new database

exec sys.sp_cdc_enable_db

4.     Check results

select name, is_cdc_enabled from sys.databases

5.     You will get a list of databases and an is_cdc_enabled

      flag. In the example shown the NWM database is enabled.

6.     Next, create a table to set up for CDC. The table must have a primary key. For convenience here we make it an identity field.

    create table nwm_cdc

( id int identity(1,1),

  field_1 varchar(32),

  field_2 varchar(32),

constraint [PK_nwm_cdc] primary key clustered ([id] asc) on [PRIMARY]

 )

7.     Add some data to the table:

    insert into nwm_cdc

    select 'one', 'ein'

8.     Check the list of CDC-enabled tables in the database. You will find that you are returned an empty dataset.

    select * from cdc.change_tables

Set up CDC for your table

In this section you prepare the data table where changes will cause CDC activity.

1.     Set up CDC for the table by running this system procedure:

exec sys.sp_cdc_enable_table  

      @source_schema = N'dbo',             --schema of table with data
      @source_name = N'nwm_cdc',           --name of table with data
      @role_name = null,                   --role of table owner (optional)
      @captured_column_list = N'id, Field_2',--Fields to capture when changed
      @capture_instance = null,            --Name of the CDC “capture instance”
      @filegroup_name = N'PRIMARY',         --filegroup to keep output in
      @supports_net_changes = 1            --1 = merge changes on the same field

2.     Re-run step 8 above. You will now get what you see below. Notice that many of the values are taken from the command in step 1 just above. 

           a.     source_object_id is the id of the table we referenced in step 8, as found in

the sys.databases view.

                                b.     object_id is the id of the capture_instance that we have just created
                                c.     schema_id is the id of the schema of the table we referenced in step 8, as found in
                                                                             the sys.schemas view.
                                d.     capture_instance is the name of the CDC setup that we have just created.
                                                                              There can be two per data table, and each must have a unique
                                                                               name – the default is the table’s own name with _cdc added.

       3.     Now look at the System Tables branch under your database
               in the SSMS Object Explorer: 

               You will see that there are a number of tables with a schema
               cdc. The one that you have just made by performing step 1
               just above is cdc.dbo_nwm_cdc_CT, and this will collect the
               data generated by the CDC system whenever the table is 
               changed.


       4.     Now go further down in the Object Explorer and open up the SQL Server Agent tree.
              It should look like this (right). Two new jobs have been created in order to hold the code necessary
              for processing the CDC operations.

Note     If you wish to disable CDC on a table then you use  sys.sp_cdc_disable_table, providing it the @source_schema, @source_name, and @capture_instance values to identify the Capture Instance.