从SSMS连接到辅助副本 [英] Connect to secondary replica from SSMS

问题描述

我有一个HA Always On设置有2个节点。我正在寻找一种可能性:)从SSMS连接到辅助只读副本。我读了几篇文章，说我在高级选项中设置了ApplicationIntent = ReadOnly，我已经这样了。每当我
连接到监听器时，我最终都会连接到主服务器。我创建了一个sql用户 'test'， 我在主要设置deny connect，然后我用这个用户登录到辅助，因为这个用户在辅助中是活动的[grant status]。我正在登录
失败错误。我是HA Always On的新手。所以想检查这是否是预期的行为，如果不是，我怎样才能使我的场景工作。此外，当我在SSMS中使用ApplicationIntent = ReadOnly并且连接到AG侦听器时，@@ servername是否会显示辅助
副本服务器名称？

I have a HA Always On set up with 2 nodes. I am looking for a possibility :) to connect to secondary read-only replica from SSMS. I read several articles that says set ApplicationIntent=ReadOnly in advanced options, which I already did. Every time when I connect to listener, I end up in connecting to primary server. I created a sql user 'test', I set deny connect in primary, then I connect with this user login to secondary as this user is active[grant status] in secondary. I am getting login failed error. I am new to HA Always On. So wanted to check if this is the expected behavior, if not how can I make my scenario working. Also, when I use ApplicationIntent=ReadOnly in SSMS and connect to AG listener, will the @@servername show secondary replica server name?

提前感谢您提供任何帮助或建议!!!

Thanks in advance for any help or suggestions!!!

推荐答案

你好Chan0078，

Hi Chan0078,

>>每当我连接到监听器时，我最终都会连接到主服务器。

>> Every time when I connect to listener, I end up in connecting to primary server.

>>我创建了一个sql用户 'test'， 我在主要设置deny connect，然后我用这个用户登录到辅助，因为这个用户在辅助中是活动的[grant status]。我正在登录失败错误。

>> I created a sql user 'test', I set deny connect in primary, then I connect with this user login to secondary as this user is active[grant status] in secondary. I am getting login failed error.

根据您的描述，我建议您检查以下内容：

According to your description, I would suggest you check the following:

1. 确保连接到正确的可用性组侦听器;
2. 确保您连接的侦听器和可用性数据库（您将连接的数据库）位于同一个AG中;
3. 确保您的只读路由配置正确，并且您可以获取
   
   监听器，客户端连接，应用程序故障转移作为参考。

1. Make sure that you connect to the right availability group listener;
2. Make sure that the listener you connect to and availability database(the database you’ll connect) are in the same AG;
3. Make sure that your read-only routing is correctly configured, and you can take Listeners, Client Connectivity, Application Failover as reference.

此外，您需要指定要连接的可用性数据库，尝试使用"初始目录=数据库名称; ApplicationIntent在SSMS中改为"ReadOnly "。您可以使用类似的
主题作为参考。

Besides, you need to specify which availability database to connect to, try to use "Initial Catalog=DB NAME;ApplicationIntent=ReadOnly" in SSMS instead. You may take this similar thread for reference.

如果您有任何其他问题，请告诉我。

If you have any other questions, please let me know.

问候，

Hannah

这篇关于从SSMS连接到辅助副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！