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?
3 Answers
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.
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!
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.
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.
Thanks for the suggestion! I’ll definitely check out Managed Instance.
I've heard about using a virtual table in Dataverse for this purpose. Does that seem like a viable solution to you?