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.