This article is about a Powershell script to Check SQL Server Database Status and Services remotely. This will help in validating SQL Server after Windows patching.
To put in another way, DBAs are responsible for validating SQL Servers after server maintenance. Usually DBAs need to do a sanity check of the SQL Server after planned or unplanned maintenance.
The most common checks while doing a SQL Server validation:
- Validate SQL Services are up and running
- SQL Databases are online
- There is no error in the error log
- Authentication protocol after the restart (Kerberos Or NTLM)
Now imagine if you have a large number of servers, how much time you need to spend on such activity. The below simple yet effective powershell script will save a lot of time.
Powershell script to Check SQL Server Database Status and Services:
###########################################################################################################
#Objective: To Check SQL Service status, Database status, Errors in Errorlog and Authentication protocol #
#Author: Tridib Dev #
#Source:https://sqlgeekspro.com/ #
#Parameter: SQL Instance Name #
###########################################################################################################
param ([parameter(Mandatory = $true)] $SQLServer)
Write-host 'SQL Service Status for: ' $SQLServer
Write-host '---------------------------------------------'
Write-host " "
Get-WmiObject -Class Win32_Service -ComputerName $SQLServer | Where-Object {$_.Name -like "*SQL*"} | Format-List -Property Displayname, state, startName
Import-Module SQLPS -DisableNameChecking
$q1 = "select name as DatabaseName, state_desc as Status from sys.databases where state_desc <> 'online'"
$q2 = @"
CREATE TABLE #ErrorLog(
LogDate DATETIME,
ErrorSource NVARCHAR(MAX),
ErrorMessage NVARCHAR(MAX)
)
INSERT INTO #ErrorLog(LogDate,ErrorSource,ErrorMessage)
EXEC xp_readerrorlog 0,1, "Error"
select LogDate, ErrorMessage from #ErrorLog
drop table #ErrorLog
"@
$q3 = "select auth_scheme from sys.dm_exec_connections where session_id=@@spid"
$r1 = Invoke-Sqlcmd -ServerInstance $SQLServer $q1
$r2 = Invoke-Sqlcmd -ServerInstance $SQLServer $q2
$r3 = Invoke-Sqlcmd -ServerInstance $SQLServer $q3
write-host 'Database status'
Write-host '-----------------'
Write-host " "
Write-host $r1.itemArray
Write-host '-----------------'
write-host 'ErrorLog:-'
Write-host '-----------------'
Write-host " "
Write-host $r2.itemArray
Write-host '-----------------'
Write-host " "
Write-host 'Authentication Type'
Write-host '-----------------'
Write-Host $r3.itemArray
Sample Output:
Note: You can run the script in PowerShell ISE or download it from ValidateSQLServerThe output will be in console. I have written this script keeping it simple and have not used advanced modules so that you don’t have to install any modules. You just need SQLPS module.
Hope this will help in saving lot of your time in validating SQL Servers after any maintenance. You may also like Powershell Script to find SQL Instance Name for a Remote Server
