Two weeks ago, you completed the cluster build in preparation for the SQL Server installation.
This week, we'll focus on the crucial step of pre-staging the SQL Server Network Virtual Computer Object (VCO). Pre-staging the VCO helps avoid permission issues during the SQL Server Failover Cluster Instance (FCI) installation.
This challenge has five objectives.
- Pre-stage the VCO in Active Directory.
- Disable the VCO in AD.
- Grant the CNO permissions to the VCO.
- Install SQL Server FCI using the pre-staged VCO.
- Verify the SQL Server Network Name and connectivity.
If you're using Vagrant, go ahead and spin up your lab using "vagrant up". This would be a good time to create a second snapshot of your cluster VMs as well (using Vagrant? use vagrant snapshot save). Just in case you want to repeat the process.
Ready?
Estimated time to complete: Less than 3 hours.
Step 1: Pre-stage the VCO in Active Directory
We'll start by creating the computer account that will represent the SQL Server Network Name in AD.
- Log on to your domain controller, DC1, using your admin_* credentials.
- Open Active Directory Users and Computers.
- Select the Computers container.
- Right-click on Computers, select New, and then Computer.
- Type SQLCLUSTER1 (or your preferred name) in the Computer name field.
- Click OK.
FYI, while you're here, go ahead and create two additional user accounts. These will be standard accounts that you'll use for the SQL Server Agent and Database Engine service accounts. I went with sqlclustengsvc and sqlclustagentsvc.
Step 2: Disable the VCO
Disabling the VCO allows the cluster to take ownership during the SQL Server installation.
- Right-click on SQLCLUSTER1 and select Disable Account.
- Confirm that the account now shows a down arrow indicating it's disabled.
Step 3: Grant the CNO Permissions to the VCO
The Cluster Name Object (CNO) needs permission to bring the VCO online.
- Ensure Advanced Features are enabled (click view in the menu and make sure Advanced Features is checked).
- Right-click on SQLCLUSTER1 and select Properties.
- Go to the Security tab and click Add.
- Click Object Types, check Computers, and then click OK.
- In the Enter the object names to select field, type the name of your CNO (e.g., CLUSTER1), then click Check Names, and OK.
- Back in the Permissions window, select the CNO (e.g., CLUSTER1$).
- Grant Full Control permissions.
- Click OK to apply the changes.
Step 4: Install SQL Server FCI on the first node
Now, you're ready to install SQL Server and use the pre-staged VCO. You'll need the SQL Server 2022 Developer Edition ISO for this step.
- Log in to CLUSTER1SRV1 using your standard user account.
- Start the SQL Server installation.
- In the SQL Server Installation Center, click Installation on the left and choose New SQL Server failover cluster installation.
- Proceed through the setup by accepting the license terms and selecting the features you need.
- On the Instance Configuration page, for SQL Server Network Name, enter SQLCLUSTER1.
- On the Cluster Resource Group page, accept defaults or specify as needed.
- On the Cluster Disk Selection page, select the shared disks you added to the cluster (E, F, and T).
- On the Cluster Network Configuration page, provide a static IP address for the SQL Server instance. Remember to use an IP that falls outside the DHCP range. I went with 192.168.88.8.
- On the Service Configuration page, provide the service accounts you created earlier (leave the startup type as manual). Check "grant perform volume maintenance tasks privileges to SQL Server Database Engine Service, and click next.
- On the next page, configure the authentication mode for mixed mode, add a SA password, and add your current user to the sysadmin server role.
- Click the Data Directories tab. Choose E:\ as the data root directory, E:\Data as the user database directory, and F:\Data as the user database log directory. The backup directory can be left as the default for now (not best practice, but you can configure a file share later).
- Click the Tempdb tab and configure the data and log directories to use T:\Data.
- Click Next or configure the remaining maxdop and memory parameters (then next).
- Click Install.
Wait for the installation to complete before proceeding to Step 5. Did you get the green checks?
Step 5: Verify the SQL Server Network Name and Connectivity
Check the VCO in Active Directory:
- On DC1, refresh Active Directory Users and Computers.
- Verify that SQLCLUSTER1 is now enabled.
Verify in Failover Cluster Manager:
- On CLUSTER1SRV, open Failover Cluster Manager.
- Expand Roles and select your SQL Server role.
- Ensure that all resources are online, including the SQL Server Network Name.
Test Connectivity:
- You can install SQL Server Management Studio on a client machine (SRV1, built in the 001 challenge, would be a good place for it).
- Try to connect to SQLCLUSTER1. Bummer, you won't be able to.
We'll stop here for this week. Your last step is to determine why you can't connect to SQLCLUSTER1 from SRV1. Leave a comment below if you solve it.
Helping Others and Sharing Your Results
That's it for this week. In the next challenge, we'll address why you can't connect yet and add our second cluster node.
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, let me know by replying to this email.
Over the course of several weeks, you've built a cluster and half of an FCI. These are a lot of steps to perform manually each time.
If you're curious to learn more about automation, I'd invite you to check out Ansible for SQL Server DBAs: Level 1. Although Level 1 won't cover clustering, you'll build a foundation, using real-world playbooks, which will support both clustering and Availability Groups later (Level 2).
Already know PowerShell and transact SQL? Great, you'll continue to use those skills while also learning how Ansible can be used not just for SQL Server, but for configuring the OS and interacting with Active Directory.
It includes a few bonuses for registering early and is limited to 10 spots. However, this course isn't for everyone. It's very much hands-on with limited slide decks.
Who Should Not Enroll?
- Those Unwilling to Commit: If you're not ready to invest time and effort, this course isn't the right fit.
- Looking for Quick Fixes: This program requires dedication and application, not a passive approach.
How to Secure Your Spot.
- Enroll Today: Click the link below to secure your spot in this exclusive pre-sale offer.
- Watch Your Email: You'll be notified as the modules and bonuses are released.
Feel free to reply to this email and share your thoughts anytime—I’m all ears!
Good luck, and I look forward to seeing your results!
Luke
Responses