sql server 群集_部署SQL Server以使用群集共享卷进行故障转移群集–第1部分

sql server 群集

Microsoft SQL Server provides us with a wide variety of solutions to architect High availability (HA) and Disaster Recovery (DR) solutions for mission-critical workloads. In this article, let’s just focus on HA, specifically Failover Clustering. Failover clustering is probably the most mature, robust and stable high availability solution which Windows Server Operating system offers. It’s been there around for few decades now and did evolve over time along with SQL Server. In this article Let’s see a hidden feature of windows server failover cluster which helps in making our already highly available SQL Server Failover clustered instances even more highly available. The new feature which we are going to talk about is Cluster Shared Volumes, AKA CSVs. Considering windows server 2019 is around the corner, I say CSVs are not a new concept in clustering, it’s been there for almost a decade now. Microsoft introduced CSVs in windows server 2008R2, but at that time SQL Server was not supported on CSVs. Well, CSVs were originally designed for Hyper-V workloads and later on enhanced for File servers and eventually landed into SQL Server beginning version 2014.

Microsoft SQL Server为我们提供了多种解决方案，以为任务关键型工作负载设计高可用性（HA）和灾难恢复（DR）解决方案。 在本文中，我们仅关注HA，特别是故障转移群集。 故障转移群集可能是Windows Server操作系统提供的最成熟，健壮和稳定的高可用性解决方案。 它已经存在了几十年，并且随着时间的推移随着SQL Server的发展而发展。 在本文中，让我们看一下Windows服务器故障转移群集的隐藏功能，该功能有助于使我们本已高度可用SQL Server故障转移群集实例更加可用。 我们将要讨论的新功能是群集共享卷，即AKA CSV。 考虑到Windows Server 2019迫在眉睫，我说CSV并不是集群中的新概念，它已经存在了将近十年了。 Microsoft在Windows Server 2008R2中引入了CSV，但是当时CSV不支持SQL Server。 嗯，CSV最初是为Hyper-V工作负载设计的，后来为文件服务器进行了增强，最终在2014版开始引入了SQL Server。

Fair enough, but why should we care about Cluster shared volumes?

足够公平，但是为什么我们要关心集群共享卷？

Well, the idea behind introducing CSVs is to provide truly shared disks to a failover cluster which are available to all the nodes for reading and write operations. Let’s talk about a traditional Failover clustered Instance setup for a moment. During Failover, to bring SQL Server resource online, the drives should be unmounted on the previous owner and remounted on the node which will act as the primary after failover. Should your IO subsystem become bottleneck for whatever reason, Unmounting and mounting process takes longer time thus impacting the availability of the system. Whereas with CSVs, there is no unmounting and mounting of disks since they are already made available for reading and write operations across all the nodes. In other words, it reduces downtime since SQL Server resource is no longer dependent on disks to come online. Let’s talk about one more scenario where CSVs outperform traditional shared storage. Let’s assume disk(s) loses connectivity from the node which is currently running SQL Server in middle of the day, under these circumstances the cluster can leverage another path (s) available to the shared disk without having to failover the resource group to another node. This will save us from potential unplanned downtime during business hours.

嗯，引入CSV背后的想法是为故障转移群集提供真正的共享磁盘，所有节点都可以使用它们进行读写操作。 让我们暂时讨论传统的故障转移群集实例设置。 在故障转移期间，要使SQL Server资源联机，应在以前的所有者上卸载驱动器，并在故障转移后将作为主要节点的节点上重新安装驱动器。 如果您的IO子系统由于某种原因成为瓶颈，则卸载和安装过程将花费更长的时间，从而影响系统的可用性。 而使用CSV时，没有磁盘的卸载和安装，因为已经可以在所有节点上进行磁盘的读写操作。 换句话说，由于SQL Server资源不再依赖于磁盘联机，因此它减少了停机时间。 让我们再谈谈CSV胜过传统共享存储的另一种情况。 假设磁盘在一天当中的某个时间与当前正在运行SQL Server的节点失去连接，在这种情况下，群集可以利用共享磁盘可用的另一条路径，而不必将资源组故障转移到另一节点。 这将使我们免于在工作时间内发生潜在的计划外停机。

