Stream RealTime data from an On Prem Microsoft Server to a Azure Data Lake.

Question

There is a need for Real Time data to be collected from a On Prem SQL Server.

This means ADF is not suitable because it only does batch.

Azure Streaming Analytics only connects to Azure SQL Server and not on prem.

Has anybody else encountered this and what is the best way to achieve this?

Answer 1

Hello @Sallie Worsley,

moving data from an on-prem SQL server to the cloud and passing rows of data through Azure Stream Analytics, yes I have done this several times.

In my case, the context was the Internet of Things (IoT) where data had to be collected both from devices and from on-prem data resources.

You are talking about real-time data in Sql Server, this makes it an excellent IoT solution.

I worked with Azure IoT Edge so local logic was running in a Docker container on a VM running within the local Network. That Edge solution had a secure outbound connection with an Azure IoT Hub.

Messages we constructed using SQL queries and sent to the IoT Hub which passed them on to the Azure Stream Analytics job.

Btw. if you only want to collect raw telemetry/row data, a direct route to ADL without the need for a Stream Analytics job is possible too.

An EventHub connection is considered less secure than using an IoT Hub.

The biggest advantage is the reliable message transfer, no messages are lost during a temporary Azure IoT Hub connection hick-up.

If you are interested, check out this free training module about Azure IoT Edge.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.