checking the status of SQL services and databases on multiserver environment

Article
01/21/2014

If you have only one SQL Server to check, it is quite straightforward to check the status of services and databases. When you have dozens of SQL servers, I am sure you don’t want to spend several hours just for a basic control. To monitor the SQL services (SQL server service and SQL agent service, you can also add Integration, Analysis and Reporting Services if you need) and databases on multiserver environment, I have written a PowerShell script and will explain the steps briefly.

I will create this demo on SQL 2012 SP1 running on Windows 8.1. As a prerequisite, I will set PowerShell execution policy to RemoteSigned with the script:

 Set-ExecutionPolicy RemoteSigned

Firstly I will create 3 txt files called AllServers.txt, status.txt and status_error.txt under the path C:\control_scripts as shown below:

Status.txt and status_error.txt files will be empty. In AllServes.txt file, there will be list of servers you want to monitor. Please write the host names which SQL Servers are running on. As a test environment, I am using only one server, but will write the hostname two times to confirm that my code can be run on multiserver environment:

Now, it is time to write the PowerShell script:

I will write the code in 3 basic parts, the first part of the code is a function which get all SQL instances for a server. The second part of the code will monitor SQL services and the last part will monitor the databases for all servers.

  #Here is the first part of the code, referring PowerShell magazine here:  
  #http://www.powershellmagazine.com/2013/08/06/pstip-retrieve-all-sql-instance-names-on-local-and-remote-computers 
  #I will use this function in the 3rd part of the code to access the SQL instances to be able to check database status. 
 
 Function Get-SQLInstance { 
 
 param ( 
  [string]$ComputerName = $env:COMPUTERNAME, 
  [string]$InstanceName
  ) 
 
 
 try { 
  $reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $ComputerName) 
  $regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" ) 
  $instances = $regkey.GetValueNames() 
 
 if ($InstanceName) { 
 if ($instances -contains $InstanceName) { 
 return $true
  } else { 
 return $false
  } 
  } else { 
  $instances
  } 
  } 
 catch { 
 Write-Error $_.Exception.Message
 return $false
  } 
  }  
 
  #I am getting the list of all SQL Servers with the following code:

   $servers = @(get-content "C:\control_scripts\AllServers.txt" -TotalCount 2) 
 
  #The code below is doing nothing but just defining columns of the output table: 
 
  $SrvcName =@{label="Service Name" ;alignment="left" ;width=20 ;Expression={$_.Name};}; 
  $SrvcMode =@{label="Start Mode" ;alignment="left" ;width=20 ;Expression={$_.StartMode};}; 
  $SrvcState =@{label="State" ;alignment="left" ;width=20 ;Expression={$_.State};}; 
  $SrvcMsg =@{label="Message" ;alignment="left" ;width=30 ; ` 
 Expression={ if ($_.State -ne "Running") {"Alarm: Stopped"} else {"OK"} };}; 

  #Here is what I am doing the basic job, With the help of Get-WmiObject cmdlet I am getting the status information of 
  #SQL Server services and SQL Agent services. And with a simple for loop, I am getting start mode, state and status information. 
 
 foreach($server in $servers) 
  { 
 
  $srvc = Get-WmiObject ` 
  -query "SELECT *  
 FROM win32_service 
 WHERE name LIKE '%MSSQL%'  
 OR name LIKE '%SQLAgent%'" ` 
  -computername $server ` 
  | Sort-Object -property name; 
 
 Write-Output ("Server: {0}" -f $server); 
 Write-Output $srvc | Format-Table $SrvcName, $SrvcMode, $SrvcState, $SrvcMsg;  
  } 
  #Here is the last part of the code, the outer loop is for all servers, and the inner one is for all instances. 
 
 foreach($server in $servers) 
  { 
  $insts= Get-SQLInstance -ComputerName $server
 
 
 foreach($inst in $insts) 
  { 
 
  #customizing the result of the function here
 
  $sqlinst =if ($inst -eq "MSSQLSERVER") {"$server"} else {"$server\$inst"}; 
 
  #T-SQL code checks the status of DBs, if all is ONLINE; then it returns OK; #if not it lists the offline DBs
 
  $q = "declare @offline table
  (instname varchar(20), 
 dbname sysname, 
 status varchar(20)) 
 
 declare @query varchar(max) 
 declare @all int
 select @all = count(name) from sys.databases
  --select @all
 declare @online int
 select @online = count(name) from sys.databases where state=0
  --select @online
 if ( @online = @all) 
 begin
 
 set @query='select @@servername as instname, ''all DBs'' as dbname, ''OK'' as status ' 
 insert into @offline
 exec (@query) 
 
 
 select getdate() as datetime, * from @offline
 end
 else 
 begin
 
 
 set @query = 'select @@servername as instname, name as db_name, state_desc from sys.databases where state<>0' 
 
 insert into @offline
 exec (@query) 
 
 
 select getdate() as datetime, * from @offline
 end"  
 
 
  #Invoke-SQLcmd cmdlet is used to run a query on a SQL instance within #Powershell. 
 Invoke-Sqlcmd -ServerInstance $sqlinst -Database "master" -Query $q ` 
  }   }

You can save the above code as status.ps1 file under the path C:\control_scripts. Last step is running this code in a bat file called STATUS.bat:

The code for STATUS.bat file should be like below:

powershell.exe "C:\control_scripts\status.ps1" -noclobber >status.txt 2> status_error.txt

If the result is successful it will write it to status.txt file as shown below:

If it gets any error, it will write the details of the error(s) to status_error.txt file.

checking the status of SQL services and databases on multiserver environment

Additional resources