Scripting Failovers for SQL Server Maintenance

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!

2 Replies to “Scripting Failovers for SQL Server Maintenance”

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

Leave a Reply

Your email address will not be published. Required fields are marked *