CSV如何工作 (How CSVs work)

Under the covers, CSVs still use the same idea of shared disk but with an abstraction layer above NTFS stack. In a traditional shared storage solution, a shared disk is accessible to only one node (Whoever the owner is) at any given point of time, whereas in CSVs all the nodes participating in WSFC will have their own logical paths to the disk(s) leveraging SMB (Server Message Block) protocol. Even though all the nodes within the cluster have both logical and physical paths to shared disk (Let’s say $SQLDATA for example), only the node which owns SQL Server resource can own that disk ($SQLData in our example), where the owner node sends I/O commands either directly using its own access path or using cluster heartbeat network. Basically, one node will be designated as coordinator node (This is the one which NTFS volume is mounted at that given point of time) which talks to all other nodes within the cluster using SMB 3.0 protocol for orchestrating Metadata. So, what is Metadata from SQL Server standpoint? Initial provisioning of Database files during database creation, Extending Database files are considered the most common Metadata events which we can see in SQL Server deployments.

在幕后，CSV仍然使用相同的共享磁盘概念，但在NTFS堆栈上方具有抽象层。 在传统的共享存储解决方案中，共享磁盘在任何给定时间点仅可访问一个节点（无论所有者是谁），而在CSV中，参与WSFC的所有节点将具有它们自己的磁盘逻辑路径。利用SMB（服务器消息块）协议。 即使群集中的所有节点都具有共享磁盘的逻辑路径和物理路径（例如，假设$ SQLDATA），但只有拥有SQL Server资源的节点才能拥有该磁盘（在我们的示例中为$ SQLData），其中所有者节点直接使用自己的访问路径或群集心跳网络发送I / O命令。 基本上，一个节点将被指定为协调器节点（这是在给定的时间点安装NTFS卷的一个节点），该节点使用SMB 3.0协议与群集中的所有其他节点进行编排元数据。 那么，从SQL Server的角度来看什么是元数据？ 在数据库创建过程中对数据库文件进行初始配置，扩展数据库文件被认为是我们在SQL Server部署中可以看到的最常见的元数据事件。

Note: Metadata orchestration is done in parallel and is non-disruptive for SQL Workload.

注意 ：元数据编排是并行完成的，并且对于SQL Workload而言是无中断的。

Now let’s see how the cluster dependency looks like when using Cluster shared volumes for SQL Server workloads.

现在，让我们看看将群集共享卷用于SQL Server工作负载时群集依赖性的外观。

In the above screenshot, you can see the dependency report doesn’t display our shared disks at all. In other words, it thinks SQL Server is not directly dependent on shared disks but behind the scenes, SQL Databases still reside on that CSV disk(s). The important thing to understand with CSV deployments is our SQL Server Instance availability still depends on CSV disk (After all that’s where our Databases are residing), but it’s getting abstracted. Compare the above screenshot with the dependency report from a traditional failover cluster Instance setup.

在上面的屏幕截图中，您可以看到依赖性报告根本不显示我们的共享磁盘。 换句话说，它认为SQL Server不是直接依赖共享磁盘，而是在幕后，SQL数据库仍然驻留在该CSV磁盘上。 CSV部署需要了解的重要一点是，SQL Server实例的可用性仍然取决于CSV磁盘（毕竟这是数据库所在的位置），但是它已经变得抽象了。 将以上屏幕截图与传统故障转移群集实例设置中的相关性报告进行比较。

Here you can see the dependency report showing SQL Server resource is directly dependent on the shared disks along with (AND Dependency) the Network name.

