Steps to monitor the SQL Server Services

Problem

I have a need to monitor the SQL Server Services in my environment.  Are there any programmatic options to do so?  Check out this tip to learn more.

Solution

To monitor the SQL Server Services I will use the Get-WmiObject cmdlet and win32_service class in PowerShell.  The Get-WmiObject cmdlet gets instances of Windows Management Instrumentation (WMI) classes or information about the available classes.

Syntax

Here is the sample syntax:

Get-WmiObject [[-Class] ] [-Authority ] [-List] [-Recurse]

 [-Amended] [-AsJob] [-Authentication {Default | None | Connect | Call | Packet

| PacketIntegrity | PacketPrivacy | Unchanged}] [-ComputerName ]

[-Credential ] [-EnableAllPrivileges]

[-Impersonation {De fault | Anonymous | Identify | Impersonate | Delegate}] [-Locale ]

[-Namespace ] [-ThrottleLimit ] []

You can get the more information on the Get-WmiObject cmdlet, by executing the commands below on the PowerShell command prompt.

## for detailed information

get-help Get-WmiObject -detailed

## For technical information, type:

get-help Get-WmiObject -full

Before we proceed with the PowerShell script to monitor the SQL Services, I will show you few examples of PowerShell script with some different options. You can practice these sample commands on the PowerShell command prompt.

The command below will list of all the Services on the local computer and its properties.

get-wmiobject -Class win32_service | select-object *

The command below will print a list of all the methods and properties associated with the win32_service class.

Get-WmiObject -Class win32_service | get-member

The command below will print a list of specific properties of the services which are used with the Select-Object clause.

get-wmiobject -Class win32_service | select-object  Name,state,systemname,startmode,startname

The command below will only list out the MSSQLServer (Default Instance) or MSSQL$InstanceName (Named Instance) SQL Services.

get-wmiobject -Class win32_service | where {$_.name -like ‘MSSQLServer’ -OR $_.name -like ‘MSSQL$*’ }

| select-object  Name,state,systemname,startmode,startname

Now I hope you have a pretty clear understanding on how to use the Get-WmiObject cmdlet to get the list of SQL Services and their status. Based on this information, we will follow the steps below to setup SQL Service monitoring using T-SQL.

Step 1 – In this step we will create two tables tbl_SQLCMDB (Windows Server and SQL Server instance to monitor) and tbl_serviceStatus (SQL Service monitoring results).

— Table to store the windows server name & SQL instance name

CREATE TABLE [dbo].[tbl_SQLCMDB](

 [SQLinstanceName] [varchar](100) NULL,

 [windowsServerName] [varchar](100) NULL

) ON [PRIMARY]

— Inserting Data into SQLCMDB

insert into tbl_SQLCMDB values (‘SQLDBPool’,’SQLDBPool’) — Default Instance

insert into tbl_SQLCMDB values (‘JugalPC’,’JugalPC\MSSQL’)

— SQL Service Monitoring Output Table

CREATE TABLE [dbo].[tbl_serviceStatus](

 [SQLServiceName] [varchar](500) NULL,

 [servicestatus] [varchar](100) NULL,

 [windowservername] [varchar](500) NULL,

 [startmode] [varchar](100) NULL,

 [UpdateDate] [datetime] NULL DEFAULT GETDATE(),

 [startname] [varchar](1000) NULL,

 [InstanceName] [varchar](1000) NULL

) ON [PRIMARY]

Step 2 – To monitor the SQL Services we are going to use the tbl_SQLCMDB table and the sample PowerShell script below.

This PowerShell command will retrieve all the services with a name containing “SQL”.

get-wmiobject -Class win32_service | where {$_.name -like ‘*SQL*’}

| select-object  Name,state,systemname,startmode,startname

As an example, we are going to run the above PowerShell script either by using SQL Server Management Studio or a stored procedure, make sure xp_cmdshell is enabled on the SQL Instance. To enable xp_cmdshell you must have at least the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

