使用只读副本卸载只读的查询工作负荷Use read-only replicas to offload read-only query workloads

适用于: Azure SQL 数据库 Azure SQL 托管实例

作为高可用性体系结构的一部分,“高级”和“业务关键”服务层级中的每个单一数据库、弹性池数据库和托管实例会自动预配有一个主读写副本和多个次要只读副本。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 several secondary read-only replicas. 为次要副本预配的计算大小与主要副本相同。The secondary replicas are provisioned with the same compute size as the primary replica. “读取扩展”功能允许使用一个只读副本的计算容量而不是在读写副本上运行它们来卸载只读工作负荷。The read scale-out 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 way, some read-only workloads can be isolated from the read-write workloads, and will not affect their 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 for load-balancing read-only workloads that 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 the feature.

只读副本

新的“高级”、“业务关键”和“超大规模”数据库中默认已启用读取扩展功能。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.

备注

托管实例的“业务关键”服务层级中始终启用读取扩展功能。Read scale-out is always enabled in the Business Critical service tier of Managed Instance.

如果在 SQL 连接字符串中配置为 ApplicationIntent=ReadOnly,则将应用程序重定向到该数据库或托管实例的只读副本。If your SQL connection string is configured with ApplicationIntent=ReadOnly, the application will be redirected to a read-only replica of that database or managed instance. 有关如何使用 ApplicationIntent 属性的信息,请参阅指定应用程序意向For information on how to use the ApplicationIntent property, see Specifying Application Intent.

如果你希望确保应用程序始终连接到主要副本,而不管 SQL 连接字符串中的 ApplicationIntent 设置如何,则必须在创建数据库或更改其配置时显式禁用读取扩展。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.

备注

只读副本不支持查询存储和 SQL Profiler 功能。Query Store and SQL Profiler features are not supported on 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.

备注

区域中的复制延迟较低,且这种情况很少见。Replication latencies within the region are low, and this situation is rare. 若要监视复制延迟,请参阅对只读副本进行监视和故障排除To monitor replication latency, see Monitoring and troubleshooting read-only replica.

连接到只读副本Connect to a read-only replica

为数据库启用读取横向扩展时,客户端提供的连接字符串中的 ApplicationIntent 选项会指示连接是要路由到写入副本还是只读副本。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. 具体而言,如果 ApplicationIntent 值为 ReadWrite(默认值),则连接会定向到读写副本。Specifically, if the ApplicationIntent value is ReadWrite (the default value), the connection will be directed to the read-write replica. 这与连接字符串中未包括 ApplicationIntent 时的行为相同。This is identical to the behavior when ApplicationIntent is not included in the connection string. 如果 ApplicationIntent 值为 ReadOnly,则连接将路由到只读副本。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.chinacloudapi.cn;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.chinacloudapi.cn;Database=<mydatabase>;ApplicationIntent=ReadWrite;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Server=tcp:<server>.database.chinacloudapi.cn;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 in the context of your database. 连接到只读副本时,它将返回 READ_ONLY。It will return READ_ONLY when you are connected to a read-only replica.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

备注

在“高级”和“业务关键”服务层级中,只有一个只读副本随时可供访问。In Premium and Business Critical service tiers, only one of the read-only replicas is accessible at any given time. “超大规模”层级支持多个只读副本。Hyperscale supports multiple read-only replicas.

对只读副本进行监视和故障排除Monitoring and troubleshooting read-only replicas

当连接到只读副本时,动态管理视图 (DMV) 反映副本的状态,并可对其进行查询以进行监视和故障排除。When connected to a read-only replica, Dynamic Management Views (DMVs) reflect the state of the replica, and can be queried for monitoring and troubleshooting purposes. 数据库引擎提供了多个视图来公开各种监视数据。The database engine provides multiple views to expose a wide variety of monitoring data.

常用视图如下:Commonly used views are:

