Moving towards an event driven environment
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
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.
AWS Management Console, go to
Services and look for the
Create a new database instance with the following options:
Microsoft SQL Server
SQL Server Standard Edition(Web & Express don’t support CDC)
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)
Additional connectivity configuration->
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 name: Copy and paste the value under
Endpointfrom your database console
SQL Server Authenticationwith
Passwordeither the password of your choosing or the autogenerated password (which you should have received a pop-up for in your console).
Connect to the instance.
Configure CDC on SQL Server Standard Edition
- 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:
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:
exec msdb.dbo.rds_cdc_enable_db mydb
Enable CDC on a specific table
First create a table:
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:
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:
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:
-- 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:
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
Operation code 1 means
delete, 2 is
insert, 3 is the value before the change and 4 is the
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.