SQL Server 2025 CES Monitor

A dark-mode Avalonia desktop app that shows SQL Server 2025 Change Event Streaming (CES) events in real time — every INSERT, UPDATE, and DELETE on the Orders table appears instantly in the UI, pushed through Azure Event Hubs.

Alongside the live feed, the app ships 5 self-contained scenario-simulation tabs that demonstrate core CES consumer design patterns (idempotency, multiple consumers, parallel partitions, multi-table routing, batching) entirely in-memory — no Event Hub or SQL Server connection required to explore them. Five of those patterns also have a live twin: Idempotency (Live) steps through the real stream one event at a time, Two Consumers (Live) runs two real consumers side by side, Batching (Live) commits whole batches in a single transaction, Multi-Table (Live) routes Orders and OrderLines events to their own tables behind one shared ledger, and Partitions (Live) runs four per-partition workers concurrently — all applying events to local destination databases with the ledger + offset pattern.

---

What is CES?

Change Event Streaming (CES) is a log-based, push-driven change streaming engine built directly into the SQL Server storage engine.

It is not CDC, not Change Tracking, and not Query Store telemetry.

CES is a continuous event pipeline that streams committed changes from the transaction log into event streams that can be consumed by:

• Kafka
• Event Hubs
• Fabric Real-Time Hub
• Custom gRPC/WebSocket consumers
• SQL Server itself (internal pipelines)

CES is designed for sub-second latency, exactly-once delivery, and schema-aware change envelopes.

CES Architecture

Log Capture Layer (LCL)

CES hooks directly into the log manager using a new internal component: XEventLogCapture (XLC).

This layer reads logical log records after commit (Insert, Delete, Update, Schema changes, Metadata changes). It does not parse physical log blocks — it uses the same logical decoding layer used by AlwaysOn.

Key properties:

• Zero table locks
• No scan of base tables
• No CDC jobs, no SQL Agent, no polling, no triggers

CES is pure log-based streaming.

Change Normalization Layer (CNL)

Converts raw log records into CES envelopes. Each envelope contains:

• Operation: Insert / Update / Delete
• Primary key
• Before image (for updates/deletes) and after image (for inserts/updates)
• Commit LSN, Transaction ID, Ordering metadata
• Schema version and column-level change mask

CES envelopes are schema-aware, including column names, data types, nullability, collation, computed column definitions, and identity metadata. This makes CES strongly typed, unlike CDC's weakly typed NVARCHAR output.

Event Stream Engine (ESE)

The heart of CES — a multi-partition, ordered, durable event pipeline inside SQL Server.

• Each CES stream is a partitioned commit-ordered queue
• Backed by memory + persisted log segments
• Uses LSNs for ordering
• Supports exactly-once semantics, consumer offsets, replay and rewind, and multi-consumer fan-out

ESE is similar to Kafka's internal log segments, but implemented inside SQL Server.

Delivery Layer (DL)

Pushes CES envelopes to external systems.

Push connectors: Kafka, Event Hubs, Fabric Real-Time Hub, custom HTTP/gRPC/WebSocket endpoints

Pull mode: Consumers can request batches by offset.

Delivery guarantees: Exactly-once (with consumer offset tracking), at-least-once (for simple consumers), idempotent replay.

How SQL Server Streams Changes

1. Transaction commits — log records written to the transaction log
2. XLC reads committed log records — no locks, no table scans
3. CNL converts log records into envelopes — strongly typed, schema-aware
4. ESE appends envelopes to stream partitions — ordered by commit LSN
5. DL pushes envelopes to consumers — Kafka/Event Hubs/Fabric/etc.
6. Consumers track offsets — SQL Server stores offsets for exactly-once delivery

CES vs CDC vs Change Tracking

Feature	CES	CDC	Change Tracking
Latency	Sub-second	Seconds/minutes	Minutes
Mechanism	Log-based streaming	Log-based batch decode	Versioning
Delivery	Push + Pull	Pull only	Pull only
Ordering	Commit-ordered	Not guaranteed	Not guaranteed
Schema	Strongly typed	Weak NVARCHAR	Weak
Before/After images	Yes	Yes	No
Exactly-once	Yes	No	No
Replay	Yes	No	No
Partitioning	Yes	No	No
Multi-consumer	Yes	No	No
Designed for	Real-time pipelines	ETL	Sync

CES is essentially SQL Server's built-in Kafka-like change log.

Performance Characteristics

