How to automatically monitor SQL Server availability

SQL Server availability overview

The core component of SQL Server is the Database Engine service. It is identified with the Windows service named MSSQLSERVER (sqlservr.exe) for the default instance, and for the named instance, the customized instance name is appended to the name of the service, e.g. MSSQLTESTDEPT.

The database Engine service plays an important role in managing and handling relational databases and maintenance within SQL Server itself. Although there are other two optional services, SQL Server Agent and SQL Server Browser, the Database Engine service is sufficient for everyday work with SQL Server.

Inspecting status

There are several methods to determine and monitor the SQL Server service’s availability, and how long is it running, in order to monitor it continuously and provide stability of the SQL Server. This can be done manually or programmatically.

Services.msc

Open the Services.msc, and check the status of the MSSQLSERVER service (and other services, as well), like in picture below:

SQL Server Configuration Manager

Review the list of SQL Server-related services and check their status through the SQL Server Configuration Manager:

Checking availability with T-SQL

• With stored procedure xp_servicecontrol

EXEC xp_servicecontrol N'querystate'
  ,N'MSSQLSERVER'

This stored procedure will show the current status of the service as a result:

Determining cause of changes

In monitoring, the availability of the SQL Server can be identified by online or offline status, simply by checking if the Database Engine service is running or not.

There are numerous reasons why this service can stop. It can be stopped by purpose or by some external event leading to the service shutting down.

The most common reason the SQL Server may have stopped is wrong logon information. It happens when the SQL Server domain account is changed, and logon information is not updated after that change. The service can be started, but without valid credentials, it can crash unexpectedly or stuck in “stopping” state. Solution for this is to timely update logon information in the properties of the specific Database Engine service (within Services.msc), whether the Local System account is chosen, or specific Windows user account:

Unlike the other SQL Server-related services, the Database Engine service can be paused, which will not cause an interruption of any active transactions to finish, but the service will not allow initializing of any new transactions on the particular SQL Server instance.

Changing the status

The Database Engine service can be paused, stopped or started again within SQL Server Management Studio, by right clicking on a desired instance in Object Explorer:

Note that MSSQLSERVER service cannot be initially started from SSMS. If the service is stopped, SSMS will not be able to connect on the particular instance and it will raise an error.

Other information

Information like temp object creation and login times can help us add context and information to SQL Server availability. The following can be useful information for tracking the availability of your SQL Servers

Determine the creation datetime of tempdb

Use this script on a desired SQL server instance to most accurately determine the actual login time of the Database Engine service, because system database, tempdb is recreated every time the Database Engine service initially starts (e.g., after system booting).

SELECT create_date
FROM sys.databases
WHERE NAME = 'tempdb'

Collect detailed information with the sys.dm_server_services view

SELECT servicename AS ServiceName
  ,startup_type_desc AS StartupType
  ,status_desc AS ServiceStatus
  ,process_id AS ProcessID
  ,last_startup_time AS LastStartupTime
  ,service_account AS ServiceAccount
FROM sys.dm_server_services
WHERE servicename = 'SQL Server (MSSQLSERVER)'

This script will show the detailed information of the particular SQL Server, including startup type, the service status, process ID, last startup time and used service account (Windows User account):

Note that in WHERE statement, servicename should contain the full service’s name, like displayed.

Get list of SQL server-related processes with PowerShell

Use this PowerShell script to get all services which contains the string “SQL Server (“, and review their statuses (running or stopped):

Get-Service -computername <name_of_the_machine> | Where-Object{$_.DisplayName -like "SQL Server (*"}

The result should appear like this:

To get information from the remote machine, change the -computername variable into desired one.

Actionable alerts

Include this PowerShell command within the custom command alert action, in order to check status of the service, and if it is not running, the script will start it:

powershell.exe "if ((Get-Service -name MSSQLSERVER).Status -eq 'Stopped'){Start-Service MSSQLSERVER}

if ((Get-Service -name MSSQLSERVER).Status -eq 'Running'){Restart-Service MSSQLSERVER}

Instead of MSSQLSERVER, other named SQL Server instance service name can be included. Also, if needed, the script can check and/or start the remote SQL Server instance:

powershell.exe "if ((Get-Service -name MSSQLSERVER -ComputerName <name_of_the_instance>).Status -eq 'Stopped'){Start-Service MSSQLSERVER}

if ((Get-Service -name MSSQLSERVER -ComputerName <name_of_the_instance>).).Status -eq 'Running'){Restart-Service MSSQLSERVER}"

Check and change configuration of the particular SQL Server service (Startup type)

To make sure the SQL server service is running continuously, use this script within custom alert action to avoid the possibility of change:

powershell.exe “if ((Get-Service -name MSSQLSERVER).StartupType -eq 'Manual'){Set-Service MSSQLSERVER -StartupType Automatic}

if ((Get-Service -name MSSQLSERVER).StartupType -eq Automatic){break}"

If the service’s startup type is any other than “Automatic”, the script will change its startup type. If it is already set to ”Automatic”, the script will terminate.