alwayson高可用组

In this article we are going to explore how to configure an Availability Group between a clustered instance and a standalone instance, showing, step-by-step, how to setup a possible Disaster Recovery environment.

在本文中，我们将探讨如何在群集实例和独立实例之间配置可用性组，逐步介绍如何设置可能的灾难恢复环境。

Introduced on SQL Server 2012, the Availability Groups brought the expectation to be an improved version of the database mirroring, which will be discontinued soon. The AlwaysOn Availability Groups was improved on SQL Server 2014, giving the capability of have more replicas, better troubleshooting possibilities and improving its availability. Comparing the Availability Groups with the database mirroring, in a very high level, we gained the possibility of have a listener to dynamically redirect the connection to the current active instance and also the capability of distribute the read workload between readable replicas. However, only the primary replica is able to write.

在SQL Server 2012上引入的可用性组带来了对数据库镜像的改进版本的期望，该版本将很快停用。 SQL Server 2014对AlwaysOn可用性组进行了改进，使其具有更多副本的功能，更好的故障排除可能性并提高了其可用性。 在较高层次上将可用性组与数据库镜像进行比较，我们获得了使监听器动态地将连接重定向到当前活动实例的可能性，并且还具有在可读副本之间分配读取工作负载的能力。 但是，只有主副本才可以写入。

If you search about “how to setup an AlwaysOn Availability Group” probably you are going to find some articles out there, but all of those talking about the typical setup, where the Availability Group is being done between two standalone instances. In this article I’m going to do something different, the idea here is configure the Availability Group between a clustered instance and a standalone one, this way we can explore the differences between the typical and the not so typical 🙂

如果搜索“如何设置AlwaysOn可用性组”，可能会找到一些文章，但所有这些文章都在讨论典型的设置，即在两个独立实例之间完成可用性组。 在本文中，我将做一些不同的事情，这里的想法是在集群实例和独立实例之间配置可用性组，这样我们就可以探索典型实例与非典型实例之间的区别。

This said, let me make an example of what is going to be our lab environment:

就是说，让我举一个例子说明我们的实验室环境：

For this setup, I will be using SQL Server 2014, Enterprise edition, in a Windows Server 2012 R2 environment. However, this will also work in an environment with SQL Server 2012.

对于此设置，我将在Windows Server 2012 R2环境中使用SQL Server 2014 Enterprise版本。 但是，这在具有SQL Server 2012的环境中也将起作用。

必需品 ( Requisites )

In order to go ahead with this, we need to already have something done. Basically we will need a clustered instance installed in an AlwaysOn Failover Cluster, of course, and a standalone SQL Server instance installed in another server, which must also be part of the Windows Failover Cluster. Just a note here: the term AlwaysOn, is a brand used by Microsoft that is grouping two technologies: the Failover Cluster and the Availability Groups.

为了继续进行下去，我们需要做一些事情。 基本上，基本上，我们将需要在AlwaysOn故障转移群集中安装一个群集实例，并在另一台服务器中安装一个独立SQL Server实例，该服务器也必须是Windows故障转移群集的一部分。 此处仅需注意：术语AlwaysOn是Microsoft使用的品牌，它将两种技术分组：故障转移群集和可用性组。

In order to make it work, we are going to use the famous . This database will be initially placed in the clustered instance SQLSRVCLT01 and in order to successfully have this being synchronized to the W2012SRV03 instance, we need to assure that the same path used to store the database files exists on both instances, and preferable with disks with the same size … 🙂

为了使其工作，我们将使用著名的 。 此数据库最初将放置在群集实例SQLSRVCLT01中，为了成功将其同步到W2012SRV03实例，我们需要确保在两个实例上都存在用于存储数据库文件的相同路径，并且对于具有一样的大小……🙂

In our case, the database AdventureWorks2014 is stored in the following paths:

在我们的例子中，AdventureWorks2014数据库存储在以下路径中：

• F:\SQL_DATA\AdventureWorks2014_Data.mdf
  F：\ SQL_DATA \ AdventureWorks2014_Data.mdf
• G:\SQL_LOG\AdventureWorks2014_Log.ldf
  G：\ SQL_LOG \ AdventureWorks2014_Log.ldf

As you can see in the image bellow, we have all the tree nodes added to the same cluster.

如下面的图像所示，我们将所有树节点添加到了同一群集中。

The clustered instance is only installed in the nodes W2012R2SRV01 and W2012R2SRV02, so it will never be active at W2012R2SRV03.

群集实例仅安装在节点W2012R2SRV01和W2012R2SRV02中，因此它将永远不会在W2012R2SRV03上处于活动状态。

开始吧！ ( Let’s Start! )

To begin, I’m going to show how to configure the basis of an Availability Group, with two readable replicas.

首先，我将展示如何配置具有两个可读副本的可用性组的基础。

By the way, when we talk about Availability Groups, we are talking about database replicas. Even a main database is a replica, the “Primary Replica”. All the others are “Secondary Replicas”, it doesn’t matters if you have 2, 3 or 4 replicas…

顺便说一句，当我们谈论可用性组时，我们在谈论数据库副本。 甚至主数据库也是副本，即“主副本”。 其他所有都是“二级副本”，如果您有2、3或4个副本都没有关系……

First of all, we need to activate the AlwaysOn High Availability feature in the instances. In order to perform that, open the SQL Server Configuration manager and go to the “SQL Server Services Node”. Let’s start from the standalone instance.

首先，我们需要在实例中激活AlwaysOn高可用性功能。 为了执行该操作，请打开SQL Server配置管理器，然后转到“ SQL Server服务节点”。 让我们从独立实例开始。

