Add SQL Server on VM DB (CDC) source to an eventstream

This article shows you how to add a SQL Server on VM DB Change Data Capture (CDC) source to an eventstream.

The SQL Server on VM DB (CDC) source connector for Fabric event streams allows you to capture a snapshot of the current data in a SQL Server database on VM. Currently, SQL Server on VM DB (CDC) is supported from the following services where the databases can be accessed publicly:

• SQL Server on Azure Virtual Machines
• Amazon Relational Database Service (RDS) for SQL Server
• Amazon RDS Custom for SQL Server
• Google Cloud SQL for SQL Server

Once the SQL Server on VM DB (CDC) source is added to the eventstream, it monitors and records future row-level changes, which can then be processed in real-time and sent to various destinations for further analysis.

Prerequisites

• Access to a workspace in the Fabric capacity license mode (or) the Trial license mode with Contributor or higher permissions.
• A running SQL Server on VM database.
• Your SQL Server on VM database must be configured to allow public access. If it resides in a protected network, connect to it by using Eventstream connector virtual network injection.
• Enable CDC in your SQL Server on VM database by running the stored procedure sys.sp_cdc_enable_db. For details, see Enable and disable change data capture.

Enable CDC in your SQL Server on VM database

1. Enable CDC for the database.

   EXEC sys.sp_cdc_enable_db; 
   

2. Enable CDC for a table using a gating role option. In this example, MyTable is the name of the SQL table.

   EXEC sys.sp_cdc_enable_table 
    @source_schema = N'dbo', 
    @source_name = N'MyTable', 
    @role_name = NULL 
   GO 
   

   After the query executes successfully, you enabled CDC in your SQL Server on VM database.

   👁 A screenshot showing CDC is enabled.
   

Add SQL Server on VM database as a source

If you haven't added any source to your eventstream yet, select the Connect data sources tile. You can also select Add source > Connect data sources on the ribbon.

👁 Screenshot that shows the selection of the tile for using an external source.

If you're adding the source to an already published eventstream, switch to Edit mode. On the ribbon, select Add source > Connect data sources.

👁 Screenshot that shows selections for adding external sources.

On the Select a data source page, search for and select Connect on the SQL Server on VM DB (CDC) tile.

Configure and connect to SQL Server on VM database

Ingest change data from SQL Server on VM databases with automatic table schema registration via CDC into Eventstream.

1. On the Connect page, select New connection.

   👁 Screenshot that shows the selection of New connection link on the Connect page.
   

2. In the Connection settings section, enter the following values for your SQL Server on VM:

   • Server: Enter the IP address or domain name of your VM, and then add a colon and the port. For example, if your IP address is xx.xxx.xxx.xxx and the port is 1433, then you should enter xx.xxx.xxx.xxx:1433 in the Server field. If the port isn't specified, the default port value 1433 is used.

   • Database: Enter the name of the database that you want to connect to on your SQL Server on VM.

     👁 Screenshot that shows the Connection settings section of the Connect page.
     

3. Scroll down, and in the Connection credentials section, follow these steps.

   • For Connection name, enter a name for the connection.

   • For Authentication kind, select Basic.

     Note

     Currently, Fabric Eventstream supports only Basic authentication.

   • Enter Username and Password for the SQL Server on VM.

     Note

     Don't select the option: Use encrypted connection.

     👁 Screenshot that shows the Connection credentials section of the Connect page.
     

4. Select Connect at the bottom of the page.

5. Now, on the Connect page, select All tables, or Enter table name(s). If you select the latter, specify tables using a comma-separated list of full table identifiers (schemaName.tableName) or valid regular expressions. For example:

   • Use dbo.test.* to select all tables whose names start with dbo.test.
   • Use dbo\.(test1|test2) to select dbo.test1 and dbo.test2.

   You can mix both formats using commas. The total character limit for the entire entry is 102,400 characters.

6. You can expand Advanced settings to configure the Decimal handling mode, which specifies how the connector handles DECIMAL and NUMERIC column values:

   • Precise: Represents values using exact decimal types (for example, Java BigDecimal) to ensure full precision and accuracy in data representation.
   • Double: Converts values to double-precision floating-point numbers. This setting improves usability and performance but can result in a loss of precision.
   • String: Encodes values as formatted strings. This setting makes them easy to consume in downstream systems but loses semantic information about the original numeric type.

Stream or source details

1. On the Connect page, follow one of these steps based on whether you're using Eventstream or Real-Time hub.

   • Eventstream:

     In the Source details pane to the right, follow these steps:

     1. For Source name, select the Pencil button to change the name.

     2. Notice that Eventstream name and Stream name are read-only.

   • Real-Time hub:

     In the Stream details section to the right, follow these steps:

     1. Select the Fabric workspace where you want to create the eventstream.

     2. For Eventstream name, select the Pencil button, and enter a name for the eventstream.

     3. The Stream name value is automatically generated for you by appending -stream to the name of the eventstream. This stream appears on the real-time hub's All data streams page when the wizard finishes.

2. Select Next at the bottom of the Configure page.

Review and connect

On the Review + connect screen, review the summary, and select Add (Eventstream) or Connect (Real-Time hub).

Schema handling page

