配置对 Always On 可用性组的次要副本的只读访问Configure read-only access to a secondary replica of an Always On availability group

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

默认情况下,允许对主要副本进行读写和读意向访问,不允许连接到 AlwaysOn 可用性组的次要副本。By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group. 本主题说明了如何通过使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management Studio或 PowerShell 来配置 Transact-SQLTransact-SQL中 AlwaysOn 可用性组的可用性副本的连接访问。This topic describes how to configure connection access on an availability replica of an Always On availability group in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell.

有关对次要副本允许只读访问的含义的信息以及有关对连接访问的介绍,请参阅关于对可用性副本的客户端连接访问 (SQL Server)活动次要副本:可读次要副本(Always On 可用性组)For information about the implications of enabling read-only access for a secondary replica and for an introduction to connection access, see About Client Connection Access to Availability Replicas (SQL Server) and Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

先决条件和限制Prerequisites and Restrictions

  • 若要配置不同的连接访问,您必须连接到承载主副本的服务器实例。To configure different connection access, you must be connected to the server instance that hosts the primary replica.

权限Permissions

任务Task 权限Permissions
在创建可用性组时配置副本To configure replicas when creating an availability group 需要 sysadmin 固定服务器角色的成员资格,以及 CREATE AVAILABILITY GROUP 服务器权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
修改可用性副本To modify an availability replica 对可用性组要求 ALTER AVAILABILITY GROUP 权限、CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

使用 SQL Server Management StudioUsing SQL Server Management Studio

配置对可用性副本的访问To configure access on an availability replica

  1. 在对象资源管理器中,连接到承载主副本的服务器实例,然后展开服务器树。In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

  2. 依次展开“Always On 高可用性” 节点和“可用性组” 节点。Expand the Always On High Availability node and the Availability Groups node.

  3. 单击要更改其副本的可用性组。Click the availability group whose replica you want to change.

  4. 右键单击该可用性副本,然后单击“属性” 。Right-click the availability replica, and click Properties.

  5. “可用性副本属性” 对话框中,可以更改主角色和辅助角色的连接访问设置,如下所示:In the Availability Replica Properties dialog box, you can change the connection access for the primary role and for the secondary role, as follows:

    • 对于辅助角色,从 “可读取辅助角色” 下拉列表中选择一个新值,如下所示:For the secondary role, select a new value from the Readable secondary drop list, as follows:

      No
      不允许与此副本的辅助数据库的用户连接。No user connections are allowed to secondary databases of this replica. 它们不可用于读访问。They are not available for read access. 这是默认设置。This is the default setting.

      仅限读意向Read-intent only
      仅允许与此副本的辅助数据库的只读连接。Only read-only connections are allowed to secondary databases of this replica. 辅助数据库全都可用于读访问。The secondary database(s) are all available for read access.

      Yes
      允许与此副本的辅助数据库的所有连接,但仅限读访问。All connections are allowed to secondary databases of this replica, but only for read access. 辅助数据库全都可用于读访问。The secondary database(s) are all available for read access.

    • 对于主角色,从 “主角色中的连接” 下拉列表中选择一个新值,如下所示:For the primary role, select a new value from the Connections in primary role drop list, as follows:

      允许所有连接Allow all connections
      主副本中的数据库允许所有连接。All connections are allowed to the databases in the primary replica. 这是默认设置。This is the default setting.

      允许读/写连接Allow read/write connections
      在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. 不允许 Application Intent 连接属性设置为 ReadOnly 的连接。Connections where the Application Intent connection property is set to ReadOnly are not allowed. 这可帮助阻止客户错误地将读意向工作负荷连接到主副本。This can help prevent customers from connecting a read-intent work load to the primary replica by mistake. 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

使用 Transact-SQLUsing Transact-SQL

配置对可用性副本的访问To configure access on an availability replica

备注

有关此过程的示例,请参阅本节后面的 示例 (Transact-SQL)For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. 连接到承载主副本的服务器实例。Connect to the server instance that hosts the primary replica.

  2. 若要指定的是新可用性组的副本,请使用 CREATE AVAILABILITY GROUPTransact-SQLTransact-SQL 语句。If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUPTransact-SQLTransact-SQL statement. 如果正在添加或修改现有可用性组的副本,请使用 ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL 语句。If you are adding or modifying a replica of an existing availability group, use the ALTER AVAILABILITY GROUPTransact-SQLTransact-SQL statement.

    • 若要配置辅助角色的连接访问,请在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 SECONDARY_ROLE 选项,如下所示:To configure connection access for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

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

      其中:where,

      NO
      不允许与此副本的辅助数据库的直接连接。No direct connections are allowed to secondary databases of this replica. 它们不可用于读访问。They are not available for read access. 这是默认设置。This is the default setting.

      READ_ONLYREAD_ONLY
      仅允许与此副本的辅助数据库的只读连接。Only read-only connections are allowed to secondary databases of this replica. 辅助数据库全都可用于读访问。The secondary database(s) are all available for read access.

      ALLALL
      允许与此副本的辅助数据库的所有连接,但仅限读访问。All connections are allowed to secondary databases of this replica, but only for read access. 辅助数据库全都可用于读访问。The secondary database(s) are all available for read access.

  3. 若要配置主角色的连接访问,请在 ADD REPLICA 或 MODIFY REPLICA WITH 子句中指定 PRIMARY_ROLE 选项,如下所示:To configure connection access for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    其中:where,

    READ_WRITEREAD_WRITE
    不允许 Application Intent 连接属性设置为 ReadOnly 的连接。Connections where the Application Intent connection property is set to ReadOnly are disallowed. 在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

    ALLALL
    主副本中的数据库允许所有连接。All connections are allowed to the databases in the primary replica. 这是默认设置。This is the default setting.

示例 (Transact-SQL)Example (Transact-SQL)

下面的示例将辅助副本添加到名为 AG2的可用性组。The following example adds a secondary replica to an availability group named AG2. 一个独立服务器实例 COMPUTER03\HADR_INSTANCE被指定为承载新的可用性副本。A stand-alone server instance, COMPUTER03\HADR_INSTANCE, is specified to host the new availability replica. 将此副本配置为对主角色允许读写连接,对辅助角色仅允许读意向连接。This replica configured to allow only read-write connections for the primary role and to allow only read-intent connections for secondary role.

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  

使用 PowerShellUsing PowerShell

配置对可用性副本的访问To configure access on an availability replica

备注

有关代码示例,请参阅本节后面的 示例 (PowerShell)For a code example, see Example (PowerShell), later in this section.

  1. 将目录 (cd) 更改为托管主副本的服务器实例。Change directory (cd) to the server instance that hosts the primary replica.

  2. 在将可用性副本添加到可用性组中时,使用 New-SqlAvailabilityReplica cmdlet。When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. 在修改现有可用性副本时,使用 Set-SqlAvailabilityReplica cmdlet。When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. 相关参数如下:The relevant parameters are as follows:

    • 若要配置辅助角色的连接访问,请指定 ConnectionModeInSecondaryRolesecondary_role_keyword 参数,其中 secondary_role_keyword 等于以下值之一:To configure connection access for the secondary role, specify the ConnectionModeInSecondaryRolesecondary_role_keyword parameter, where secondary_role_keyword equals one of the following values:

      AllowNoConnectionsAllowNoConnections
      不允许直接连接到辅助副本中的数据库,且不支持读取这些数据库。No direct connections are allowed to the databases in the secondary replica and the databases are not available for read access. 这是默认设置。This is the default setting.

      AllowReadIntentConnectionsOnlyAllowReadIntentConnectionsOnly
      只允许连接应用程序意向属性设置为 ReadOnly的辅助副本中的数据库。Connections are allowed only to the databases in the secondary replica where the Application Intent property is set to ReadOnly. 有关此属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about this property, see Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnectionsAllowAllConnections
      允许针对辅助副本中的数据库的所有连接进行只读访问。All connections are allowed to the databases in the secondary replica for read-only access.

    • 若要配置主要角色的连接访问,请指定 ConnectionModeInPrimaryRole_primary_role_keyword_参数,其中 primary_role_keyword 等于以下值之一:To configure connection access for the primary role, specify ConnectionModeInPrimaryRoleprimary_role_keyword, where primary_role_keyword equals one of the following values:

      AllowReadWriteConnectionsAllowReadWriteConnections
      不允许 Application Intent 连接属性设置为 ReadOnly 的连接。Connections where the Application Intent connection property is set to ReadOnly are disallowed. 在 Application Intent 属性设置为 ReadWrite 或者未设置 Application Intent 连接属性时,将允许连接。When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. 有关 Application Intent 连接属性的详细信息,请参阅 Using Connection String Keywords with SQL Server Native ClientFor more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnectionsAllowAllConnections
      主副本中的数据库允许所有连接。All connections are allowed to the databases in the primary replica. 这是默认设置。This is the default setting.

    备注

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL Server 2017SQL Server 2017 cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2017SQL Server 2017 PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor more information, see Get Help SQL Server PowerShell.

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

示例 (PowerShell)Example (PowerShell)

下面的示例将 ConnectionModeInSecondaryRoleConnectionModeInPrimaryRole 参数均设置为 AllowAllConnectionsThe following example, sets the both the ConnectionModeInSecondaryRole and ConnectionModeInPrimaryRole parameters to 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  
  

跟进:为可用性副本配置只读访问后Follow Up: After Configuring Read-Only Access for an Availability Replica

对可读取辅助副本的只读访问Read-only access to a readable secondary replica

在故障转移后可能会影响触发器和作业的因素Factors that might affect triggers and jobs after a failover

如果您在非可读取辅助数据库或可读取辅助数据库上正运行时具有将失败的触发器和作业,则需要编写针对这些触发器和作业的脚本,以便对给定副本进行检查以确定该数据库是主数据库还是可读取辅助数据库。If you have triggers and jobs that will fail when running on a non-readable secondary database or on a readable secondary database, you need to script the triggers and jobs to check on a given replica to determine whether the database is a primary database or is a readable secondary database. 若要获取该信息,请使用 DATABASEPROPERTYEX 函数以返回数据库的 Updateability 属性。To obtain this information, use the DATABASEPROPERTYEX function to return the Updateability property of the database. 若要标识只读数据库,请按如下所示将 READ_ONLY 指定为值:To identify a read-only database, specify READ_ONLY as the value, as follows:

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

若要标识读写数据库,请将 READ_WRITE 指定为值:To identify a read-write database, specify READ_WRITE as the value.

相关任务Related Tasks

相关内容Related Content

另请参阅See Also

AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
活动次要副本:可读次要副本(Always On 可用性组) Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)
关于对可用性副本的客户端连接访问 (SQL Server)About Client Connection Access to Availability Replicas (SQL Server)