You can execute the below script to check and enable xp_cmdshell.

declare @chkCMDShell as sql_variant

select @chkCMDShell = value from sys.configurations where name = ‘xp_cmdshell’

if @chkCMDShell = 0

begin

 EXEC sp_configure ‘xp_cmdshell’, 1

 RECONFIGURE;

end

else

begin

 Print ‘xp_cmdshell is already enabled’

end

Step 3 – Execute the script below to monitor the SQL Services and their status. You can also create a stored procedure with the T-SQL code below and execute it for monitoring purposes.

set nocount on

— Variable to store windows server name

DECLARE @server_name varchar(100)

DECLARE @SQLInstance_name varchar(100)

— table to store PowerShell script output

CREATE TABLE #output

(line varchar(max) null)

— Declaring cursor to fetch windows server name

DECLARE server_cursor CURSOR FOR

select distinct LTRIM(rtrim(windowsservername)) as windowsServerName,SQLInstanceName from tbl_sqlcmdb

OPEN server_cursor

FETCH NEXT FROM server_cursor

INTO @server_name, @SqlInstance_Name

WHILE @@FETCH_STATUS = 0

BEGIN

declare @svrName varchar(255)

declare @sql varchar(400)

set @svrName = @server_name

— Preparing PowerShell Dynamic Statement

set @sql = ‘powershell.exe -c “Get-WmiObject  -ComputerName ‘ + QUOTENAME(@svrName,””) + ‘ -Class win32_service |  where {$_.name -like ‘ + QUOTENAME(‘*SQL*’,””) + ‘} | select-object  Name,state,systemname,startmode,startname  | for each{$_.name+”|”+$_.state+”%”+$_.systemname+”*”+$_.startmode+”@”+$_.startname+”!”}”‘

— Inserting PowerShell Output to temporary table

insert #output

EXEC xp_cmdshell @sql

— Deleting the rows which contains error or has not sufficient data

delete from #output  where len(line) < 30

update #output set line = line + ‘!’ where line not like ‘%!%’

IF (SELECT COUNT(*) FROM #output where line like ‘%Get-Wmi%’) = 0

begin

insert into tbl_serviceStatus(SQLServiceName,servicestatus,windowservername,startmode,startname,InstanceName)

select  rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as SQLServiceName

,(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1, (CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) ))) as ServiceStatus

–,(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1, (CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) ))) as WindowsServerName

,@server_name

,(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘*’,line)+1, (CHARINDEX(‘@’,line) -1)-CHARINDEX(‘*’,line)) ))) as startmode

,(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘@’,line)+1, (CHARINDEX(‘!’,line) -1)-CHARINDEX(‘@’,line)) ))) as startname

,@SQLInstance_name

from #output

where line is not null and LEN(line) > 30

end

— Clearing output table

truncate table #output

— Next windows record

FETCH NEXT FROM server_cursor

INTO @server_name,@SQLInstance_name

END

CLOSE server_cursor;

DEALLOCATE server_cursor;

— dropping the temporary table

drop table #output

Sample Output

Here is some sample output as a point of reference:

SELECT * FROM dbo.tbl_serviceStatus

SQL Services Sample Output

Automation

To automate this process, can create a SQL Server Agent Job from the above T-SQL code to monitor the SQL Services in your environment and you can generate different kind of reports by querying the tbl_serviceStatus table.

For example:

  • List of SQL Services Stopped
  • List of SQL Services which has start mode Manual
  • List of SQL Service running under local system account
  • List of SQL Service AD accounts in the environment

Next Steps

  • Configure Database Mail on your SQL Server instances and generate alert emails to help improve the automation and notification.
  • Add additional field to tbl_SQLCMDB table for example ServerType (PRD, QA, UAT or DEV), Version, IsCluster, etc. to help prioritize issues.
  • Be proactive and begin monitoring the status of your SQL Server services in your environment.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *