Add SQL Server on VM DB (CDC) as source in Real-Time hub (preview)

This article describes how to add SQL Server on VM DB (CDC) as an event source in Fabric Real-Time hub.

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. The connector then monitors and records any future row-level changes to the data. Once these changes are captured in the eventstream, you can process this data in real-time and send it to various destinations for further processing or analysis.

Prerequisites

• Access to the Fabric premium workspace 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.
• 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.

Get events from SQL Server on VM DB (CDC)

You can get events from an SQL Server on VM DB (CDC) into Real-Time hub Using the Add source experience.

Launch Add source experience

1. Sign in to Microsoft Fabric.

2. Select Real-Time hub on the left navigation bar.

3. On the Real-Time hub page, select + Add source in the top-right corner of the page.

   

Add SQL Server on VM DB CDC as a source

1. On the Select a data source screen, select SQL Server on VM 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 publicly accessible 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.

     

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.

     Note

     Don't select the option: Use encrypted connection.

     

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.

   

View data stream details

1. On the Review and create page, if you select Open eventstream, the wizard opens the eventstream that it created for you with the selected SQL Server on VM DB (CDC) as a source. To close the wizard, select Close or X* in the top-right corner of the page.
2. In Real-Time hub, select All data streams. To see the new data stream, refresh the All data streams page. For detailed steps, see View details of data streams in Fabric Real-Time hub.

Related content

To learn about consuming data streams, see the following articles:

• Process data streams
• Analyze data streams
• Set alerts on data streams