Azure SQL Database Read Scale-Out
Read Scale-Out is a little-known feature that allows you to load balance Azure SQL Database read-only workloads using the capacity of read-only replicas, for free.
As mentioned in my blog Azure SQL Database high availability, each database in the Premium tier (DTU-based purchasing model) or in the Business Critical tier (vCore-based purchasing model) is automatically provisioned with several AlwaysON read-only replicas using synchronous-commit mode to support the availability SLA of 99.99% (these AlwaysON replicas and created automatically even if you are not using geo-replication). These replicas are provisioned with the same performance level as the read-write replica used by the regular database connections. The Read Scale-Out feature allows you to load balance SQL Database read-only workloads using the capacity of one of the read-only replicas instead of all queries hitting the read-write replica. This way the read-only workload will be isolated from the main read-write workload and will not affect its performance. This feature is intended for applications that include logically separated read-only workloads, such as analytics, and therefore could gain performance benefits using this additional capacity at no extra cost.
I highlighted “one” above to bring attention to the fact that only one replica is used, meaning it does not use multiple read-only replica’s and load balance between them.
Another option for read-only workloads is if you also decide to use geo-replication (which is not free), this will create secondary databases (currently four) using asynchronous-commit mode that can be made readable and you can direct connections to each of those secondary’s directly in the connection string, and do your own load balancing between them. For more info on geo-replication see my blog Azure SQL Database disaster recovery.
And if you are using Read Scale-Out to load balance read-only workloads on a database that is geo-replicated (e.g. as a member of a failover group), make sure that Read Scale-Out is enabled on both the primary and the geo-replicated secondary databases. This will ensure the same load-balancing effect when your application connects to the new primary after failover.
To read how to enable Read Scale-Out and send queries to the read-only replica, check out Use read-only replicas to load balance read-only query workloads (preview).
More info:
Overview: Active geo-replication and auto-failover groups
Azure SQL DB Readable Secondary
How To Connect Power BI to Read-only Replica of Azure SQL Database & More
James, this is very kewl, thanks for pointing it out! The online doc still says “preview”, so is this still the case, is any signup required or anything?
Hi James,
I am being told that Azure geo-replication did not work (did not fail-over VMs replicated to other regions) during the recent US-SouthCentral data center outage. Can you confirm the validity of this statement? I find it hard to believe that the geo-replication redundancy failed, but I need a credible source (like you) to confirm otherwise.
Many thanks for your insight on this issue!