alwayson高可用组
In continuation to the Availability Groups series, here you have another curiosity coming to make your job easier and help you to provide a solution to your customers 🙂
在“可用性组”系列的延续中，您还有另一个好奇心可以使您的工作更轻松，并帮助您为客户提供解决方案solution
This time we will be talking about listeners. Basically, we will be talking about the limitation of one listener per Availability Group. If you never tested / tried this, this is what happens when you already have a listener in the Availability Group, and try to create another one:
这次我们将谈论听众。 基本上，我们将讨论每个可用性组只能有一个侦听器的限制。 如果您从未测试/尝试过此操作，那么在可用性组中已经有一个侦听器并尝试创建另一个侦听器时，会发生这种情况：
To be honest, I have no idea why Microsoft imposed this limitation. In general, one listener should be enough, but there are cases where more than one listener would be handy.
老实说，我不知道微软为什么要施加此限制。 通常，一个听众就足够了，但是在某些情况下，不只一个听众会很方便。
可用性组上的侦听器 ( Listener on Availability Groups )
Listeners are not a mandatory in order to setup an Availability Group, but are desirable in order to have a good setup. The purpose of a listener on SQL Server 2012 and 2014 is redirect the connections to the primary replica or secondary replica, depending on the connection string. The listener is a Virtual Network name (VNN), associated to one or more IPs and is reachable using a specified port.
侦听器对于设置可用性组不是强制性的，但为了具有良好的设置，它是必需的。 SQL Server 2012和2014上侦听器的目的是根据连接字符串将连接重定向到主副本或辅助副本。 侦听器是一个虚拟网络名称（VNN），与一个或多个IP关联，可以使用指定的端口访问。
When a failover happens, the listener will redirect to the “new” primary replica.
发生故障转移时，侦听器将重定向到“新”主副本。
In the case where a read-only routing is set, all the read-intent connections will be redirected to the proper secondary read-only replica.
在设置了只读路由的情况下，所有的读意图连接将被重定向到正确的辅助只读副本。
This means that the application have no need to know the name / IP of the physical replicas of the Availability Group, in order to setup the connection.
这意味着应用程序无需知道可用性组的物理副本的名称/ IP，就可以建立连接。
为什么我需要另一个听众？ ( Why I would need another listener? )
This is the question that I usually hear. The answer is: there are legacy applications where you cannot change the connection string. When you need to migrate the underline database to another instance, there are two solutions to workaround that problem:
这是我通常听到的问题。 答案是：有些传统应用程序无法更改连接字符串。 当您需要将下划线数据库迁移到另一个实例时，有两种解决方案来解决该问题：
- Create a DNS pointer to redirect the connections to the new instance. 创建一个DNS指针以将连接重定向到新实例。
- Create a local alias in the application’s server. 在应用程序的服务器中创建本地别名。
Both of those solutions are out of the DBA’s control and, by the way, the described problem is not something that would make me create mora than one listener in an Availability Group, right?
这两种解决方案都不在DBA的控制范围内，顺便说一句，所描述的问题不是让我比可用性组中的一个侦听器更令人讨厌的事情，对吗？
Ok, what if you have a consolidation project on your hands, and the purpose is migrate some different databases, supporting multiple legacy databases and you also need to migrate the deprecated Database Mirroring solution to Availability Groups? Now this is more complex. So let’s see an example:
好的，如果您手头有一个整合项目，目的是迁移一些不同的数据库，以支持多个旧数据库，并且还需要将不赞成使用的数据库镜像解决方案迁移到可用性组？ 现在，这更加复杂。 因此，让我们看一个例子：
Basically, the image is showing the described scenario, where we migrated 6 databases from 3 old SQL Server 2005 instances to a brand new SQL Server 2014 instance. All those six databases are going to be part of the same Availability Group, but the applications are not able to have their connection string changed. We need six listeners created here, but only one is allowed. Now what?
基本上，该图像显示了所描述的场景，其中我们将6个数据库从3个旧SQL Server 2005实例迁移到了全新SQL Server 2014实例。 所有这六个数据库将成为同一可用性组的一部分，但是应用程序无法更改其连接字符串。 我们需要在此处创建六个侦听器，但只允许一个侦听器。 怎么办？
如何在可用性组中创建多个侦听器？ ( How to create more than one Listener in an Availability Group? )
First of all I need to show this:
首先，我需要证明一下：
You can see the original text here.
您可以在此处看到原始文本。
This means that Microsoft is not recommending a change in the Availability Group by using other tool than SQL Server Management studio.
这意味着Microsoft不建议使用SQL Server Management Studio以外的其他工具来更改可用性组。
However, the same Microsoft, posted this:
但是，同一个Microsoft发布了此信息：
You can access this article using this link.
您可以使用此链接访问本文。
I will describe, step-by-step, how to add more than one listener in your Availability Group, by skipping the SQL Server Management studio, which is not recommended by Microsoft, so have more than one listener in your instance may not be supported by Microsoft.
我将逐步介绍如何通过跳过SQL Server Management Studio来在可用性组中添加多个侦听器（Microsoft不建议这样做），因此您的实例中可能不支持多个侦听器由Microsoft。
This process was tested in a SQL Server 2012 and a SQL Server 2014 instance, and worked like a charm! I also tested in the SQL Server 2016 CTP 2.1, but unfortunately the SQL Server is not recognizing the new listener.
此过程已在SQL Server 2012和SQL Server 2014实例中进行了测试，并且非常有用！ 我也在SQL Server 2016 CTP 2.1中进行了测试，但不幸的是SQL Server无法识别新的侦听器。
收据 ( The receipt )
You will need:
你会需要：
- 1 unit of SQL Server Management Studio 1个SQL Server Management Studio单元
- 1 unit of an AlwaysOn Failover Cluster 1个AlwaysOn故障转移群集单元
- 1 unit of an AlwaysOn Availability Group 1个AlwaysOn可用性组的单位
- 1 or more IPs 1个或更多IP
- 1 unit of a Virtual Network Name (VNN) 1个虚拟网络名称（VNN）
The steps:
步骤：
- Open the SQL Server Management Studio (SSMS) 打开SQL Server Management Studio（SSMS）
Go to “Run” (or just click
转到“运行”（或单击Go to the “Roles” node and select the role with the same name of the Availability group that you want to add the listener. In my case the AG’s name is “AG02”, so I need to select the “AG02” role.
转到“角色”节点，然后选择与要添加侦听器的“可用性”组名称相同的角色。 在我的情况下，AG的名称为“ AG02”，因此我需要选择“ AG02”角色。
Right-click on the role, select “Add Resource” and click on “Client Access Point”.
右键单击角色，选择“添加资源”，然后单击“客户端访问点”。
Now you will need to fill the Virtual Network Name and the relate IP, as bellow.
现在，您将需要填写以下虚拟网络名称和相关IP。
Click “Next” and confirm the inserted values.
单击“下一步”并确认插入的值。
If everything looks good, click next and wait to the resource to be created In the role. When the process is done, you will see the following.
如果一切正常，请单击“下一步”，然后等待角色中要创建的资源。 该过程完成后，您将看到以下内容。
- Just click “Finish” to close the window. 只需单击“完成”即可关闭窗口。
Now you will be able to see the resource in the role.
现在，您将可以看到角色中的资源。
As you can see, both the Virtual Network Name and the IP address are offline. So right click the Virtual Network Name, and click on “Bring Online”. This will force the IP to be taken online as well.
如您所见，虚拟网络名称和IP地址都处于脱机状态。 因此，右键单击虚拟网络名称，然后单击“联机”。 这也将迫使该IP联机。
Now that both VNN and the IP are online, we need to take another step to make it visible to SQL Server. Just right-click the AG resource (the one with the same name given to the AG and the same name of the Role) and go to Properties, as bellow:
现在，VNN和IP都已联机，我们需要进一步采取措施，使其对SQL Server可见。 只需右键单击AG资源（具有与AG相同名称且与角色相同名称的资源），然后转到“属性”，如下所示：
With the resource properties opened, go to the “Dependencies” tab and add a dependency on the Virtual Network Name (VNN) as you can see in the image. Do not forget to set the condition from “AND” to “OR”, otherwise if one of the listeners fail, the entire Availability Group will be taken offline.
在资源属性打开的情况下，转到“依赖性”选项卡，然后对虚拟网络名称（VNN）添加依赖性，如在镜像中看到的。 不要忘记将条件从“ AND”设置为“ OR”，否则，如果其中一个侦听器失败，则整个可用性组将脱机。
- Click “Ok”, and you are done with the Failover Cluster part. 单击“确定”，您将完成“故障转移群集”部分。
Now go to the SQL Server Management Studio, expand your Availability Group and see the “Listeners” node.
现在转到SQL Server Management Studio，展开您的可用性组，然后查看“侦听器”节点。
You will notice that the new listener is now visible from SQL Server!
您会注意到，现在可以从SQL Server中看到新的侦听器！
If you go to the Lister properties, you will notice that the port value is missing, so you will need to fill this and click in the “Ok” button in order to have the new listener working properly.
如果转到“ Lister”属性，则会注意到端口值丢失，因此需要填写该值并单击“确定”按钮，以使新的侦听器正常工作。
- You are done! 大功告成！
I hope this was useful for you and you keep reading the following articles about Availability Groups curiosities
我希望这对您有用，并且您继续阅读以下有关可用性组好奇心的文章
Thank you for reading!
感谢您的阅读！
翻译自: https://www.sqlshack.com/alwayson-availability-groups-curiosities-to-make-your-job-easier-part-3/
alwayson高可用组
277
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
