question

MSTechie-7364 avatar image
0 Votes"
MSTechie-7364 asked ·

Azure SQL Read Replica using Geo-Replication

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.



azure-sql-database
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MSTechie-7364, wanted to follow up on this thread and seek your inputs based on answer provided.

0 Votes 0 ·

1 Answer

AnuragSharma-MSFT avatar image
0 Votes"
AnuragSharma-MSFT answered ·

Hi @MSTechie-7364, 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.



· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AnuragSharma-MSFT
is there any link from Microsoft website , which says replication lag is 0 seconds for SAME Region Geo-replication

Want to elaborate on my use case a bit.

We have several dotnet client apps which are accessing our SQL database of nearly 200 GB size and there are several performance issues due to it. So we have decided to create a read replica of our database , so that few client will access the main database and rest access the read-replica. So for PERFORMANCE reasons we are using ReadReplica option via Geo Replication feature

Azure SQL Geo-Replication sounded a good option for it. We are using the Standard Service tier for our Azure SQL database. But we see 2 options in Geo replication --> Standard Geo Replication and Active Geo Replication. So which option of Geo Replication should we go for - Standard or Active ?

0 Votes 0 ·

Hi @MSTechie-7364, thanks for replying back.

Replication lag depends on many factors. It could vary from 0 seconds to some value based on regions or other factors like when was the last commit took place. Or depends on how much data is syncing from primary and secondary. Please check the below link which explains the same:

Monitoring geo-replication lag

For the other query, as you mentioned read-replica will be frequently accessed by clients and should have the most updated copy of data, I would suggest to use the Active geo-replication. As per the article:

78544-image.png

Please let us know if this helps.

0 Votes 0 ·
image.png (64.2 KiB)

Hi @MSTechie-7364, please let me know if this answers your query or else we can discuss further.

0 Votes 0 ·