I'm looking for the most cost-effective method to set up an Azure Functions App integrated with either Blob Storage or Azure SQL Server. Ideally, I'd like insights on how to pull data from an on-premises SQL Server database into Azure Functions or Blob Storage, if anyone has done something similar. I have a project coming up next month that requires this setup, so any experiences or advice would be greatly appreciated! If you've built something like this with minimal usage, what were your costs?
5 Answers
You can create an HTTP trigger in your Function App to handle data integrations, particularly if your SQL Server is set up with an ExpressRoute or Site-to-Site VPN. Alternatively, you could place your Function App behind Azure Front Door with an IP whitelist for security.
If you’re focused on the cheapest route, remember that tech debt can run up costs more than a low-code solution like Azure Data Factory. If you're asking this question, you likely don't have a development team ready to handle elaborate coding, so a more straightforward pipeline might be better.
There are multiple approaches you can take. If it's a simple setup and you don't have existing Azure networking, using Azure Data Factory with an on-premise runtime can be the cheapest option for a single pipeline. Just keep in mind that if you utilize a Functions App, you may need a VPN or virtual network unless you expose your on-prem data through an Azure NAT gateway.
You could definitely push data from an on-prem app, but I wonder why you're leaning towards Azure Functions. We started with functions but switched to using containers; they’re a bit more complex but provide more flexibility. If you can manage a Function App, building a Docker container shouldn't be too hard. Just be cautious using Python for MS SQL in containers, as it's more challenging than the app itself.
It sounds like you need to clarify your requirements a bit more. If you're looking at SQL Server 2025, it can call REST APIs (like Azure Functions) and write directly to Blob Storage using a managed identity through Azure Arc. But without more details, it's tough to give precise advice.
That's not always the case; if you host the app on-premises, you can avoid exposing anything. You could push data up securely with firewall rules and a static IP. For remote triggers, consider using a service bus that the on-prem system can pick up. But, again, without knowing more of your context, it’s tricky to recommend the best solution.