Edit

Share via

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

  • Article

This article shows you how to add a SQL Server on VM DB 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. 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.

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

Note

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

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

  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 SQL Server on VM database

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

      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.

      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 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 + create screen, review the summary, and then select Connect.

    Screenshot that shows the selection of the Add button.

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 the Publish button highlighted.

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.

Related content

Other connectors: