Database - 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 Copied
The Microsoft SQL Database States Agentless Opspack allows you to monitor the state of a given database without having to install an 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 Copied
| Service Check | Description |
|---|---|
| Microsoft SQL Agentless - Database State | Monitor the state of a given database |
Prerequisites Copied
To use this Opspack, your Windows host must first be configured for Powershell Agentless Monitoring.
Setup Windows Host for Monitoring Copied
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 Copied
Add the Host Template Copied
Add the Database - Microsoft SQL Database States Agentless Host Template to your Opsview Monitor host.
Note
For more information, refer to the documentation on Adding Host Templates to Hosts.
Add and configure variables required for this host Copied
| 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. |
Note
For more information, refer to the documentation on 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.
Apply Changes Copied
Apply Changes and the system will then be monitored:
