Azure SQL Read Replica using Geo-Replication

Question

I understand that for Azure SQL PaaS offering, for redundancy purposes, we have Geo-replication feature.

1)Can we create a read-replica of the target replicated database ? or is it only Active-active geo replica

2)Also for geo-replication of an Azure SQL DB, is there any requirements or prerequisites to be met like all tables should have primary key or something like that ?

3) Also i think geo-replication is asynchronous . So how much delay is there in replicating data to secondary replica ?

Please help.

Answer 1

Hi @MS Techie , welcome to Microsoft Q&A forum.

1) Could you please elaborate a use case to understand the question please?

2) There is no pre-requisite as such but its always recommended to create the secondary server with same configuration/tier as the primary one. If you want to scale them, first scale secondary up and then scale primary up. Same way if you want to scale down, scale primary down first and then secondary. This will help in not throttling the request when data is syncing between the databases. Please read more in below link:

Configuring secondary database

3) Yes geo-replication is asynchronous and data replication depends on which 2 regions have we chosen the databases in. If we have the same regions the data replication will be very fast and be almost simultaneously. We can run below query to know the replication lag. I tried in same zone and replication_lag_sec was '0' that means almost at the same time replication happened.

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

It also depends on how much data we are writing to primary database. If we talk about huge data it could take a little more time.