Build a SQL Cluster Lab Part 1

This article is Part 1 in a series of articles showing how to build a SQL Cluster Lab. It covers building a Windows Cluster in Hyper-V that supports both Availability Groups and Failover Cluster Instances. Once you build the environment, you can use it for training purposes, POCs, or the testing of features and functions surrounding High Availability and Disaster Recovery. Here are links to the other articles in the series.

• Part 1 – Building the environment. Hyper-v VMs, Active Directory, DNS, and Routing and Remote Access Services
• Part 2 – Windows Failover Clustering. Cluster validation, networks, and quorum
• Part 3 – Availability Group. Dedicated network and AD permissions
• Part 4 – Failover Cluster Instance. Creating shared storage using iSCSI and adding the storage to the cluster.

SQL Cluster Lab Architecture

Before we start to build a SQL Cluster Lab, let’s look at the desired result. You will build a 3-node cluster replicating an environment that has two data centers. As a result, the first two nodes will reside in data center 1 and the third node in data center 2. We are creating this architecture because it is the most common architecture I see for Availability Groups. It is multi-subnet and can solve for both HA and DR.

You will notice the domain controller in the middle. That piece is certainly not representative of a production environment. However, we are using it in our lab for several different functions and being a router is one of them.

Prerequisites

Before you start watching the build a SQL Cluster Lab video at the end of this post, there are several things you need to accomplish first. You will need to have Hyper-V installed on your machine and create 4 VMs. I used Windows Server 2019, but you could also use Windows Server 2012 R2 or Windows 2016.

You will need 2 virtual switches in Hyper-V. The first is for an external network and attached to a physical NIC card in your machine. Your second is for an internal network that allows all VMs to talk to each other.

VM Configuration

You now have your virtual switches configured and need to create your VMs. Once you install the OS, you will need to rename the server and also install a stand-alone instance of SQL Server on Node1, Node2, and Node3. I will be using SQL Server 2017.

The idea is for this lab to be self-contained and able to run on a machine with 16GB of RAM. It’s a tight fit, but I have been doing it for years and so can you. Below are the names of the VMs and the configuration settings for each in Hyper-V. In addition, all network adapters will be mapped to your internal network switch except for one NIC on DC1 that will be mapped to your external network switch. Stick with the default for any setting not in this table.

	Memory MB	Processor Count	Network Adapter Count
DC1	2048	2	7
Node 1	4096	2	3
Node 2	2048	2	3
Node 3	2048	2	3

Network Configuration

You now have the OS installed for all 4 VMs, renamed them, and installed a stand-alone instance of SQL Server on the 3 cluster nodes. The last prerequisite before jumping into the video walk-through is to configure the NIC cards with IPs and label them. This chart contains all the addresses you will need to accomplish this task. Please note that Data Center in the below chart is abbreviated as “DC” and not to be confused with the domain controller.

	DC1	Node1	Node2	Node3
DC1 Net1 Public IP	10.0.0.40	10.0.0.1	10.0.0.2
DC1 Net1 Public Subnet	255.255.255.0	255.255.255.0	255.255.255.0
DC1 Net1 Public Gateway	None	10.0.0.40	10.0.0.40
DC1 Net1 Public Preferred DNS	127.0.0.1	10.0.0.40	10.0.0.40
DC1 Net2 Private IP	192.168.5.40	192.168.5.1	192.168.5.2
DC1 Net2 Private Subnet	255.255.255.0	255.255.255.0	255.255.255.0
DC1 Net2 Private Gateway	None	192.168.5.40	192.168.5.40
DC1 Net2 Private Preferred DNS	127.0.0.1	10.0.0.40	10.0.0.40
DC2 Net1 Public IP	172.16.0.40			172.16.0.3
DC2 Net1 Public Subnet	255.240.0.0			255.240.0.0
DC2 Net1 Public Gateway	None			172.16.0.40
DC2 Net1 Public Preferred DNS	127.0.0.1			10.0.0.40
DC2 Net2 Private IP	172.17.0.40			172.17.0.3
DC2 Net2 Private Subnet	255.248.0.0			255.248.0.0
DC2 Net2 Private Gateway	None			172.17.0.40
DC2 Net2 Private Preferred DNS	127.0.0.1			10.0.0.40
DC1 Net3 AG IP	10.0.1.40	10.0.1.1	10.0.1.2
DC1 Net3 AG Subnet	255.255.255.0	255.255.255.0	255.255.255.0
DC1 Net3 AG Gateway	None	10.0.1.40	10.0.1.40
DC1 Net3 AG Preferred DNS	127.0.0.1	10.0.0.40	10.0.0.40
DC2 Net3 AG IP	172.18.0.40			172.18.0.3
DC2 Net3 AG Subnet	255.252.0.0			255.252.0.0
DC2 Net3 AG Gateway	None			172.18.0.40
DC2 Net3 AG Preferred DNS	127.0.0.1			10.0.0.40
NAT to Host IP	DHCP
NAT to Host Subnet	DHCP
NAT to Host Gateway	DHCP
NAT to Host Preferred DNS	127.0.0.1

Here are the addresses you will use for the Cluster, Availability Group Listener, and Failover Cluster Instance in subsequent articles.

	Data Center 1 IP	Data Center 2 IP
Cluster	10.0.0.10	172.16.0.10
AG Listener	10.0.0.11	172.16.0.11
FCI	10.0.0.15	N/A

Build a SQL Cluster Lab Video

You now have all of your prerequisites resolved and can begin the build of your lab. In this video I show you my prerequisites completed and start with the first step of making DC1 a domain controller. Promoting DC1 as a domain controller also means installing and configuring DNS. The last thing I walk you through is installing Routing and Remote Access Services. We use RRAS to handle the routing of our LAN traffic as well as provide a way for each VM to get internet access without adding a 4th NIC to each VM.