Use read-only replicas to load-balance read-only query workloads

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

As part of the High Availability architecture, each database in the Premium and Business Critical service tier is automatically provisioned with a primary replica and several secondary replicas. The secondary replicas are provisioned with the same compute size as the primary replica. 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 sharing 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. The feature is intended for the applications that include logically separated read-only workloads, such as analytics. In the Premium and Business Critical service tiers, applications could gain performance benefits using this additional capacity at no extra cost.

The Read Scale-Out feature is also available in the Hyperscale service tier when at least one secondary replica is created. Multiple secondary replicas can be used if read-only workloads require more resources than available on one secondary replica. The High Availability architecture of Basic, Standard, and General Purpose service tiers does not include any replicas. The Read Scale-Out feature is not available in these service tiers.

The following diagram illustrates it using a Business Critical database.

Readonly replicas

The Read Scale-Out feature is enabled by default on new Premium, Business Critical, and Hyperscale databases. For Hyperscale, one secondary replica is created by default for new databases. If your SQL connection string is configured with ApplicationIntent=ReadOnly, the application will be redirected by the gateway to a read-only replica of that database. For information on how to use the ApplicationIntent property, see Specifying Application Intent.

If you wish to ensure that the application connects to the primary replica regardless of the ApplicationIntent setting in the SQL connection string, you must explicitly disable read scale-out when creating the database or when altering its configuration. For example, if you upgrade your database from Standard or General Purpose tier to Premium, Business Critical or Hyperscale tier and want to make sure all your connections continue to go to the primary replica, disable Read Scale-out. For details on how to disable it, see Enable and disable Read Scale-Out.

Note

Query Data Store, Extended Events, SQL Profiler and Audit features are not supported on the read-only replicas.

Data consistency

One of the benefits of replicas is that the replicas are always in the transactionally consistent state, but at different points in time there may be some small latency between different replicas. Read Scale-Out supports session-level consistency. It means, if the read-only session reconnects after a connection error caused by replica unavailability, it may be redirected to a replica that is not 100% up-to-date with the read-write replica. Likewise, if an application writes data using a read-write session and immediately reads it using a read-only session, it is possible that the latest updates are not immediately visible on the replica. The latency is caused by an asynchronous transaction log redo operation.

Note

Replication latencies within the region are low and this situation is rare.

Connect to a read-only replica

When you enable Read Scale-Out for a database, the ApplicationIntent option in the connection string provided by the client dictates whether the connection is routed to the write replica or to a read-only replica. Specifically, if the ApplicationIntent value is ReadWrite (the default value), the connection will be directed to the database’s read-write replica. This is identical to existing behavior. If the ApplicationIntent value is ReadOnly, the connection is routed to a read-only replica.

For example, the following connection string connects the client to a read-only replica (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Either of the following connection strings connects the client to a read-write replica (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadWrite;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Verify that a connection is to a read-only replica

You can verify whether you are connected to a read-only replica by running the following query. It will return READ_ONLY when connected to a read-only replica.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability')

Note

At any given time only one of the AlwaysON replicas is accessible by the ReadOnly sessions.

Monitoring and troubleshooting read-only replica

When connected to a read-only replica, you can access the performance metrics using the sys.dm_db_resource_stats DMV. To access query plan statistics, use the sys.dm_exec_query_stats, sys.dm_exec_query_plan and sys.dm_exec_sql_text DMVs.

Note

The DMV sys.resource_stats in the logical master database returns CPU usage and storage data of the primary replica.

Enable and disable Read Scale-Out

Read Scale-Out is enabled by default on Premium, Business Critical and Hyperscale service tiers. Read Scale-Out cannot be enabled in Basic, Standard, or General Purpose service tiers. Read Scale-Out is automatically disabled on Hyperscale databases configured with 0 replicas.

You can disable and re-enable Read Scale-Out on single databases and elastic pool databases in Premium or Business Critical service tier using the following methods.

Note

The ability to disable Read Scale-Out is provided for backward compatibility.

Azure portal

You can manage the Read Scale-out setting on the Configure database blade.

PowerShell

Managing Read Scale-Out in Azure PowerShell requires the December 2016 Azure PowerShell release or newer. For the newest PowerShell release, see Azure PowerShell.

You can disable or re-enable Read Scale-Out in Azure PowerShell by invoking the Set-AzSqlDatabase cmdlet and passing in the desired value – Enabled or Disabled -- for the -ReadScale parameter.

To disable read scale-out on an existing database (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

Set-AzSqlDatabase -ResourceGroupName <myresourcegroup> -ServerName <myserver> -DatabaseName <mydatabase> -ReadScale Disabled

To disable read scale-out on a new database (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

New-AzSqlDatabase -ResourceGroupName <myresourcegroup> -ServerName <myserver> -DatabaseName <mydatabase> -ReadScale Disabled -Edition Premium

To re-enable read scale-out on an existing database (replacing the items in the angle brackets with the correct values for your environment and dropping the angle brackets):

Set-AzSqlDatabase -ResourceGroupName <myresourcegroup> -ServerName <myserver> -DatabaseName <mydatabase> -ReadScale Enabled

REST API

To create a database with read scale-out disabled, or to change the setting for an existing database, use the following method with the readScale property set to Enabled or Disabled as in the below sample request.

Method: PUT
URL: https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014-04-01-preview
Body:
{
   "properties":
   {
      "readScale":"Disabled"
   }
}

For more information, see Databases - Create or Update.

Using TempDB on read-only replica

The TempDB database is not replicated to the read-only replicas. Each replica has its own version of TempDB database that is created when the replica is created. It ensures that TempDB is updateable and can be modified during your query execution. If your read-only workload depends on using TempDB objects, you should create these objects as part of your query script.

Using Read Scale-Out with geo-replicated databases

If you are using Read Scale-Out to load-balance read-only workloads on a database that is geo-replicated (for example, 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 configuration will ensure that the same load-balancing experience continues when your application connects to the new primary after failover. If you are connecting to the geo-replicated secondary database with read-scale enabled, your sessions with ApplicationIntent=ReadOnly will be routed to one of the replicas the same way we route connections on the primary database. The sessions without ApplicationIntent=ReadOnly will be routed to the primary replica of the geo-replicated secondary, which is also read-only. Because geo-replicated secondary database has a different endpoint than the primary database, historically to access the secondary it wasn't required to set ApplicationIntent=ReadOnly. To ensure backward compatibility, sys.geo_replication_links DMV shows secondary_allow_connections=2 (any client connection is allowed).

Note

Round-robin or any other load-balanced routing between the local replicas of the secondary database is not supported.

Next steps