Stream Data Changes from a CDC-Enabled Azure SQL Database to an External Target Using Striim

by | Jul 21, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

This blog is part of the Change Data Capture in Azure SQL Databases Blog Series, which started with the announcement on releasing CDC in Azure SQL Databases in early June 2021. You can view the release announcement here: https://aka.ms/CDCAzureSQLDB


 


Introducing Change Data Capture in Azure SQL Databases


Change data capture (CDC) provides historical change information for a user table by capturing both the fact that Data Manipulation Language (DML) changes (insert / update / delete) were made and the changed data. Changes are captured in real time by using a capture process that reads changes from the transaction log and places them in corresponding change tables. These change tables provide a historical view of the changes made over time to source tables. CDC functions enable the change data to be consumed easily and systematically.


 


CDC is now available in public preview in Azure SQL, enabling customers to track data changes on their Azure SQL Database tables in near real-time. Now in public preview, CDC in PaaS offers similar functionality to SQL Server and Azure SQL Managed Instance CDC, providing a scheduler which automatically runs change capture and cleanup processes on the change tables.


 


Streaming Change Data to External Targets


Data integration platforms such as Striim can integrate with your CDC-enabled Azure SQL Database to stream data changes to diverse targets in real-time. 


 


“Real-time information is vital to the health of enterprises,” says Codin Pora, VP of Technology and Partnership at Striim. “Striim is excited to support the new change data capture (CDC) capabilities of Azure SQL Database and help companies drive their digital transformation by bringing together data, people, and processes. Striim, through its Azure SQL Database CDC pipelines, provides real-time data for analytics and intelligence workloads, operational reporting, ML/AI implementations and many other use cases, creating value as well as competitive advantage in a digital-first world. Striim builds continuous streaming data pipelines with minimal overhead on the source Azure SQL Database systems, while moving database operations (inserts, updates, and deletes) in real time with security, reliability, and transactional integrity.”


 


To learn more about using Striim for real-time ETL to Azure SQL Databases, go here. You can also try out setting up an ETL pipeline to your chosen Azure SQL Database by using Striim’s free trial.


 


Current Use Case


For this tutorial, we will use Striim to send CDC change data from an Azure SQL Database to another Azure SQL Database target in a separate region. The source database is enabled for CDC. Apart from that, each table that is tracked for data changes is enabled for CDC. To learn more about enabling and disabling CDC  on databases and tables, go here.


 


Striim will connect to the source database and will push CDC changes from the change tables to the downstream target. This can be helpful for customer scenarios such as global data synchronization (i.e.  keep databases in different regions around the world synchronized) or distributed applications (i.e.  synchronize data across databases that store diverse workloads). 


 


Steps for Sending CDC Data Changes from an Azure SQL Database with Striim



  1. Create, purchase and deploy your solution by following these steps: Striim with Azure Storage or  SQL Database

  2. In the Striim web GUI, go to the Apps section. 

  3. Click on the Add App button to start a new app. Given our scenario, we will start a new app from scratch by clicking on the Start From Scratch button. Depending on your use case, you might need one app to run an initial snapshot of your source database and one separate app to replicate incremental changes using CDC. For this scenario, you will get zero downtime migration. However, you might decide to execute your initial load outside  of Striim by using backup and restore tools. For the purposes of this demo, we will have two apps – one for running an initial load (SQLDBInitLoadTest app) and one for replicating incremental changes from source to target  database, for which CDC needs to be enabled on the source database (SQLDBCDCTest app).

  4. We will start with the SQLDBInitLoadTest app configuration. In the Name your App section, give  your app a name and a namespace (namespaces are logical groupings of applications). Click Save.  5. From the drag-and-drop Striim web UI, select your source, which in our case will be  SQLDbInitLoad_source DatabaseReader. Learn more about Database Readers here. Configure the  Adapter, Connection URL (JDBC), Username, Password, and the Output, which can be either new  or existing. You can select the Tables to read from as well. In our case, we will send the initial load  to the SQLDbInitLoad_stream, which will send it down to target.

  5. When configuring the target, in our case SQLDbInitLoad_target, edit the Adapter  (DatabaseWriter), Connection URL (JDBC), Username, Password, Tables (comma-separated pairs  of source-target tables). 

  6. Once you have configured the source, stream, and target, Deploy the app and Start the app.  The initial snapshot of the source database should show up in the target database. In case there  are errors starting the app, you can use the Message Log for debugging, then Undeploy the app  and Resume again once the errors have been fixed. In case of networking errors, make sure that  your Client IP address is allowed to access the database server; you can enable access within the  Azure Portal (Update Server Firewall Rules). 

  7. As your application is running, you can monitor the progress for the replication, as seen in the  screenshot below. Once the initial load is completed, you should check your target database and  see that it’s in sync with the source.

  8. Now that the initial load is complete, we will configure the app for replicating incremental changes  from source to target. For this step, CDC must be enabled on the source database and tracked  tables. To learn more about enabling and disabling CDC on databases and tables, go here.

  9. Similar to configuring your source/stream/target on the SQLDbInitLoadTest app, now go to the  SQLDBCDCTest app and configure your source (SQLDBCDC_source), stream (SQLDBCDC_stream),  and target (SQLDBCDC_target).

  10. Deploy and Start app. Your incremental data changes should be replicating to the target. 


One of the benefits of Striim is that it supports in-flight transformations and processing as the data flows through its in-memory data pipelines for filtering, aggregating, enrichment, and alerting in real time. Many transformations are available out of the box as a drag-and-drop item from the Striim Flow Designer for a variety of popular operations, Striim  Continuous Query (CQ) functionality allows  users to write their own custom SQL code to run and act on their streaming data as it flows through the pipeline.


 


Blog Series for Change Data Capture in Azure SQL Databases


We are happy to continue the bi-weekly blog series for customers who’d like to learn more about enabling CDC in their Azure SQL Databases! This series explores different features/services that can be integrated with CDC to enhance change data functionality.


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment