As much as we’d all like to set up our SQL instances and never again let anyone touch them, that’s just not possible in real life. We live in a world where entities, for good or for evil, are constantly exposing vulnerabilities both in the SQL software and Windows itself. For that reason it is the responsibility of good DBA’s to keep their systems updated, and that means failovers.
It is typical in many environments to have a separation of duties, often keeping DBA’s from having control over the Windows Failover Cluster itself. In my particular case I actually have access to the cluster. However, our OCC (Operations Command Center) is tasked with carrying out Windows Updates on a regular basis. Letting them fail instances over means the servers get updated regularly (and also that I have to get up in the middle of the night for one less thing). I needed a way to allow their team members, regardless of experience level, to carry out Windows Updates on our cluster. Enter PowerShell scripting.
The code below is a modified version of what our OCC uses to carry out Windows Updates on our Failover Cluster. (There are options for Availability Groups too, but that’s a post for another day.) In the simplified example below there are six instances (SQL Instance 1-6) balanced across six nodes (sqlnode1-6). To keep things simple, a few assumptions are made in the script:
1. All instances are named with a particular convention. In this case that is “SQL Instance #”.
2. The instance numbers have their permanent homes on nodes of the same number (ie SQL Instance 1 belongs on sqlnode1).
3. The script is run on one of the nodes of the cluster.
4. The script is run in PowerShell ISE section by section, not all at once.
With that in mind I present the code to you below. It is written in a manner such that a Systems Administrator would be reading it as an instruction sheet and carrying out the tasks on your behalf. I am sure there are more elegant and efficient ways to accomplish this task, but this has been successful for us and I hope it can help you in some way. If you have any questions please feel free to comment or contact me on Twitter. Enjoy!
#### 1. Import Cluster Module ####
Import-Module FailoverClusters
#### 2. Fail all instances over to nodes 04-06. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode4
Move-ClusterGroup "SQL Instance 2" -Node sqlnode5
Move-ClusterGroup "SQL Instance 3" -Node sqlnode6
Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6
#### 3. Remove nodes 1-3 as possible owners and add 4-6.. ####
$ClusterGroups = Get-ClusterGroup
foreach ($Service in $ClusterGroups)
{
If ($Service -like "SQL Instance*")
{
Set-ClusterOwnerNode -Group $Service.Name -Owners sqlnode4,sqlnode5,sqlnode6
Get-ClusterOwnerNode -Group $Service.Name
}
}
#### 4. Install updates on nodes 1-3. ####
#### 5. Remove nodes 4-6 as possible owners and add 1-3. ####
$ClusterGroups = Get-ClusterGroup
foreach ($Service in $ClusterGroups)
{
If ($Service -like "SQL Instance*")
{
Set-ClusterOwnerNode -Group $Service.Name -Owners sqlnode1,sqlnode2,sqlnode3
Get-ClusterOwnerNode -Group $Service.Name
}
}
#### 6. Fail all instances over to nodes 1-3. ####
Move-ClusterGroup "SQL Instance 1" -Node sqlnode1
Move-ClusterGroup "SQL Instance 2" -Node sqlnode2
Move-ClusterGroup "SQL Instance 3" -Node sqlnode3
Move-ClusterGroup "SQL Instance 4" -Node sqlnode1
Move-ClusterGroup "SQL Instance 5" -Node sqlnode2
Move-ClusterGroup "SQL Instance 6" -Node sqlnode3
#### 7. Install updates on 04-06. ####
#### 8. Add all nodes as possible owners. ####
#### Note: This list is mostly random. It only has two goals.
#### Goal 1 is to set the permanent home at the first of the list. ####
#### Goal 2 is to make sure each of the cluster nodes is listed and that they are somewhat randomized. ####
Set-ClusterOwnerNode -Group "SQL Instance 1" -Owners sqlnode1,sqlnode5,sqlnode2,sqlnode4,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 2" -Owners sqlnode2,sqlnode4,sqlnode5,sqlnode3,sqlnode1,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 3" -Owners sqlnode3,sqlnode6,sqlnode1,sqlnode2,sqlnode5,sqlnode4
Set-ClusterOwnerNode -Group "SQL Instance 4" -Owners sqlnode4,sqlnode3,sqlnode6,sqlnode5,sqlnode1,sqlnode2
Set-ClusterOwnerNode -Group "SQL Instance 5" -Owners sqlnode5,sqlnode2,sqlnode4,sqlnode1,sqlnode3,sqlnode6
Set-ClusterOwnerNode -Group "SQL Instance 6" -Owners sqlnode6,sqlnode1,sqlnode3,sqlnode2,sqlnode4,sqlnode5
#### 9. Fail displaced instances back over to their permanent nodes. ####
Move-ClusterGroup "SQL Instance 4" -Node sqlnode4
Move-ClusterGroup "SQL Instance 5" -Node sqlnode5
Move-ClusterGroup "SQL Instance 6" -Node sqlnode6
Can I schedule the execution of the stored procedures by using a job scheduler other than SQL Server Agent?
Thanks for your question! I don’t see why you couldn’t use other schedulers. I personally have only used SQL Agent and Task Scheduler (starting PowerShell scripts).