Setting Up Debezium SQL Server Integration: 3 Easy Steps

Modern applications continuously make changes in the Databases, store and retrieve information. Companies want to feed their applications with up-to-date data. Slow data processing delays business decisions. Organizations streamline various business activities by monitoring the changes in the Database and performing different tasks based on the change in data. 

Debezium SQL Server Integration enables users to develop applications dependent on changes in the Database such as insert, update, and delete events. In this article, you will learn the steps to set up Integration. You will also read about the benefits of using the Integration and how it helps companies make better business decisions in real time by getting up-to-date data into their applications.

Prerequisites 

• SQL Server installed on your local machine.
• Apache ZooKeeper, Apache Kafka, and Kafka Connect on your local machine.
• A brief knowledge of Kafka.

Introduction to Debezium

Debezium is an open source low latency data streaming and distributed platform for change data capture (CDC). It is used by organizations and Developers to monitor any data change in the Database and then generate triggers for every row-based data change. Debezoum uses Kafka Connect for managing CDC in the Databases and streaming it to the applications. 

Key Features of Debezium

Debezium helps companies immediately respond to changes in the Database with low latency. Some of the main features of Debezium are listed below.

• Masking: Debezium offers users an option to mask sensitive information such as card numbers, bank details, etc. To ensure data privacy while streaming data.
• Message Transformations: Debezium comes with many ready-to-use message transformations that allow users to handle message routing, filtering, event flattening, etc.
• Low Latency Streaming: Debezium makes it possible for companies to stay up to date with the data even in the low latency environment. It uses Kafka Connect to immediately respond to the change data capture.

Introduction to SQL Server

SQL Server is a fully developed relational DBMS by Microsoft. It can be used for handling and supporting many forms of data-driven applications, ranging in scope from small setups to enterprise-level large databases. SQL Server provides services to store data, retrieve it, and support advanced analytics. Known for robust performance and security features, it is highly extensible. The structured data present in SQL Server can be accessed and manipulated using the SQL language.

Key Features of SQL Server

Some of the key features of SQL Server are highlighted below:

• Advanced Analytics: It supports advanced analytics, including integration with SQL Server Analysis Services and SQL Server Reporting Services for data mining, reporting, and analysis.
• High Performance: It gains performance through in-memory processing, query optimisation, and indexing, ensuring rapid data retrieval and transaction processing.
• Robust Security: It has security features such as Transport Data Encryption (TDE), Row-level Security, and Dynamic Data Masking that make sure sensitive data is safe and compliant.

Use Cases of Debezium SQL Server Integration

• Change Data Capture (CDC) for Analytics: Suppose the e-commerce platform wants to track in real time user activities and purchase trends. By integrating Debezium with SQL Server, it can stream the transaction data changes continuously to a Big Data analytics platform, greatly enhancing business intelligence with real-time data and reporting.
• Real-Time Monitoring and Alerting: A financial services company needs to monitor all transactions against fraud in real-time. Using Debezium, all changes flowing from the SQL Server database into the monitoring system are streamed; all data is analysed in real-time, alerting in case of suspicious activities.
• Disaster Recovery: If your organisation wants to develop a disaster recovery plan, Debezium offers continuous data capture from a SQL Server’s primary database to its standby secondary database to guarantee minimal loss of data and prompt recovery in case of failure.

Setting Up Debezium SQL Server Integration

Now that you have understood about Debezium and SQL Server. In this section, you will learn about the steps to set up Debezium SQL Server Integration. The Integration uses connectors and before that, it is essential to enable the Change Data Capture feature in the SQL Server. The following steps for Debezium SQL Server Integration are listed below.

Step 1: Enabling CDC on the SQL Server

• To enable the CDC, you have to run a system stored procedure. It can be done using SQL Server Management Studio or using Transact-SQL.
• Make sure that SQL Server Agent is running.
• Here, SQL Server Management Studio will be used for Debezium SQL Server Integration.
• Open the SQL Server Management Studio, click on the “View” menu and select the “Template Explorer” option.
• Then expand the “SQL Server Templates” option by clicking on it.
• Here, from the “Change Data Capture” option select the “Configuration” option and click on the “Enable Database for CDC” option.
• Now in the template, replace the Database name that you want to enable for CDC, next to the USE command.
• The following example code is given below to enable CDC for Debezium SQL Server Integration.

USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO

• Now, let’s enable the CDC for a particular table in the SQL Server Database.
• For this the stored procedure “sys.sp_cdc_enable_table” will be used.
• Click on the “View” menu and select the “Template Explorer” option.
• Then expand the “SQL Server Templates” option by clicking on it.
• Here, from the “Change Data Capture” option select the “Configuration” option and click on the “Enable Table Specifying Filegroup Option“.
• Now in the template, replace the table name that you want to enable for CDC, next to the USE command.
• The following example code is given below to enable CDC in a table for Debezium SQL Server Integration.

USE MyDB
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable', 
@role_name = N'MyRole',  
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 0
GO

Step 2: Deploying the Debezium SQL Server Connector 

• For this make sure you have Apache ZooKeeper, Apache Kafka, and Kafka Connect installed.
• Download the Debezium SQL Server connector plug-in archive.
• Extract the files of the connector into your Kafka Connect environment.
• Now, add the directory with the JAR files into Kafka Connect’s plugin.path.
• Configure the Connector and add the configurations such as port, address.
• You need to configure the connector in a JSON file by setting the configuration properties.
• The sample of a JSON file for configurations of Debezium SQL Server Connector is given below.

{
    "name": "inventory-connector", 
    "config": {
        "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector", 
        "database.hostname": "192.168.99.100", 
        "database.port": "1433", 
        "database.user": "sa", 
        "database.password": "Password!", 
        "database.dbname": "testDB", 
        "database.server.name": "fullfillment", 
        "table.include.list": "dbo.customers", 
        "database.history.kafka.bootstrap.servers": "kafka:9092", 
        "database.history.kafka.topic": "dbhistory.fullfillment" 
    }
}

• In the above JSON file, the address and port of the SQL Server instance are “192.168.99.100” and “1433” respectively.
• The user of SQL Server instance is “sa”.
• Now, you can send the configuration of this Debezium SQL Server connector using the POST command to the running Kafka Connect services. It will record the configuration and start one connector task which will perform the following tasks listed below.
  • Connects to the SQL Server database.
  • Reads the transaction log.
  • Records change events to Kafka topics.

Step 3: Running the Debezium SQL Server Connector 

• To run the Debezium SQL Server connector, you need to create a connector configuration.
• For that, you have to use the Kafka Connect REST API so that you can add the connector configuration to the Kafka Cluster.
• It can be configured using the listener’s configuration option in Kafka Connect.
• The format for the list of listeners is given below.

protocol://host:port,protocol2://host2:port2

• Here, the protocols are “HTTPS” or “HTTP”.
• Once you start the Debezium SQL Server connector, it will start taking snapshots of the SQL Server Database. Then it will start generating data change events to stream the change event records to Kafka topics.

That’s it! You have completed the Debezium SQL Server Integration.

Benefits of Debezium SQL Server Integration

Some of the main features of Debezium SQL Server Integration are listed below.

• Integration allows users to push the data streams for every event data change in the SQL Server Database.
•  Debezium SQL Server Connector publishes many metrics about the connector’s activities that can be monitored through JMX. 
• Integration helps in moving the change stream data into a data lake to allow for disaster recovery or replaying part of the data.

Conclusion 

In this article, you learnt about Debezium, SQL Server, and the steps to set up Integration using the Debezium SQL Server connector. You also read about the benefits of using the connector and how it helps companies manage the change data capture events and trigger events.

See how Debezium integrates with MySQL for a powerful solution to manage and synchronize changes in MySQL databases.

Debezium helps organizations keep the data up to date in applications and other systems that can help them automatically trigger particular events.

Share your experience of learning about Integration in the comments section below!

FAQs about Debezium SQL Server Integration