59_Change_Event_Streaming.sql

/**************************************************************
 * SQL Server 2025 Change Event Streaming Tutorial
 * Description: This script demonstrates Change Event Streaming (CES)
 *              introduced in SQL Server 2025 (17.x). It covers:
 *              - Enabling CES for a database
 *              - Creating event stream groups
 *              - Adding tables to stream groups
 *              - Configuring Azure Event Hubs integration
 *              - Monitoring and troubleshooting CES
 *              - Understanding CloudEvents format
 *              - Best practices for streaming data changes
 **************************************************************/

-------------------------------------------------
-- Region: 1. Introduction and Setup
-------------------------------------------------
USE master;
GO

/*
  Create a test database for Change Event Streaming examples.
*/
IF DB_ID('CESDemo') IS NOT NULL
BEGIN
    ALTER DATABASE CESDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CESDemo;
END
GO

CREATE DATABASE CESDemo;
GO

USE CESDemo;
GO

-- Set compatibility level for SQL Server 2025 (17.x)
ALTER DATABASE CESDemo SET COMPATIBILITY_LEVEL = 170;
GO

-- Enable preview features (required for CES in SQL Server 2025)
ALTER DATABASE SCOPED CONFIGURATION SET PREVIEW_FEATURES = ON;
GO

-------------------------------------------------
-- Region: 2. Understanding Change Event Streaming
-------------------------------------------------
/*
  Change Event Streaming (CES) captures and publishes incremental DML changes
  (INSERT, UPDATE, DELETE) to Azure Event Hubs in near real-time.
  
  Key Concepts:
  - Streams data as CloudEvents (JSON or Avro Binary format)
  - Captures schema, previous values, and new values
  - Requires Azure Event Hubs namespace and instance
  - Supports up to 4,096 stream groups
  - Supports up to 40,000 tables per group
  
  Prerequisites:
  1. Azure Event Hubs namespace and instance
  2. db_owner role or CONTROL DATABASE permission
  3. PREVIEW_FEATURES enabled (SQL Server 2025 only)
  4. Proper firewall/network configuration for Azure Event Hubs
*/

-------------------------------------------------
-- Region: 3. Creating Sample Tables
-------------------------------------------------
/*
  Create tables that we'll configure for streaming.
*/

-- 3.1 Create a sales orders table
CREATE TABLE dbo.Orders
(
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 DEFAULT SYSDATETIME(),
    TotalAmount DECIMAL(10,2),
    Status NVARCHAR(20) DEFAULT 'Pending',
    CreatedDate DATETIME2 DEFAULT SYSDATETIME(),
    ModifiedDate DATETIME2 DEFAULT SYSDATETIME()
);
GO

-- 3.2 Create an order details table
CREATE TABLE dbo.OrderDetails
(
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES dbo.Orders(OrderID)
);
GO

-- 3.3 Create a product inventory table
CREATE TABLE dbo.ProductInventory
(
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    QuantityOnHand INT,
    LastUpdated DATETIME2 DEFAULT SYSDATETIME()
);
GO

-- Insert sample data
INSERT INTO dbo.ProductInventory (ProductID, ProductName, QuantityOnHand)
VALUES 
    (1, 'Laptop', 50),
    (2, 'Mouse', 200),
    (3, 'Keyboard', 150);
GO

-------------------------------------------------
-- Region: 4. Enabling Change Event Streaming
-------------------------------------------------
/*
  Enable CES for the current database.
  This is a prerequisite before creating stream groups.
*/

-- 4.1 Check if CES is enabled
SELECT 
    name AS DatabaseName,
    is_event_stream_enabled AS CESEnabled
FROM sys.databases
WHERE name = 'CESDemo';
GO

-- 4.2 Enable CES for the database
BEGIN TRY
    EXEC sys.sp_enable_event_stream;
    PRINT 'Change Event Streaming enabled successfully.';
