This is part 4 of a series on working with change data capture (CDC) in Azure SQL Database. This part discusses how to enable it and how to use it.
When I was reading the documentation for CDC in Azure SQL Database, I kept coming across a mention that it required at least an S3 service level objective (SLO), if you were using a DTU-based database.
I really hoped that wasn't the case.
I was left saying "please say it ain't so!".
I thought it was time to find out what actually happens when you try it.
I started by creating a database with the lowest SLO (Basic). Now I know these don't hold enough data to really even be very meaningful, but I wanted to know what would happen.
I didn't get far.
I created the table from the scripts in part 3 of this series, and then tried to do the first enable of CDC.
To say that's disappointing is an understatement. But i tmakes it clear that Basic, S0, S1, and S2 aren't going to let you enable it.
What about Scaling?
Then I wondered what would happen if I enabled it on an S3, and then tried to scale down.
So I scaled the DB to S3 ok, and enabled CDC as in part 3. All worked as expected.
I then scaled the DB back to S2 using the Azure Portal. And alas, that failed too:
I suppose I'm not surprised, but I had hoped it might at least have automatically disabled CDC if it really had to.
It's a Mistake
I think these licensing restrictions for CDC in Azure SQL DB are a mistake.
Back in SQL Server 2016 SP1, I was one of the main instigators to get the product team to make sure that the programming surface of the database worked across all the tiers (Enterprise, Standard, Developer, Express) wherever possible.
This restriction on CDC goes directly against that ethos. It harks back again to large single database thinking, not cloudy thinking.
I understand that it's probably needed for performance in production scenarios, but what I'm always trying to tell the team is that production scenarios aren't the only scenarios.
Developers also need to write code. We should let them write and test functionality. It can be clear to them that it won't work the same as on a production instance, but that's no different to many other aspects of the system. A Basic SLO database won't run a complex query over a lot of data either, but we don't expect it to.
But it's important to let them write their code and make sure it basically functions, no matter what level of SLO they're working with. If you require them all to have an expensive database, just to test code or concepts, you greatly limit how many developers will use the features.
Having those types of restrictions also restricts the cloudiness of the solution, in that you couldn't scale up/down across a range of SLOs. We often take S7 or S8 databases and temporarily "park" them at S2 while not in use. This type of restriction kills those options as well, and makes the overall solution less cloudy.
And One More Part to This Series
That's service level objectives for the use of CDC in Azure SQL DB. We'll move to accessing CDC data from another Azure SQL DB next.
- Why use Change Data Capture for Azure SQL Database?
- How Change Data Capture works in Azure SQL Database
- Enabling and using Change Data Capture in Azure SQL Database
- Change Data Capture and Azure SQL Database Service Level Objectives
- Accessing Change Data Capture Data from Another Azure SQL Database