Configure change event streaming

2025-06-19

Applies to: SQL Server 2025 (17.x) Preview

This article describes how to configure change event streaming (CES) feature introduced in SQL Server 2025 (17.x) Preview.

Note

Change event streaming is currently in preview for SQL Server 2025.

Overview

To configure and use change event streaming, follow this sequence of steps:

Use an existing or create a new Azure Event Hubs namespace and Event Hubs instance. Event Hubs instance receives events.
Enable change event streaming for a user database.
Create an event stream group. With this group, configure the destination, credentials, message size limits, and partitioning schema.
Add one or more tables to the event stream group.

Each step is described in detail in the following sections of this article.

Prerequisites

To configure change event streaming, you need the following:

Azure Event Hubs namespace
Azure Event Hubs instance
Azure Event Hubs host name
Policy with Send access level
A login in the db_owner role or that has CONTROL DATABASE permission for the database where you intend to enable CES.

Configure Azure Event Hubs

To learn how to create Azure Event Hubs, review Create an event hub using the Azure portal.

To configure streaming to Azure Event Hubs with AMQP protocol (default, native Azure Event Hubs protocol), generate a SAS token for your Azure Event Hubs namespace and instance name. You can do that programmatically with any programming or scripting language. The example in this article demonstrates how to generate a SAS token from a new or existing policy by using a PowerShell script.

Install required modules

To manage Azure Event Hubs resources with PowerShell scripts, you need to have the following modules:

Az PowerShell module
Az.EventHub PowerShell module

The following script installs the required modules:

Install-Module -Name Az -AllowClobber -Scope CurrentUser -Repository PSGallery -Force
Install-Module -Name Az.EventHub -Scope CurrentUser -Force

If you already have the required modules, and want to update them to the latest version, run the following script:

Update-Module -Name Az -Force
Update-Module -Name Az.EventHub -Force

Connect to Azure

You can either use Azure Cloud Shell or login and set your subscription context.

To run with Azure Cloud Shell, review Sign in to Azure.

Define a policy

To create the SAS token, you need a policy. You can either:

Create a new policy with the specific rights.

Or
Use an existing policy with the correct rights.

Create SAS token for a new or existing policy

Note

For improved security, SAS token authentication is strongly recommended over key-based authentication whenever possible. Best practices for SAS tokens include: define an appropriate access scope, set an expiration date, and rotate the SAS key regularly. For key-based authentication, ensure keys are rotated periodically. Store all secrets securely using Azure Key Vault or a similar service.

When creating a new policy, ensure it has the Send right. If you use an existing policy, verify that it has the Send right.

The following script will create a new policy, or get an existing one, and then generates from it a full SAS token in an HTTP authorization header format.

Replace values in angle brackets (<value>) with values for your environment.

