# encoding: ascii # api: powershell # title: Staff SQL Server DBA # description: Reboot a cluste or stand-alone server. For cluster, reboot each node one by one. Verify that RDP works for each server, verify all SQL Server instances can be connected, and all databases are in healthy state. Send email with the result of rebooting. The script works for Windows Server 2003 and later versions. This script requires Powershell3.0. # version: 0.1 # type: function # author: William Wang # license: CC0 # function: Email-RebootResult # x-poshcode-id: 4282 # x-archived: 2016-10-27T12:48:35 # x-published: 2013-07-02T01:22:00 # # #======================================================================== #Credits to http://poshcode.org/2886 as I leveraged some functions #Please modify parameters in Email-RebootResult # #======================================================================== #======================================================================== #START: Initialization param ($system) cls $Error.Clear() $ErrorActionPreference = "Stop" [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null function Email-RebootResult { param($subject) $emailrecipients = "" $emailfrom = "" $emailserver = "" Send-MailMessage -to $emailrecipients -from $emailfrom -smtpserver $emailserver -Subject $subject -attachment $log } function Write-RebootLog { param($log, $msg, $errormsg) $now = (get-date).tostring('yyyy-MM-dd HH:mm:ss -') "$now $msg" | out-file -FilePath $log -Append Write-Host "$now $msg" if ($errormsg) { $errormsg | Out-File -FilePath $log -Append Write-Host $errormsg } } function Get-ClusterNode { param($cluster) gwmi -class MSCluster_Node -namespace "root\mscluster" -computername $cluster -Authentication PacketPrivacy | add-member -pass NoteProperty Cluster $cluster } #Get-ClusterNode function Get-ClusterGroup { param($cluster) gwmi -class MSCluster_ResourceGroup -namespace "root\mscluster" -computername $cluster -Authentication PacketPrivacy | add-member -pass NoteProperty Cluster $cluster | add-member -pass ScriptProperty Node ` { gwmi -namespace "root\mscluster" -computerName $this.Cluster -Authentication PacketPrivacy -query "ASSOCIATORS OF {MSCluster_ResourceGroup.Name='$($this.Name)'} WHERE AssocClass = MSCluster_NodeToActiveGroup" | Select -ExpandProperty Name } | add-member -pass ScriptProperty PreferredNodes ` { @(,(gwmi -namespace "root\mscluster" -computerName $this.Cluster -Authentication PacketPrivacy -query "ASSOCIATORS OF {MSCluster_ResourceGroup.Name='$($this.Name)'} WHERE AssocClass = MSCluster_ResourceGroupToPreferredNode" | Select -ExpandProperty Name)) } } #Get-ClusterGroup function Get-SQLInstance { param($system, $isCluster) if ($isCluster) { $instances = (gwmi -class "MSCluster_Resource" -namespace "root\mscluster" -computername $system -Authentication PacketPrivacy | where {$_.type -eq "SQL Server"} | Select @{n='Cluster';e={$cluster}}, Name, State, @{n='VirtualServerName';e={$_.PrivateProperties.VirtualServerName}}, @{n='InstanceName';e={$_.PrivateProperties.InstanceName}}, ` @{n='ServerInstance';e={("{0}\{1}" -f $_.PrivateProperties.VirtualServerName,$_.PrivateProperties.InstanceName).TrimEnd('\')}}, ` @{n='Node';e={$(gwmi -namespace "root\mscluster" -computerName $system -Authentication PacketPrivacy -query "ASSOCIATORS OF {MSCluster_Resource.Name='$($_.Name)'} WHERE AssocClass = MSCluster_NodeToActiveResource" | Select -ExpandProperty Name)}}).VirtualServerName } else { $instances = (Get-WmiObject win32_service -ComputerName $system | where {$_.displayname -like "SQL Server (*)" -and $_.startmode -eq "Auto"}).name $instances = $instances.replace("MSSQL$", "$system\") $instances = $instances.replace("MSSQLSERVER", "$system") } return $instances } #Get-SQLInstance function Verify-AllSQLInstances { param($system, $isCluster, $log) try { Write-RebootLog $log "Getting all SQL instances" $instances = Get-SQLInstance $system $isCluster Write-RebootLog $log "$system has the following SQL instances:" $instances } catch { Write-RebootLog $log "Collecting SQL instance names failed with the following error: " $Error[0] throw "Collecting SQL instance names failed!" } foreach ($sql in $instances) { try { $sqlinstance = New-Object Microsoft.SqlServer.Management.Smo.server($sql) $sqlinstance.Refresh() Write-RebootLog $log "SQL instance $sqlinstance can be connected successfully" $db = $sqlinstance.Databases.Item("master") $ds = $db.ExecuteWithResults("select name, state_desc from sys.databases where state > 2") $t = $ds.Tables[0] if ($t.Rows.Count -ne 0) { Foreach ($r in $t.Rows) { Write-RebootLog $log "SQL Instance $sqlinstance has the following databases in problematic state" $name = $r.Item($t.Columns[0]) $state = $r.Item($t.Columns[1]) Write-RebootLog $log "Database $name is in $state state" } throw "database $name is in $state state" } else { Write-RebootLog $log "All databases on $sqlinstance are healthy" } } catch { Write-RebootLog $log "SQL instance $sqlinstance has the following error:" $error[0] } } }#Verify-AllSQLInstances $log = "C:\temp\Reboot-$system.log" #Create a cluster reboot log if (Test-Path $log) { remove-item $log -ErrorAction SilentlyContinue } try { $PSComputerName = (Get-WMIObject -Class Win32_OperatingSystem -ComputerName $system).PSComputerName } catch { Write-RebootLog $log "Connecting WMI for $system failed with the following error: " $Error Email-RebootResult "Reboot-Alert: Get-WMIObject failed for $system, probably due to firewall issue" break; } $isCluster = ($system -ne $PSComputerName) #END: Initialization #======================================================================== #======================================================================== #START: Reboot each node of the given cluster try { if ($isCluster) { Write-RebootLog $log "Start rebooting the cluster $system" $servers = (get-clusternode -cluster $system | select name | sort name).name Write-RebootLog $log "The following nodes were found in the cluster $system" $servers Write-RebootLog $log "Rebooting each node in cluster $system one by one" } else { Write-RebootLog $log "Start rebooting the stand-alone server $system" $servers = $system } foreach($server in $servers) { Write-RebootLog $log "$server is rebooting" #Restart computer and wait until Wmi is up, Timeout after 15 mininutes restart-computer -computername $server -Wait -Timeout 900 -For Wmi -force -ErrorAction Stop Write-RebootLog $log "$server is back" #================================================================ #START: Verify RDP $rdpTimeout = 150 $rdpInterval = 30 $rdpMaxCheckCount = $rdpTimeout/$rdpInterval $checkcount = 0 while ($checkcount -le $rdpMaxCheckCount) { try { $checkcount++ Write-RebootLog $log "Verifying $server can be connected over port 3389 (RDP)" $rdp = New-Object System.Net.Sockets.TCPClient -ArgumentList $server,3389 Write-RebootLog $log "Verified $server can be connected over port 3389 (RDP)" break } catch { if ($checkcount -le $rdpMaxCheckCount) { Write-RebootLog $log "RDP Verification failed, try again after $rdpInterval seconds" start-sleep -seconds 30 $Error.Remove($Error[0]) } else { Write-RebootLog $log "$server cannnot be connected over port 3389 (RDP)" break } } } #END: Verify RDP #================================================================ } } catch { Write-RebootLog $log "The system $system was not rebooted successfully with the following error:" $error Write-RebootLog $log "Script terminated due to the above error!" Email-RebootResult "Reboot-Alert: The system $system was not rebooted successfully, script terminated" break } #END: Reboot each node of the given cluster #======================================================================== #======================================================================== #START: For cluster, rebalance and verify each group is running on its preferred node. #START: For both cluster and stand-alone server, verify all instances are healthy try { if ($isCluster) { #======================================================================== #START: Rebalance groups so that every group runs on its preferred node try { Write-RebootLog $log "Rebalancing groups so that each group runs on its preferred node" $clustergroup = get-clustergroup -cluster $system foreach($group in $clustergroup) { try { #if the group is not running on its preferred node, move the group to its preferred node. $currentnode = $group.node if ($group.PreferredNodes.count -gt 0) { $preferrednode = $group.PreferredNodes[0] } $groupname = $group.Name if ($preferrednode -ne $null -and $currentnode -ne $preferrednode) { Write-RebootLog $log "Moving group $groupname from $currentnode to its preferred node $preferrednode" $group.MoveToNewNode($preferrednode, 120) } #bring the group online $group.BringOnline(120) } catch { Write-RebootLog $log "Moving group $groupname failed with the following error:" $error[0] } } } catch { Write-RebootLog $log "Rebalancing cluster groups failed with the following error:" $error[0] } #END: Rebalance groups so that every group runs on its preferred node #======================================================================== #======================================================================== #START: Verify that each group is running on its preferred node and each SQL instance can be connected try { $clustergroup = get-clustergroup -cluster $system foreach($group in $clustergroup) { try { $currentnode = $group.node if ($group.PreferredNodes.count -gt 0) { $preferrednode = $group.PreferredNodes[0] } $groupname = $group.Name $groupstate = $group.State #Notify DBA for each of the following situations: #a. the group is not online #b. the group is not running on its preferred node #c. SQL cannot be connected #verify the group is online if ($groupstate -ne 0) { Write-RebootLog $log "Group $groupname cannot be brought online" throw "Group $groupname cannot be brought online" } else { Write-RebootLog $log "Group $groupname has been brought online" } #verify the group is running on its preferred node if ($preferrednode -ne $null) { if ($preferrednode -ne $currentnode) { Write-RebootLog $log "Group $groupname is not running on its preferred node" throw "Group $groupname is not running on its preferred node" } else { Write-RebootLog $log "Group $groupname is running on its preferred node $preferrednode" } } } catch { Write-RebootLog $log "Group $groupname encountered the following error:" $error[0] } } } catch { Write-RebootLog $log "Verifying cluster failed with the following error:" $error[0] } #END: Verify that each group is running on its preferred node and each SQL instance can be connected #======================================================================== } Verify-AllSQLInstances $system $isCluster $log } finally { if ($Error.Count -eq 0) { Write-RebootLog $log "The system $system was rebooted successfully" Email-RebootResult "Reboot-Confirmation: The system $system was rebooted successfully" } else { Write-RebootLog $log "The reboot of system $system encountered some issues" Email-RebootResult "Reboot-Alert: The reboot of system $system encountered some issues" } } #END: For cluster, rebalance and verify each group is running on its preferred node. #END: For both cluster and stand-alone server, verify all instances are healthy #========================================================================