名称Name 目的Purpose
sys.dm_db_resource_statssys.dm_db_resource_stats 提供最近一小时的资源利用率指标,包括相对于服务目标限制的 CPU、数据 IO 和日志写入使用率。Provides resource utilization metrics for the last hour, including CPU, data IO, and log write utilization relative to service objective limits.
sys.dm_os_wait_statssys.dm_os_wait_stats 提供数据库引擎实例的聚合等待统计信息。Provides aggregate wait statistics for the database engine instance.
sys.dm_database_replica_statessys.dm_database_replica_states 提供副本运行状况状态和同步统计信息。Provides replica health state and synchronization statistics. 重做队列大小和重做速率用作只读副本上数据延迟的指示。Redo queue size and redo rate serve as indicators of data latency on the read-only replica.
sys.dm_os_performance_counterssys.dm_os_performance_counters 提供数据库引擎性能计数器。Provides database engine performance counters.
sys.dm_exec_query_statssys.dm_exec_query_stats 提供每个查询的执行统计信息,例如执行次数、已用 CPU 时间等。Provides per-query execution statistics such as number of executions, CPU time used, etc.
sys.dm_exec_query_plan()sys.dm_exec_query_plan() 提供缓存的查询计划。Provides cached query plans.
sys.dm_exec_sql_text()sys.dm_exec_sql_text() 提供缓存的查询计划的查询文本。Provides query text for a cached query plan.
sys.dm_exec_query_profilessys.dm_exec_query_profiles 提供正执行查询时的实时查询进度。Provides real time query progress while queries are in execution.
sys.dm_exec_query_plan_stats()sys.dm_exec_query_plan_stats() 提供最近的已知实际执行计划,包括查询的运行时统计信息。Provides the last known actual execution plan including runtime statistics for a query.
sys.dm_io_virtual_file_stats()sys.dm_io_virtual_file_stats() 提供所有数据库文件的存储 IOPS、吞吐量和延迟统计信息。Provides storage IOPS, throughput, and latency statistics for all database files.

备注

逻辑 master 数据库中的 sys.resource_statssys.elastic_pool_resource_stats DMV 返回主要副本的资源利用率数据。The sys.resource_stats and sys.elastic_pool_resource_stats DMVs in the logical master database return resource utilization data of the primary replica.

监视具有扩展事件的只读副本Monitoring read-only replicas with Extended Events

连接到只读副本时,无法创建扩展事件会话。An extended event session cannot be created when connected to a read-only replica. 但是在 Azure SQL 数据库中,主要副本上创建和更改的数据库范围内扩展事件会话的定义将复制到只读副本(包括异地副本),并捕获只读副本上的事件。However, in Azure SQL Database, the definitions of database-scoped Extended Event sessions created and altered on the primary replica replicate to read-only replicas, including geo-replicas, and capture events on read-only replicas.

可独立于主要副本来启动和停止基于主要副本中会话定义的只读副本上的扩展事件会话。An extended event session on a read-only replica that is based on a session definition from the primary replica can be started and stopped 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

可始终将只读副本上运行的查询映射到快照事务隔离级别。Queries that run on read-only replicas are always mapped to the snapshot transaction isolation level. 快照隔离使用行版本控制,以避免在读取器阻止写入器的情况下发生阻止。Snapshot isolation uses row versioning to avoid blocking scenarios where readers block writers.

极少数情况下,如果快照隔离事务访问已在其他并发事务中修改的对象元数据,则可能会收到错误 3961,“数据库 "%.*ls" 中的快照隔离事务失败,因为自此事务启动后,该语句所访问的对象已由其他并发事务中的 DDL 语句修改。In rare cases, if a snapshot isolation transaction accesses object metadata that has been modified in another concurrent transaction, it may receive error 3961, "Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. 禁用它是因为元数据未进行版本控制。It is disallowed because the metadata is not versioned. 如果与快照隔离混合,对元数据进行并发更新可能导致不一致。”A concurrent update to metadata can lead to inconsistency if mixed with snapshot isolation."

只读副本上的长时间运行的查询Long-running queries on read-only replicas

只读副本上运行的查询需要访问查询中引用的对象的元数据(表、索引、统计信息等)极少数情况下,如果在主要副本上修改元数据对象,而查询持有对只读副本上同一对象的锁定,则该查询可以阻止将主要副本的更改应用到只读副本的过程。Queries running on read-only replicas need to access metadata for the objects referenced in the query (tables, indexes, statistics, etc.) In rare cases, if a metadata object is modified on the primary replica while a query holds a lock on the same object on the read-only replica, the query can block the process that applies changes from the primary replica to the read-only replica. 如果此类查询长时间运行,会导致只读副本与主要副本明显不同步。If such a query were to run for a long time, it would cause the read-only replica to be significantly out of sync with the primary replica.

如果只读副本上长时间运行的查询导致此类阻止,则自动终止它,并且会话收到错误 1219,“由于高优先级 DDL 操作,会话已断开连接”。If a long-running query on a read-only replica causes this kind of blocking, it will be automatically terminated, and the session will receive error 1219, "Your session has been disconnected because of a high priority DDL operation".

备注

如果针对只读副本运行查询时收到错误 3961 或错误 1219,则重试查询。If you receive error 3961 or error 1219 when running queries against a read-only replica, retry the query.

提示

在“高级”和“业务关键”服务层级中,当连接到只读副本时,sys.dm_database_replica_states DMV 中的 redo_queue_sizeredo_rate 列都可用于监视数据同步过程,作为只读副本上数据延迟的指示。In Premium and Business Critical service tiers, when connected to a read-only replica, the redo_queue_size and redo_rate columns in the sys.dm_database_replica_states DMV may be used to monitor data synchronization process, serving as indicators of data latency on the read-only 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. “读取扩展”在配置了 0 个副本的“超大规模”数据库上自动禁用。Read scale-out is automatically disabled on Hyperscale databases configured with zero replicas.

