Event Stream in Data Factory connection for SQL Server on VM DB (CDC)

[cfccai]

hello 

I have a requirement that Near real time data replication(CDC) from Sql server on VM to Fabric.  As far as I know, Mirror does not support  Sql server on VM currently(the customers database in on VM), while it support Azure SQL DB I know. So I turn to Eventstream. Fabric published 'Enhanced capabilities' for Eventstream which support  SQL Server on VM DB (CDC) connection , although it is in Preview.

 While when I was trying to use that ' SQL Server on VM DB (CDC)', it doesn't provide an entry to let me input the data gateway.  I used that data gateway to connect to the SQL server on VM succcessfully by both Fabric Dataflow gen2 and copy data in pipeline.

It really confused me. I read some blogs and below says that the SQL server on VM must be allowed public access. 

I cannot get it -- The customer's database is always in a private environment to keep data secure, so they keep their database on the VM, even not on Azure, they just want to use their own VM database. 

And now, Fabric provides the function to let me connect to SQL server on VM, but it is not actuall 'on VM', while it must be allow public access.

Add SQL Server Change Data Capture as a source to eventstream - Microsoft Fabric | Microsoft Learn

I am not sure if I had any misunderstanding on that 'SQL server on VM '.  If anyone can help me understand that will be much appreicate. 

[v-yilong-msft · Accepted solution]

Hi @cfccai ,

Here are some of my personal thoughts on your question:

The current implementation of SQL Server on VM DB (CDC) in Fabric's Eventstream requires the SQL Server to be publicly accessible. This means the server must have a public IP address or domain name that can be accessed over the internet.

As you mentioned, many customers prefer to keep their databases in a private environment for security reasons. This is a valid concern, and exposing the database to the public internet might not be acceptable for all organizations.

While the Eventstream feature currently does not provide an entry for a data gateway directly, you can use a data gateway with other Fabric services like Dataflow Gen2 and pipelines. This might indicate that the feature is still evolving, and support for data gateways in Eventstream might be added in the future.

Consider using Azure Private Link or a VPN to securely connect your on-premises SQL Server to Azure services without exposing it to the public internet. This setup allows you to maintain a private connection while still leveraging Azure services.

Since it's still in Preview stage so far, I'm sure further optimization of the current deficiencies will be done in the next update.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cfccai]

Thanks very much for your explanation, Yilong. 

As you mentioned, if we consider Azure Private Link or a VPN to get the private connection, can you please advise any Azure service that can match our requirement -- a low latency service, like 5mins, that copy data from SQL server to Fabric Onelake(Lakehouse)?

Thanks a lot.

[v-yilong-msft]

Hi @cfccai ,

This brings us back to the original question.

Currently the only way to do this in Fabric is through Mirroring, a Preview feature.

However, it has a lot of issues, so we'll have to wait for an update to it.

If you are in Azure, you can use the following two features to achieve your needs:

Azure Private Link: This service allows you to connect to Azure services (like Azure Storage, SQL Database, and more) privately and securely over a private endpoint in your virtual network. It ensures that traffic between your virtual network and the service stays on the Microsoft backbone network, eliminating exposure to the public internet.

Azure VPN Gateway: This service enables you to establish secure, cross-premises connectivity between your virtual network and on-premises locations over the public internet. It supports both site-to-site VPNs and point-to-site VPNs, providing flexibility depending on your specific needs.

Of course you can also check out these two official documents below:

Troubleshoot Azure Private Link Service connectivity problems | Microsoft Learn

Azure Private Link frequently asked questions (FAQ) | Microsoft Learn

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.