Getting Started with Change Event Streaming in SQL Server 2025 (Part 1: Setup and Configuration)

August 18, 2025 — Leonard Lobel

Change Event Streaming (CES) is one of the most exciting new features coming in SQL Server 2025. It allows you to continuously stream row-level changes from your tables directly into Azure Event Hubs, where multiple consumer applications can subscribe to the event data in real time.

Note: This post is based on SQL Server 2025 CTP 2.1. Syntax and behavior are subject to subtle changes by the time the product is released. Change Event Streaming (CES) will ultimately be supported across all SKUs of SQL Server, including SQL Server 2025 for Windows, SQL Server 2025 for Linux, Azure SQL Database, and Managed Instance.

In this two-part series, I’ll show you how to set up and configure CES (Part 1), and then how to build a consumer application to process the streamed changes (Part 2).

Let’s dive in!

Step 1: Create an Event Hub

Before SQL Server can stream changes, you need a target destination. CES is designed to stream directly into Azure Event Hubs.

Create an Event Hub Namespace

An event hub lives within an event namespace. To create a new event hub namespace:

In the Azure portal, create a new resource.
From the Marketplace, create a new Event Hubs resource.
Provide a name for a new Event Hubs namespace in either a new or existing resource group.
Choose the Basic pricing tier with 1 throughput unit (sufficient for development and testing).
Click Review + create, and then Create.

Create an Event Hub

Now you can create a new event hub within the new event hub namespace:

On the namespace Overview page, click + Event Hub.
Provide a name for the new event hub, and leave all other options at their default settings.
Click Review + create, and then Create.

Create an Event Hub Policy

Now create a policy that allows managing the event hub:

Under Settings on the left, click Shared Access Policies.
Click + Add to create a new policy.
Provide a name for the policy.
Check Manage (which automatically includes Send and Listen).
Click Create.

Generate a SAS Token

Finally, you’ll need a Shared Access Signature (SAS) token for SQL Server and other clients to authenticate against the Event Hub. Unfortunately, the Azure portal does not provide a GUI for generating SAS tokens for Event Hub, so you must generate one programmatically using PowerShell, Azure CLI, or the Azure SDK. In this walkthrough, we’ll use PowerShell.

Install PowerShell Modules

Run PowerShell as an administrator and install the necessary modules.

Note: You only need to install these modules once on a machine. If you’ve already installed them previously, you can skip this step.

# Install the general Azure cmdlets (this can take up to 20 minutes)
Install-Module -Name Az -Scope CurrentUser -Repository PSGallery -Force

# Install the Event Hub module (runs quickly)
Install-Module -Name Az.EventHub -Scope CurrentUser -Force

Create the SAS Token Script

Copy the following code into a new file named Generate-SasToken.ps1. This script was adapted from Microsoft’s documentation at https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/change-event-streaming/configure.

function Generate-SasToken {

    # Provide values for the following resources:
    $resourceGroupName  = "ces-demo-rg"
    $namespaceName      = "ces-namespace"
    $eventHubName       = "ces-hub"
    $policyName         = "ces-policy"

    # Login to Azure and select the Azure Subscription
    Connect-AzAccount -InformationAction SilentlyContinue | Out-Null

    # Validate the existence of the specified resource group, event hub namespace, and event hub
    Get-AzResourceGroup -Name $resourceGroupName -ErrorAction Stop | Out-Null
    Get-AzEventHubNamespace -ResourceGroupName $resourceGroupName -Name $namespaceName -ErrorAction Stop | Out-Null
    Get-AzEventHub -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -Name $eventHubName -ErrorAction Stop | Out-Null

    # Get the event hub authorization policy (it must have Manage rights)
    $policy = Get-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -ErrorAction SilentlyContinue

    if (-not ("Manage" -in $policy.Rights)) {
        throw "Authorization rule '$policyName' does not exist, or is missing the required 'Manage' right"
    }

    # Get the Primary Key of the Shared Access Policy
    $keys = Get-AzEventHubKey -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName

    if (-not $keys) {
        throw "Could not obtain Azure Event Hub Key"
    }

    if (-not $keys.PrimaryKey) {
        throw "Could not obtain Primary Key"
    }

    $primaryKey = ($keys.PrimaryKey) 

    # Define a function to create the SAS token
    function Create-SasToken {
        param ([string]$resourceUri, [string]$keyName, [string]$key)

        $sinceEpoch = [datetime]::UtcNow - [datetime]"1970-01-01"
        $expiry = [int]$sinceEpoch.TotalSeconds + (60 * 60 * 24 * 31 * 6)  # 6 months
        $stringToSign = [System.Web.HttpUtility]::UrlEncode($resourceUri) + "`n" + $expiry
        $hmac = New-Object System.Security.Cryptography.HMACSHA256
        $hmac.Key = [Text.Encoding]::UTF8.GetBytes($key)
        $signature = [Convert]::ToBase64String($hmac.ComputeHash([Text.Encoding]::UTF8.GetBytes($stringToSign)))
        $sasToken = "SharedAccessSignature sr=$([System.Web.HttpUtility]::UrlEncode($resourceUri))&sig=$([System.Web.HttpUtility]::UrlEncode($signature))&se=$expiry&skn=$keyName"

        return $sasToken
    }

    # Construct the resource URI for the SAS token
    $resourceUri = "https://$namespaceName.servicebus.windows.net/$eventHubName"

    # Generate the SAS token using the primary key from the new policy
    $sasToken = Create-SasToken -resourceUri $resourceUri -keyName $policyName -key $primaryKey

    # Output the SAS token
    Write-Host "`n-- Generated SAS Token --" -ForegroundColor Gray
    Write-Host $sasToken -ForegroundColor White
    Write-Host "-- End of generated SAS Token --`n" -ForegroundColor Gray

    # Copy the SAS token to the clipboard
    $sasToken | Set-Clipboard
    Write-Host "The generated SAS token has been copied to the clipboard." -ForegroundColor Green
}

