Monday, November 18, 2019

Data Platform Tips 2 - Active geo-replication for Azure SQL Database


active geo-replicationActive Geo-replication is a business continuity feature on Azure SQL Database for quick disaster recovery in case of regional disasters. With geo-replication enabled, it creates readable secondary in the same or different region data center. It supports up to 4 secondaries in the same or different region and can be used for read only query access.

Supports only manual failover.

Active Geo-replication uses Always-On technology to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation.

To guarantee that the changes in primary is replicated in the secondary before initiating a failover, the application can call the stored procedure "sp_wait_for_database_copy_sync" to force the synchronisation from Primary to Secondaries. Also use the stored procedure "sys.dm_geo_replication_link_status" to check the replication status.

Initiate geo-replication using Azure Portal

a) Create a new resource group named "active-geo-replication".





b) Create a new Azure SQL Database




c) Select the newly created database "active-geo-replication-db1" and click on geo-replication.












d) As you can see below the secondary database for active geo-replication hasn't been configured and let us configure it.



e) Select the secondary database region as "Southeast Asia" and configure the secondary database and server


























f) Once the secondary replica database is created, you can see the replica database created in the "Southeast Asia" region.

i) The secondary replica is a read only replica as shown below.

Initiating Failover using Azure Portal


a) Initiate a failover by right clicking on the secondary replica and clicking on "Forced failover" and click ok to continue. Note: With forced failover, you may encounter data loss. To avoid data loss, the data synchronisation needs to be completed before initiating the failover using "sp_wait_for_database_copy_sync"

b) Once the failover the complete, you can notice the database in "Southeast Asia" region as primary and secondaries in "Australia East" region becomes readonly.


c) The failover is now complete.

No comments:

Post a Comment