function Generate-SasToken {
$subscriptionId = "<Azure-Subscription-ID>"
$resourceGroupName = "<Resource-group-name>"
$namespaceName = "<Azure-Event-Hub-Namespace-name>"
$eventHubName = "<Azure-Event-Hubs-instance-name>"
$policyName = "<Policy-name>"

# Modifying the rest of the script is not necessary.

# Login to Azure and set Azure Subscription.
Connect-AzAccount

# Get current context and check subscription
$currentContext = Get-AzContext
if ($currentContext.Subscription.Id -ne $subscriptionId) {
    Write-Host "Current subscription is $($currentContext.Subscription.Id), switching to $subscriptionId..."
    Set-AzContext -SubscriptionId $subscriptionId | Out-Null
} else {
    Write-Host "Already using subscription $subscriptionId."
}

# Try to get the authorization policy (it should have Send rights)
$rights = @("Send")
$policy = Get-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -ErrorAction SilentlyContinue

# If the policy does not exist, create it
if (-not $policy) {
    Write-Output "Policy '$policyName' does not exist. Creating it now..."

    # Create a new policy with the Manage, Send and Listen rights
    $policy = New-AzEventHubAuthorizationRule -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName -Rights $rights
    if (-not $policy) {
        throw "Error. Policy was not created."
    }
    Write-Output "Policy '$policyName' created successfully."
} else {
    Write-Output "Policy '$policyName' already exists."
}

if ("Send" -in $policy.Rights) {
    Write-Host "Authorization rule has required right: Send."
} else {
    throw "Authorization rule is missing Send right."
}

$keys = Get-AzEventHubKey -ResourceGroupName $resourceGroupName -NamespaceName $namespaceName -EventHubName $eventHubName -AuthorizationRuleName $policyName

if (-not $keys) {
    throw "Could not obtain Azure Event Hub Key. Script failed and will end now."
}
if (-not $keys.PrimaryKey) {
    throw "Could not obtain Primary Key. Script failed and will end now."
}

# Get the Primary Key of the Shared Access Policy
$primaryKey = ($keys.PrimaryKey) 
Write-Host $primaryKey

## Check that the primary key is not empty.

# Define a function to create a SAS token (similar to the C# code provided)
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-Output @"
-- Generated SAS Token --
$sasToken
-- End of generated SAS Token --
"@
}

Generate-SasToken

Enable and configure change event streaming

To enable and configure change event streaming, change the database context to the user database and then follow these steps:

If it's not already configured, set the database to the full recovery model.
Create a master key and a database scoped credential.
Enable event streaming.
Create the event stream group.
Add one or more tables to the event stream group.

The examples in this section demonstrate how to enable CES for the AMQP protocol and the Apache Kafka protocol.

The following are sample parameter values for the examples in this section:

@stream_group_name = N'myStreamGroup'
@destination_location = N'myEventHubsNamespace.servicebus.windows.net/myEventHubsInstance'
@partition_key_scheme = N'None'
Primary or secondary key value: Secret = 'BVFnT3baC/K6I8xNZzio4AeoFt6nHeK0i+ZErNGsxiw='
EXEC sys.sp_add_object_to_event_stream_group N'myStreamGroup', N'dbo.myTable'

Example: Stream to Azure Event Hubs via AMQP protocol (SAS token auth)

Replace values in angle brackets (<value>) with values for your environment.

USE <database name>

-- Create the Master Key with a password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

CREATE DATABASE SCOPED CREDENTIAL <CredentialName>
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<Generated SAS Token>'

EXEC sys.sp_enable_event_stream

EXEC sys.sp_create_event_stream_group
    @stream_group_name =      N'<EventStreamGroupName>',
    @destination_type =       N'AzureEventHubsAmqp',
    @destination_location =   N'<AzureEventHubsHostName>/<EventHubsInstance>',
    @destination_credential = <CredentialName>,
    @max_message_size_kb =    <MaxMessageSize>, 
    @partition_key_scheme =   N'<PartitionKeyScheme>'

EXEC sys.sp_add_object_to_event_stream_group
    N'<EventStreamGroupName>',
    N'<SchemaName>.<TableName>'

Example: Stream to Azure Event Hubs via AMQP protocol (Key value auth)

Replace values in angle brackets (<value>) with values for your environment.

USE <database name>

-- Create the Master Key with a password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

CREATE DATABASE SCOPED CREDENTIAL <CredentialName>
    WITH IDENTITY = '<Azure Event Hubs SAS Policy name>',
    SECRET = '<Primary or Secondary key value>'

EXEC sys.sp_enable_event_stream

EXEC sys.sp_create_event_stream_group
    @stream_group_name =      N'<EventStreamGroupName>',
    @destination_type =       N'AzureEventHubsAmqp',
    @destination_location =   N'<AzureEventHubsHostName>/<EventHubsInstance>',
    @destination_credential = <CredentialName>,
    @max_message_size_kb =    <MaxMessageSize>,
    @partition_key_scheme =   N'<PatitionKeyScheme>'