可以使用以下方法,对“高级”或“业务关键”服务层级中的单一数据库和弹性池数据库禁用和重新启用读取扩展。You can disable and re-enable read scale-out on single databases and elastic pool databases in the Premium or Business Critical service tiers using the following methods.

备注

对于单一数据库和弹性池数据库,禁用读取扩展的功能是出于后向兼容性而提供的。For single databases and elastic pool databases, the ability to disable read scale-out is provided for backward compatibility. 无法在“业务关键”托管实例上禁用读取扩展。Read scale-out cannot be disabled on Business Critical managed instances.

Azure 门户Azure portal

可以在“配置”数据库边栏选项卡上管理“读取扩展”设置。You can manage the read scale-out setting on the Configure database blade.

PowerShellPowerShell

重要

仍然支持 PowerShell Azure 资源管理器模块,但是所有未来的开发都是针对 Az.Sql 模块。The PowerShell Azure Resource Manager module is still supported, but all future development is for the Az.Sql module. Azure 资源管理器模块至少在 2020 年 12 月之前将继续接收 bug 修补程序。The Azure Resource Manager module will continue to receive bug fixes until at least December 2020. Az 模块和 Azure 资源管理器模块中的命令参数大体上是相同的。The arguments for the commands in the Az module and in the Azure Resource Manager modules are substantially identical. 若要详细了解其兼容性,请参阅新 Azure PowerShell Az 模块简介For more information about their compatibility, see Introducing the new Azure PowerShell Az module.

在 Azure PowerShell 中管理读取横向扩展需要安装 Azure PowerShell 2016 年 12 月版或更高版本。Managing read scale-out in Azure PowerShell requires the December 2016 Azure PowerShell release or newer. 有关最新的 PowerShell 版本,请参阅 Azure PowerShellFor the newest PowerShell release, see Azure PowerShell.

在 Azure PowerShell 中调用 Set-AzSqlDatabase cmdlet,并为 -ReadScale 参数传入所需的值(EnabledDisabled),即可禁用或重新启用读取扩展。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 <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -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 <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -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 <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Enabled

REST APIREST API

若要创建已禁用读取扩展的数据库,或更改现有数据库的设置,请在将 readScale 属性设置为 EnabledDisabled 的情况下使用以下方法,如以下示例请求所示。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 following sample request.

Method: PUT
URL: https://management.chinacloudapi.cn/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.

在只读副本上使用 tempdb 数据库Using the tempdb database on a read-only replica

主要副本上的 tempdb 数据库不会复制到只读副本。The tempdb database on the primary replica is not replicated to the read-only replicas. 每个副本具有自身的 tempdb 数据库,该数据库是创建该副本时创建的。Each replica has its own tempdb database that is created when the replica is created. 这确保 tempdb 可更新,并可以在执行查询期间进行修改。This ensures that tempdb is updateable and can be modified during your query execution. 如果只读工作负荷依赖于使用 tempdb 对象,则应创建这些对象作为查询脚本的一部分。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

异地复制的辅助数据库具有与主数据库相同的高可用性体系结构。Geo-replicated secondary databases have the same High Availability architecture as the primary databases. 如果要连接到启用了读取扩展的异地复制辅助数据库,则设置为 ApplicationIntent=ReadOnly 的会话将路由到其中一个高可用性副本,就像在主要的可写入数据库上路由它们一样。If you're connecting to the geo-replicated secondary database with read scale-out enabled, your sessions with ApplicationIntent=ReadOnly will be routed to one of the high availability replicas in the same way they are routed on the primary writeable database. 而未设为 ApplicationIntent=ReadOnly 的会话将路由到异地复制辅助数据库的主要副本,该副本也为只读。The sessions without ApplicationIntent=ReadOnly will be routed to the primary replica of the geo-replicated secondary, which is also read-only.

通过这种方式,创建异地副本将为读写主数据库另外提供两个只读副本,总计三个只读副本。In this fashion, creating a geo-replica provides two more read-only replicas for a read-write primary database, for a total of three read-only replicas. 其他每个异地副本提供另一对只读副本。Each additional geo-replica provides another pair of read-only replicas. 可在任何 Azure 区域(包括主数据库的区域)创建异地副本。Geo-replicas can be created in any Azure region, including the region of the primary database.

备注

异地复制的辅助数据库的副本之间没有自动轮循机制或任何其他负载均衡路由。There is no automatic round-robin or any other load-balanced routing between the replicas of a geo-replicated secondary database.

后续步骤Next steps