Accessing a SQL Server without going via VM

Question

Good day Members

I have connect using an IPSec to a on prem SQL DB(this is the only way). I can connect to the SQL DB in a VM. I have been using PowerBI and SSMS in the VM. I want to sink the data into an Azure SQL and use it without going via VM.

I tried this approach https://learn.microsoft.com/en-us/azure/data-factory/tutorial-deploy-ssis-packages-azure but it's expensive.

Can you please advice on how best can l achieve this. I am open to use Datafractory

Answer 1

Hey,

Ideally the secure way to access any database hosted within a VNET/ On prem within POwer BI/ ADF is via a gateway (On premises gateway for Power BI and Self hosted IR in case of ADF)

The gateway acts a bridge between the VNET/On Prem and cloud and secures the data during transit.

So to connect an On Prem database via ADF/PowerBI , you would need a VM which would act as a bridge by installation of the gateway.

Note: You can craete a managed end point if need be which would still need a VNet aspect

Answer 2

@StudentLearner
Welcome to Microsoft Q&A platform and thanks for posting your question here.

To achieve the connection to your on-premises SQL database without using a VM, and to sink the data into Azure SQL, you can consider using Azure Data Factory with a self-hosted integration runtime.

Here's how you can do this, along with the cost advantages and disadvantages:

Firstly, you need to install SHIR on an on-premises machine that can access your SQL database. SHIR acts as a bridge between your on-premises data source and ADF without needing a VM. Once you have installed SHIR, you can use ADF to create a data pipeline that connects to your on-premises SQL database via SHIR. Configure the pipeline to copy data from your SQL database to Azure SQL Database.

To enhance security and avoid exposing your SQL database over the internet, you can use Azure Private Link Service. Create a managed private endpoint in ADF to connect to your on-premises SQL Server using the private IP address.

When it comes to cost advantages, ADF operates on a pay-as-you-go model, which means you only pay for what you use, potentially making it cost-effective for variable workloads. Since SHIR is installed on your own infrastructure, there's no need to maintain additional Azure resources, which can save costs. ADF also allows you to scale your resources up or down based on demand, potentially saving costs when demand is low.

However, there are also cost disadvantages to consider. While consumption-based pricing is flexible, it may result in higher total costs over time compared to on-premises solutions. Predicting costs can be challenging due to the variety of factors that influence pricing, such as the number of activity runs, data integration unit hours, and type of compute used.

In summary, using ADF with SHIR allows you to securely and efficiently transfer data from your on-premises SQL database to Azure SQL. This method eliminates the need for a VM, which can reduce costs and complexity. However, it's important to carefully monitor and manage your ADF usage to control costs. You can use the Azure pricing calculator to estimate costs before starting and Azure Cost Management to monitor ongoing costs.

Reference
https://stackoverflow.com/questions/73694208/access-on-prem-sql-server-without-vms-through-azure-data-factory
Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.