当“可读辅助”为“只读”时，使用 SSMS 连接到 SQL Server 可用性组副本

问题

当连接到“可读辅助”设置为“仅读取意图”的SQL Server AlwaysOn 数据库副本时，您会收到以下错误消息：

使用 SQL Server Management Studio (SSMS)

 解决方案

首先我们需要了解为什么会出现这个错误。

通过 SQL Server Management Studio (SSMS) 连接到数据库时，错误消息不清楚，但查询抛出的错误非常清楚地说明了发生这种情况的原因。

在可用性副本上配置只读访问时， 您有 3 个选项：

1. 否- 不允许用户连接到该副本的辅助数据库。它们不可用于读取访问。这是默认设置。
2. 是- 允许与此副本的辅助数据库建立所有连接，但仅限于读取访问。辅助数据库均可供读取访问。
3. 只读 -只允许对此副本的辅助数据库进行只读连接。辅助数据库均可供读取访问。

仅读意向副本意味着辅助副本仅接受为此目的显式配置的连接，当您尝试连接到仅配置为读意向的 AG 辅助副本数据库而不显式使用正确的参数时，就会出现问题。

有两种选择可以解决此问题：

连接到应用程序意图=只读的副本

连接到 AG 辅助副本实例时，请使用ApplicationIntent=ReadOnly 参数。

要从 SSMS 执行此操作，请在连接之前在连接窗口中按“选项>>”按钮。

然后转到“其他连接参数”选项卡并 在文本框中输入ApplicationIntent=ReadOnly 。您现在应该可以连接了。

对于应用程序，您需要在连接字符串中添加适当的参数。这是一个例子：

("Driver={SQL Server Native Client 11.0};server=AG_Listener;Database=AdventureWorks;trusted_connection=yes;ApplicationIntent=readonly”)

使用sqlcmd实用程序连接时，还需要提供正确的参数 (-K)

sqlcmd -SAG_Listener -E -dDatabaseName -Kreadonly

　　

将副本可读辅助选项配置为是

仅当您确定不会影响任何应用程序时才应遵循此选项，因为更改此配置后它不会自动将它们重定向到只读副本。

注意：需要在主副本服务器上执行以下配置。

 

USE [master]
GO
ALTER AVAILABILITY GROUP [AG_Name]
MODIFY REPLICA ON N'ReplicaInstance' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
GO

 

　　

或者，您可以使用 SSMS 通过编辑 AG 属性（AlwaysOn 高可用性/可用性组/<“AG 名称”>）来更改配置，然后更改可读辅助值，如下所示：

其他注意事项

使用 AG 侦听器而不是 SQL Server 实例名称连接到 AG 数据库始终是一个好的策略。侦听器将保证您始终连接到主副本或只读辅助副本，具体取决于连接参数。

引用

https://www.mssqltips.com/sqlservertip/4511/connect-to-sql-server-availability-group-replica-with-ssms-when-readable-secondary-is-readintent-only/