Understanding CDC and Implementation in SSIS (Part – II)

Introduction

In my previous article we are discussing about CDC implementation of Microsoft SQL Server. If you are not aware about it please visit it before continue this article as it is mandatory to understand my previous article for that.

The link is given bellow:

http://www.sqlknowledgebank.blogspot.in/2015/11/understanding-cdc-and-implementation-in.html

In this article we are going to discuss about the Microsoft SQL Server 2012 SSIS implementation of CDC. Hope it will be informative and you enjoy the session

Case Scenario

First we must understand what we want to do in SSIS for CDC. Here the case scenario is provided.

We have a table object named tbl_EmployeeDetails like this

EmpID (PK)	EmpName	Designation	DOJ
1	Joydeep Das	A	2015-02-01
2	Deepasree Das	B	2013-05-01
3	Santi Nath Mandal	A	2012-03-01

Now we want to incremental load of data from Table tbl_EmployeeDetails to Table tbl_Employee with same columns name.

Here the term Incremental Load is very important. That’s means

1.    First time it loads all the data from Table tbl_EmployeeDetails to table tbl_Employee.

2.    Second time it loads only those data that are newly inserted or Modified.

So, CDC play a very important role incases of Incremental load to understand which data is newly added and which data is updated. Hope from my previous article you can understand the scenario well.

How to Implement in SSIS

Here we are going to make two packages for that.

Step – 1 [ The CDC Control Task – Mark Initial Load Start ]

Drag and drop the CDC control task into Control Flow tab and configure it.

Here we find

·         Add a new ADO.NET connection manager for the Source database

·         Set CDC Control Operation to Mark initial load start

·         Create a new package variable (CDC_State) to hold the CDC state information.

·         Set the connection manager for the Destination database

·         Create a table for storing the state ([cdc_states]). This table will be used to track the CDC load information, so that you only pick up new changes each time the incremental load package is run. It will be created in the Destination database.

·         Set the state name (CDC_State). This value acts as a key for the CDC state information. Packages that are accessing the same CDC data should be using a common CDC state name.

CDC Control Operation

Mark initial load start

This operation is used when executing an initial load from an active database without a snapshot. It is invoked at the beginning of an initial-load package to record the current LSN in the source database before the initial-load package starts reading the source tables. A walkthrough of how this process works can be found in my CDC in SSIS for SQL Server 2012 post.

Mark CDC start

This operation is used when then the initial load is made from a snapshot database database or from a quiescence database. It is invoked at any point within the initial load package. The operation accepts a parameter that can be a snapshot LSN, a name of a snapshot database (from which the snapshot LSN will be derived automatically) or it can be left empty, in which case the current database LSN is used as the start LSN for the change processing package. This operation is used as an alternative to the Mark Initial Load Start/End operations.

Get processing range

This operation is used in a change processing package before invoking the data flow that uses the CDC Source data flow. It establishes a range of LSNs that the CDC Source data flow reads when invoked. The range is stored in an SSIS package variable (StateVariable property) that is used by the CDC Source during data flow processing.

Mark processed range

This operation is used in a change processing package at the end of a CDC run (after the CDC data flow is completed successfully) to record the last LSN that was fully processed in the CDC run. The next time Get processing range is used, this position determines the start of the next processing range.

Reset CDC state

This operation is used to reset the persistent CDC state associated with the current CDC context. After this operation is run, the current maximum LSN from the LSN-timestamp sys.fn_cdc_get_max_lsn table becomes the start of the range for the next processing range. An example of when this operation is used is when you want to process only the newly created change records and ignore all old change records.

Step – 2 [ The CDC Control Task – Mark Initial Load End ]

Same as The CDC Control Task – Mark Initial Load Start but only one configuration that we need to change is Set CDC Control Operation to Mark initial load End

Step -3 [ Package Details Flow ]

In-between two CDC Control Task, we use the data flow task and configure it.

Control flow Tab of the package

Data flow Tab of package

Now check the CDC_states table object

SELECT * FROM [dbo].[cdc_states];

name	state
CDC_State	ILEND/IR/0x000000CA000001200002…

Step – 4 [ Incremental Load ]

Configuration Changes in CDC Control Task

The only configuration that we need to change in both the CDC Control Task is CDC Control Operation is Get Processing Range and Mark Processed Range

Data Flow Tab Details

Configuration of CDC Source

Configuration of CDC Splitter

NO need of any configuration.

We assume that the other configuration like OLED Command you now that and no need to describe.

Hope you like it.

Posted by: MR. JOYDEEP DAS

