Last week, you prestaged the SQL Server Network Virtual Computer Object (VCO) and installed SQL Server 2022 on the first cluster node. But you couldn't connect remotely and needed to add the second node.
We'll address both of these issues in this week's challenge. By the end, you'll have a fully functional failover cluster instance.
This challenge has three objectives.
- Install SQL Server 2022 Developer Edition on the second node.
- Configure Windows Firewall rules using PowerShell for SQL Server.
- Verify remote connectivity to the SQL Server instance.
Ready?
Estimated time to complete: Less than 1 hour.
Step 1: Add the Second Node to the SQL Server FCI
- Start the SQL Server Installation on CLUSTER1SRV2.
- In the SQL Server Installation Center, click Installation, then choose Add node to a SQL Server failover cluster. Click next until you get to the rule check.
- Review and address any errors presented in the rule check. If all is well, click next.
- Review the Cluster Node Configuration, then click next.
- Review the Cluster Network Configuration, then click next.
- Set the passwords for the engine and agent services and check the box next to "Grant Perform Volume Maintenance Tasks privilege to SQL Server Database Engine service." Click Next.
- Click Install.
Wait until the installation is completed. You'll notice the install on the second node completes much faster than the install on the first node.
Step 2: Configure the Windows Firewall Rules using PowerShell
Now, you're ready to allow access to the SQL Server instance. You'll need to add a firewall rule on both nodes. You can do that in several different ways: explicitly allowing inbound connections to the default SQL Server port (TCP 1433) or by allowing access to the SQL Server executable. Let's take the first approach.
- Open PowerShell as Administrator on both CLUSTER1SRV1 and CLUSTER1SRV2.
- Run New-NetFirewallRule -DisplayName "Allow SQL Server Inbound" -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
- Review the output and ensure you see the status "The rule was parsed successfully from the store."
Don't forget to run this command on both cluster nodes.
Step 3: Verify Remote Connectivity to the SQL Server Instance
Now that the second node is added and firewall rules are configured test remote connectivity.
- Install SQL Server Management Studio (SSMS). If not installed, download and install SSMS on a client machine (e.g., SRV1 from the 001 challenge).
- Test Connectivity. Open SSMS. In the server name field, enter the SQL Server Network Name (SQLCLUSTER1). Choose optional from the Encryption drop-down menu.
- If you cannot connect, check the following: Firewall Settings and SQL Server Network Configuration Manager (verify that TCP/IP protocol is enabled). Cluster Status (ensure that the SQL Server role is running and online in Failover Cluster Manager).
Now, you'll test failover.
- Open Failover Cluster Manager on one of the cluster nodes.
- Right-click on the SQL Server role and select Move > Select Node.
- Choose the other node and click OK.
- Verify that the role moves successfully and you can still connect via SSMS.
Helping Others and Sharing Your Results
Congratulations on successfully adding the second node to your SQL Server 2022 FCI and configuring firewall rules using PowerShell! Now would be a good time to patch SQL Server on both nodes by applying the latest updates.
Spend time exploring the cluster, practicing failover, and familiarizing yourself with the firewall configurations. Understanding how these components interact is crucial for managing a production environment.
If you have tips or experiences to share, please post them in the comments or tag me on LinkedIn with the #dbachallenges hashtag.
While you're there, I've created a poll regarding how valuable Communities are when combined with a course. Would you mind voting?
Poll: Do you find online communities valuable when learning something new?
Feedback
If there's a DBA Challenge you'd like to see, let me know by replying to this email.
P.S. If you're interested in automating tasks such as building and configuring new SQL Server instances, I cover five key problems Ansible helps to solve in the video below (along with the final solution we build in the Ansible for SQL Server DBAs course).
Feel free to reply to this email with any thoughts or questions.
Good luck, and I look forward to seeing your results!
Luke
Responses