Azure SQL Database Replication

Achieve high availability and improve read scalability ! 

There are several replication strategies available. One common approach is to set up a master database and create read replicas. Azure SQL Database offers two primary replication technologies:

1. **Active Geo-Replication:** Active Geo-Replication is a built-in feature of Azure SQL Database that allows you to create up to four readable secondary databases in different Azure regions. These secondary databases act as read replicas. The primary database serves as the master, where all write operations are directed. The secondary databases are read-only copies of the primary database, and they can be used to offload read-heavy workloads.

   Key points to note about Active Geo-Replication:

   - Readable Secondary Databases: The secondary databases are read-only, meaning you can't write to them directly. All write operations must be directed to the primary database.

   - Asynchronous Replication: Data is asynchronously replicated from the primary to the secondary databases, so there might be some data lag between the primary and secondary databases.

   - Geo-Redundancy: By placing replicas in different Azure regions, you improve the disaster recovery capabilities of your database, as well as providing better read performance to users in different geographical locations.

   - Failover: In the event of a regional outage, you can manually or automatically failover to one of the secondary databases to promote it to become the new primary.

2. **Read Scale-Out with Read Replicas:** In addition to Active Geo-Replication, Azure SQL Database also allows you to create up to four readable replicas within the same region as the primary database. These read replicas can be used to offload read workloads within the region itself.

   Key points to note about Read Scale-Out with Read Replicas:

   - Readable Replicas: Read replicas are readable, meaning you can direct read-heavy queries to these replicas, distributing the read load and improving query performance.

   - Synchronous or Asynchronous Replication: You have the flexibility to choose between synchronous or asynchronous replication to the read replicas. Synchronous replication provides stronger consistency at the expense of increased latency for write operations.

   - Load Balancing: You can use Azure Traffic Manager or connection strings with "ApplicationIntent=ReadOnly" to enable automatic load balancing of read queries across the read replicas.

When setting up replication for Azure SQL Database with a master database and at least two read replicas, you have multiple options. You can use Active Geo-Replication to have two read replicas in different Azure regions for disaster recovery and improved read performance across regions. Additionally, you can use Read Scale-Out with Read Replicas to have two read replicas within the same region to distribute read workloads and improve query performance locally. These replication strategies provide high availability, disaster recovery, and scalability to your Azure SQL Database deployments.