Understanding CDC and Implementation in SSIS (PART-I)

Introduction

Change Data Capture (CDC) is introduced by Microsoft from SQL Server 2008. It is most popular in case of Incremental load. We are going to study a scenario and understand why the CDC is so important. The CDC is little complicated in MS SQL Server 2008 but Microsoft improve the functionality and made it easier for us from MS SQL 2012. In this article we are going to discuss CDC related to MS SQL 2012.

The article based on two things

1.    How CDC works on MS SQL Server 2012

2.    Implementation of MS SQL Server 2012 CDC into SSIS

Hope it will be informative and you enjoy the session.

Why CDC is needed a Scenario

We have a table named tbl_EmployeeDetails and it contains EmpID, EmpName, DesigNation and DOJ. The data is loaded from tbl_EmployeeDetails to Production server table named tbl_Employee.

Suppose we have 50 thousand records in the tbl_EmployeeDetails and data of that table is frequently changed by Insert/Update and Delete. So it is not possible to delete the destination table records and load it every time due to performance factors.

So we have to understand which records are Inserted/Updated/Deleted and make the operation on this sets only. Here in this source table tbl_EmployeeDetails we are unable to understand it and hence the CDC comes into the picture.

Before MS SQL 2008 we used alternate approaches that include timestamp columns, triggers, or complex queries often hurt performance and increase complexity.

How CDC Works

To understand it properly here we provide a pictorial diagram.

When any Insert/Update/Delete occurs in the database table it logged into the log file. The Scheduler reads the log file and stores the data into CDC table. This is done by LSN (Log Sequence Number). From this CDC table we can track the changes of the records in the table.

Implementation of CDC in MS SQL Server 2012

Before Working with CDC the SQL Server Agent Services must be STARTED

Step-1 [  Enabling Change Data Capture (CDC) in Database ]

USE TEST

GO

EXEC sys.sp_cdc_enable_db;

GO

To check the CDC is properly activated on the database

SELECT [name], database_id, is_cdc_enabled 

FROM   sys.databases WHERE name = 'TEST';     

GO

name	database_id	is_cdc_enabled
TEST	7	1

Here is_cdc_enabled = 1 means the CDC is enabled in the database named TEST.

                                               

After this execution of the command we find some table objects is created automatically as a system table in the database and we have to understand those table clearly.

                                                           

cdc.captured_columns 

This table returns result for list of captured column.

cdc.change_tables 

This table returns list of all the tables which are enabled for capture.

cdc.ddl_history 

This table contains history of all the DDL changes since capture data enabled.

cdc.index_columns 

This table contains indexes associated with change table.

cdc.lsn_time_mapping 

This table maps LSN number (for which we will learn later) and time.

Step-2 [ Create the Source Table ]

IF OBJECT_ID(N'[dbo].[tbl_EmployeeDetails]', N'U') IS NOT NULL

   BEGIN

       DROP TABLE [dbo].[tbl_EmployeeDetails];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeDetails]

(

EmpID        INT           NOT NULL IDENTITY PRIMARY KEY CLUSTERED,

EmpName      VARCHAR(50)   NOT NULL,

DesigNation  CHAR(1)       NOT NULL,

DOJ          DATETIME NOT NULL

);

Step-3  [Enabling Change Data Capture (CDC) in One or More Table ]

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'tbl_EmployeeDetails',

@role_name     = NULL

GO

@source_schema is the table schema where we want to enable the CDC. In our case it is [dbo].

@source_name is the name of the table objects where we want to enable CDC.

@role_name   If there is any restriction of how data should be extracted from database, this option is used to specify any role which is following restrictions and gating access to data to this option if there is one.  If you do not specify any role and, instead, pass a NULL value, data access to this changed table will not be tracked and will be available to access by everybody.

This will create two jobs in the SQL Server Agent.

Job 'cdc.TEST_capture' started successfully.

Job 'cdc.TEST_cleanup' started successfully.

TEST_Capture

These jobs execute a system stored procedure named sys.sp_MScdc_capture_job. This procedure cannot be executed explicitly when a change data capture log scan operation is already active or when the database is enabled for transactional replication. This system SP enables SQL Server Agent, which in facts enable Change Data Capture feature.

TEST_Cleanup 

When this job is executed it runs the system stored procedure sys.sp_MScdc_cleanup_job. This system SP cleans up database changes tables.

After this operation there is another table is automatically created with name of the table that we created earlier (tbl_EmployeeDeatails). The name of the mirror table is [cdc].[dbo_tbl_EmployeeDetails_CT]