END TRY
BEGIN CATCH
    PRINT 'Error enabling CES: ' + ERROR_MESSAGE();
    PRINT 'Ensure preview features are enabled and you have proper permissions.';
END CATCH
GO

-- 4.3 Verify CES is enabled
SELECT 
    name AS DatabaseName,
    is_event_stream_enabled AS CESEnabled
FROM sys.databases
WHERE name = 'CESDemo';
GO

-------------------------------------------------
-- Region: 5. Creating Event Stream Groups
-------------------------------------------------
/*
  A stream group defines the destination (Azure Event Hubs) and configuration
  for a group of tables. Each group has a unique stream_group_id.
  
  Note: The following examples use placeholder values.
  Replace with your actual Azure Event Hubs connection details.
*/

-- 5.1 Create master key (required for credentials)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';
GO

-- 5.2 Create database scoped credential for Event Hubs authentication
/*
  Option 1: Using Managed Identity (recommended for Azure resources)
  
  CREATE DATABASE SCOPED CREDENTIAL [CESEventHubCredential]
  WITH IDENTITY = 'Managed Identity',
       SECRET = '{"resourceid":"https://eventhubs.azure.net"}';
  GO
*/

/*
  Option 2: Using Shared Access Signature (SAS)
  
  CREATE DATABASE SCOPED CREDENTIAL [CESEventHubCredential]
  WITH IDENTITY = 'Shared Access Signature',
       SECRET = 'your-event-hub-sas-connection-string';
  GO
*/

-- 5.3 Create a stream group
/*
  Note: Replace placeholders with your actual Event Hubs details.
  
  DECLARE @stream_group_id BIGINT;
  
  EXEC sys.sp_create_event_stream_group
      @group_name = N'OrdersStreamGroup',
      @event_hub_connection_string = N'Endpoint=sb://<namespace>.servicebus.windows.net/;SharedAccessKeyName=<keyname>;SharedAccessKey=<key>',
      @event_hub_name = N'<event-hub-name>';
  
  -- The procedure will display the generated stream_group_id
  PRINT 'Stream group created successfully.';
*/
GO

-------------------------------------------------
-- Region: 6. Adding Tables to Stream Groups
-------------------------------------------------
/*
  Add tables to the stream group to begin streaming their changes.
*/

-- 6.1 Add Orders table to stream group
/*
  EXEC sys.sp_add_object_to_event_stream_group
      @group_name = N'OrdersStreamGroup',
      @schema_name = N'dbo',
      @object_name = N'Orders';
  
  PRINT 'Orders table added to stream group.';
*/
GO

-- 6.2 Add OrderDetails table to stream group
/*
  EXEC sys.sp_add_object_to_event_stream_group
      @group_name = N'OrdersStreamGroup',
      @schema_name = N'dbo',
      @object_name = N'OrderDetails';
  
  PRINT 'OrderDetails table added to stream group.';
*/
GO

-- 6.3 Add ProductInventory table to stream group
/*
  EXEC sys.sp_add_object_to_event_stream_group
      @group_name = N'OrdersStreamGroup',
      @schema_name = N'dbo',
      @object_name = N'ProductInventory';
  
  PRINT 'ProductInventory table added to stream group.';
*/
GO

-------------------------------------------------
-- Region: 7. Verifying Configuration
-------------------------------------------------
/*
  Verify which tables are configured for streaming.
*/

-- 7.1 Check which tables are replicated (streamed)
SELECT 
    SCHEMA_NAME(schema_id) AS SchemaName,
    name AS TableName,
    is_replicated AS IsStreaming
FROM sys.tables
WHERE is_replicated = 1;
GO

-- 7.2 Get detailed information about streaming configuration
/*
  EXEC sp_help_change_feed_table
      @source_schema = 'dbo',
      @source_name = 'Orders';
*/
GO

-------------------------------------------------
-- Region: 8. Testing Change Event Streaming
-------------------------------------------------
/*
  Generate data changes and observe them being streamed.
*/