EXEC sys.sp_add_object_to_event_stream_group
    N'<EventStreamGroupName>',
    N'<SchemaName>.<TableName>'

Example: Stream to Azure Event Hubs via Apache Kafka protocol (Connection string auth)

Replace values in angle brackets (<value>) with values for your environment.

USE <database name>

-- Create the Master Key with a password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

CREATE DATABASE SCOPED CREDENTIAL credential1
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<Event Hubs Namespace – Primary or Secondary connection string>'

EXEC sys.sp_enable_event_stream

EXEC sys.sp_create_event_stream_group
    @stream_group_name =      N'<EventStreamGroupName>',
    @destination_type =       N'AzureEventHubsApacheKafka',
    @destination_location =   N'<AzureEventHubsHostName>:<port>/<EventHubsInstance>',
    @destination_credential = <CredentialName>,
    @max_message_size_kb =    <MaxMessageSize>,
    @partition_key_scheme =   N'<PatitionKeyScheme>'

EXEC sys.sp_add_object_to_event_stream_group
    N'<EventStreamGroupName>',
    N'<SchemaName>.<TableName>'

Example: Stream to Azure Event Hubs via Apache Kafka protocol (Key value auth)

Replace values in angle brackets (<value>) with values for your environment.

USE <database name>

-- Create the Master Key with a password.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Password>'

CREATE DATABASE SCOPED CREDENTIAL credential1
    WITH IDENTITY = '<Azure Event Hubs SAS Policy name>',
    SECRET = '<Primary or Secondary key value>' -- BVFnT3baC/K6I8xNZzio4AeoFt6nHeK0i+ZErNGsxiw=

EXEC sys.sp_enable_event_stream

EXEC sys.sp_create_event_stream_group
    @stream_group_name =      N'<EventStreamGroupName>',  -- myStreamGroup
    @destination_type =       N'AzureEventHubsApacheKafka',
    @destination_location =   N'<AzureEventHubsHostName>:<port>/<EventHubsInstance>', -- myEventHubsNamespace.servicebus.windows.net:9093/myEventHubsInstance
    @destination_credential = <CredentialName>,
    @max_message_size_kb =    <MaxMessageSize>,       -- 1024
    @partition_key_scheme =   N'<PatitionKeyScheme>'  -- N'None'

EXEC sys.sp_add_object_to_event_stream_group
    N'<EventStreamGroupName>',
    N'<SchemaName>.<TableName>' -- dbo.myTable

View CES configuration and function

In sys.databases, is_event_stream_enabled = 1 indicates that change event streaming is enabled for the database.

The following query returns all databases with change event streaming enabled:

SELECT * FROM sys.databases WHERE is_event_stream_enabled = 1

In sys.tables, is_replicated = 1 indicates a table is streamed, and sp_help_change_feed_table provides information about the table group and table metadata for change event streaming.

The following query returns all tables with change event streaming enabled, and provides metadata information:

SELECT name, is_replicated FROM sys.tables

EXEC sp_help_change_feed_table @source_schema = '<schema name>', @source_name = '<table name>'

Note

Currently, CES isn't supported on database that configured with change data capture (CDC), transactional replication, or Fabric Mirrored Databases for SQL Server.

CES stored procedures, system functions, and DMVs

The following table lists the stored procedures, system functions, and DMVs that are used to configure, disable and monitor change event streaming:

System object	Description
Configure CES
sys.sp_enable_event_stream	Enables CES for the current user database.
sys.sp_create_event_stream_group	Creates a stream group, which is a streaming configuration for a group of tables. The stream group also defines the destination and related details (such as authentication, message size, partitioning). The stream_group_id is automatically generated and displayed for the end user when the procedure completes.
sys.sp_add_object_to_event_stream_group	Adds a table to the stream group.
Disable CES
sys.sp_remove_object_from_event_stream_group	Removes a table from the stream group.
sys.sp_drop_event_stream_group	Drops the stream group. The stream group must not be in use.
sys.sp_disable_event_stream	Disables CES for the current user database.
Monitor CES
sys.dm_change_feed_errors	Returns delivery errors.
sys.dm_change_feed_log_scan_sessions	Returns information about log scan activity.
sys.sp_help_change_feed_settings	Provides the status and information of configured change event streaming.
sys.sp_help_change_feed	Monitors the current configuration of the change stream.
sys.sp_help_change_feed_table_groups	Returns metadata that is used to configure change event streaming groups.
sys.sp_help_change_feed_table	Provides the status and information of the streaming group and table metadata for change event streaming.

Limitations

Change event streaming (CES) has the following limitations:

Server-level and general limitations

CES isn't supported on SQL Server 2025 on Linux or SQL Server 2025 Express edition.
CES emits events only for data changes from INSERT, UPDATE, and DELETE DML statements.
CES doesn't handle schema changes (DDL operations), which means it won't emit events for DDL operations. However, DDL operations aren't blocked, so if executed, the schema of subsequent DML events reflect the updated table structure. Users are expected to gracefully handle events with the updated schema.
When JSON is the specified output format, large event messages might be split at approximately 25% of the configured maximum message size per stream group. This limitation doesn't apply to the binary output type.
If a message exceeds the Azure Event Hubs message size limit, the failure is currently only observable via Extended Events.
Table and column renames are blocked for tables configured for CES. Database renames are allowed.

Database-level limitations

CES is only supported on databases configured with the full recovery model.
CES isn't supported on databases configured with Fabric Mirrored Databases for SQL Server, transactional replication, change data capture, or Azure Synapse Link. Change tracking is supported on databases configured with CES.
CES can only stream from writable primary databases. Secondary databases that are part of Always On availability groups or that use the Managed Instance Link can't be configured as streaming sources.
CES can't be enabled on views or materialized views.

Table-level limitations

A table can belong to only one streaming group. Streaming the same table to multiple destinations isn't supported.
Only user tables can be configured for CES. System tables aren't supported.
You can configure up to 4,096 stream groups. Each stream group can include up to 40,000 tables.
While CES is enabled on a table, primary key constraint can't be added to, or dropped from, that table.
ALTER TABLE SWITCH PARTITION isn't supported on tables configured for CES.
TRUNCATE TABLE isn't supported on tables enabled for CES.
CES doesn't support tables that use any of the following features:
- Clustered columnstore indexes
- Temporal history tables or ledger history tables
- Always Encrypted
- In-memory OLTP (memory-optimized tables)
- Graph tables
- External tables

Column-level limitations

The following data types aren't supported by CES. Columns of these types are skipped by streaming:
- json
- image
- text / ntext
- xml
- rowversion / timestamp
- sql_variant
- User-defined types (UDT)
- geometry
- geography
- vector

Permissions in the source database

For row-level security, CES emits changes from all rows, regardless of user permissions.
Dynamic data masking doesn't apply to data sent via CES. Data is streamed unmasked, even if masking is configured.
CES doesn't emit events related to object-level permission changes (for example, granting permissions to specific columns).

Share via

Configure change event streaming

Overview

Prerequisites

Configure Azure Event Hubs

Install required modules

Connect to Azure

Define a policy

Create SAS token for a new or existing policy

Enable and configure change event streaming

Example: Stream to Azure Event Hubs via AMQP protocol (SAS token auth)

Example: Stream to Azure Event Hubs via AMQP protocol (Key value auth)

Example: Stream to Azure Event Hubs via Apache Kafka protocol (Connection string auth)

Example: Stream to Azure Event Hubs via Apache Kafka protocol (Key value auth)

View CES configuration and function

CES stored procedures, system functions, and DMVs

Limitations

Server-level and general limitations

Database-level limitations

Table-level limitations

Column-level limitations

Permissions in the source database

Related content

Feedback

Additional resources