Generate-SasToken

At the top of the script (lines 4 through 7), fill in the values for your resource group, namespace, event hub, and policy. Also, the script generates a token that expires after 6 months. To adjust the expiration, edit the $expiry assignment on line 42.

Run the Script

Before you can execute the script, you must allow PowerShell to run local scripts:

Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

When prompted for confirmation, type A (for “Yes to All”) and press Enter.

Now run the script:

.\Generate-SasToken.ps1

You’ll be prompted to log in to your Microsoft account and select your Azure subscription. The script will then generate the SAS token and display it between the lines:

-- Generated SAS Token --
<your SAS token>
-- End of generated SAS Token --

The script also copies the generated SAS token to the clipboard so that you can paste it into Notepad. You’ll need it later when configuring SQL Server to stream events to Event Hubs, and then again (Part 2) when configuring your consumer applications that subsequently read those events from Event Hubs.

Step 2: Create the Demo Database

We’ll use a small sample database to demonstrate CES. Run this script in SSMS to create the database:

-- Create the demo database
USE master
GO

CREATE DATABASE CesDemo
GO

USE CesDemo
GO

-- Create some demo tables
CREATE TABLE Customer (
  CustomerId    int IDENTITY PRIMARY KEY,
  CustomerName  varchar(50),
  City          varchar(20)
)
GO

SET IDENTITY_INSERT Customer ON
INSERT INTO Customer
  (CustomerId,  CustomerName,               City) VALUES
  (1,           'Shutter Bros Wholesale',   'New York'),
  (2,           'Aperture Supply Co.',      'Los Angeles')
SET IDENTITY_INSERT Customer OFF

CREATE TABLE Product (
  ProductId     int IDENTITY PRIMARY KEY,
  Name          varchar(80),
  Color         varchar(15),
  Category      varchar(20),
  UnitPrice     decimal(8, 2),
  ItemsInStock  smallint
)
GO

SET IDENTITY_INSERT Product ON
INSERT INTO Product
  (ProductId, Name,                                  Color,     Category,       UnitPrice,  ItemsInStock) VALUES 
  (1,         'Canon EOS R5 Mirrorless Camera',      'Black',   'Camera',       3899.99,    10),
  (2,         'Nikon Z6 II Mirrorless Camera',       'Silver',  'Camera',       1996.95,    8),
  (3,         'Sony NP-FZ100 Rechargeable Battery',  'Black',   'Accessory',    78.00,      25)
SET IDENTITY_INSERT Product OFF

CREATE TABLE [Order] (
  OrderId       int IDENTITY PRIMARY KEY,
  CustomerId    int REFERENCES Customer(CustomerId),
  OrderDate     datetime2
)
GO

CREATE TABLE OrderDetail (
  OrderDetailId int IDENTITY PRIMARY KEY,
  OrderId       int REFERENCES [Order](OrderId),
  ProductId     int REFERENCES Product(ProductId),
  Quantity      smallint
)
GO

-- This table lacks Primary Key. Combining that with IncludeAllColumns = 0 results in events that
-- have no primary key, which is essentially useless
CREATE TABLE TableWithNoPK (
  Id        int IDENTITY,
  ItemName  varchar(50)
)
GO

