I have a table on Azure SQL Database that I would like to have replicated/mirrored to our on-premise SQL Server
So the on-prem SQL Server would have a copy of table from Azure that is always up-to-date, available for read-only queries
Is there a technology for this ?
The reason I need this is because I need to join this Azure table to some tables on-premise (on 300K + rows) in a query, and linked server is not working for me very well, despite all the tricks and workarounds I have tried
Regards,
Best Answer
In theory, Azure Data Sync does this, but it's been in "preview" status for years, and there's a lot of gotchas. Read the documentation carefully to see if its limits can work for you, and just know that in a preview program, Microsoft can end up removing support for it later.
A better solution would be something like SSIS jobs to keep the data in sync, but that's obviously not easy, either. As your tables change, you'll need to keep changing the ETL jobs.