Azure SQL Active Geo-Replication. Let’s Understand.

Active geo-replication enables you to create readable replicas of your database allowing you to increase your business continuity and also in some cases the performance of your applications as well.

Today let’s understand why we need Active Geo-Replication and how you can configure it on Azure.

Why you would want to use it?

1. The primary problem that this solves is business continuity. Let’s say you have an issue with your data center, you can just fail over to another keeping business continuity intact.
2. Because the secondary databases are readable, they can be used to offload read-only workloads such as reporting jobs. If you are using active geo-replication.
3. Another two main benefits are migration and cloning. You can use active geo-replication to migrate a database from one server to another online with minimum downtime.
4. You can create an extra secondary as a failback copy during application upgrades.

Regions and Availability Zones

Things you should know

1. The failover must be initiated manually by the application or the user. After failover, the new primary has a different connection endpoint. You can automate this failover with Azure CLI and Azure REST API as well. But you want to go with fully automatic failover you can try Azure SQL Auto-Failover Groups
2. Both primary and secondary databases are required to have the same service tier. It is also strongly recommended that the secondary database is created with the same compute size (DTUs or vCores) as the primary.
3. Active geo-replication will cost similar to the primary Azure SQL Database on which we have configured the geo-replication. The cost of geo-replication traffic between the primary and the online secondary is included in the cost of the online secondary

How it works underneath

Active geo-replication replicates changes by streaming database transaction log. It is unlike transactional replication, which replicates changes by executing DML (INSERT, UPDATE, DELETE) commands.

Few more things,

1. Up to four secondaries are supported in the same or different regions, and the secondaries can also be used for read-only access queries.
2. Geo-replication always replicates the entire database. Replication is asynchronous and continuous, with latency below 5 seconds, backed by an SLA. If there is a network failure between two regions, we retry every 10 seconds to re-establish connections.
3. Creating a geo-replica on a logical server in a different Azure tenant is not supported when Azure Active Directory only authentication for Azure SQL is active (enabled) on either primary or secondary logical server.

Hope you have learned something new today! :D