Zero impact on OLTP — CES uses asynchronous log reading, memory buffers, no table access, no locks, no triggers.

High throughput — benchmarks show >100,000 events/sec per stream, <50 ms latency, horizontal scaling via partitions.

Minimal log overhead — CES piggybacks on existing log records.

---

Architecture

SQL Server 2025
  └─ CES (dbo.Orders)
       │  AMQP
       ▼
Azure Event Hubs (ces-poc-od / orders)
       │  Kafka protocol (port 9093, SASL/SSL)
       │
       ├─ consumer group $Default    → Live Feed tab (display only)
       ├─ consumer group consumer1   → CES_Destination1 (Two Consumers Live)
       ├─ consumer group consumer2   → CES_Destination2 (Two Consumers Live)
       ├─ consumer group idempotency → CES_IdempotencyDemo (Idempotency Live, single-step)
       ├─ consumer group batching    → CES_Batching (Batching Live, one transaction per batch)
       ├─ consumer group multitable  → CES_MultiTable (Multi-Table Live, Orders + OrderLines routing)
       └─ consumer group partitions  → CES_Partitions (Partitions Live, 4 concurrent workers)

One stream, three independent readers — Event Hubs fan-out. The Live Feed only displays events; the two live consumers apply them to their own destination database using a ledger + offset store for exactly-once semantics.

---

Prerequisites

Requirement	Notes
SQL Server 2025	Preview features must be enabled
Azure subscription	Event Hubs Standard tier
.NET 8 SDK	dotnet --version
Windows	App uses Win32 renderer

---

Setup

1 — Azure Event Hubs (detailed)

1a — Create the namespace

1. Go to portal.azure.com and sign in
2. In the top search bar type Event Hubs and click the result
3. Click + Create (top left)
4. Fill in:
   • Subscription — pick yours
   • Resource group — create new, e.g. ces-poc-rg
   • Namespace name — e.g. ces-poc-od (must be globally unique)
   • Location — nearest region
   • Pricing tier — Standard ← required, Basic does not support the Kafka endpoint
5. Click Review + create → Create
6. Wait ~1 minute, then click Go to resource

The finished namespace looks like this (Standard tier, Kafka surface enabled, one orders hub with 4 partitions):

1b — Create the Event Hub

1. Inside the namespace, click + Event Hub (top toolbar)
2. Name — orders
3. Leave everything else as default
4. Click Review + create → Create

1c — Get the connection string