The [cdc].[dbo_tbl_EmployeeDetails_CT] table will contains all the changes (Insert/Update/Delete) in the table named [dbo].[ tbl_EmployeeDeatails].

Step – 4 [ Observing the cdc].[dbo_tbl_EmployeeDetails_CT] Table ]

SELECT * FROM [cdc].[dbo_tbl_EmployeeDetails_CT];

Here we got Five new columns and others are the columns from original table

Step- 5 [ Now Make some Operation on Source table and See the CDC changes ]

Insert Records

INSERT INTO [dbo].[tbl_EmployeeDetails]

     (EmpName, DesigNation, DOJ)

VALUES('Joydeep Das', 'A', '2012-09-08'),

      ('Deepasree Das', 'B', '2013-05-01');

GO

SELECT * FROM [dbo].[tbl_EmployeeDetails];

SELECT * FROM [cdc].[dbo_tbl_EmployeeDetails_CT];

EmpID	EmpName	DesigNation	DOJ
1	Joydeep Das	A	2012-09-08 00:00:00.000
2	Deepasree Das	B	2013-05-01 00:00:00.000

__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	EmpID
0x00000086000000A80020	NULL	0x00000086000000A8001D	2	0x0F	1
0x00000086000000A80020	NULL	0x00000086000000A8001F	2	0x0F	2

EmpName	DesigNation	DOJ
Joydeep Das	A	2012-09-08 00:00:00.000
Deepasree Das	B	2013-05-01 00:00:00.000

Here _$operation = 2 means data is Inserted. Look at the _$start_lsn = 0x00000086000000A80020 means both the records in same transaction.

Update Records

UPDATE [dbo].[tbl_EmployeeDetails]

    SET EmpName = 'Shipra Das'

WHERE EmpID = 1;

SELECT * FROM [dbo].[tbl_EmployeeDetails];

SELECT * FROM [cdc].[dbo_tbl_EmployeeDetails_CT];

EmpID	EmpName	DesigNation	DOJ
1	Shipra Das	A	2012-09-08 00:00:00.000
2	Deepasree Das	B	2013-05-01 00:00:00.000

                                   

__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	EmpID
0x00000086000000A80020	NULL	0x00000086000000A8001D	2	0x0F	1
0x00000086000000A80020	NULL	0x00000086000000A8001F	2	0x0F	2
0x00000088000001980003	NULL	0x00000088000001980002	3	0x02	1
0x00000088000001980003	NULL	0x00000088000001980002	4	0x02	2

EmpName	DesigNation	DOJ
Joydeep Das	A	2012-09-08 00:00:00.000
Deepasree Das	B	2013-05-01 00:00:00.000
Joydeep Das	A	2012-09-08 00:00:00.000
Shipra Das	A	2012-09-08 00:00:00.000

After Update operation there 2 new records inserted _$start_lsn = 0x00000088000001980003 which have _$operation = 3 which is Value Before Update And _$operation = 4 which is Value After Update

Delete Records

DELETE [dbo].[tbl_EmployeeDetails]

WHERE  EmpID = 1;

                                                           

SELECT * FROM [dbo].[tbl_EmployeeDetails];

SELECT * FROM [cdc].[dbo_tbl_EmployeeDetails_CT];

EmpID	EmpName	DesigNation	DOJ
2	Deepasree Das	B	2013-05-01 00:00:00.000

__$start_lsn	__$end_lsn	__$seqval	__$operation	__$update_mask	EmpID
0x00000086000000A80020	NULL	0x00000086000000A8001D	2	0x0F	1
0x00000086000000A80020	NULL	0x00000086000000A8001F	2	0x0F	2
0x00000088000001980003	NULL	0x00000088000001980002	3	0x02	1
0x00000088000001980003	NULL	0x00000088000001980002	4	0x02	2
0x00000097000001680005	NULL	0x00000097000001680002	1	0x0F	1

EmpName	DesigNation	DOJ
Joydeep Das	A	2012-09-08 00:00:00.000
Deepasree Das	B	2013-05-01 00:00:00.000
Joydeep Das	A	2012-09-08 00:00:00.000
Shipra Das	A	2012-09-08 00:00:00.000
Shipra Das	A	2012-09-08 00:00:00.000

For delete operation a new row is added where _$start_lns = 0x00000097000001680005 and _$operation = 1 which means Deleted Records.

In my next article I am going demonstrate the using of CDC in SSIS component.

Hope you like it.

Posted by: MR. JOYDEEP DAS