- Article
- 12 minutes to read
Applies to: 
Azure SQL-DatenbankAzure SQL Managed Instance
As part ofhigh availability architecture, each individual database, elastic pool database, and managed instance in the Premium and Business Critical service tiers is automatically provisioned with a primary read/write replica and one or more secondary read replicas. Azure SQL Managed Instance supports a read-only secondary replica. Secondary replicas are provisioned with the same compute size as the primary replica. Himscalable readingThis feature allows you to offload read-only workloads using the compute capacity of one of the read-only replicas instead of running them on the read/write replica. This allows some read-only workloads to be isolated from read-write workloads and not impact their performance. The feature is intended for applications that contain logically separated read-only workloads such as analytics. At Premium and Business Critical service levels, applications can achieve performance benefits by utilizing this additional capacity at no additional cost.
Himscalable readingThe capability is also available at the Hyperscale service tier if at least one is presentsecondary replicaIt will be added. Secondary Hyperscalenamed replicasprovide independent scaling, access isolation, workload isolation, support for a variety of read scaling scenarios, and other benefits. multiple secondaryThe HA replicaIt can be used to balance read-only workloads that require more resources than are available on a high-availability secondary replica.
The High Availability architecture of the Basic, Standard, and Universal Purpose service levels does not include replicas. Himscalable readingThe feature is not available on these service levels. When using Azure SQL Databasegeographic replicasmay provide similar functionality at these service levels. When using Azure SQL Managed Instance and failover groups, theRead-only listener for failover groupscan each provide similar functions.
The following diagram illustrates the role of premium and mission-critical databases and managed instances.
Himscalable readingThe feature is enabled by default on new Premium, Business Critical, and Hyperscale databases.
Use
Read scaling is always enabled at the mission-critical service tier of SQL Managed Instance and for hyperscale databases with at least one secondary replica.
If your SQL connection string is configured withApplicationIntent = ReadOnly, the application is redirected to a read-only replica of that database or managed instance. Information on using thepurpose of useproperty, seeSpecify the app intent.
Only for Azure SQL Database if you want to ensure that your application connects to the primary replica regardlesspurpose of usein the SQL connection string, you must explicitly disable the read extension when creating the database or changing its configuration. For example, if you are upgrading your database from Standard or General Tier to Premium or Mission Critical tier and want to ensure that all your connections continue to go to the primary replica, disable read clamping. For details on how to disable, seeEnable and disable horizontal reading scaling.
Use
Query Store and SQL Profiler features are not supported on read-only replicas.
The consistency of the data
Data changes made on the primary replica are persisted in read-only replicas either synchronously or asynchronously, depending on the type of replica. However, for all replica types, reads from a read-only replica are always asynchronous to the parent replica. Within a session attached to a read-only replica, reads are always transaction-consistent. Because the latency for data propagation is variable, different replicas may return data at slightly different times relative to the primary and each other. If a read-only replica becomes unavailable and a session reconnects, it can connect to a replica that is at a different point in time than the original replica. Likewise, if an application modifies data using a read/write session on the primary server and reads it immediately using a read-only session on a read-only replica, the latest changes might not be visible immediately.
Typical data seeding latency between the primary replica and read-only replicas ranges from tens of milliseconds to tens of seconds. However, there is no hard limit to the latency of data propagation. Conditions such as high resource utilization on the replica can significantly increase latency. Applications that require guaranteed data consistency between sessions or immediate reading of committed data should use the primary replica.
Use
For information on monitoring data propagation latency, seeMonitor and troubleshoot read-only replicas.
Connect to a read-only replica
When you enable read scale-out for a database, thepurpose of useThe option in the client-supplied connection string determines whether the connection is forwarded to the write replica or a read-only replica. Especially if thepurpose of usethe value isRead Write(Default) the connection is directed to the read/write replica. This is identical to the when behaviorpurpose of useIt is not included in the connection string. If hepurpose of usethe value isJust read, the connection is forwarded to a read-only replica.
For example, the following connection string connects the client to a read-only replica (replacing the items in the brackets with the correct values for your environment and removing the brackets):
Server=tcp:<servidor>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Cifrar=Verdadero;To connect to a read-only replica using SQL Server Management Studio (SSMS), chooseoptions
SelectAdditional connection parametersand enterApplicationIntent = ReadOnlyand then selectConnect
One of the following connection strings connects the client to a read/write replica (replace the items in the brackets with the correct values for your environment and remove the brackets):
Server=tcp:<servidor>.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; Cifrar=Verdadero;Verify that a read-only replica is connected
You can verify that you are connected to a read-only replica by running the following query in the context of your database. Returns READ_ONLY when connected to a read-only replica.
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Atualizar');Use
In the Premium and Business Critical service levels, only one of the read-only replicas can be accessed at a time. Hyperscale supports multiple read-only replicas.
Monitor and troubleshoot read-only replicas
When connected to a read-only replica, dynamic management views (DMVs) reflect the status of the replica and can be queried for monitoring and troubleshooting purposes. The database engine provides multiple views to expose a variety of monitoring data.
The following views are commonly used for replica monitoring and troubleshooting:
| Name | purpose |
|---|---|
| sys.dm_db_resource_stats | Provides resource utilization metrics for the last hour, including CPU utilization, data I/O, and log writes versus service target thresholds. |
| sys.dm_os_wait_stats | Provides aggregate wait statistics for the DB engine instance. |
| sys.dm_database_replica_states | Provides replica health and synchronization statistics. The redo queue size and redo rate serve as indicators of data propagation latency on the read-only replica. |
| sys.dm_os_performance_counters | Provides performance counters for the database engine. |
| sys.dm_exec_query_stats | Provides execution statistics per query, e.g. B. Number of executions, CPU time used, etc. |
| sys.dm_exec_query_plan() | Provides cached query plans. |
| sys.dm_exec_sql_text() | Provides query text for a cached query plan. |
| sys.dm_exec_query_profiles | Provides real-time query progress as queries are executed. |
| sys.dm_exec_query_plan_stats() | Provides the actual last known execution plan, including runtime statistics, for a query. |
| sys.dm_io_virtual_file_stats() | Provides latency, throughput, and storage IOPS statistics for all files in the database. |
Use
Himsys.resource_statsjsys.elastic_pool_resource_statsDMV in LogikMaestroThe database returns resource usage data from the primary replica.
Monitoring of read-only replicas with extended events
An extended event session cannot be created when connecting to a read-only replica. However, in Azure SQL Database, database scope definitionsextended eventSessions created and modified on the primary replica are replicated to read-only replicas, including geographic replicas, and capture events on read-only replicas.
An extended event session on a read-only replica based on a primary replica's session definition can be started and terminated independently of the primary replica. When an extended event session is dropped on the primary replica, it is also dropped on all read-only replicas.
Transaction isolation level on read-only replicas
Transactions on read-only replicas always use the snapshottransaction isolation level, regardless of the session's transaction isolation level and any query hints. Snapshot isolation uses row versioning to avoid deadlock scenarios where readers block writers.
On rare occasions, when a snapshot isolation transaction accesses object metadata that has been modified in another concurrent transaction, you may receive an error message3961, "Snapshot isolation transaction in database '%.*ls' failed because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. Not allowed because the metadata is not versioned. Simultaneously updating metadata combined with snapshot isolation can lead to inconsistencies."
Long-running queries on read-only replicas
Queries run against read-only replicas need access to the metadata of the objects referenced in the query (tables, indexes, statistics, etc.). In rare cases when object metadata is changing on the primary replica while a query is blocking it. Object on the read-only replica can queryBlockthe process that applies changes from the primary replica to the read-only replica. Running this query for too long would cause the read-only replica to become significantly out of sync with the primary replica. For replicas that are potential targets for failover (secondary replicas in the Premium and Business Critical service tiers, hyperscale high-availability replicas, and all geographic replicas), this would also delay database recovery if this occurs. a failover, which would result in more than expected downtime.
If a long-running query against a read-only replica directly or indirectly causes this type of blocking, it can be automatically terminated to avoid excessive data latency and potential impact on database availability. The session receives error 1219 "Your session was disconnected due to a high priority DDL operation" or error 3947 "The transaction was interrupted because the secondary computation failed. Try the transaction again.”
Use
If you get errors 3961, 1219, or 3947 when running queries against a read-only replica, try the query again. Alternatively, avoid operations that change object metadata (schema changes, index maintenance, statistics updates, etc.) on the primary replica while long-running queries are running on secondary replicas.
addendum
If you connect to a read-only replica at the Premium and Business Critical service tiers, theredo_queue_sizejredo_ratecolumns insidesys.dm_database_replica_statesThe DMV can be used to monitor the data synchronization process and serves as an indicator of the latency of data propagation on the read-only replica.
Enable and disable read scale-out for SQL database
For SQL Managed Instance, the read extension is automatically enabled in the Business Critical service tier and is not available in the General service tier. It is not possible to disable and re-enable horizontal reading scaling.
For SQL Database, read scale-out is enabled by default in Premium, Business Critical, and Hyperscale service levels. Reader Extension cannot be enabled on the Basic, Standard, or Universal service tiers. Read spanning is automatically disabled on Hyperscale databases configured with zero secondary replicas.
For single and pooled databases in Azure SQL Database, you can disable and re-enable read extension in Premium or Business Critical service tiers using the Azure portal and Azure PowerShell. These options are not available for SQL Managed Instance because the read extension cannot be disabled.
Use
For single databases and pooled elastic databases, the ability to disable read expansion for backward compatibility is provided. Readout scaling cannot be disabled on mission-critical managed instances.
blue portal
For Azure SQL Database, you can manage read extension settings inTo set updatabase sheet. Using the Azure portal to enable or disable the read extension is not available for Azure SQL Managed Instance.
Power Shell
Important
The PowerShell Azure Resource Manager module will continue to be supported, but all future developments will target the Az.Sql module. The Azure Resource Manager module will continue to receive bug fixes through at least December 2020. The command arguments in the Az module and the Azure Resource Manager modules are essentially the same. For more information on compatibility, seeIntroducing the new Azure PowerShell Az module.
Managing the read extension in Azure PowerShell requires the December 2016 version of Azure PowerShell or later. For the latest version of PowerShell, seeAzure-PowerShell.
In Azure SQL Database, you can disable or re-enable read extension in Azure PowerShell by using theSet-AzSqlDatabasecmdlet and passing the desired value (activatedÖdisabled people) For the-ReadScaleParameter. Disabling read extension for SQL Managed Instance is not available.
To disable read scale-out for an existing database (replacing the items in the brackets with the correct values for your environment and removing the brackets):
Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale desativadoTo disable read scale-out in a new database (replacing the items in the brackets with the correct values for your environment and removing the brackets):
New-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled -Edition PremiumTo re-enable read scale-out for an existing database (replacing the items in the brackets with the correct values for your environment and removing the brackets):
Set-AzSqlDatabase -ResourceGroupName <resource group name> -ServerName <server name> -DatabaseName <database name> -ReadScale EnabledAPI-REST
To create a database with read extensions disabled or to change the configuration of an existing database, use the following method with theleerEscalaproperty defined asactivatedÖdisabled people, as in the example request below.
Method: PUTURL: https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014- 04-01-previewBody: { "Properties": { "readScale": "Disabled" }}For more information, seeDatabases - create or update.
Use oftempdbDatabase on a read-only replica
HimtempdbThe database on the primary replica is not replicated to read-only replicas. Each replica has its own.tempdbThe database created when the replica is created. This ensures thistempdbIt is updatable and can be modified during query execution. When your read-only workload depends on usagetempdbobjects, you must create these objects as part of the same workload while attached to a read-only replica.
Using read spanning with geographically replicated databases
Secondary geo-replicated databases have the same high-availability architecture as primary databases. If you connect to the secondary geo-replicated database with the read extension enabled, your sessions will be withApplicationIntent = ReadOnlyThey are routed to one of the HA replicas in the same way they are routed to the primary writable database. sessions withoutApplicationIntent = ReadOnlyit is forwarded to the primary replica of the geo-replicated secondary, which is also read-only.
In this way, creating a geographic replica can provide multiple additional read-only replicas for a read/write primary database. Each additional geographic replica provides another set of read-only replicas. Geo-replicas can be created in any Azure region, including the database's primary region.
Use
There is no automatic rotation or other load-balanced routing between the replicas of a geo-replicated secondary, except for a hyperscaled geo-replica with more than one HA replica. In this case, read-only intent sessions are distributed across all HA replicas in a geographic replica.
Support for roles on read-only replicas
The following is a list of some features' behavior on read-only replicas:
- Monitoring of read-only replicas is automatically enabled. See for more details on storage folder hierarchy, naming conventions, and record formatSQL database audit log format.
- Insights into query performancebased on data fromconsulting business, which is not currently tracking any activity on the read-only replica. Query Performance Insight does not show any queries running on the read-only replica.
- Autotuning is based on Query Store as described inAutofit-Paper. Autotuning only works for workloads running on the primary replica.
Next Steps
- For information about the SQL Database Hyperscale offering, seeHyperscale-Servicelevel.