Build a SQL Cluster Lab Part 1

This article is Part 1 in a series of articles showing how to build a SQL Lab. It covers building a Windows 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.

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.

Lab Architecture.png


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 2019, but you could also use 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.

Virtual Switch - External.png

Virtual Switch - Internal.png

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  
DC1 Net1 Public Subnet  
DC1 Net1 Public Gateway None  
DC1 Net1 Public Preferred  
DC1 Net2 Private IP  
DC1 Net2 Private Subnet  
DC1 Net2 Private Gateway None  
DC1 Net2 Private Preferred  
DC2 Net1 Public IP
DC2 Net1 Public Subnet
DC2 Net1 Public Gateway None
DC2 Net1 Public Preferred DNS
DC2 Net2 Private IP
DC2 Net2 Private Subnet
DC2 Net2 Private Gateway None
DC2 Net2 Private Preferred DNS
DC1 Net3 AG IP  
DC1 Net3 AG Subnet  
DC1 Net3 AG Gateway None  
DC1 Net3 AG Preferred DNS  
DC2 Net3 AG IP
DC2 Net3 AG Subnet
DC2 Net3 AG Gateway None
DC2 Net3 AG Preferred DNS
NAT to Host IP DHCP      
NAT to Host Subnet DHCP      
NAT to Host Gateway DHCP      
NAT to Host Preferred DNS      

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
AG Listener

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.



This article was originally published by Microsoft's Secure Blog. You can find the original article here.