1. In the Schema handling step, choose one of the following options:

   • Analytics-ready events & auto-updated schema (DeltaFlow Preview): The connector transforms raw CDC events into analytics-ready streams that mirror your source table structure. DeltaFlow enriches events with metadata such as change type (insert, update, or delete) and timestamps, and automatically manages destination tables and schema evolution.
   • Raw CDC events: The connector ingests and makes available the raw CDC events. Optionally, the connector can autodiscover table schemas and register them in the schema registry. Use this option when you want schema awareness without DeltaFlow transformation.

   Note

   The following screenshot shows Azure SQL Database CDC. The schema handling options are the same for all supported CDC source connectors.

   👁 Screenshot showing the schema handling step with DeltaFlow and Raw CDC event options for a CDC source connector.
   

2. Enable event schema association.

3. For Workspace, select a Fabric workspace for the schema set.

4. For Schema set, + Create is selected by default, which creates a new schema set. You can change it to select an existing event schema set.

5. If you selected the + Create option in the previous step, enter a name for the schema set.

6. On the Review + create screen, review the summary, and select Add (Eventstream) or Connect (Real-Time hub).

   👁 Screenshot that shows the selection of the Add button.
   

   For all tables or selected tables in the SQL Server on VM database, the connector autodiscovers and creates schemas, and registers them with the schema registry.

DeltaFlow: Analytics-ready event transformation (Preview)

When you enable Analytics-ready events & auto-updated schema (DeltaFlow), the connector provides the following capabilities:

• Analytics-ready event shape: Raw Debezium CDC events are transformed into a tabular format that mirrors the source table structure. Events are enriched with metadata columns including the change type (insert, update, or delete) and the event timestamp.
• Automatic destination table management: When you route DeltaFlow-enabled streams to a supported destination like an eventhouse, destination tables are automatically created to match the source table schema. You don't need to manually create or configure destination tables.
• Schema evolution handling: When source database tables change (for example, new columns are added or tables are created), DeltaFlow automatically detects the changes, updates the registered schemas, and adjusts the destination tables accordingly. This feature minimizes manual intervention caused by schema changes.

For details on how DeltaFlow transforms raw CDC events into analytics-ready output, including operation types and metadata columns, see DeltaFlow output transformation.

View updated eventstream

You can see the SQL Server on VM DB CDC source added to your eventstream in Edit mode.

👁 A screenshot of the added SQL Server on VM DB CDC source in Edit mode with extended features.

To implement this newly added SQL Server on VM DB CDC source, select Publish. After you complete these steps, your SQL Server on VM DB CDC source is available for visualization in the Live view.

👁 A screenshot of the added SQL Server on VM DB CDC source in Live view mode with extended features.

Configure Eventstream destinations to use schemas

Currently, only the Eventhouse, custom endpoint, and derived stream destinations are supported for Eventstreams with associated schemas. This section shows you how to add and configure an Eventhouse destination when extended features (like schema support) are enabled for the eventstream.

Configure a schema for a custom endpoint destination

1. Select Transform events or add destination, and then select CustomEndpoint.

2. On the Custom endpoint pane, specify a name for the destination.

3. For Input schema, select the schema for events. You make a selection in this box when you enable schema support for an eventstream.

👁 Screenshot that shows the pane for configuring a custom endpoint.

For detailed steps on configuring a custom endpoint destination, see Add a custom endpoint or custom app destination to an eventstream.

Configure schemas for an eventhouse destination

1. Select Transform events or add destination, and then select Eventhouse.

2. On the Eventhouse pane, configure the following schema-related settings:

   1. For Input schema, select one or more schemas from the dropdown list.

      👁 Screenshot that shows the eventhouse configuration pane with an input schema selected.
      

      Note

      If you selected the Dynamic schema via headers option when configuring an Event Hubs source, you might have configured multiple schemas for the source and mapped them to various properties and their values.

   2. For Table creation method, select A single table with all schemas combined or Separate tables for each schema, depending on your requirements.

      👁 Screenshot that shows the eventhouse configuration pane with table creation methods.
      

   3. For Write data with, select one of the following options:

      • Payload only: Write extracted payload data to the table. If there are multiple input schemas, data is sent to multiple tables.
      • Metadata and payload: Write metadata and payload data to a single table. Example columns include source , subject, type, and data.

      👁 Screenshot that shows the eventhouse configuration pane with the options for writing data.
      

For detailed steps on configuring an eventhouse destination, see Add an eventhouse destination to an eventstream.

View DeltaFlow analytics-ready output (Preview)

If you enabled Analytics-ready events & auto-updated schema (DeltaFlow), the destination tables are automatically created in a shape that mirrors your source database tables. Each table includes the original columns along with metadata columns for the change type and timestamp.

👁 Screenshot showing the Eventhouse destination tables created by DeltaFlow in analytics-ready shape.

You can query these tables using Kusto Query Language (KQL) or other analytics tools without needing to parse raw Debezium CDC payloads.

Related content

Other connectors:

• Amazon Kinesis Data Streams
• Azure Cosmos DB
• Azure Event Hubs
• Azure IoT Hub
• Azure SQL Database Change Data Capture (CDC)
• Confluent Kafka
• Custom endpoint
• Google Cloud Pub/Sub
• PostgreSQL Database CDC
• Sample data
• Azure Blob Storage events
• Fabric workspace event