A few years back, while working for a large healthcare organization, I managed a SQL Server failover clustered instance that would failover at random times.
This business-critical instance was used for reporting (data marts). By having a cluster, everyone thought it would provide high availability. However, we noticed when the instance would get busy, usually during data loads, the instance would spontaneously failover, killing the data loads and any other active connections in the process. Not every time, though, just occasionally.
We had two issues in this case.
First, the network needed to be faster for the amount of data we were ingesting. The 1 Gb connection wasn't enough. Second, it was missing one critical, although optional, component -- an isolated network to support cluster traffic (heartbeat).
Last week, you built your private network to isolate iSCSI traffic from client requests between CLUSTER1SRV1, CLUSTER1SRV2, and STORSRV1.
Today, you'll finish the iSCSI network, verify firewall rules, and build the private network to isolate cluster traffic from client requests and storage traffic. This will reduce the risk of delays and help prevent false failovers.
There are five objectives for this challenge:
- Change the name of the NIC used for iSCSI traffic for easier identification.
- Validate that iSCSI traffic is not blocked by reviewing the Windows Firewall rules.
- Introduce a new virtual network which will be used to isolate cluster traffic.
- Add one additional NIC to CLUSTER1SRV1 and CLUSTER1SRV2. Attach this NIC to the vnet created above. Rename the NIC for easier identification.
- Assign a static IP address to each NIC.
Ready?
Estimated time to complete: Less than 2 hours.
Step 1: Rename Ethernet1 to iSCSI_NET.
You'll be using multiple networks. It's good to name the specific NICs to make it easy to identify their purpose.
On all cluster nodes and STORSRV1, rename Ethernet1 to iSCSI_NET. Your environment should match the screenshot below when finished.
Step 2: Verify Windows Firewall rules are enabled.
Fortunately, when installing the iSCSI target feature, Windows firewall rules were also added. But let's make sure.
Open Windows Firewall advanced settings and review the status of the iSCSI Target (TCP-in) and iSCSI Target Service (RPC-in) rules.
Verify both are enabled.
Step 3: Add heartbeat VNET.
In this step, you'll create a new virtual network. Steps may differ and depend on which hypervisor you're using.
Be sure to disable the DHCP service on this network. This small network is limited to 6 usable hosts (CIDR Notation: /29).
Subnet IP: 172.16.0.0
Subnet Mask: 255.255.255.248
Here's an example of what this looks like when using VMware Workstation Pro.
Here's a great tool for figuring out what network size you need.
Step 4: Add one additional NIC to cluster nodes.
For this step perform the following actions on CLUSTER1SRV1 and CLUSTER1SRV2:
- Add a second virtual NIC to CLUSTER1SRV1 and CLUSTER1SRV2.
- Attach the virtual NIC to the new virtual network you created in step 3.
Step 5: Assign the static IP address.
Set the static IP address for each of the new NICs. There's no need to define the DNS server or gateway for these. In my example, I used the following.
CLUSTER1SRV1 - IP: 172.16.0.1, Subnet Mask: 255.255.255.248
CLUSTER1SRV2 - IP: 172.16.0.2, Subnet Mask: 255.255.255.248
While you're here, go ahead and rename the NIC to Heartbeat on both nodes.
Bonus challenge: Determine which port(s) must be open for cluster communication and heartbeats.
Helping Others and Sharing Your Results
That's it for this week. By the end of this challenge, you'll have all of the networking components in place. Next week, we'll work on our shared disks and add firewall rules for cluster communication and heartbeats.
If you have tips other readers can learn from, please share them in the comments. You can message me on LinkedIn or post about it and tag me with the #dbachallenges hashtag.
Feedback
If there's a DBA Challenge you'd like to see, please let me know by replying to this email. Have ideas on how to make these more useful? I'd love to hear those too.
I'm looking for feedback on how DevOps or Platform Engineering may be impacting your role as a DBA. If you'd be interested in a quick, 10-15 minute Zoom call, or just want to send over a quick summary via email, I'd love to hear about it. I'm currently building the course below, and want to ensure it addresses what is important to you in either of these practices.
Ansible for SQL Server DBAs: Level 1
Want to share this newsletter with a friend? They can sign up below.
Oh, if you're using the Automated Sandbox Framework, I've updated the vagrant file to include the 3rd nic and virtual network provisioners. Time to build the virtual machines from scratch - about 15 - 20 minutes (less if the automatesql/win2022 box has already been downloaded). Grab it below. Just reply to this email if you need the password.
Good luck and I'm looking forward to seeing your results!
Luke
Responses