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 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 RDS for SQL Server
  • Amazon RDS Custom for SQL Server
  • Google Cloud SQL for SQL Server

Note

AWS RDS SQL Server, AWS RDS Custom SQL Server, and Google Cloud SQL SQL Server do not support the Express version. Make sure you are using an appropriate edition of SQL Server for CDC.

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.

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: