Build a SQL Cluster Lab Part 1

This post has been republished via RSS; it originally appeared at: Premier Field Engineering articles.

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.

 

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

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.

 

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 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.

 

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.