Edit

Share via

Add Azure SQL Managed Instance CDC source to an eventstream

  • Article

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.

Note

This source is not supported in the following regions of your workspace capacity: West US3, Switzerland West.

Prerequisites

  • Access to a workspace in the Fabric capacity license mode (or) the Trial license mode 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.

Note

The maximum number of sources and destinations for one eventstream is 11.

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.

Screenshot that shows the Networking page with Public endpoint option enabled.

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.

    A screenshot of showing cdc has enabled.

Add Azure SQL Managed Instance CDC as a source

  1. In Fabric Real-Time Intelligence, select Eventstream to create a new eventstream.

    A screenshot of creating a new eventstream.

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

    A screenshot of selecting Add external source.

Configure and connect to Azure SQL Managed Instance CDC

  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 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.

      Screenshot that shows the Networking page with Endpoint information.

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

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

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

  4. Select Connect at the bottom of the page.

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

  6. Select Next.

    Screenshot that shows selection of All tables option.

  7. On the Review + connect page, review the summary, and then select Connect.

    Screenshot that shows the selection of the Add button.

View updated eventstream

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

A screenshot of the added Azure SQL MI DB CDC source in Edit mode with the Publish button highlighted.

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.

A screenshot of the added Azure SQL MI DB CDC source in Live view mode.

Related content

Other connectors: