Category: Disaster Recovery

Step by Step MS SQL Database Cluster configuration using SQL 2019 on Server 2022

Failover Clusters provide seamless Availability and Uptime. On failover clusters, we can run different services/applications. In general, we can run Hyper V VMs, VM Replicas, DHCP and also Database applications. A typical failover cluster consists of 2 nodes (servers), shared network and shared storage systems. 

Today, we will be looking at the exact steps in order to create a failover cluster and how to run MS SQL server on the configured cluster. 

There are some Prerequisites as mentioned below

  1. Active Directory infrastrcture
  2. AD administrator account
  3. A shared storage system (iSCSI, FCoE or FC supported protocols)
  4. 2 or more nodes (Servers)
  5. MS SQL server ISO
  6. Functional DNS server
  7. Few free IP addresses

Lets look at my setup

Server Name dc.chatz.local node1.chatz.local node2.chatz.local
Server IP 192.168.79.129 192.168.79.130 192.168.79.131
Installed Roles

Active Directory / File Server (iSCSI) / DNS

 Failover Cluster Manager Failover Cluster Manager
Notes

iSCSI shares

512 MB – Witness

20 GB – Cluster Shared Volume

 192.168.11.100  (for cluster heartbeat)

192.168.11.101 (for cluster heartbeat)

This process consists of 2 phases, Phase 01 would be the Cluster Creation and Phase 02 is the MS SQL DB Cluster setup.

Phase 01 – Windows Failover Cluster setup

NOTE: When working with the AD services, make sure to create an OU and place all the cluster related objects in the OU which you create. As per my setup, the OU looks like this

As the very first step, you need to install Failover Cluster role on all the participating nodes (in this example, we have only 2). Once you are done with the role installation, apply the Windows patches. And then you may finalize setting up the iSCSI shares. Finally, make sure to add all those nodes to the Active Directory Domain. 

Then, you need to add those iSCSI storage as volumes on the selected nodes. This can be done using the Disk Management console.

Once you are done with assigning the volumes, you are good to validate the cluster. You can simply access the Failover Cluster Manager role from a selected node

Continue reading “Step by Step MS SQL Database Cluster configuration using SQL 2019 on Server 2022”

New features of Win Server 2022 Failover Clustering

Failover Cluster feature being one of the top ones on Windows Servers. So Microsoft keeps adding new enchancements. Lets discuss on these.

Cluster Affinity with Azure Stack HCI

Affinity is a rule you would set up that establishes a relationship between two or more roles (i,e, virtual machines, resource groups, and so on) to keep them together. AntiAffinity is the same but is used to try to keep the specified roles apart from each other. AntiAffinity was only available upto 2019. So basically this provides “Streched Cluster” capabilities. Hopefully across different geo locations. read more https://docs.microsoft.com/en-us/azure-stack/hci/manage/vm-affinity

AutoSites

AutoSites is another feature brought over from Azure Stack HCI. AutoSites is basically what is says. When you configure Failover Clustering, it will first look into Active Directory to see if Sites are configured. If they are and the nodes are included in a site, we will automatically create site fault domains and put the nodes in the fault domain they are a member of.

Granular Repair

When a node goes down for maintenance, crashes, or whatever the case may be, once it comes back up, there is a “repair” job run where data is moved around and onto the drives, if necessary, of the node that came back. A repair is basically a resync of the data between all the nodes. Depending on the amount of time the node was down, the longer it could take for the repair to complete. read more https://docs.microsoft.com/en-us/azure-stack/hci/manage/storage-repair-speed

Cluster Shared Volumes and Bitlocker

Cluster Shared Volumes (CSV) enable multiple nodes in a Windows Server Failover Cluster or Azure Stack HCI to simultaneously have read-write access to the same LUN (disk) that is provisioned as an NTFS volume. BitLocker Drive Encryption is a data protection feature that integrates with the operating system and addresses the threats of data theft or exposure from lost, stolen, or inappropriately decommissioned computers.

BitLocker on volumes within a cluster are managed based on how the cluster service “views” the volume to be protected. BitLocker will unlock protected volumes without user intervention.

SMB Encryption

Windows Server 2022 SMB Direct now supports encryption. Previously, enabling SMB encryption disabled direct data placement, making RDMA performance as slow as TCP. Now data is encrypted before placement, leading to relatively minor performance degradation while adding AES-128 and AES-256 protected packet privacy. Furthermore, Windows Server Failover Clusters now support granular control of encrypting intra-node storage communications for Cluster Shared Volumes (CSV) and the storage bus layer (SBL). This means that when using Storage Spaces Direct and SMB Direct, you can decide to encrypt the east-west communications within the cluster itself for higher security.

[source: Microsoft]