Now, look for “SQL Server (<INSTANCENAME>)”, right-click it and go to “Properties”. In the properties window, choose the tab “AlwaysOn High Availability” and check the box “Enable AlwaysOn Availability Groups”.

现在，查找“ SQL Server（<INSTANCENAME>）”，右键单击它，然后转到“属性”。 在属性窗口中，选择“ AlwaysOn高可用性”选项卡，然后选中“启用AlwaysOn可用性组”框。

Now you can click “Ok” and restart the instance. You can do this, right there, in the Configuration Manager.

现在，您可以单击“确定”并重新启动实例。 您可以在配置管理器中直接执行此操作。

Follow the same steps for the clustered instance. The catch here is that you need to perform this in the node where the instance is active.

对集群实例执行相同的步骤。 这里的要点是您需要在实例处于活动状态的节点中执行此操作。

As you can see, if you try to enable the feature from a passive node, the following message will be shown:

如您所见，如果尝试从被动节点启用功能，将显示以下消息：

This way, perform the steps from a node where an instance is active.

这样，从实例处于活动状态的节点执行步骤。

Once more, restart an instance. As this is a clustered instance, do this from the Failover Cluster Manages, by taking the SQL Server service Offline and then Online again.

再一次，重新启动实例。 由于这是群集实例，因此可以通过使SQL Server服务脱机然后再联机来从“故障转移群集管理器”中执行此操作。

Yes! We are done with the first part!!! 🙂

是! 我们已经完成了第一部分！！！ 🙂

准备数据库 ( Preparing the database )

Now that our instance is ready to support Availability Groups, it’s time to take care of the database that we are going to join to the Availability Group.

现在我们的实例已准备好支持可用性组，是时候照顾要加入可用性组的数据库了。

As said in the beginning of this article, we are going to use the AdventureWorks2014 database. This database is already attached to the clustered instance. I choose this one to be the start point, but you can choose any other “replica”.

如本文开头所述，我们将使用AdventureWorks2014数据库。 该数据库已附加到集群实例。 我选择这个作为起点，但是您可以选择任何其他“副本”。

In order to make the database a real candidate to be placed into an Availability Group, we need to meet some prerequisites:

为了使数据库成为要放入可用性组的真正候选者，我们需要满足一些先决条件：

• A database must be in the FULL recovery mode
  数据库必须处于完全恢复模式
• A database should have at least one FULL backup
  一个数据库应至少有一个完整备份
• A database needs to have at least one T-Log backup
  数据库需要至少一个T-Log备份

With an intention to assure this, let’s check the database properties and change the Recovery Model from “Simple” to “Full”.

为了确保这一点，让我们检查数据库属性并将恢复模型从“简单”更改为“完整”。

Now we need to perform one FULL backup of the database. I’m going to store this backup to use in another step in this article.

现在，我们需要对数据库执行一次完整备份。 我将存储此备份以用于本文的另一步骤。

With the full backup done, we can perform the T-Log backup. Notice that I’m storing the backups in a fileshare on W2012R2SRV03. The reason is that I’m going to restore this database in the standalone instance running on that server.

完成完整备份后，我们可以执行T-Log备份。 请注意，我将备份存储在W2012R2SRV03的文件共享中。 原因是我要在该服务器上运行的独立实例中还原该数据库。

We are doing this in a lab, so the environment is fully controlled by me, but in a real environment, you may have Backup Agent running continuously, and making transaction log backups periodically… Make sure to stop those backups during this configuration period, otherwise you won’t be able to synchronize the databases between the replicas.

我们在实验室中进行此操作，因此环境完全由我控制，但是在实际环境中，您可能会连续运行备份代理，并定期进行事务日志备份……请确保在此配置期间停止这些备份，否则您将无法在副本之间同步数据库。

With the FULL and transaction log backups done, it’s time to restore a database in the other instance, the future Secondary Replica. In our case, this will be the instance W2012R2SRV03. So let’s work in the restore of the FULL backup previously made.

完成FULL和事务日志备份后，该在另一个实例（将来的辅助副本）中还原数据库了。 在我们的情况下，这将是实例W2012R2SRV03。 因此，让我们开始还原先前完成的FULL备份。

Make sure to set the “Recovery State” to “RESTORE WITH NORECOVERY”.

确保将“恢复状态”设置为“无恢复恢复”。

After the completion of this restore, we can start the transaction log backup.

完成此还原后，我们可以开始事务日志备份。

Again, make sure to select the option to RESTORE WITH NORECOVEY, as shown:

同样，请确保选择RESTORE WITH NORECOVEY选项，如下所示：

After those steps, we will have the following scenario, where the clustered instance has a database in the normal “online” state and the standalone instance has the database in the restore state.

完成这些步骤之后，我们将遇到以下情形，其中群集实例的数据库处于正常的“联机”状态，而独立实例的数据库处于还原状态。

Now we are ready to start creating the Availability Group!! So check the of this article in order to see the next steps.

现在我们准备开始创建可用性组！！ 因此，请查看本文的 ，以查看后续步骤。

In this article, we introduced the basic configuration and requisites in order to have an Availability Group setup between a clustered instance and a standalone instance. In the continuation of this article, we will show how to create the Availability Group itself, how to configure the listener, logins synchronization, etc… Keep in touch 🙂

在本文中，我们介绍了基本配置和必要条件，以便在集群实例和独立实例之间进行可用性组设置。 在本文的续篇中，我们将展示如何创建可用性组本身，如何配置侦听器，登录同步等……保持联系🙂

Next articles in this series:

本系列的下一篇文章：

翻译自:

alwayson高可用组