SQL Server Database States Agentless Monitoring

Microsoft SQL Database States Agentless Opspack

Microsoft SQL is a relational database management system (RDBMS) which allows you to process transactions, collect business intelligence and analyse data. Microsoft SQL incoporates various data management and analytics tools, and supports technologies such as cloud computing and mobile devices.

What You Can Monitor

The Microsoft SQL Database States Agentless Opspack allows you to monitor the state of a given database without having to install the Opsview agent on the target host.

A database is always in one specific state, such as ONLINE, OFFLINE, or SUSPECT (see Microsoft - Database States for the full list). The service check provided in this Opspack will report this state, allowing you to know when your database needs your attention, and track the state of your database over a historical timeframe.

Service Checks

Service Check	Description
Microsoft SQL Agentless - Database State	Monitor the state of a given database

Prerequisites

Ensure your Opsview Monitor version is at least 6.7.0. Check Opsview Release Notes for the latest version of Opsview Monitor.
To use this Opspack, your Windows host must first be configured for Powershell Agentless Monitoring.

Setup Windows Host for Monitoring

By default, Windows hosts will not allow remote PowerShell scripts to run, which is required for Opsview Agentless Monitoring plugins to work.

This can be configured manually by the Windows Host administrator, or automatically using our recommended approach by running the ConfigureRemoting.ps1 Powershell script on the Windows Host.

Powershell Agentless Monitoring requires at least version 5.0 of Powershell. Check the Powershell version on your Windows Host by running:

$PSVersionTable.PSVersion

Run the ConfigureRemoting.ps1 script with Administrator privileges using a Powershell terminal. This will configure firewall rules, self-signed SSL certificates and authentication for PowerShell remoting.

Check this has been configured properly by running:

winrm quickconfig

You should get the following output:

WinRM service is already running on this machine.
WinRM is already set up for remote management on this computer.

By default port 5985 must be opened from the Opsview monitoring server to the Windows host you wish to check. WinRM utilises the HTTP/HTTPS protocol and can be configured to use certificates to secure the data in transit.

Ensure the service is listening by running:

For HTTP: netstat -an | findstr 5985

For HTTPS: netstat -an | findstr 5986

When using basic authentication with WinRM, the following commands must also be run on the windows host:

winrm set winrm/config/service/auth '@{Basic="true"}'
winrm set winrm/config/service '@{AllowUnencrypted="true"}'
winrm set winrm/config/client/auth '@{Basic="true"}'

If you receive a 500 error, which is a known issue on Windows Server 2016, you may need to install WinRM-IIS-Ext. You can do so by running the following command:

Add-WindowsFeature winrm-IIS-Ext

Setup and Configuration

To use this Opspack, you need to add the 'Database - Microsoft SQL Database States Agentless' Opspack to your Opsview Monitor system.

Step 1: Import the Opspack

Download the database-microsoft-sql-database-states-agentless.opspack file from the Releases section of this repository.

Navigate to Host Template Settings inside Opsview Monitor and select Import Opspack in the top right corner.

Then click Browse and select the database-microsoft-sql-database-states-agentless.opspack file. Click Upload and then click Import when the file is uploaded.

For more information, refer to Opsview Knowledge Center - Importing Opspacks

Step 2: Add the Host Template

Add the Database - Microsoft SQL Database States Agentless Host Template to your Opsview Monitor host.

For more information, refer to Opsview Knowledge Center - Adding Host Templates to Hosts.

Step 3: Add and configure variables required for this host

Variable	Description
WINRM_TRANSPORT	Used for authenticating with the remote host. The Value is the Authentication Transport Type - must be one of: Basic, Certificate, Kerberos or NTLM. NOTE: CredSSP is not supported. Override the Username and Password with the credentials to be used for authentication with the remote host. Scheme can be overridden to make the check use HTTP (defaults to https). Extra arguments can be used to pass extra arguments to check_by_winrm.py
MSSQL_DATABASE_NAME	For each database you wish to monitor within your MSSQL instance, add a MSSQL_DATABASE_NAME variable to your host. A service check will be added to the host for each database name variable added.
KERBEROS_REALM	Only required if authenticating using Kerberos. The Value is the Kerberos realm.

For more information, refer to Opsview Knowledge Center - Adding Variables to Hosts.

Optional:

If you want to monitor an MSSQL server instance other than the default instance, you can add the instance name using the MSSQL_INSTANCE_NAME variable. Add the instance name in the Value field for this variable. Only one instance can be monitored per host - add additional hosts if you want to monitor multiple MSSQL server instances.