INSERT INTO TableWithNoPK (ItemName) VALUES
  ('Camera'),
  ('Automobile'),
  ('Oven'),
  ('Couch')
GO

-- Create a DML trigger on OrderDetail that updates the ItemsInStock column in the Product table
-- based on the Quantity column in the OrderDetail table
CREATE TRIGGER trgUpdateItemsInStock ON OrderDetail AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  -- Handle insert
  IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock - i.Quantity
    FROM
      Product AS p
      INNER JOIN inserted AS i ON p.ProductId = i.ProductId

  -- Handle update
  ELSE IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) AND UPDATE(Quantity)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock + d.Quantity - i.Quantity
    FROM
      Product AS p
      INNER JOIN inserted AS i ON p.ProductId = i.ProductId
      INNER JOIN deleted AS d ON p.ProductId = d.ProductId

  -- Handle delete
  ELSE IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS (SELECT * FROM inserted)
    UPDATE Product
    SET ItemsInStock = p.ItemsInStock + d.Quantity
    FROM
      Product AS p
      INNER JOIN deleted AS d ON p.ProductId = d.ProductId
END
GO

-- Add some procs to handle orders
CREATE OR ALTER PROC CreateOrder
  @CustomerId int
AS
BEGIN
  INSERT INTO [Order](CustomerId, OrderDate)
  VALUES (@CustomerId, SYSDATETIME())

  SELECT OrderId = SCOPE_IDENTITY()
END
GO

CREATE OR ALTER PROC CreateOrderDetail
  @OrderId int,
  @ProductId int,
  @Quantity smallint
AS
BEGIN
  INSERT INTO OrderDetail (OrderId, ProductId, Quantity)
  VALUES (@OrderId, @ProductId, @Quantity)

  SELECT OrderDetailId = SCOPE_IDENTITY()
END
GO

CREATE OR ALTER PROC DeleteOrder
  @OrderId int
AS
BEGIN
  BEGIN TRANSACTION
    DELETE FROM OrderDetail WHERE OrderId = @OrderId
    DELETE FROM [Order] WHERE OrderId = @OrderId
  COMMIT TRANSACTION
END
GO

This database includes:

Customer, Order, OrderDetail, and Product tables
Stored procedures for inserting/deleting rows in the Order and OrderDetail tables.
A trigger on OrderDetail that updates inventory in Product. This is to demonstrate that CES also streams changes to tables that are updated by triggers, not just changes to tables that you issue direct DML statements on.
A TableWithNoPK to illustrate why tables without primary keys can be problematic when used with CES.

Step 3: Configure CES

With the Event Hub and database ready, let’s enable CES.

Create a Database Master Key

You need to store the SAS token in SQL Server as a database scoped credential, and that requires creating a password-protected database master key first so that SQL Server encrypt that SAS token credential.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'H@rd2Gue$$P@$$w0rd'

Create a Database Scoped Credential

To store the SAS token securely in SQL Server, run the following statement (paste the SAS token you copied to Notepad into the SECRET parameter—keep the entire string intact).

CREATE DATABASE SCOPED CREDENTIAL SqlCesCredential
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  SECRET = '<your SAS token>'

Enable CES for the Database

Execute this T-SQL to enable CES for the current database:

EXEC sys.sp_enable_event_stream

Now verify that CES is enabled:

SELECT * FROM sys.databases WHERE is_event_stream_enabled = 1

Create an Event Stream Group

An event stream group defines the Event Hub target for your events. Be sure to provide the correct values for your event hub namespace and event hub names in the @destination_location parameter:

EXEC sys.sp_create_event_stream_group
  @stream_group_name      = 'SqlCesGroup',
  @destination_location   = 'ces-namespace.servicebus.windows.net/ces-hub',
  @destination_credential = SqlCesCredential,
  @destination_type       = 'AzureEventHubsAmqp'

Add Tables to the Event Stream Group

Decide whether to include old values and whether to include all columns. Each table in our demo uses different settings for different reasons; old values and all values are included when we need that extra context, and they are excluded in favor of reduced bandwidth for smaller event payloads when we don’t.

-- Customer: full row in each event, no old values
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Customer',
  @include_old_values = 0,      -- do not include old values on updates/deletes
  @include_all_columns = 1      -- include all columns even if unchanged

-- Product: only changed columns, include old values (important for inventory diffs)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Product',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- Order: only changed columns, include old values (for auditing changes)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.Order',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- OrderDetail: only changed columns, include old values (quantity updates matter)
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.OrderDetail',
  @include_old_values = 1,      -- include old values for changed columns
  @include_all_columns = 0      -- only include changed columns

