Azure SQL DB - max number of readable replica limit?

Question

If I currently have an azure SQL database that's configured as Business Critical (with Availability Zones) can I still use Geo-replication? If yes, will this make the total readable secondary replica count to 4?

This is what I understand so far.

BC (w/ AZ) - 4 replicas (pri + 3 secondary [1 readable - 2 passive])

Now if I add active geo-replication, could I further add 3 more readable secondaries? or is that 4 more?

So, can BC w/ AZ + Active Geo-rep can have up to 5 readable secondary replicas?

Answer 1

Hi Carl, Business Critical has 4 replicas as a part of offering. Out of these one is primary (P) and three are secondary(S) replicas. If you enable read-scale feature of business critical, you would be allowed to read from one of the secondary replicas. (Let us call it as Region1 (R1) and replicas would be R1P1, R1S1. R1S2, R1S3). Out of these R1P1 is writable and R1S1 is readable secondary. Now, if you add a Geo secondary to this database (say in Region 2 - R2), it would also be a Business Critical, which would again have 4 replicas. The data from Primary R1P1 would be sent to R2P1 which would further send to R2S1, R2S2 and R2S3) If you enable Read-scale on this geo secondary then you would have total two readable copies here (say R2P1, R2S1). Without Read-scale, just one readable copy. In above example (with read-scale enabled on both sides) you have 1 primary & 1 readable copy in R1 and 2 readable copies in R2. You can extend this further by adding more Geo secondary replicas (up to 4) and then you can also do chaining of Geo Replicas as mentioned by Shakti. Your question was about total secondary replica or readable replicas? I have tried to answer both above. Keep in mind, the connection string of Geo Secondary DB would be different as it has to be created on a different logical server than primary.

Answer 2

Hi Carl Nicko Ardiente •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to know max number of readable replica limit in Azure SQL Database.

In Business Critical Tier, considering Availability we have 3 replicas, 1 read-scale replica,
zone-redundant high availability (HA).

For Multiple readable geo-secondaries:

Up to four geo-secondaries can be created for a primary. If there is only one secondary, and it fails, the application is exposed to higher risk until a new secondary is created. If multiple secondaries exist, the application remains protected even if one of the secondaries fails. Additional secondaries can also be used to scale out read-only workloads.

If you are using active geo-replication to build a globally distributed application and need to provide read-only access to data in more than four regions, you can create a secondary of a secondary (a process known as chaining) to create additional geo-replicas. Replication lag on chained geo-replicas may be higher than on geo-replicas connected directly to the primary. Setting up chained geo-replication topologies is only supported programmatically, and not from Azure portal.

Refer: Active geo-replication

Service tiers

Hope this information helps. Please let us know if you have further queries. Thank you.