This blog is part of the Change Data Capture in Azure SQL Databases Blog Series, which started with the announcement on releasing CDC in Azure SQL Databases in early June 2021. You can view the release announcement here: https://aka.ms/CDCAzureSQLDB
Key Change Data Capture Concepts
Below is a list of the key CDC components that are worth understanding before enabling CDC at the database and table levels:
Now in public preview, CDC in Azure SQL Databases offers a similar functionality to SQL Server and Azure SQL Managed Instance CDC. However, on Azure SQL Databases, CDC provides a scheduler which automatically runs the capture and cleanup processes, which are run as SQL Server Agent jobs on SQL Server and on Azure SQL Managed Instance.
Enabling Change Data Capture on Azure SQL Databases
1. Enable CDC at the database level:
EXEC sys.sp_cdc_enable_db
System tables (e.g. cdc.captured_columns, cdc.change_tables) and the cdc schema will be created, stored in the same database.
2. Enable CDC at the table level:
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@role_name = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
The associated change table (cdc.dbo_MyTable_CT) will be created, along with the capture and cleanup jobs (cdc.cdc_jobs). Be aware that in Azure SQL Databases, capture and cleanup are run by the scheduler, while in SQL Server and Azure SQL Managed Instance they are run by the SQL Server Agent.
3. Run DML changes on source tables and observe changes being recorded in the associated CT tables.
4. Table-valued functions can be used to collect changes from the CT tables.
5. Disable CDC at the table level:
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'MyTable',
@capture_instance = N'dbo_MyTable'
6. Disable CDC at the database level:
EXEC sys.sp_cdc_disable_db
Limitations for CDC in Azure SQL Databases
Performance implications for CDC in Azure SQL Databases
The performance impact from enabling change data capture on Azure SQL Database is similar to the performance impact of enabling CDC for SQL Server or Azure SQL Managed Instance. Factors that may impact performance:
Consider increasing the number of vCores or upgrade to a higher database tier to ensure the same performance level as before CDC was enabled on your Azure SQL Database. Monitor space utilization closely and test your workload thoroughly before enabling CDC on databases in production.
Blog Series for Change Data Capture in Azure SQL Databases
We are happy to continue the bi-weekly blog series for customers who’d like to learn more about enabling CDC in their Azure SQL Databases! This series explores different features/services that can be integrated with CDC to enhance change data functionality.
The next blog will focus on using Azure Data Factory to send Change Data Capture Data to external destinations.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.