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
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: 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
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
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)