在这里，您可以看到依赖性报告，其中显示了SQL Server资源直接依赖于共享磁盘以及网络名称（并具有依赖性）。

将CSV用于SQL Server工作负载有什么好处？ (What are the benefits of using CSVs for SQL Server workloads?)

• It provides us with a very highly scalable solution where we can have virtually unlimited disk drives with no dependency on the drive letters. You can see in the below screenshot how CSVs get created when deployed. Basically, a folder named ‘ClusterStorage’ gets created under C$ on every node within the windows server failover cluster under which CSVs are placed. You can log in to any node of the cluster to access these disk drives.

  它为我们提供了高度可扩展的解决方案，其中我们几乎可以拥有无​​限数量的磁盘驱动器，而无需依赖驱动器号。 您可以在下面的屏幕截图中看到部署后如何创建CSV。 基本上，在Windows服务器故障转移群集中每个放置CSV的节点上，在C $下创建一个名为“ ClusterStorage”的文件夹。 您可以登录到群集的任何节点以访问这些磁盘驱动器。

  

• Time it takes to failover gets considerably improved (As discussed earlier, there is no concept of unmounting/mounting of the disk drives during failover). Also, CSVs are resilient to certain types of storage failures.
  故障转移所需的时间得到了显着改善（如前所述，在故障转移期间没有卸载/安装磁盘驱动器的概念）。 而且，CSV可以抵抗某些类型的存储故障。
• CHKDSK operation is integrated, In CSVs scanning (Is an online operation) and repair operations are separated. This could potentially bring down the time it takes to perform certain file system corrections to seconds/minutes as opposed to hours.
  集成了CHKDSK操作，在CSV中扫描（是在线操作）和维修操作是分开的。 这有可能将执行某些文件系统校正所需的时间减少到几秒/分钟，而不是几小时。
• Ease of management. As we have an abstraction to which node within the Failover Cluster owns the disk, we can manage them from any node irrespective of which node the drive is mapped under the covers.
  易于管理。 由于故障转移群集中磁盘拥有哪个节点的抽象，我们可以从任何节点管理它们，而不考虑驱动器映射到幕后哪个节点。

Note: AlwaysON Availability Groups are not supported by cluster shared volumes, Only FCIs are supported. If you have plans to deploy AGs, sorry CSVs are not for you.

注意：群集共享卷不支持AlwaysON可用性组，仅支持FCI。 如果您计划部署AG，那么不适合您使用CSV。

One important consideration when deploying CSVs for production SQL Server workloads is to disable iSCSI networks for the cluster use to prevent CSV traffic on those networks. Also, it is recommended to have multiple cluster networks (or teamed NICs) to carry CSV traffic.

为生产SQL Server工作负载部署CSV时，一个重要的考虑因素是禁用iSCSI网络供群集使用，以防止这些网络上的CSV通信。 另外，建议使用多个群集网络（或成组的NIC）来传输CSV流量。

In the next part of this short series, I will show you step by step process, how to install SQL Server Failover Clustered Instance leveraging Cluster Shared Volumes.

在本简短系列的下一部分中，我将逐步向您展示如何利用群集共享卷安装SQL Server故障转移群集实例。

常见问题 (FAQs)

What are the Main advantages of using CSVs?

使用CSV的主要优点是什么？

Manageability, Scalability and High Availability.

可管理性，可伸缩性和高可用性。

Will there be any performance impact using CSVs?

使用CSV会对性能产生影响吗？

Not really. These disks should be considered as a traditional shared disks w.r.t monitoring.

并不是的。 这些磁盘应被视为监视的传统共享磁盘。

Can I use CSVs for deploying Availability Groups?

我可以使用CSV部署可用性组吗？

No, AGs are not supported with Cluster Shared volumes.

否，群集共享卷不支持AG。

翻译自: https://www.sqlshack.com/deploy-sql-server-for-failover-clustering-with-cluster-shared-volumes-part-1/

sql server 群集