You are here

SQL Server Agentless Performance Monitoring

Requires Opsview Cloud or Opsview Monitor 6.7
check_circle
Opsview Supported

Microsoft SQL Performance 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 Performance Agentless Opspack allows you to monitor the state of a given database, without having to install the Opsview agent on the target host.

The service checks provided in this Opspack will report the performance metrics of your Microsoft SQL Database, allowing you to track a variety of statistics and identify any performance issues as soon as they appear.

Service Checks

Service Check Description
Microsoft SQL Performance Agentless - Active Transactions The number of active update transactions for the database
Microsoft SQL Performance Agentless - Average Wait Time The average amount of wait time (ms) for each lock request that resulted in a wait
Microsoft SQL Performance Agentless - Batch Requests/sec The number of batch requests that SQL Server receives per second
Microsoft SQL Performance Agentless - Buffer Cache hit Ratio Percentage of pages found in the buffer cache without having to read from disk
Microsoft SQL Performance Agentless - Compilation Statistics The number of times SQL Server compiles an execution plan per second and number of times an execution plan is invalidated due to some significant event, and SQL Server re-compiles it
Microsoft SQL Performance Agentless - Database Size The size of the database
Microsoft SQL Performance Agentless - Forwarded Records/sec The number of records the SQL server forwards per second (Where the row cannot fit in the existing page, the row migrates and access to the row will traverse a pointer)
Microsoft SQL Performance Agentless - Full Scans/sec The number of unrestricted full scans per second
Microsoft SQL Performance Agentless - Latch Statistics Average Latch Wait Time, Latch Wait Time, SuperLatch Promotions/Sec and SuperLatch Demotions/Sec
Microsoft SQL Performance Agentless - Lazy Writes/sec How many times a second that the Lazy Writer process is moving pages from the buffer to disk in order to free up buffer space
Microsoft SQL Performance Agentless - Lock Statistics Deadlocks/Sec, Lock Requests/Sec, Lock Wait Time, Lock Timeouts/Sec and Lock Waits/Sec
Microsoft SQL Performance Agentless - Log Statistics Log Cache Hit Ratio, Log Flush Wait Time, Log Flush Write Time and Log Growths
Microsoft SQL Performance Agentless - Paging Statistics Checkpoint Pages/Sec, Database Pages, Page Life Expectancy, Page Lookups/Sec, Page Reads/Sec, Page Splits/Sec, Page Writes/Sec, Readahead Pages/Sec and Target Pages
Microsoft SQL Performance Agentless - Processes Blocked The number of blocked processes
Microsoft SQL Performance Agentless - Stolen Server Memory The number of stolen pages in kb. Amount of memory used by SQL server but not for database pages
Microsoft SQL Performance Agentless - User Connections The number of different users that are connected to SQL Server

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 Performance Agentless' Opspack to your Opsview Monitor system.

Step 1: Import the Opspack

Download the database-microsoft-sql-performance-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-performance-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 Performance 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
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.

Step 4: Apply changes and the system will now be monitored

SQL Server Agentless Performance Service Checks