I've recently been tasked with migrating our data warehouse to Azure and I need some guidance on how to keep my tables in sync between our on-premise SQL Server 2019 and the Azure SQL Database. I'm looking for suggestions for a daily synchronization method. I tried using the CDC feature in Azure Data Factory, but it didn't work with our on-prem setup. Any recommendations?
2 Answers
Another reliable approach is transactional replication from your on-prem SQL Server to the Azure SQL Database. It’s been around for a while and is usually effective. Just keep in mind some potential networking issues. It’s old but trustworthy technology.
Totally agree, this method works well!
One solution I've found effective is using Azure Synapse. By setting up a Self-Hosted Integration Runtime on a local machine, it acts as a secure gateway for data requests from Azure. You can pull data from your SQL Server into an Azure SQL database for your data warehouse. I built linked services for Synapse, connecting to both local SQL and Azure. Then I scheduled the jobs to run overnight and even more frequently for some datasets, making sure Power BI has access to the cleaned-up data without performance issues. If you have questions, feel free to ask!
Can we use ADF instead to do incremental loads from on-prem to SQL Server? I thought that was a key step for syncing to an ODS layer daily. This whole process can feel overwhelming, but I’m tackling it bit by bit. My database is less than 1 TB, so I'm hopeful we can make things work with the right tools.
What if you also considered syncing the data for Power BI later on? Might be worth planning for that now while you set it all up.
Are you using Azure Data Sync with that method? Does it impact the performance of your OLTP system significantly? Also, can it manage deleted records?