Deploying WSFC and the SQL Server Failover Cluster on Alibaba Cloud ECS

WSFC is a feature of the Windows Server platform, which is generally used to improve the high availability of applications and services on your network. WSFC is a successor to the Microsoft Cluster Service (MCS). SQL Server takes advantage of WSFC services and capabilities to support Always On availability groups and SQL Server failover cluster instances. We recommend you use Windows Server Failover Clustering (WSFC) and SQL Server AlwaysOn Availability Groups as your SQL Server high availability (HA) solution on Alibaba Cloud’s Elastic Compute Service (ECS) instances.

An Alibaba Cloud ECS instance provides fast memory and the latest Intel CPUs to help you to power your cloud applications and achieve faster results with low latency. All ECS instances come with Anti-DDoS protection to safeguard your data and applications from DDoS and Trojan attacks.

The Alibaba Cloud ECS allows you to load applications with multiple operating systems and manage network access rights and permissions. Within the user console, you can also access the latest storage features, including auto snapshots, which is perfect for testing new tasks or operating systems as it allows you to make a quick copy and restore later. It offers a variety of configurable CPU, memory, data disk and bandwidth variations allowing you to tailor each Instance to your specific needs.

When using WSFC in conjunction with Alibaba Cloud ECS, if one cluster node fails, another node can take over. We can configure this failover to happen automatically, which is the usual configuration, or we can manually trigger a failover.

In this tutorial, we will deploy a SQL Server high availability (HA) solution with WSFC and the SQL Server Failover Cluster on an Alibaba Cloud ECS instance. This tutorial assumes a basic understanding of Alibaba Cloud’s suite of products and services, the Alibaba Cloud Console, failover clustering, the Active Directory (AD), and the administration of Windows Server.

1.Introduction

1.1 The Architecture

We recommend the following configuration, which contains three servers and runs across the Alibaba Cloud Virtual Private Cloud (VPC) to provide an isolated cloud network to operate your resource in a secure environment:

ItemSpecification and version numberECSCPU-4 Cores, Memory-16GB,OSWindows Server 2016 DataCenter X64SQL ServerSQL Server 2016 Enterprise X64Demo DBfailover, created by HammerDB(SQL Server TPC-C)ManagementSSMS (SQL Server Management Studio)

Image for post
Image for post

1.2 Prerequisites

For the purposes of this tutorial, we will assume the following tasks are complete:

2.Add SQL Server into the Domain

2.1 Configure Your NIC (Network Interface Card)

First, we need to configure the DNS of the management network card as the IP address of the AD server (which is 192.168.0.119 in this example).

Image for post
Image for post
Image for post
Image for post

The IP address of the network card has been changed to a static IP address instead of the default DHCP assignment when creating the ECS. If we do not complete this step, there will be a problem when creating a failover cluster (you will not be able to find the input box for entering the cluster IP address.)

You can also check your Network Connection Details on the SQL Server UI:

Image for post
Image for post

2.2 Two Servers Join the Domain

Now, we need to change the system settings for the domain.

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

3.Configure the iSCSI Target Server

The iSCSI server and AD domain server need to be combined. Here are the steps to configure the iSCSI target server and achieve this:

3.1 Creating a Cloud Disk and Mounting to iSCSI Server

Go back to the Cloud Disks page and refresh it. You should see the following:

Image for post
Image for post

3.2 Add iSCSI Target Server Role

Image for post
Image for post

3.3 Add iSCSI Virtual Disks

Next, create three virtual disks attached to the AD/iSCSI server as iSCSI virtual disks:

Image for post
Image for post

Add the desired name and size of the server.

Choose New iSCSI target

Target Name: iscsi-target-data

Then, create the iSCSI virtual disk.

After all three have been created, you will see the initial list of status, although these are not yet connected:

Image for post
Image for post

3.4 Mount the iSCSI Virtual Disk on the Servers

Next, initiate and configure your iSCSI initiators on two servers in the Cluster and make virtual disks on AD / ISCSI server as targets by:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

4.Create WSFC

4.1 Validate Configuration

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

4.2 Create the Cluster

Image for post
Image for post
Image for post
Image for post

4.3 Add HAVIP to Alibaba Cloud Console

Alibaba Cloud no longer supports multi-IP on a single NIC. Even if you can attach multiple IP addresses on the same NIC, it cannot ping successfully.

Image for post
Image for post

By adding HAVIP (High Availability IP), this problem is circumvented:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

4.4 Verification on Failover

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

4.5 Add a Disk in Cluster Manager

Now we need to add a disk by performing the following steps in the Cluster Manager:

Image for post
Image for post

4.6 Configure the Quorum Disk

The quorum is sometimes referred to as the Disk or File Witness. It is simply a small clustered disk which is in the available cluster storage group. To configure the quorum, you should:

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

4.7 Format Other Disks

The other two DATA and LOG disks are also formatted as NTFS. You can see that these two disks are now on the cluster’s WSFC-2 node, so the states seen in disk management for WSFC-1 and WSFC-2 are as follows:

WSFC-1:

Image for post
Image for post

WSFC-2:

Image for post
Image for post

Turn off maintenance mode after formatting on the WSFC-2 node and check the status via Failover Cluster Manager:

For easy identification and operation, move all three disk resources to the WSFC-1 node.

4.8 Add Cluster Shared Volumes

Finally, click to add Cluster Shared Volumes to your configuration by following these screenshots:

Image for post
Image for post
Image for post
Image for post

You can view the shared volume on My Computer. This volume will be used for SQL Server.

Image for post
Image for post

5.Deploy SQL Server Failover Cluster

The SQL Server failover cluster is equivalent to the primary and secondary machines, while only one SQL Server service is running, the database is stored in shared storage and has only one copy.

We now need to deploy the SQL Server failover cluster using the following steps:

5.1 Run System Configuration Checker

First, check your system configuration by clicking the following tool on the SQL Server menu:

Image for post
Image for post

The following results should appear:

Image for post
Image for post

5.2 Install SQL Server Failover Cluster on WSFC-1

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

5.3 Add WSFC-2 as a SQL Server Failover Cluster Node

Image for post
Image for post

5.5 SQL Server Network Configuration

Image for post
Image for post

5.6 Check the SQL Server Status of Both Nodes

Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

6.Check Your Configuration

You have now to deployed WSFC and the SQL Server Failover Cluster on an Alibaba Cloud ECS instance. You can check your configuration with the following steps:

6.1 Generate test data with tool HammerDB

Image for post
Image for post
Image for post
Image for post

6.2 Move SQL Server Failover Cluster to the WSFC-2 Node and Verify

Image for post
Image for post

Conclusion

That’s it! You have now checked and verified your WSFC and the SQL Server Failover Cluster on an Alibaba Cloud ECS instance.

If you need any further help with your installation, please go to https://www.alibabacloud.com/help or contact one of the Alibaba Cloud Support team.

To read the other tutorials covering Windows Server Failover Clusters, SQL Servers and Windows Server Failover Clustering, visit:

Reference: https://www.alibabacloud.com/blog/deploying-wsfc-and-the-sql-server-failover-cluster-on-alibaba-cloud-ecs_594045?spm=a2c41.12108728.0.0

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store