-- TableWithNoPK: demonstrates CES limitations without a primary key
EXEC sys.sp_add_object_to_event_stream_group
  @stream_group_name = 'SqlCesGroup',
  @object_name = 'dbo.TableWithNoPK',
  @include_old_values = 0,      -- no old values
  @include_all_columns = 0      -- changed columns only (essentially useless without a PK)

Customer: All columns included for upsert scenarios. Old values aren’t important here.
Product: Old values are essential to calculate stock and pricing diffs.
Order: Old values matter for audit.
OrderDetail: Old and new quantities are needed for downstream stock adjustments.
TableWithNoPK: Included as a demo; in real-world scenarios, CES requires a primary key to make the events useful.

Verify CES on Each Table

Run the following statements to confirm that CES is enabled on all the tables you just added to the event stream group, and to view the associated CES metadata associated with each table:

EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Customer'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Product'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'Order'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'OrderDetail'
EXEC sp_help_change_feed_table @source_schema = 'dbo', @source_name = 'TableWithNoPK'

The CloudEvent Payload

At this point, CES is fully configured! From here on out, all changes to these tables will be streamed to your Event Hub, where they can be consumed by multiple clients in real-time. Specifically, each event is generated and streamed as a CloudEvent with the following JSON structure:

{
	"specversion": "1.0",
	"type": "com.microsoft.SQL.CES.DML.V1",
	"source": "\/",
	"id": "cc3fcdca-09c0-4f46-a8d3-5d0c3c1eb85a",
	"logicalid": "8376457a-17af-49f4-b9ea-0d5071f515f4:0000002C000007300011:00000000000000000002",
	"time": "2025-06-30T12:29:46.290Z",
	"datacontenttype": "application\/avro-json",
	"operation": "UPD",
	"segmentindex": 1,
	"finalsegment": true,
	"data": "{\n  \"eventsource\": {\n    \"db\": \"CesDemo\",\n    \"schema\": \"dbo\",\n    \"tbl\": \"Product\",\n    \"cols\": [\n      {\n        \"name\": \"ProductId\",\n        \"type\": \"int\",\n        \"index\": 0\n      },\n      {\n        \"name\": \"ItemsInStock\",\n        \"type\": \"smallint\",\n        \"index\": 5\n      }\n    ],\n    \"pkkey\": [\n      {\n        \"columnname\": \"ProductId\",\n        \"value\": \"2\"\n      }\n    ],\n    \"transaction\": {\n      \"commitlsn\": \"0000002C:00000730:0011\",\n      \"beginlsn\": \"0000002C:00000730:000C\",\n      \"sequencenumber\": 2,\n      \"committime\": \"2025-06-30T12:29:46.290Z\"\n    }\n  },\n  \"eventrow\": {\n    \"old\": \"{\\\"ProductId\\\": \\\"2\\\", \\\"ItemsInStock\\\": \\\"8\\\"}\",\n    \"current\": \"{\\\"ProductId\\\": \\\"2\\\", \\\"ItemsInStock\\\": \\\"7\\\"}\"\n  }\n}"
}

In this sample, the operation property is UPD, indicating an UPDATE operation on a table. Also notice that there is nested JSON contained in the data property, which you can unpack to get the necessary details of each DML operation:

{
	"eventsource": {
		"db": "CesDemo",
		"schema": "dbo",
		"tbl": "Product",
		"cols": [
			{
				"name": "ProductId",
				"type": "int",
				"index": 0
			},
			{
				"name": "ItemsInStock",
				"type": "smallint",
				"index": 5
			}
		],
		"pkkey": [
			{
				"columnname": "ProductId",
				"value": "2"
			}
		],
		"transaction": {
			"commitlsn": "0000002C:00000730:0011",
			"beginlsn": "0000002C:00000730:000C",
			"sequencenumber": 2,
			"committime": "2025-06-30T12:29:46.290Z"
		}
	},
	"eventrow": {
		"old": "{\"ProductId\": \"2\", \"ItemsInStock\": \"8\"}",
		"current": "{\"ProductId\": \"2\", \"ItemsInStock\": \"7\"}"
	}
}

Here, the eventsource property describes the database, schema, table, columns, primary key, and transaction for the event. And the eventrow property is yet another nested level of JSON within the CloudEvent which provides the actual column values affected by the event as a collection of key/value pairs.

In Part 2, we’ll build a consumer application that listens for events, unpackages the CloudEvent payload, and processes them in real time.

Posted in Azure SQL Database, SQL Server 2025. Tags: azure-event-hubs, change-event-streaming, sql, SQL Server. Leave a Comment »

Lenni's Technology Blog

Leonard Lobel

Leonard Lobel