2 minute read

Replicating data from SQL Server to Azure SQL MI & DB

A standard use case for enabling read-scale and real-time analytics without further taxing the infrastructure of an On-Premises SQL Server involves data replication to Azure's SQL offerings.

Some standard requirements for this setup might include:

  1. Support older On-Prem SQL Server versions as the data source (say SQL Server 2012 SP3)
  2. Minimize replication latency as much as possible
  3. Leverage a managed service for the secondary replica

Considering these requirements, we could leverage Azure SQL DB or Azure SQL MI as managed services to host our secondary replica.

The setup would look something like this:

SQL Server Replication Use Case
SQL Server Replication Use Case

Replication mechanism: overview of available options

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.

Syncing data from SQL Server to Azure SQL DB & MI
Syncing data from SQL Server to Azure SQL DB & MI

SQL Server Replication

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:

SQL Server Replication: All 6 Options
SQL Server Replication: All 6 Options

Sample implementation: Standard Replication with SQL MI

A sample implementation could choose to proceed with say, SQL MI for a 1:1 replication from SQL Server, while leveraging Standard Transactional Replication:

Sample implementation: Standard Transactional Replication with SQL MI
Sample implementation: Standard Transactional Replication with SQL MI

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)

For Development/POC purposes, we can choose to leverage the SQL MI's Public Endpoint, using the NSG available as part of SQL MI to only accept traffic from our On-Prem SQL Server's Public IP.

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.

Get in touch 👋

If you have any questions or suggestions, feel free to open an issue on GitHub!

© 2023 Raki Rahman.