01 Dec 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 ServerEdition:SQL Server Standard Edition(Web & Express don’t support CDC)Templates:Dev/TestDB instance size: you can select this based upon your database needs, for this tutorial I went with the lowest class availableCredential settings: choose a secureMaster 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 EngineServer name: Copy and paste the value underEndpointfrom your database consoleAuthentication:SQL Server AuthenticationwithLogin:adminand yourPasswordeither 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
- As stated previously, you need either a Standard, Developer or Enterprise Edition to enable CDC.
- You also need
sysadminrights to enable CDC. AWS RDS provides an admin account that already has this privilege. - 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:
|
1 |
CREATE DATABASE mydb |
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:
|
1 |
exec msdb.dbo.rds_cdc_enable_db mydb |
Enable CDC on a specific table
First create a table:
|
1 2 3 4 5 6 |
CREATE TABLE tasks ( task_id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, status TINYINT NOT NULL, priority TINYINT NOT NULL, ) |
Now enable CDC on that specific table:
|
1 2 3 4 5 6 |
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'tasks', @role_name = NULL, @supports_net_changes = 1 GO |
Check if CDC is properly enabled on it:
|
1 2 3 4 |
EXECUTE sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'tasks'; GO |
You should see an output like this:
Insert data into the table and verify changes are being captured:
|
1 2 3 4 5 6 7 8 |
-- Insert data into the table INSERT INTO tasks Values (1, 'Brush teeth', 1, 8) INSERT INTO tasks Values (2, 'Do homework', 0, 9) INSERT INTO tasks Values (3, 'Dance!', 0, 6) -- Query the results of the captured changes SELECT * FROM [cdc].[dbo_tasks_CT] GO |
Which should give something like the following output:
More operations to verify CDC is working properly:
|
1 2 3 |
DELETE FROM tasks WHERE task_id = 1 UPDATE tasks SET title = 'Do the dishes' WHERE task_id = 2 DELETE FROM tasks WHERE task_id = 3 |
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 