-- 8.1 Insert new orders (generates INSERT events)
INSERT INTO dbo.Orders (CustomerID, TotalAmount, Status)
VALUES 
    (1001, 299.99, 'Pending'),
    (1002, 450.50, 'Pending');
GO

-- 8.2 Update order status (generates UPDATE events)
UPDATE dbo.Orders
SET Status = 'Processing',
    ModifiedDate = SYSDATETIME()
WHERE OrderID = 1;
GO

-- 8.3 Delete an order (generates DELETE events)
DELETE FROM dbo.Orders
WHERE OrderID = 2;
GO

-- 8.4 Add order details
DECLARE @orderId INT = (SELECT TOP 1 OrderID FROM dbo.Orders WHERE Status = 'Pending');

INSERT INTO dbo.OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES 
    (@orderId, 1, 1, 1299.99),
    (@orderId, 2, 2, 29.99);
GO

-- 8.5 Update inventory (generates UPDATE events)
UPDATE dbo.ProductInventory
SET QuantityOnHand = QuantityOnHand - 10,
    LastUpdated = SYSDATETIME()
WHERE ProductID = 1;
GO

-------------------------------------------------
-- Region: 9. Monitoring Change Event Streaming
-------------------------------------------------
/*
  Monitor CES activity and troubleshoot issues.
*/

-- 9.1 Check for CES errors
SELECT 
    error_id,
    error_time,
    error_type,
    error_message,
    source_task
FROM sys.dm_change_feed_errors
ORDER BY error_time DESC;
GO

-- 9.2 Monitor log scan sessions
SELECT 
    session_id,
    start_time,
    end_time,
    error_count,
    commands_scanned
FROM sys.dm_change_feed_log_scan_sessions
ORDER BY start_time DESC;
GO

-- 9.3 Create Extended Events session for CES monitoring
/*
  CREATE EVENT SESSION CESMonitoring
  ON SERVER
  ADD EVENT sqlserver.synapse_link_error
  ADD TARGET package0.event_file (SET filename=N'C:\Logs\CESMonitoring.xel')
  WITH (STARTUP_STATE = OFF);
  GO
  
  -- Start the session
  ALTER EVENT SESSION CESMonitoring ON SERVER STATE = START;
  GO
*/

-------------------------------------------------
-- Region: 10. Understanding CloudEvents Format
-------------------------------------------------
/*
  CES sends changes as CloudEvents (JSON or Avro Binary).
  
  Example CloudEvent structure (JSON):
  {
    "specversion": "1.0",
    "type": "com.microsoft.sqlserver.change",
    "source": "sql://servername/databasename/schema/tablename",
    "id": "unique-event-id",
    "time": "2025-12-25T10:30:45Z",
    "datacontenttype": "application/json",
    "data": {
      "operation": "INSERT", // or UPDATE, DELETE
      "schema": {...},
      "before": null, // Previous values (for UPDATE/DELETE)
      "after": {...}  // New values (for INSERT/UPDATE)
    }
  }
  
  The 'before' field contains old values for UPDATE and DELETE operations.
  The 'after' field contains new values for INSERT and UPDATE operations.
*/

-------------------------------------------------
-- Region: 11. Removing Tables and Disabling CES
-------------------------------------------------
/*
  Remove tables from streaming or disable CES entirely.
*/

-- 11.1 Remove a table from the stream group
/*
  EXEC sys.sp_remove_object_from_event_stream_group
      @group_name = N'OrdersStreamGroup',
      @schema_name = N'dbo',
      @object_name = N'ProductInventory';
  
  PRINT 'ProductInventory table removed from stream group.';
*/
GO

-- 11.2 Drop the stream group (must not be in use)
/*
  EXEC sys.sp_drop_event_stream_group
      @group_name = N'OrdersStreamGroup';
  
  PRINT 'Stream group dropped.';
*/
GO

