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.

Monitoring SQL Server availability with ApexSQL Monitor

Among the other special performance counters like SQL Server Agent status and System availability, ApexSQL Monitor incorporates the feature named SQL Server availability, which monitors the SQL Server service status, display the information whether SQL Server is online or offline, and fires alert in moments when SQL Server passes from offline to online state, and vice versa.

Basic information

When All instances are chosen in left pane, the list of the instances will appear in ApexSQL Monitor Group dashboard. If availability status of some instance changed in the meantime, the alert notification will be displayed under SQL Server field:

Also, when specific instance is selected, SQL Server overview will show additional information about SQL Server status (is it online or offline, and the login:

In this Dashboard view, information about current status of the service and its running time are present (marked above). In addition, there is the pie chart named Server, and in this case, the ratio is 89% available/11% unavailable.

Availability alerts

Alerts related to the SQL Server availability change from unavailable to available, and vice versa, can be reviewed and resolved in Alerts view, if needed. Also, resolved alerts can be documented with Reports feature (under Resolved alerts tab).s group.

As can be seen, there are two values on the scale, Online and Offline. Chart on the picture presents the moment when SQL Server becomes unavailable, and then available.

Availability metrics

The SQL Server availability counter is the part of the SQL Server metrics group.

Alerts

To assure that SQL Server instance availability monitoring is going smooth, achieve minimum downtime of the service, and to automate the monitoring process, set the email alerting (for the email notification on raised alerts related to Server availability counter), and/or custom command alert actions in ApexSQL Monitor to restart or change configuration of monitored SQL server service.

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.

January 5, 2017