Azure Active Geo-Replication Services

Azure Active Geo-Replication Services is one of the powerful services in Azure used for replicating the SQL database data to another Regions using the same concept of always on data are readable on the another regions and synced on time , With Active Geo-Replication we can configure up to four readable secondary databases in the same or different data center locations (regions).

• What is Azure Geo-Replication
• What is Geo-Replication Mode
• How to Configure Active Geo-replication
• Geo-Replication Permission
• Benefits from the secondary and Geo-Replication
• How to Failover to Secondary
• Checking the replication Status
• Remove Geo-Replication
• Conclusion

What is Azure Geo-Replication

It is Azure Services used for replicating the data in multiple regions and it is available on all Services Tiers (basic, Stander, Premium) and it provides readable replica secondary Database in a different region, it is allowed to replica in DB Size, and it is allowed to scale up the secondary Database. Finally, you can Expect the ERT (Estimated Recovery Time) < 30 Second and RPO (Recovery point objective) < 1 Hour and it can be used for Read-only Scale or in Failover in case of disaster

What is Geo-Replication Mode

Stander Option provide non-Readable DB Replicas so you will have the option to failover but you can read from the other nodes and this feature ended at April 2017, ACTIVE Option Recommended option it is providing 4 readable databases replicas and it provides 30 Second as RTO and 5 seconds as RPO for more information check this post

How to Configure Active Geo-replication

• Connect azure Portal
• Select azure SQL DB and on the right select Geo-Replication
• select the Region you need to replication the Azure DB with it, you can select up to 4 secondaries Regions.
• Start the wizard configuration by selecting the secondary server , if you don;t have secondary server on the selected Regions from the the same wizard steps you can create the new server

Geo-Replication Permission

Geo-Replication Permission: the user should have at least permission as DBManger on both of primary server and Secondary Server

Benefits from the secondary and Geo-Replication

• We can query the data at any time on Secondary Azure SQL Database,
• Secondary are available for Failover in case of data center outage (disaster),
• We can offload the reports and the heavy read only Query to the secondary Azure SQL Database.
• Data Migration we can use the Geo-Replication for Database Migration from Server to another server in same Region or in different Region.

Failover to Secondary

It is like SQL Server Always on Failover we can do the Failvoer using azure portal As Forced Failover or using T-SQL

ALTER DATABASE AzureSQLDB2 FAILOVER

But the failover can be done from secondary azure SQL Database not from Primary azure SQL Database. And before doing failover we should make sure the data synced from primary to secondary using  sp_wait_for_database_copy_sync. And this very helpful for unplanned replication to ensure the data forced to written on secondary before committed to the application and for sure this option can do some latency on the app as performance issue.

Checking the replication Status

Microsoft provide us DMV T-SQL we can use it for checking the replication and data sync status

SELECT   
     link_guid  
   , partner_server  
   , last_replication  
   , replication_lag_sec   
FROM sys.dm_geo_replication_link_status

Remove Geo-Replication

It can be done from Azure portal on Geo-replication on Secondary Region right click and select Stop replication or we can use T-SQL

ALTER DATABASE AzureSQLDB2
REMOVE SECONDARY ON SERVER azuresqldbserversv2

Conclusion

Active Geo-Replication Feature it is very helpful for many X of reasons and it is working with the same concept of SQL Server Always on Replication, For more information about azure services check my previous azure posts and articles  (Latest Microsoft Azure Articles and Posts)