#sql cluster

Our App Supp team recently took over Windows Patching. SQL cluster patching proved a dilemma because we can’t failover the active node during business hours. We couldn’t put the SQL clusters on the regular schedule for other servers because we had to patch the passive then failover to the active and patch the active.

Simplified manual process for Maintenance Window 1:

Place SQL clusters into SCOM maintenance mode

Identify active & passive nodes in clusters

Patch the passive node 

Reboot passive servers

(Maintenance Window 2)

Place SQL clusters into SCOM maintenance mode

Failover the active node (DBAs screamed over this one!!)

Patch the active node

Reboot active node servers

Verify SQL cluster service is online

The manual process took the whole maintenance window and we’d have to stop and continue at a different window. Since this was all manual, this meant our team was putting in 12+ hrs on these days. We had servers that hadn’t been updated since 2013. The word “manual” is deceptive beause it encompasses this --> remote into each individual SQL cluster server, check the status on each one, failover, check for updates, check when updates are found, download/install updates, check when install is done, reboot the server, wait for server to come back, verify updates, check for more updates (because we were very behind!), repeat the process until no more updates found then wait for the server to reboot one last time to verify updates and services online - this took a ridiculous amount of hours for just one server multiplied by how many servers we had to do.

When we reviewed complete automation using SCOM and SCCM, our team determined that it would take long to develop the solution (something we’d work towards) but we needed a half-way solution to cut our manual process NOW. 

We used a PowerShell script to handle the failover (remote execution is not allowed so we still had to log on to each server to run it). We were still manually updating the servers though, which required keeping a manual eye on downloads, installs, reboots, download again, install again, reboot again (repeat until we get caught up on updates). 

While we tailor our fully automated process, we will pilot this half-way method:

Semi-Automated SCCM SQL Cluster Patching Process

Create two groups for SQL Cluster...one dubbed Active Node Group and one dubbed Passive Node Group

SCCM patches and restarts the passive node group 

We manually failover cluster and verify failover success

PUT THESE FILES IN THE SAME LOCATION ON THE SERVER

SAVE THIS AS A BAT

powershell.exe -ExecutionPolicy Bypass -File .\MoveClusterGroup.ps1

SAVE THIS AS MoveClusterGroup.PS1

Get-ExecutionPolicy

Import-Module FailoverClusters

$Node = Get-ClusterNode

$Node1 = $Node[0].Name $Node2 = $Node[1].Name

function mainMenu() {

   Clear-Host;    Get-ClusterGroup    Write-Host    Write-Host    

   Write-Host "Select the Node to Failover to:"    Write-Host    Write-Host "Press '1' to Failover to:" $Node1; Write-Host "Press '2' to Failover to:" $Node2;    Write-Host }

function returnMenu($option) { Write-Host    Write-Host    Write-Host "Failingover to Node: $option"    Write-Host     Get-ClusterGroup |    foreach-object `    {

       #Write-Host -Name $_.Name -Node $option        Move-ClusterGroup -Name $_.Name -Node $option

   }

   Write-Host    Write-Host    Write-Host    Write-Host    Write-Host "Press any key to close ..."

   $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

   exit }

do { mainMenu; $input = Read-Host "Select a Number then press Enter.  Press `"Q`" to Quit." switch ($input) { "1" { returnMenu $Node1; } "2" { returnMenu $Node2; } "Q" {            exit } default { Clear-Host; Write-Host "Invalid input. Please enter a valid option. Press any key to continue."; $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown"); } }

} until ($input -eq "Q");

When I have to reinstall SQL cluster. On my last day at current work. Because I think it was made by ten drunk batarians...

Hi All,

Yesterday I had to add a LUN to an existing SQL cluster. Child's play you say. Not so. The process is pretty simple.

1. Expose the LUN to each cluster server

2. Make sure it shows up in Disk Management

3. Add it as a storage resource in the Failover Cluster Manager

4. Add the storage resource to the SQL cluster

Now this last step I didn't know about - In order for the SQL server to see the new drive as a location you need to:

5. Add the new storage resources as dependencies in the SQL Server service in Failover Manager

This is actually kinda hard to find - Expand the SQL cluster in the Applications and Services item in Failover Manager. Then look at the bottom of the panel below the network section, drives section and everything else - You should see the SQL Server and SQL Agent services listed there.

Right click on SQL Server and there is your dependencies tab with the existing drives already listed there - Add the new drives.

Who woulda thunk it was so convoluted.

Have A Great Day -