1. In the namespace left menu, click Settings to expand it
2. Click Shared access policies
3. Click RootManageSharedAccessKey
4. A panel opens on the right — copy the Primary connection string (starts with Endpoint=sb://ces-poc-od.servicebus.windows.net/;...)
5. Also note the Primary key (shorter value) — needed for the SQL credential

Tip: The Primary connection string = full value for CES_CONNECTION_STRING The Primary key = shorter value for the SQL SECRET field

2 — SQL Server

Everything on the SQL Server side lives in one script: scripts/ces_demo.sql, organised in numbered parts:

Part 1 — ContosoOrders database + Orders table
Part 2 — enable CES + credential for Event Hubs
Part 3 — stream group → Event Hubs + add Orders table
Part 4 — verify the CES setup
Part 5 — destination databases for the Two Consumers (Live) tab
Part 6 — generate test events (INS / UPD / DEL)
Part 7 — diagnostics & recovery (only when something is wrong)

Fill in the placeholders first: <your-strong-password> (master key) and <your-sas-primary-key-here> (the SAS Primary key from step 1c). Then run parts 1–5 once, in order.

3 — Environment variable

Create set-env.local.ps1 (already gitignored) with your connection string:

$env:CES_CONNECTION_STRING = "Endpoint=sb://ces-poc-od.servicebus.windows.net/;SharedAccessKeyName=RootManageSharedAccessKey;SharedAccessKey=<your-key>"

4 — Run the app

. .\set-env.local.ps1
dotnet run --project src\CES.UI

---

Testing

With the app running, execute any of these in SSMS:

scripts/neworder.sql          — quick single INSERT for repeated demo runs
scripts/ces_demo.sql Part 6   — one INS, one UPD and one DEL event in a row

Each change appears in the app within a second, colour-coded by operation:

Badge	Operation
INS (green)	INSERT
UPD (amber)	UPDATE
DEL (red)	DELETE

---

Deleting & Recreating the Azure Resources

To save money, the resource group (ces-poc-rg) can be deleted when not in use and recreated later — the whole Azure side rebuilds in a couple of minutes. But recreating the namespace generates a new SAS key, and SQL Server will keep silently failing with the old one. Symptom: the app connects fine, inserts succeed, but no events appear.

Check the CES error log to confirm:

USE [ContosoOrders];
SELECT TOP 10 entry_time, error_number, error_message
FROM sys.dm_change_feed_errors
ORDER BY entry_time DESC;

If you see InvalidSignature: The token has an invalid signature, the SQL credential still holds the old key. Recovery steps:

1. Recreate the Azure resources — follow Setup step 1 again (same namespace name ces-poc-od, same hub name orders, Standard tier), or do it in one go with the Azure CLI:

   az group create --name ces-poc-rg --location westeurope
   
   az eventhubs namespace create --resource-group ces-poc-rg --name ces-poc-od `
       --location westeurope --sku Standard
   
   az eventhubs eventhub create --resource-group ces-poc-rg `
       --namespace-name ces-poc-od --name orders
   
   # Prints the new connection string (for set-env.local.ps1)
   # and primary key (for the SQL credential)
   az eventhubs namespace authorization-rule keys list `
       --resource-group ces-poc-rg --namespace-name ces-poc-od `
       --name RootManageSharedAccessKey `
       --query "{connectionString: primaryConnectionString, primaryKey: primaryKey}"

   And when you're done, tear everything down with:

   az group delete --name ces-poc-rg --yes

2. Update set-env.local.ps1 with the new Primary connection string (Shared access policies → RootManageSharedAccessKey).

3. Update the SQL credential with the new Primary key:

   USE [ContosoOrders];
   ALTER DATABASE SCOPED CREDENTIAL eventhubscred
   WITH IDENTITY = 'RootManageSharedAccessKey',
        SECRET   = '<new-primary-key>';

4. Recreate the stream group — this step is essential. CES caches the signed SAS token, so updating the credential alone is not enough; the InvalidSignature errors keep coming until the stream group is restarted:

   EXEC sys.sp_drop_event_stream_group N'OrdersCESGroupKafka';
   
   EXEC sys.sp_create_event_stream_group
       @stream_group_name      = N'OrdersCESGroupKafka',
       @destination_type       = N'AzureEventHubsAmqp',
       @destination_location   = N'ces-poc-od.servicebus.windows.net/orders',
       @destination_credential = eventhubscred;
   
   EXEC sys.sp_add_object_to_event_stream_group N'OrdersCESGroupKafka', N'dbo.Orders';
   EXEC sys.sp_add_object_to_event_stream_group N'OrdersCESGroupKafka', N'dbo.OrderLines';

   (Both steps are ready to run in scripts/ces_demo.sql Part 7.)

5. Verify — insert a row (scripts/neworder.sql), then check that sys.dm_change_feed_errors shows no new rows and the event appears in the app.

Note: because SQL Server (not the app) restarts publishing from the stream group's creation point, events that failed to deliver while the key was wrong are not replayed — only changes committed after the stream group is recreated flow through.

---

Scenario Simulation Tabs

The app opens with a tabbed window. Besides Live Feed (the real Kafka consumer above), 5 tabs replay the consumer design patterns from docs/ces_idempotent.sql using canned in-memory events — click through them with no Azure or SQL Server connection needed:

Tab	What it shows
Idempotency & Offsets	A duplicate replayed event is detected via the ledger and skipped — no double-apply
Two Consumers	Two independent consumers (Replication, Analytics) track separate ledgers/offsets without interfering
Parallel Partitions	4 partition workers each process their own event stream independently
Multi-Table Routing	One shared ledger/offset routes events to the correct target table (Orders vs OrderLines)
Batching	Events buffer into a batch; the offset updates once per commit, and a simulated mid-batch crash proves replay-safety

---

Idempotency (Live)

The Idempotency (Live) tab is the real version of the Idempotency & Offsets simulation: the same single-step UI, but the events come from the actual Event Hub (consumer group idempotency) and every click of Process Next Event applies exactly one of them to the local CES_IdempotencyDemo database.

The ledger and offsets on screen are real rows in CES_IdempotencyDemo:

• Incoming events buffer in a queue — the action bar previews the next one (Next: seq …)
• Start loads the existing ces_ledger/ces_offsets rows from the database first, so restarting the app shows the ledger persisting
• Kafka offsets are never committed: Stop + Start replays the stream from the beginning, and every already-processed event shows up as DUPLICATE — skipped
• Reset DB empties the ledger, offsets and Orders table; stop + start then replays and applies everything again

Extra setup: scripts/ces_demo.sql Part 5 creates the CES_IdempotencyDemo database, and the consumer group is created like the ones below:

az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
    --namespace-name ces-poc-od --eventhub-name orders --name idempotency

---

Batching (Live)

The Batching (Live) tab is the real version of the Batching simulation. Events from the Event Hub (consumer group batching) pile up in the incoming queue; Add Next Event to Batch buffers up to 5 of them — still no SQL — and Commit Batch applies the whole batch to CES_Batching in one SQL transaction: a ledger check + DML + ledger row per event, and the offset updated once per partition at the end.

A simulated crash throws the buffered batch away — the log shows nothing was persisted, and after Stop + Start the same events reappear in the queue:

The destination table is real — the committed batches are visible in SSMS:

• Simulate Crash Mid-Batch throws away the uncommitted batch: since nothing was written until the commit, the database is untouched — Stop + Start replays the stream and the events come back
• Duplicates inside a committed batch (e.g. after such a replay) are skipped by the ledger check, per event, inside the same transaction
• Fewer, bigger transactions = the throughput pattern; the trade-off is more re-work after a crash, which the ledger makes harmless

The consumer group is created the same way:

az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
    --namespace-name ces-poc-od --eventhub-name orders --name batching

---

Partitions (Live)

The Partitions (Live) tab is the real version of the Parallel Partitions simulation. The orders Event Hub has 4 partitions; one subscription (consumer group partitions) feeds four per-partition queues, and every Process Next Event (all partitions) tick lets each worker apply one event to CES_Partitions concurrently — four parallel SQL transactions per click.

The schema makes the independence visible: partition_id is part of the ces_ledger primary key and is the ces_offsets primary key, so every worker tracks its own position without coordinating with the others. Within a partition CES guarantees commit order; across partitions there is no ordering — which is exactly what the four panels show.

In SSMS the same state is visible as real rows — one ledger keyed by (partition_id, sequence_number) and one offset row per partition:

az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
    --namespace-name ces-poc-od --eventhub-name orders --name partitions

---

Multi-Table (Live)

The Multi-Table (Live) tab is the real version of the Multi-Table Routing simulation. Both dbo.Orders and dbo.OrderLines are in the CES stream group, so their events arrive interleaved on the same stream (consumer group multitable). Each Process Next Event click routes one event to the matching table in CES_MultiTable — and one shared ces_ledger/ces_offsets pair guards both tables, which is the point: the consumer tracks a single position in the stream no matter how many tables it fans out to.

After clicking through the queue, Orders landed left, OrderLines right, one shared ledger position for both:

• The action bar previews the next event including its target table (Next: seq … INS on dbo.OrderLines)
• OrderLines.LineTotal is a computed column (Quantity × UnitPrice) and is never written by the consumer
• The destination OrderLines has no FK to Orders: the two tables' events can arrive on different partitions, so apply order across tables isn't guaranteed
• scripts/ces_demo.sql Part 6 ends with an INS/UPD/DEL trio on OrderLines to feed this tab

Extra setup: Part 5 creates the CES_MultiTable database, Part 3 adds dbo.OrderLines to the stream group, and the consumer group is created the same way:

az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
    --namespace-name ces-poc-od --eventhub-name orders --name multitable

---

Two Consumers (Live)

The Two Consumers (Live) tab is the real version of the Two Consumers simulation: two actual Kafka consumers, each with its own Event Hubs consumer group, applying the same CES stream to its own destination database with the ledger + offset pattern from docs/ces_idempotent.sql.

Extra setup

1. Destination databases — run scripts/ces_demo.sql Part 5 once. It creates CES_Destination1, CES_Destination2, CES_IdempotencyDemo, CES_Batching, CES_MultiTable and CES_Partitions, each with a copy of Orders plus the ces_ledger and ces_offsets tables (CES_MultiTable also gets OrderLines).

2. Consumer groups — add consumer1 and consumer2 to the orders hub (the Kafka group.id maps to an Event Hubs consumer group; the Live Feed keeps $Default):

   az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
       --namespace-name ces-poc-od --eventhub-name orders --name consumer1
   az eventhubs eventhub consumer-group create --resource-group ces-poc-rg `
       --namespace-name ces-poc-od --eventhub-name orders --name consumer2

   (Portal: Event Hub orders → Consumer groups → + Consumer group.)

   With all live tabs set up, the hub ends up with 7 consumer groups — each an independent reader of the same stream:

   

How it works

For every event, the consumer runs a single SQL transaction against its destination database:

1. Check ces_ledger for (partition_id, sequence_number) — if present, the event is a duplicate and is skipped
2. Apply the DML: MERGE into Orders for INS/UPD (with IDENTITY_INSERT, so OrderIDs match the source), DELETE for DEL
3. Insert the ledger row and upsert ces_offsets
4. Commit

The Kafka offset is deliberately never committed — every Start replays the full stream from the beginning. That is the demo: the first Start applies everything; Stop + Start again and every event comes back as duplicate — skipped while the destination data stays correct. Exactly-once semantics live in the destination database, not in the transport.

Try it: Start both consumers, insert a row in ContosoOrders (scripts/neworder.sql), and watch it apply to both databases. Then restart one consumer and watch the ledger reject the entire replay.

---

Project Structure

CES/
├── docker-compose.yml          # Redpanda (local Kafka — for future use)
├── set-env.local.ps1           # Local secrets (gitignored)
├── docs/
│   └── ces_idempotent.sql      # Design notes for the 5 scenario tabs (not runnable)
├── scripts/
│   ├── ces_demo.sql            # Complete SQL-side setup + test + recovery, in 7 parts
│   └── neworder.sql            # Quick test INSERT for repeated demo runs
└── src/CES.UI/
    ├── Models/
    │   ├── ChangeEvent.cs            # Live event record
    │   ├── LiveApplyEntry.cs         # Applied/duplicate log entry (live consumers)
    │   ├── SimulatedEvent.cs         # Canned event for scenario tabs
    │   └── SimulationModels.cs       # LedgerEntry, OffsetEntry
    ├── Services/
    │   ├── KafkaConsumerService.cs   # Kafka consumer → UI dispatcher (Live Feed)
    │   ├── LiveConsumerService.cs    # Kafka consumer → idempotent SQL apply
    │   ├── IdempotencyLiveService.cs # Single-step consumer (Idempotency Live)
    │   ├── BatchingLiveService.cs    # Batch-commit consumer (Batching Live)
    │   ├── MultiTableLiveService.cs  # Table-routing consumer (Multi-Table Live)
    │   ├── ParallelPartitionsLiveService.cs # Per-partition workers (Partitions Live)
    │   ├── OrderEventApplier.cs      # Shared parse + idempotent-apply logic (single, batch, OrderLines)
    │   └── DestinationDatabase.cs    # Shared load/reset/count helpers for the local DBs
    ├── ViewModels/
    │   ├── MainWindowViewModel.cs        # Shell VM — live feed + tab VMs
    │   ├── IdempotencyTabViewModel.cs
    │   ├── IdempotencyLiveTabViewModel.cs
    │   ├── TwoConsumersTabViewModel.cs
    │   ├── TwoConsumersLiveTabViewModel.cs
    │   ├── ParallelPartitionsTabViewModel.cs
    │   ├── ParallelPartitionsLiveTabViewModel.cs
    │   ├── MultiTableTabViewModel.cs
    │   ├── MultiTableLiveTabViewModel.cs
    │   ├── BatchingTabViewModel.cs
    │   └── BatchingLiveTabViewModel.cs
    └── Views/
        ├── MainWindow.axaml              # TabControl shell
        ├── LiveFeedView.axaml            # Dark-mode event feed UI
        ├── IdempotencyView.axaml
        ├── IdempotencyLiveView.axaml
        ├── TwoConsumersView.axaml
        ├── TwoConsumersLiveView.axaml
        ├── ParallelPartitionsView.axaml
        ├── ParallelPartitionsLiveView.axaml
        ├── MultiTableView.axaml
        ├── MultiTableLiveView.axaml
        ├── BatchingView.axaml
        └── BatchingLiveView.axaml

---

Security

• The SAS key is never committed to git
• set-env.local.ps1 is gitignored
• The SQL script uses a placeholder <your-sas-primary-key-here> for the credential SECRET

---

References

• MicrosoftDocs/sql-docs — official SQL Server documentation source

---

Blog Post

This project accompanies the blog post Just say YES to SQL Server 2025 CES — a hands-on walkthrough covering CES setup, Azure Event Hubs wiring, and consuming change events in a .NET Avalonia desktop app.

---

Author

Ronald de Groot ronald.de.groot@opendata.nl dbaronald.nl