-- 11.3 Disable CES for the database
/*
  EXEC sys.sp_disable_event_stream;
  
  PRINT 'Change Event Streaming disabled.';
*/
GO

-------------------------------------------------
-- Region: 12. Best Practices and Considerations
-------------------------------------------------
/*
  Best Practices:
  
  1. Start/Stop Behavior:
     - If CES stops, it doesn't resume automatically
     - Changes during downtime are NOT captured
     - Manually restart CES to continue streaming
  
  2. No Initial Snapshot:
     - CES only streams changes AFTER it's enabled
     - Existing data is NOT streamed initially
  
  3. Performance:
     - Monitor log truncation (CES prevents it until events are delivered)
     - Failed deliveries will block log truncation
     - Consider impact on transaction log size
  
  4. Monitoring:
     - Use sys.dm_change_feed_errors for errors
     - Use sys.dm_change_feed_log_scan_sessions for activity
     - Create Extended Events sessions for detailed monitoring
  
  5. Limitations:
     - Row-level security: CES emits all rows regardless of permissions
     - Dynamic data masking: Data is streamed unmasked
     - Unsupported data types: geography, geometry, hierarchyid, 
       sql_variant, text/ntext, vector, xml, UDTs
  
  6. Capacity:
     - Maximum 4,096 streaming groups
     - Maximum 40,000 tables per group
  
  7. Network:
     - Ensure firewall allows access to Azure Event Hubs
     - Configure Network Security Perimeter if applicable
  
  8. Authentication:
     - Prefer Managed Identity for Azure resources
     - Use SAS tokens with appropriate expiration
     - Rotate credentials regularly
*/

-------------------------------------------------
-- Region: 13. Real-World Use Cases
-------------------------------------------------
/*
  Common scenarios for Change Event Streaming:
  
  1. Real-time Analytics:
     - Stream changes to data warehouses (Azure Synapse, Fabric)
     - Enable near real-time reporting and dashboards
  
  2. Event-Driven Architectures:
     - Trigger downstream processes based on data changes
     - Integrate with Azure Functions, Logic Apps
  
  3. Data Synchronization:
     - Keep multiple systems in sync
     - Replicate data to caching layers (Redis, Cosmos DB)
  
  4. Audit and Compliance:
     - Capture all data changes for audit trails
     - Store change history in immutable storage
  
  5. Microservices Integration:
     - Share data changes across microservices
     - Implement CQRS patterns
  
  6. Machine Learning Pipelines:
     - Stream training data updates
     - Trigger model retraining on data changes
*/

-------------------------------------------------
-- Region: 14. Troubleshooting Common Issues
-------------------------------------------------
/*
  Common Issues and Solutions:
  
  1. CES won't enable:
     - Check PREVIEW_FEATURES is enabled
     - Verify db_owner or CONTROL DATABASE permissions
     - Ensure SQL Server 2025 (17.x) or Azure SQL Database
  
  2. Events not streaming:
     - Verify stream group is created successfully
     - Check tables are added to stream group (is_replicated = 1)
     - Review sys.dm_change_feed_errors for delivery issues
     - Verify Azure Event Hubs connectivity
  
  3. Log truncation blocked:
     - Check for failed event deliveries
     - Review sys.dm_change_feed_log_scan_sessions
     - Verify Event Hubs is accepting messages
     - Consider stopping CES if issue persists
  
  4. Performance degradation:
     - Monitor transaction log growth
     - Check Event Hubs throughput units
     - Review error counts in DMVs
     - Consider reducing frequency of changes or batch updates
  
  5. Authentication failures:
     - Verify database scoped credential is correct
     - Check SAS token hasn't expired
     - Ensure Managed Identity has proper permissions
     - Test Event Hubs connectivity independently
*/

-------------------------------------------------
-- Region: 15. Cleanup
-------------------------------------------------
/*
  Optional: Clean up the demo database.
*/
-- USE master;
-- GO
-- DROP DATABASE IF EXISTS CESDemo;
-- GO