Add Azure SQL Managed Instance CDC source to an eventstream (preview)

This article shows you how to add an Azure SQL Managed Instance Change Data Capture (CDC) source to an eventstream.

The Azure SQL Managed Instance CDC source connector for Microsoft Fabric event streams allows you to capture a snapshot of the current data in a SQL Managed Instance database. The connector then monitors and records any future row-level changes to this data. Once the changes are captured in the eventstream, you can process this CDC data in real-time and send it to different destinations within Fabric for further processing or analysis.

Prerequisites

• Access to the Fabric premium workspace with Contributor or higher permissions.
• A running Azure SQL Managed Instance database.
• Your Azure SQL Managed Instance must enable public endpoint and not be behind a firewall or secured in a virtual network.
• CDC enabled in your Azure SQL Managed Instance by running the stored procedure sys.sp_cdc_enable_db. For details, see Enable and disable change data capture.

Enable public endpoint in your Azure SQL managed instance

Go to the Azure portal, open your Azure SQL managed instance, select Networking, and enable public endpoint.

Enable CDC in your Azure SQL managed instance

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 Azure SQL managed instance.

   

Add Azure SQL Managed Instance CDC as a source

1. In Fabric Real-Time Intelligence, select Eventstream to create a new eventstream. Make sure the Enhanced Capabilities (preview) option is enabled.

   

2. On the next screen, select Add external source.

   

Configure and connect to Azure SQL Managed Instance CDC

1. On the Select a data source screen, select Azure SQL MI DB (CDC) as the data source.

   

2. On the Connect page, select New connection.

   

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

   • Server: Enter the Endpoint from the Azure portal, replacing the comma between the host and port with a colon. For example, if your Endpoint is xxxxx.public.xxxxxx.database.windows.net,3342, then you should enter xxxxx.public.xxxxxx.database.windows.net:3342 in the Server field.

     

   • Database: Enter the name of the database you want to connect to within your Azure SQL Managed Instance.

     

4. 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 event streams supports only Basic authentication.

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

5. Select Connect at the bottom of the page.

6. Now, on the Connect page, select All tables, or enter the table names separated by commas, such as: dbo.table1, dbo.table2.

7. Select Next.

   

8. On the Review and create screen, review the summary, and then select Add.

   

You can see the Azure SQL MI DB (CDC) source added to your eventstream in Edit mode.

To implement this newly added Azure SQL Managed Instance source, select Publish. After you complete these steps, your Azure SQL Managed Instance source is available for visualization in the Live view.

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