Track changes in SQL Server on Google Cloud using Change Data Capture

Knowing the exact changes made to a SQL Server database on Google Cloud is now possible with the release of support for Change Data Capture (CDC) in Cloud SQL for SQL Server. The following SQL Server versions now support CDC in Cloud SQL:

• SQL Server 2017 Standard
• SQL Server 2017 Enterprise

This blog post will walk through the steps required to enable CDC along with an example query to view captured changes.

First you will need to create a SQL Server instance in Cloud SQL which you can do from the Google Cloud Console.

For the full details on creating the SQL Server instance and connecting to it using Azure Data Studio see my previous post: Try out SQL Server on Google Cloud at your own pace

Create database and table

We’ll start by creating a new database on our SQL Server instance. With Azure Data Studio connected to your SQL Server instance, right click the server in Azure Data Studio and select “New Query”.

Enter the following SQL statement to create a new database to work with:

CREATE DATABASE demo;
GO
USE demo;
GO

and click the “Run” button.

Next we’ll create a new table named “leaderboard”. Enter the following query:

CREATE TABLE leaderboard (
    entryID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    playerName VARCHAR(255),
    score BIGINT,
    timestamp DATETIME2 DEFAULT CURRENT_TIMESTAMP
)

and click the “Run” button.

Enable Change Data Capture (CDC) on database and table

Okay! Now it’s time to perform the main focus of this post, enabling change data capture. This will take two steps, enabling CDC on the database and then on the table. First enable CDC on the database named “demo” by entering the following SQL statement:

EXEC msdb.[dbo].[gcloudsql_cdc_enable_db] 'demo'

and click the “Run” button.

Then enter the following SQL statement to enable CDC on the table named “leaderboard”:

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'leaderboard',
@role_name = NULL,
@supports_net_changes = 1

and click the “Run” button.

Confirm that CDC is working as expected

Alright! Now that we’ve got CDC enabled on our database and table let’s run a couple of queries to insert some data and then we can test out a CDC query to confirm that changes to our table are being tracked via CDC as expected. CDC will capture all change operations made to the table like execution of INSERT, UPDATE or DELETE statements. We’ll just run a couple of INSERT statements to demonstrate how this operation is captured by CDC.

Enter the following SQL statements to insert two records into the table named “leaderboard”:

INSERT INTO leaderboard (playerName, score) VALUES ('First Player', 1000000);
INSERT INTO leaderboard (playerName, score) VALUES ('Second Player', 1000042);

and click the “Run” button.

Now that we’ve made some changes to the table, let’s wrap things up by running a query that will get all the results captured by CDC in a special table named as “<schema>_<table_name>_CT”. In this case for the table we created, it’s named “dbo_leaderboard_CT”.

Enter the following query:

SELECT * FROM cdc.dbo_leaderboard_CT

and click the “Run” button. Voila! The INSERT operations have been captured along with the CDC metadata that can be used for tracking exactly when and what changes were made to your database tables in SQL Server.