Setting up a SQL Server with Change Data Capture (CDC)

Setting up a SQL Server with Change Data Capture (CDC)

Moving towards an event driven environment

Context

While more corporations are seeing the benefits of adapting to an event-driven architecture, it’s time to take a dive into the Change Data Capture (CDC) functionality and all its merits.

CDC is a database service that enables the capturing of change events (INSERT, UPDATE, DELETE) in your database. Subsequently, these events can be provided to a messaging layer so other applications in your environment can act on them. E.g. when a user changes their order, the change gets sent to a message broker which in turn triggers other services to handle the event. An example tool that provides this functionality is Debezium. The integration pattern itself is called ‘Data Liberation pattern’ and commonly used to event-enable existing applications. More info in this blog.

But first, you must correctly configure CDC in your database. In this blog, I’ll be discussing how to set up a SQL Server database in AWS to support the CDC feature.

Create your SQL Server database on AWS

To enable CDC on SQL Server, you require either a Standard Edition, Developer Edition or an Enterprise Edition. If you already have an existing SQL Server database that meets this requirement you can skip this section and go to the next section.

In AWS Management Console, go to Services and look for the RDS feature.
Create a new database instance with the following options:

  • Engine type: Microsoft SQL Server
  • Edition: SQL Server Standard Edition (Web & Express don’t support CDC)
  • Templates: Dev/Test
  • DB instance size: you can select this based upon your database needs, for this tutorial I went with the lowest class available
  • Credential settings: choose a secure Master password (can also be autogenerated: will be given to you upon database creation)
  • Connectivity -> Additional connectivity configuration -> Public access: Yes (Add an external route to the database so you can connect to it later with SQL Server Management Studio)

Other settings can be left on default, but feel free to look around.
When you are happy with the configuration click on Create database. This could take a few minutes to complete.

Connecting to your SQL Server database instance

Once your setup has been completed you will be able to connect to it via the values shown on your AWS console.

Open SQL Server Management Studio and connect to your instance with the following options:

  • Server Type: Database Engine
  • Server name: Copy and paste the value under Endpoint from your database console
  • Authentication: SQL Server Authentication with Login: admin and your Password either the password of your choosing or the autogenerated password (which you should have received a pop-up for in your console).

Proceed to Connect to the instance.

Configure CDC on SQL Server Standard Edition

Prerequisites

  1. As stated previously, you need either a Standard, Developer or Enterprise Edition to enable CDC.
  2. You also need sysadmin rights to enable CDC. AWS RDS provides an admin account that already has this privilege.
  3. A SQL Server Agent needs to be running on the SQL Server instance.

Below are the steps to implement this SQL Server feature, making use of the SQL Server Management Studio.

Create a database

First and foremost, you need to have a database:

Enable CDC on database level

CDC works on specific tables which we will whitelist later, but before we can do this we have to enable the service for the entire database:

Enable CDC on a specific table

First create a table:

Now enable CDC on that specific table:

Check if CDC is properly enabled on it:

You should see an output like this:

Insert data into the table and verify changes are being captured:

Which should give something like the following output:

More operations to verify CDC is working properly:

Output:

Operation code 1 means delete, 2 is insert, 3 is the value before the change and 4 is the update value.

Congratulations! You have successfully enabled CDC for your database! A next step could be to implement a tool such as Debezium to process these database changes and you are on your way to having a more event driven environment.

Author: Piet Jacobs

Samuel Vandecasteele

Samuel Vandecasteele

Curious to know more about this topic?

Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015

Also worth reading

AWS AppFlow: Streamlining SaaS Integrations with AWS Services

In today’s digital world, organizations are constantly looking for ways to streamline their workflows and improve their data management processes. One of the key challenges that organizations face is integrating their various software as a service (SaaS) applications with their data management systems. This is

Read More »

Apigee Scope Validation using OpenAPI Specification

In API security and management, we often use a lot of different security mechanisms to protect the requested resource behind the API Gateway. One of these mechanisms is the validation of scopes to authorize a client on a specific sub-resource of the API. Most of

Read More »