Hi,
AlwaysOn Availability Groups (AG) has been around since SQL Server 2012 and today it is widely used in HADR solutions. It also gives us the ability to have readable secondary replicas, allowing read-only access to the respective secondary databases. If you have read-intensive workloads (like reporting, ETL applications, etc.) you can have a significant performance benefit by simply directing those workloads to the secondary replicas.
In this post I’m assuming that you’re already familiar with this “old” feature so I’m not going to elaborate about it.
The thing is that AG is not present in Azure SQL Database (PaaS), so my purpose here is to show how to implement an HADR solution in Azure SQL Database, since we can’t use our well known (and beloved) AG and you’ll see that it’s actually pretty easy 😊
I’ll present three features, one in each post:
1. Scale out read-only Database
Scale out read-only Database
“As part of High Availability architecture, each single database, elastic pool database, and managed instance in the Premium and Business Critical service tier is automatically provisioned with a primary read-write replica and one or more secondary read-only replicas.”
Taken from (https://learn.microsoft.com/en-us/azure/azure-sql/database/read-scale-out?view=azuresql)
So, if I choose the Premium or Business Critical service tier for my database in Azure SQL Database, I automatically gain access to local redundancy and the ability to have a secondary read-only database, allowing to isolate read-only workloads from the read-write workloads and it’s included in the pricing.
Great, but how does this work and how can I configure the applications to connect to the secondary read-only replica? Let’s answer this question.
I’m going to use the Azure Portal to get a read-only Database for AdventureWorks. For the moment it’s in the “Basic” pricing tier, hence I can’t have a read-only replica:
Now if I choose either a business critical or a Premium service tier, the option “Read scale-out” becomes available. There is also another interesting option that becomes available which is the ability to make the database zone redundant (instead of local redundant):
In this demo I’ll simply enable “Read scale-out” in order to get the read-only replica and in this case I’ll go with the business critical service tier. In order to activate my choice, I’ll press the Apply button and that’s it! I now have a secondary read-only replica for AdventureWorks, it couldn’t be simpler!
Now the next question is how do I connect and take advantage of it for read-only intensive workloads? Again, very simple as I’m about to demonstrate.
First I’m going to connect to the primary replica:
To connect to the secondary read-only replica, I’ll use exactly the same server name and the same database but I’ll put an additional parameter into the client connection string, that by the way, is the same we use when using AG, i.e. “ApplicationIntent=ReadOnly“.
Important: make sure you also mention the name of the right database in the connection string.
And that’s it! I’m now connected to the read-only replica of AdventureWorks, pretty cool, right?
Sorry, what did you ask? Oh, how can I be sure that this is the read-only replica? Well there is a very simple way to confirm this, I’ll use this function (DATABASEPROPERTYEX(DB_NAME(), ‘Updateability’)) in both connections and we’ll take a look at the results:
Connected to the primary replica (using the database server name and the desired database name):
And now connected to the secondary read-only replica (using the database server name, the desired database name and ADDING “ApplicationIntent=ReadOnly” parameter to the connection string):
So if I try any DDL or DML statement (other than SELECT) in the read-only replica I’ll receive an error:
This feature is great since it is very straightforward to implement and to use but I can think of at least two potential drawbacks:
- It is only available in Premium and Business Critical tiers (or Hyperscale when at least one secondary replica is added), not the “lower” ones.
- Although we can choose Zone Redundancy, it’s not possible to have Geo Redundancy for this read-only replica, that in some DR scenarios might be more suitable.
But, if you find yourself in a position where you may feel any of the two drawbacks above, don’t worry, there are two easy ways to overcome them. Stay tuned for Part II of “HADR in Azure SQL Database” where I’ll speak about Active Geo-Replication 🙂
Cheers!
SQL Land 