An Overview of CDC Replication in SQL Server
SQL Server Replication is a technology that is currently used to copy and share data and databases from one source to another. The advantage here is that once the copying and sharing process is completed the technology automatically synchronizes between the databases to make sure that the consistency and integrity of the data are maintained. This synchronization can be tuned to run continually or at pre-set intervals whenever any activity takes place regardless of whether it is bi-directional, one-way, or one-to-many. Before the advent of SQL Server Replication, most applications worked on a standalone environment. A single server responded to multiple users working from various locations leading to a host of performance, availability, and maintenance problems, issues that were later ironed out with this system.
Change Data Capture (CDC) is a technology that detects and records changes that have been made to a database and replicates them to other applications and databases. Integrating SQL server replication and CDC replication, SQL server CDC replication is a technology in-built in the SQL Server that records, inserts, updates, and deletes activities applied to a user table. It then stores this data in a form that can be used by an ETL application such as SQL Server Integration Services (SSIS). Change Data Capture when applied to SQL Server tables gives an account of what changed, when, and where in simple relational “change tables”. These change tables have columns that imitate the column structure of the source table chosen along with the metadata to understand the changes that are made. The native change data capture feature offered by Microsoft is not the only CDC in SQL Server environments available today. Currently, there are several solutions offered by third-parties for both SQL database replication and CDC that provide the same Microsoft’s in-built SQL server CDC replication functionality. Businesses often use applications where it is required that the value of data in the database be recorded before it is changed that is, the history of all changes must be saved. The 2005 version of SQL Server almost found a solution by introducing “after update”, “after insert”, and “after delete” triggers. An improvement was made in SQL Server 2008 through Change Data Capture (CDC) that enabled SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming. SQL server CDC replication tracks only the changes made in user-created tables. Here are some tips to optimize CDC in SQL Server replication. Make sure that you have already enabled SQL Server Agent before allowing Change Data Capture at the table level. Next, change settings so that the history data from the auxiliary change table is removed. If not the history data might grow in size to become unmanageable, thereby decreasing the overall performance of the replication script. All these issues can be avoided though. Compulsorily use the primary keys in the tables only.