配置对可用性副本的只读访问 (SQL Server)

默认情况下,允许对主副本进行读写和读意向访问,不允许连接到 AlwaysOn 可用性组的辅助副本。 本主题介绍如何使用 SQL Server Management Studio、Transact-SQL 或 PowerShell 在 SQL Server 2014 中对 AlwaysOn 可用性组的可用性副本 (replica) 配置连接访问。

有关为辅助副本 (replica) 启用只读访问的含义以及连接访问简介的信息,请参阅关于对可用性副本 (SQL Server) 和活动辅助数据库的客户端连接访问 :可读次要副本 (AlwaysOn 可用性组)

开始之前

先决条件和限制

  • 若要配置不同的连接访问,您必须连接到承载主副本的服务器实例。

安全性

权限

任务 权限
在创建可用性组时配置副本 需要 sysadmin 固定服务器角色的成员资格,以及 CREATE AVAILABILITY GROUP 服务器权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。
修改可用性副本 对可用性组要求 ALTER AVAILABILITY GROUP 权限、CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。

使用 SQL Server Management Studio

配置对可用性副本的访问

  1. 在对象资源管理器中,连接到承载主副本的服务器实例,然后展开服务器树。

  2. 依次展开 “AlwaysOn 高可用性” 节点和 “可用性组” 节点。

  3. 单击要更改其副本的可用性组。

  4. 右键单击该可用性副本,然后单击“属性” 。

  5. “可用性副本属性” 对话框中,可以更改主角色和辅助角色的连接访问设置,如下所示:

    • 对于辅助角色,从 “可读取辅助角色” 下拉列表中选择一个新值,如下所示:


      不允许与此副本的辅助数据库的用户连接。 它们不可用于读访问。 这是默认设置。

      仅限读意向
      仅允许与此副本的辅助数据库的只读连接。 辅助数据库全都可用于读访问。


      允许与此副本的辅助数据库的所有连接,但仅限读访问。 辅助数据库全都可用于读访问。

    • 对于主角色,从 “主角色中的连接” 下拉列表中选择一个新值,如下所示:

      允许所有连接
      主副本中的数据库允许所有连接。 这是默认设置。

      允许读/写连接
      在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。 不允许 Application Intent 连接属性设置为 ReadOnly 的连接。 这可帮助阻止客户错误地将读意向工作负荷连接到主副本。 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native Client

“使用 Transact-SQL”

配置对可用性副本的访问

注意

有关此过程的示例,请参阅本节后面的 示例 (Transact-SQL)

  1. 连接到承载主副本的服务器实例。

  2. 如果指定的是新可用性组的副本,请使用 CREATE AVAILABILITY GROUPTransact-SQL 语句。 如果要添加或修改现有可用性组的副本,请使用 ALTER AVAILABILITY GROUPTransact-SQL 语句。

    • 若要配置辅助角色的连接访问,请在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 SECONDARY_ROLE 选项,如下所示:

      SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

      其中:


      不允许与此副本的辅助数据库的直接连接。 它们不可用于读访问。 这是默认设置。

      READ_ONLY
      仅允许与此副本的辅助数据库的只读连接。 辅助数据库全都可用于读访问。

      ALL
      允许与此副本的辅助数据库的所有连接,但仅限读访问。 辅助数据库全都可用于读访问。

  3. 若要配置主角色的连接访问,请在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 PRIMARY_ROLE 选项,如下所示:

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    其中:

    READ_WRITE
    不允许 Application Intent 连接属性设置为 ReadOnly 的连接。 在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native Client

    ALL
    主副本中的数据库允许所有连接。 这是默认设置。

示例 (Transact-SQL)

下面的示例将辅助副本添加到名为 AG2的可用性组。 一个独立服务器实例 COMPUTER03\HADR_INSTANCE被指定为承载新的可用性副本。 将此副本配置为对主角色允许读写连接,对辅助角色仅允许读意向连接。

ALTER AVAILABILITY GROUP AG2   
   ADD REPLICA ON   
      'COMPUTER03\HADR_INSTANCE' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',  
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )  
         );   
GO  

使用 PowerShell

配置对可用性副本的访问

注意

有关代码示例,请参阅本节后面的 PowerShell 示例。

  1. 将目录 (cd) 更改为承载主副本的服务器实例。

  2. 在将可用性副本添加到可用性组中时,请使用 New-SqlAvailabilityReplica cmdlet。 在修改现有可用性副本时,请使用 Set-SqlAvailabilityReplica cmdlet。 相关参数如下:

    • 若要为辅助角色配置连接访问,请ConnectionModeInSecondaryRole指定 secondary_role_keyword 参数,其中 secondary_role_keyword 等于以下值之一:

      AllowNoConnections
      不允许直接连接到辅助副本中的数据库,且不支持读取这些数据库。 这是默认设置。

      AllowReadIntentConnectionsOnly
      只允许连接应用程序意向属性设置为 ReadOnly的辅助副本中的数据库。 有关此属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native Client

      AllowAllConnections
      允许针对辅助副本中的数据库的所有连接进行只读访问。

    • 若要配置主角色的连接访问,请指定 ConnectionModeInPrimaryRoleprimary_role_keyword,其中 primary_role_keyword 等于以下值之一:

      AllowReadWriteConnections
      不允许 Application Intent 连接属性设置为 ReadOnly 的连接。 在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native Client

      AllowAllConnections
      主副本中的数据库允许所有连接。 这是默认设置。

    注意

    若要查看 cmdlet 的语法,请在 SQL Server 2014 PowerShell 环境中使用 Get-Help cmdlet。 有关详细信息,请参阅 Get Help SQL Server PowerShell

若要设置和使用SQL Server PowerShell 提供程序,请参阅 SQL Server PowerShell 提供程序

下面的示例将 ConnectionModeInSecondaryRoleConnectionModeInPrimaryRole 参数均设置为 AllowAllConnections

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  

Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" `
 -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" `
-InputObject $primaryReplica

跟进:为可用性副本配置只读访问后

对可读取辅助副本的只读访问

在故障转移后可能会影响触发器和作业的因素

如果您在非可读取辅助数据库或可读取辅助数据库上正运行时具有将失败的触发器和作业,则需要编写针对这些触发器和作业的脚本,以便对给定副本进行检查以确定该数据库是主数据库还是可读取辅助数据库。 若要获取该信息,请使用 DATABASEPROPERTYEX 函数以返回数据库的 Updatability 属性。 若要标识只读数据库,请按如下所示将 READ_ONLY 指定为值:

DATABASEPROPERTYEX([db name],'Updatability') = N'READ_ONLY'  

若要标识读写数据库,请将 READ_WRITE 指定为值:

Related Tasks

相关内容

另请参阅

AlwaysOn 可用性组概述 (SQL Server)
活动次要副本:可读辅助副本(AlwaysOn 可用性组)
关于对可用性副本的客户端连接访问 (SQL Server)