Best way to validate SQL Server status after OS patching

Why We should patch ?

OS patching is the process of updating an operating system with the latest security patches, bug fixes, and feature enhancements. These patches are released by the operating system vendor to address known vulnerabilities and improve the stability and performance of the system.

Patching is important because it helps keep your system secure and up-to-date. Without regular patching, your system may be vulnerable to cyber-attacks and other security threats.

why we should validate SQL Server status after OS patching ?

Validating the status of the SQL Server after OS patching is important to ensure that the patching process has not caused any issues with the SQL Server service. Patching an operating system can sometimes cause unexpected issues with other software running on the system, including SQL Server.

By validating the status of the SQL Server after OS patching, you can ensure that the SQL Server service is running as expected and that there are no issues that need to be addressed. This can help you avoid potential downtime or other issues that could impact your business operations.

To validate the status of the SQL Server after OS patching on multiple servers, you can use the below PowerShell.

Execute this script on system where all server is accessible.


#Mutliple Server validation

$ServerList = Get-content "C:\ServerList.txt"
$SQLServer = Get-WmiObject win32_service -ComputerName $ServerList | where-Object {$_.name -like "*SQL*"} | select __Server, name, state
$SQLService | Format-Table -AutoSize


#convert to html
$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; border-collapse: collapse; background-color: #6495ED}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black; border-collapse: collapse;}
</style>
"@

$SQLService | select-object __Server, name, state | ConvertTo-Html -head $Header -body "<h1> SQL Service status</h1>" | Out-file 'C:\output\Server.txt'
Format-Table -AutoSize

#If you want to Export Server status to excel then install ImportExcel if it is not installed on server.

Install-Module -Name ImportExcel
$SQLService | select-object __Server, name, state | Export-Excel -path c:\output\server.xlsx

Leave a comment