Replicating data from SQL Server to Azure SQL MI & DB
Some standard requirements for this setup might include:
- Support older On-Prem SQL Server versions as the data source (say SQL Server 2012 SP3)
- Minimize replication latency as much as possible
- Leverage a managed service for the secondary replica
The setup would look something like this:
The next step is to align on the replication mechanism - where we have several routes to achieving the same end goal of populating our secondary replica.
SQL Server Replication is a robust feature offered natively as part of the core SQL Server Engine - generally offering the least replication latency from our options above.
There are 6 flavors to choose from when configuring SQL Server replication, each slightly catering to different use cases. The diagram below provides a bird's eye view of each:
A sample implementation could choose to proceed with say, SQL MI for a 1:1 replication from SQL Server, while leveraging Standard Transactional Replication:
From a security perspective, we'd want to restrict replication traffic to our private network - specially for Production. For this we'd need to implement:
- ExpressRoute/VPN - so the replication traffic flows from our On-Prem SQL Server into SQL MI's Virtual Network through private channels
- We'd also need to configure DNS resolution, so that the SQL Instance FQDN resolves to the Private IP (i.e. via the routes we enabled through ExpressRoute/VPN)
The idea here is to document the specific steps/configurations required up front, and generating Replication Scripts in Development that we can localize and apply to Production.
For larger databases, we might also want to kickstart the replication process using Backup & Restore, which could be faster than initializing through the Snapshot Agent (which actually uses
bcp to generate snapshots). We can utilize the method outlined here to restore a SQL Server Backup File onto SQL MI.