Enter your search text in the box above

Select a result to preview

Change Tracking in SQL Server 2025: Exploring Change Event Streaming vs CDC

#fabcon2026 #session #learning #microsoft #fabcon2026-session

Description

Need to ship row level data changes for processing downstream? Change Data Capture (CDC) used to be the go-to option. But now there's a new choice with SQL Server 2025: Change Event Streaming (CES). Come and learn about their architectures as well as the pros and cons of each. Leave knowing which is going to work best for your scenario.

Key Takeaways

My Notes

Action Items

Slides

Change Tracking in SQL
Server 2025:
Exploring Change Event
Streaming vs CDC
Deborah Melkin
(she\her)
Data Engineer
About Me
•25+ years as a DBA
•Data Platform Women in Tech (WIT) Virtual UG,
Co-leader
•WITspiration, Co-founder
•Redgate Community Ambassador
•Microsoft MVP, Data Platform
•In my spare time, I can usually be found doing
something musical or something geeky with my
husband, Andy, and our dog, Sebastian.
Here’s the Scenario:
•Breaking up a legacy monolith system into smaller parts
• Microservices
• Many databases
• Teams choosing their database RDBMS & set ups
•Creation of analytical system
• (Near) Real-time analytics
•No cross-database joins
•Need to share data across the systems
•Need before and after data changes
Options We’re
Ruling Out
Option 1: Add columns to the table
Pros:
• We can use these columns directly
Cons:
• Querying large tables can take time
• Not guaranteed to always be populated properly
• Only has the updated data
Option 2: Temporal Tables\System Versioned Tables
Pros:
• Maintained by SQL Server
• Stores both previous and current versions of rows
Cons:
• Querying large tables can take time
• Potential overhead if not set up properly with additional indexing and
retention periods.
• See documentation for limitations
Option 3: Change Tracking
Pros:
• Only identifies that row that has changed
• Managed by SQL Server
Cons:
• Does not capture the original or updated data
• Requires additional set up and overhead.
What are we left with?
Change Data Capture (CDC)
• Introduced SQL Server 2008
• “Pull”
Change Event Streaming (CES)
• Introduced SQL Server 2025 (Preview)
• “Push”
Agenda
•How CDC & CES Work
•Setup
•Demos
•“Cautionary Tales”
•When To Use Each
How They Work
Overview of Capture Process for CDC & CES
•Specify tables to track
•Transaction Logs are scanned for changes which are sent to the
appropriate system
• CDC – cdc schema tables inside the database
• CES – Azure Event Hubs
• Managed, high-throughput message service
•Logs receive acknowledgement of changes
•After retention period, change data is removed
How this works:
Fake\Mock
Transaction Log
UPDATE Customer
SET State = 'MA'
WHERE FirstName = 'Sebastian'
Log Seq Number
(LSN)
Table
Record Info
Chg
0000000A
Customer
Sebastian-AZ
Old
0000000A
Customer
Sebastian-MA
New
Clear?
Acknowledgement
OR
cdc schema tables
CDC
Azure Event Hubs
Changes Sent
CES
Transaction Logs – Word of Warning
•Can’t clear logs if changes can’t be written or messages haven’t
been acknowledged.
•Monitor Log Growth to make sure that these are not blockers:
SELECT db.name,
db.is_cdc_enabled,
db.is_change_feed_enabled,
db.log_reuse_wait_desc,
usage.total_log_size_in_bytes/1024 AS TotalSizeInKB,
usage.used_log_space_in_bytes/1024 AS TotalSpaceInKB,
usage.used_log_space_in_percent,
usage.log_space_in_bytes_since_last_backup/1024 AS SpaceUsedSinceLastBackupInKB
FROM sys.databases AS db
JOIN sys.dm_db_log_space_usage AS usage ON usage.database_id = db.database_id
Setup
CDC
Azure Event Hubs
CES
CDC Set up
•Enable CDC in the database
•Enable CDC for the tables
•SQL Server Agent must be running
• 2 jobs are created per database: Capture & Cleanup
• Scheduler for Azure SQL DB
CDC SQL Agent Jobs – Capture
•Settings for job procs
• MaxTrans = # of transactions in each scan cycle (Default 10000)
• Maxscans = # of scan cycles to execute to get all the rows (Default 10)
• Continuous = Is this running continuously? (Default 1)
• PollingInterval = # of seconds between scans (Default 5)
•Must be running for CDC to be working
•Starts when SQL Agent Job starts by default
• Consider adding an additional job schedule to start if it is stopped
• Add check to monitoring\alerting system to see if it’s not running
CDC SQL Agent Jobs – Cleanup
•Settings for job procs
• Retention Period Default 4320 minutes (3 days)
• Threshold Default 5000
•Deletes in batches:
delete top( @p1 ) from [cdc].[dbo_Users_CT]
where __$start_lsn < @p2
• Performance Consequence if not run in a while
• Azure SQL DB - Runs every hour while database is running
CES Set up - Azure Event Hubs
•Must be set up before CES is enabled
•Choose protocol for the messages
• AMQP
• Kafka
•Security currently differs depending on your SQL engine
• EntraID (Azure SQL DB)
• Security Access Key (SQL Server)
•Can have multiple partitions for messages in Event Hub
1. Create the
Namespace
Pricing Tiers:
• Basic
• Standard
• Premium
• Dedicated
2. Create the Event
Hub
3. Create the Shared
Access Policy –
AMQP Protocol
Skip if using EntraID
(Azure SQL DB)
https://learn.microsoft.com/enus/sql/relationaldatabases/trackchanges/change-eventstreaming/configure?view=sqlserver-ver17&tabs=sasaccess%2Csas-token-auth
3. Create the Shared
Access Policy –
Kafka Protocol
Skip if using EntraID
(Azure SQL DB)
CES Set up - Database
•Enable Preview Features as this is technically still in Preview
• Not needed for Azure SQL DB
•Enable Event Stream for the database
•Create an Event Stream Group for sending the messages
•Add the table to the stream group
• Tables can only be in one stream group
NOTE: CES is incompatible with:
• CDC, DB Mirroring in Fabric, Replication
Demos
• Set up database
• Make changes
Cautionary
Tales
aka Problems to Look For
High Availability – Availability Groups
•Neither are AG aware
•Consider making modifications to the jobs:
• Add a job step to make sure that the database is the primary replica.
• If it is, continue as normal. Otherwise, end job.
• Add the jobs to the replicas in advance so they are ready to kick off if/when
a failover occurs.
•If you are using read replicas to get LSNs for processing, the LSNs
could be out of sync if the AGs are out of sync.
Disaster Recovery
•Restores
• CDC requires an additional command for cdc to be retained: WITH
KEEP_CDC
• CES restores do not keep CES settings
•Disabling CDC erases everything!
Data Initialization
•CDC and CES are about capturing and streaming changes
•May require full resets at some point for some reason
• New Setup
• Disaster Recovery
•Have a plan for reseeding the data for the target systems
Processing Messages (CES)
•Ordering of events isn’t necessarily maintained
• May need additional help to identify the order of the updates
• Within a partition, the logicalid value may be able to help
•Duplicate Messages are possible
• If an acknowledgment isn’t received for some reason, SQL Server will keep
retrying the send
• Consumer should be aware and know how to handle these cases
•Learning Curve: JSON, .NET, Kafka, Stream Analytics Jobs, etc.
Money! Money! Money!
•CES & Azure Event Hubs
• Pricing Guide: Azure Event Hubs Pricing
• Make sure you have the right tier set up
• Know what happens when you hit limits
• Emulator (unsupported) for development purposes:
• MS Learn - Azure Event Hubs Emulator Overview
•CDC on Azure SQL DB
• I/O of writing to and deleting from cdc schema tables
• Transaction logs
When to use each
When to use CDC
•Changes do not need to be changed outside the environment
• Same database
• Different database, same SQL Server instance
• Different SQL Server instance, same network
•No Azure or other cloud service
•Also using Replication, DB Mirroring in Fabric
•Batch processing
•SQL Server 2022 or earlier (Can still use with SQL 2025)
When to use CES
•Distributed systems
• Azure DBs only
• Hybrid Cloud & On Prem
• Different RDBMS
•Real-time/near real-time processing
• “Streaming data”
Resources
Transaction Log Resources
•MS Learn - SQL Server Transaction Log Architecture and
Management Guide
•Paul Randal Blog Series:
• https://www.sqlskills.com/blogs/paul/category/transaction-log/
• https://www.sqlskills.com/blogs/paul/the-sql-server-transaction-log-part1-logging-basics/
CDC Resources
•MS Learn - About Change Data Capture SQL Server
•MS Learn - Change Data Capture Overview (Azure SQL DB)
•MS Learn - Enable and Disable Change Data Capture - SQL Server
•MS Learn - Administer and Monitor Change Data Capture
•MS Learn - System Tables for Change Data Capture
•MS Learn - System Stored Procs - Change Data Capture
CES & Azure Event Hubs Resources
•MS Learn - Change Event Streaming Overview
•MS Learn - Change Event Streaming – Configure
•MS Learn - Azure Event Hubs - Create
•MS Learn - Azure Event Hubs Emulator Overview
•Azure Event Hubs Pricing
Any Questions?
Thank You For Coming!
Github:
https://github.com/DebtheDBA/C
ES_vs_CDC
Blog: DebtheDBA.wordpress.com
BlueSky: @dgmelkin.bsky.social