How Can I Access On-Prem Data from Azure DB Without Linked Servers?
Asked by
CuriousCoder92
I'm planning to migrate my database to Azure DB for the first time. My current setup includes views and stored procedures that access another database through a linked server. Since I recognize that Azure DB doesn't support linked servers, I'm looking for suggestions on how to refactor these views and stored procedures so that they can still access the data on my on-prem server. What options do I have?
Answers
If you find you need linked servers and want to minimize reworking your architecture, consider looking into Azure SQL Managed Instance. It offers many of the features of Azure SQL DB while allowing better compatibility with on-prem SQL servers. Yes, it requires a little more setup, like a dedicated VNet, but it also offers automatic backups, patching, and high availability.
Reply to this answer
Linked Servers aren't available in Azure SQL Database, which makes things a bit tricky. Instead, consider alternative methods to access your on-prem data. For example, think about using Azure Data Factory to move data periodically, or explore combining databases to maintain access. Sometimes, creative solutions might be necessary to achieve your needs!
TechGuru88: Absolutely! Just remember that Azure SQL DB isn't ideal for cross-database queries. You can create external connections, but they are generally better for data imports than for quick queries.
Reply to this answer
A straightforward option is to regularly push data changes (deltas) from your on-prem server to the Azure DB, and adjust your stored procedures and views to pull from those local tables instead.
DataNinja23: I've heard about using a virtual table in Dataverse for this purpose. Does that seem like a viable solution to you?
Reply to this answer
Add your answer
Answers are reviewed before publication.
CloudAdventurer12: Thanks for the suggestion! I’ll definitely check out Managed Instance.