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:

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.

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:

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:

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.