Table of Contents
  • SELECT column1, column2, column 3

2.5 quintillion bytes of data are produced every day, and those numbers are continually increasing. With such astronomical volumes of data, businesses have to understand and interpret data faster than ever before.

However, data transfers must occur for businesses with millions of data entry points to properly store and interpret their data.

Whether the business decides to transfer data to data warehouses such as Amazon Redshift or Snowflake, data transport is critical to any successful analytics strategy.

Before your business can securely transfer data, there are many security measures and safeguards you must take.

Today, we will break down why you need to utilize a Change Data Capture to mitigate the risk of data loss and vulnerabilities and turn it on for your SQL system properly.

  1. What Is Change Data Capture (CDC)?
  2. Why Should You Use CDC?
  3. How Do You Turn On CDC?
  4. The Power of ETLs With CDCs
  5. Combining Integrate.io and CDC

What Is Change Data Capture (CDC)?

Change Data Capture (CDC) has emerged as the best method for real-time data movement from relational databases (such as SQL Server or Oracle) to data warehouses, data lakes, and other databases.

CDC allows you to capture changes made in the source database and write them directly into an offline storage location known as a Change Data Capture target.

You will be able to see what changed within your tables at different points of time using historical snapshots taken by CDC when querying against Change_Table in future queries.

Why Should You Use CDC?

Using CDC eliminates the need for costly, time-consuming data transfer fixes and decreases the resources required for an ETL process.

It is a perfect fit for real-time reporting and analytics, as well as being able to conduct large-volume queries on your source database without affecting other users performing read-only operations against it.

Last but not least, CDC ensures that you have all your data in one place so there won't be any errors caused by replication within BI systems.

How Do You Turn On CDC?

The CDC schema, CDC user, metadata tables, and other system objects are generated for a database when enabled for Change Data Capture.

The Change Data Capture metadata tables are included within the CDC schema, including associated system functions for querying for change data after you enable source tables.

Change Data Capture enabled at the database, table, and column levels in the same order, altered data from a SQL Server source can be captured. You will have to enable the Change Data Capture separately whether you are using tables or columns and at the database or table level.

For the SQL commands below, we use the following statements: SELECT, USE, GO EXEC, TYPE & FROM.

The syntax and use case scenario for each are as follows:

You would use SELECT to select data from a database

  • USE DatabaseName;

Used to select any database in the SQL schema

  • GO number

Used to group SQL commands

  • EXEC <stored process name> WITH <execute_option>

Used to execute stored procedures

Here are the steps to ensuring your CDC is enabled correctly:

1. Checking to See If Your CDC Is Already Enabled

Before you go through enabling your CDC, start by identifying whether CDC is already enabled.

The way you do that is by running the following query on any database:

SELECT name, is_cdc_enabled FROM sys.databases

2. Enable CDC at the Base Level

Once you have identified whether CDC is already enabled for the database, move to enable CDC at the base level. To do this, run a query using the following:

USE [Database_Name]

GO

EXEC sys.sp_cdc_enable_db

3. Checking If CDC Is Enabled at the Table Level

Next, you want to check if CDC has been enabled at the table level.

The query search to do so is:

USE [Database_Name]

GO

SELECT name, TYPE, type_desc, is_tracked_by_cdc FROM sys.tables

4. Enabling CDC at the Table Level

Run the following script to enable CDC on Table_Name table:

USE [Database_Name]

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'Schema Name', 

@source_name = N'Table_Name',

@role_name = null,

@captured_column_list = N'Column1, Column2' [Optional Parameter],

@capture_instance = N'Specified name' [Optional Parameter],

@supports_net_changes = 1

5. Enabling CDC on the Partition Table

Run the following script to enable CDC on Partition_Table_Name partition table:

USE [Database_Name]

GO

EXEC sys.sp_cdc_enable_table

@source_schema = 'dbo', 

@source_name = 'Partition_Table_Name',

@role_name = null,

@captured_column_list = N'Column1, Column2' [Optional Parameter],

@capture_instance = N'Specified name' [Optional Parameter],

@supports_net_changes = 1,

@allow_partition_switch = 0

After CDC has been enabled for a SQL Server source database, it is time to capture changes made in your tables by enabling CDC on specific tables within them. This can be done through command-line tools or another ETL tools that you may have installed on your system.

The Power of ETL With CDC

With ETL tools, you can achieve continuous availability of your database while still meeting reporting and analytics needs and performing large-volume queries on the source without impacting other users.

ETLs work by pulling in historical snapshots taken by CDC when querying against Change_Table in future queries and then feeding them into another table where they will either overwrite old information or append new information depending upon how it has been configured. This allows you to create parity between different databases at all times.

During the transformation stage, data is processed and converted into the desired format for the target destination. Legacy ETL has a lengthy transformation process, but modern ETL platforms such as Integrate.io replace disk-based processing with in-memory processing to enable real-time data analysis, enrichment, and transfer. 

Combining Integrate.io and CDC

When data transferring occurs, Change Data Capture and ETL tools make for a robust and secure way to ensure information is being transferred without errors. CDC-enabled applications can also use CDC functionality in SQL Server by using Change Data Capture APIs directly for transferring changes from a source database to another destination.

Through the powerful combination of CDC and Integrate.io, you will be able to transfer and analyze data free of inaccuracies and misrepresentation like never before.

If you are ready to maximize your business performance, schedule a call to begin your 7-day pilot with Integrate.io today.