您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

使用只读副本对只读的查询工作负荷进行负载均衡Use read-only replicas to load-balance read-only query workloads

备注

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor 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. 读取扩展功能允许使用一个只读副本的容量而不是共享读写副本,对 SQL 数据库只读工作负载进行负载均衡。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.

只读副本

新的“高级”、“业务关键”和“超大规模”数据库中默认已启用读取扩展功能。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. 如果在 SQL 连接字符串中配置了 ApplicationIntent=ReadOnly,则网关会将应用程序重定向到该数据库的只读副本。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. 有关如何使用 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. 例如,如果你将数据库从标准层或常规用途层升级到 Premium、业务关键或超大规模层,并且想要确保所有连接继续转到主副本,请禁用读取横向扩展。有关如何禁用它的详细信息,请参阅启用和禁用读取横向扩展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 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.

备注

区域中的复制延迟较低,且这种情况很少见。Replication latencies within the region are low and this situation is rare.

连接到只读副本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 database’s read-write replica. 这与现有行为相同。This is identical to existing behavior. 如果 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.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. 连接到只读副本时,它将返回 READ_ONLY。It will return READ_ONLY when connected to a read-only replica.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability')

备注

在任何给定时间,ReadOnly 会话只能访问一个 AlwaysON 副本。At any given time only one of the AlwaysON replicas is accessible by the ReadOnly sessions.

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

连接到只读副本后,可以使用 sys.dm_db_resource_stats DMV 访问性能指标。When connected to a read-only replica, you can access the performance metrics using the sys.dm_db_resource_stats DMV. 若要访问查询计划统计信息,请使用 sys.dm_exec_query_statssys.dm_exec_query_plansys.dm_exec_sql_text 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.

备注

逻辑 master 数据库中的 DMV sys.resource_stats 返回主要副本的 CPU 使用率和存储数据。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. “读取扩展”在配置了 0 个副本的“超大规模”数据库上自动禁用。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.

备注

禁用读取扩展的功能是出于后向兼容性而提供的。The ability to disable Read Scale-Out is provided for backward compatibility.

Azure 门户Azure portal

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

PowerShellPowerShell

在 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.

您可以通过调用AzSqlDatabase cmdlet 并传入所需的值(EnabledDisabled--对于 -ReadScale 参数)来禁用或重新启用读取横向 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 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 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.

对只读副本使用 TempDBUsing TempDB on read-only replica

TempDB 数据库不会复制到只读副本。The TempDB database is not replicated to the read-only replicas. 每个副本具有自身的 TempDB 数据库版本,该版本是创建该副本时创建的。Each replica has its own version of TempDB database that is created when the replica is created. 系统确保 TempDB 可更新,并可以在执行查询期间进行修改。It 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

如果你正在使用读取扩展在异地复制数据库(例如,作为故障转移组成员的数据库)上对只读工作负荷进行负载均衡操作,请确保主数据库和异地复制辅助数据库上都启用了读取扩展。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. 如果要连接到启用了读取扩展的异地复制辅助数据库,则设置为 ApplicationIntent=ReadOnly 的会话将路由到其中一个副本,就像我们在主数据库上路由连接一样。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. 而未设为 ApplicationIntent=ReadOnly 的会话将路由到异地复制辅助数据库的主要副本,该副本也为只读。The sessions without ApplicationIntent=ReadOnly will be routed to the primary replica of the geo-replicated secondary, which is also read-only. 由于异地复制辅助数据库的终结点与主数据库不同,因此之前访问辅助数据库不需要设置 ApplicationIntent=ReadOnlyBecause 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. 为确保后向兼容性,sys.geo_replication_links DMV 显示secondary_allow_connections=2(允许的任何客户端连接)。To ensure backward compatibility, sys.geo_replication_links DMV shows secondary_allow_connections=2 (any client connection is allowed).

备注

不支持在辅助数据库的本地副本之间执行轮循机制或任何其他负载均衡路由。Round-robin or any other load-balanced routing between the local replicas of the secondary database is not supported.

后续步骤Next steps