Friday, August 8, 2014

Powershell - Get ALL SQL services running Status in SQL server VMs

I created the following script to check any SQL services running on SQL server VMs. The script will read servers name in SQLSvrList.txt file and retrieve SQL services running status and startmode from WMI service module. It will export to SQLServerStatus.csv. If there is error in connection, the errors will be written to Error.txt.

Enjoy Scripting....

$($ServerList = 'E:\PowerShell\SQLServerList\SQLSvrList.txt'
if(!(test-path $ServerList))
{
    Write-Error "SQLSvrList.txt is not Found" -ea Stop
}
if(test-path $ServerList)
{
$errorlog = 'E:\PowerShell\SQLServerStatus\Error.txt'
$exportcsv ='E:\PowerShell\SQLServerStatus\SQLSeverStatus.csv'
$servername = Get-Content $ServerList
Clear-Content $exportcsv
foreach($server in $servername){
Try{

$WMI=Get-WmiObject win32_service -computer $server | Where-Object {$_.DisplayName -match 'SQL'}|

Select-Object @{Expression={$_.systemName};Label = "ServerName"},
                       @{Expression={$_.Name};Label = "SQLService"},
                       @{Expression={$_.StartName};Label = "Account"},
                       @{Expression={$_.StartMode};Label = "StartMode"},
                       @{Expression={$_.State};Label = "State"},
                       @{Expression={$_.DisplayName};Label = "ServiceName"}
                       $WMI|Export-csv $exportcsv -Append -NoTypeInformation
                                       

}
Catch
{
    Clear-Content $errorlog
     "Fail to get information $server +' ' + $RunTime :$_" |Out-File $errorlog -Append
}
}
})
Get-Job | Wait-Job | Out-Null
Remove-Job -State Completed

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...