You can download this text in pdf document TomCdcGuide.pdf

Checkout the video presenting installation and configuration steps http://vimeo.com/tomcdc/intro or download it from TomCdcPresentation.wmv

What is TomCdc?

TomCdc is designed to capture insert, update and delete activity applied to SQL Server tables along with rich context information like user name, host name, application name and activity date.
Provided Manager Application makes configuration process very easy, while Viewer Application facilitates viewing of all changes applied to tracked table records.

How TomCdc works?

To capture DML activity TomCdc uses triggers. Although this method is considered intrusive, it has certain advantages over build in feature Change Data Capture of Microsoft Sql Server.
There are three components:
  • Manager Application
  • Viewer Application
  • Cdc Agent Service
Apart from the database which will be tracked, two additional are needed. Staging database, this will reflect the source table structure and temporarily hold captured transactions. Storage database, this will permanently store captured transactions in a flexible structure which easily accommodates schema changes of the source tables. Only source fields of which content has changed are transferred to the storage, saving space significantly.
The Staging Database has to be created on the same SQL engine as tracked database, the trigger created on the source table dumps data into it.
Transformation Agent Service retrieve data from Staging Database identify what has changed, transform it and load to Storage Database. Agent is a windows service which can run on any machine having access to Staging and Storage database.

Notice: Figure 1 shows components involved in data flow, they are located on three different servers, but they can be installed on one machine.
sysview.gif


TomCdc Quick Install – Windows Authentication

1. Download installation package from http://tomcdc.codeplex.com
2. Run installer and select all components for installation.
setupS1.jpg

3. Create staging and storage databases for the tracking database. For example: NorthwindStage, NotrthwindStore
Note: Every user performing DML operations on the tracked database must be at least member of dbdatawriter role on the stage database._
4. Databases can be located on different servers, to provide data consistency Distributed Transaction Coordinator service has to be running on machine where Agent Service is installed and TomCdc Manager used.
msdtc.jpg


5. Run TomCdc Manager and connect to all databases accepting setup script installation requests.
Note: The only objects created on tracked database are function CdcIsUpdated and capturing trigger on tables selected for tracking.
Note: next steps describe configuration which use Windows Authentication to connect to sql server, to use SqlServer Authentication refer to “TomCdc Quick Install – SqlServer Authentication”
tomcdcmanagerS1.jpg

6. Configure CdcAgentService by going to menu “Tools” and clicking “Configure Local Agent Service...”.
tomcdcmanagerS2.jpg


7. In previous steps Windows Authentication was used to connect to databases. In this case Agent Service needs to be configured to run in the context of an account which is a member of:
  • db_datareader on tracked database
  • dbdatawriter, dbdatareader on staging database
  • dbdatawriter, dbdatareader on storage database
Next set a startup type to Automatic and start the service.
winservice.jpg

8. At this point configuration is completed. Run TomCdc Manager to configure tables and columns for changes tracking.

TomCdc Quick Install – SqlServer Authentication

1. Download installation package from http://tomcdc.codeplex.com
2. Run installer and select all components for installation.
setupS1.jpg

3. Create staging and storage databases for the tracking database. For example: NorthwindStage, NotrthwindStore
Note: Every user performing DML operations on the tracked database must be at least member of dbdatawriter role on the stage database._
4. Databases can be located on different servers, to provide data consistency Distributed Transaction Coordinator service has to be running on machine where Agent Service is installed and TomCdc Manager used.
msdtc.jpg


5. Run TomCdc Manager and connect to all databases accepting setup script installation requests. Use SqlServer Authentication, thanks to it there will be no need to run Agent Service in the context of a user with rights to connect to selected databases. Login used for authentication must be a member of:
  • db_datareader on tracked database
  • dbdatawriter, dbdatareader on staging database
  • dbdatawriter, dbdatareader on storage database

Note: The only objects created on tracked database are function CdcIsUpdated and capturing trigger on tables selected for tracking.
Note: next steps describe configuration which use SqlServer Authentication to connect to sql server, to use Windows Authentication refer to “TomCdc Quick Install – Windows Authentication”
tomcdcmanagerS1B.jpg

6. Configure CdcAgentService by going to menu “Tools” and clicking “Configure Local Agent Service...”.
tomcdcmanagerS2.jpg

7. Go to Windows Service manager and set Chang Data Capture Agent service startup type to Automatic. At this point configuration is completed. Run TomCdc Manager to configure tables and columns for changes tracking.

Last edited Oct 23, 2011 at 11:13 PM by TomaszChrzanowski, version 4

Comments

No comments yet.