Microsoft SQL Server Monitoring Tools

Microsoft SQL Server is a relational database management system. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications which may run either on the same computer or on another computer across a network. Microsoft markets numerous editions of Microsoft SQL Server aimed at varying audiences/workloads (ranging from small single-machine applications to large Internet-facing applications) and our Opspack allows you to monitor your Microsoft SQL server to ensure it is operating correctly every step of the way.

What You Can Monitor on Microsoft SQL Server

Opsview contains all the important service checks to make sure your Microsoft SQL Server is up and running. Service Checks for Microsoft SQL Server monitoring include:

Database size, memory, and agents checks
Database Log Information
SQL Stats including Server cache statistics and wait statistics
SQL transactions per second, time to connect and more

Service Checks

Service Check	Description
Microsoft SQL - Active transactions	Number of active transactions for the Database
Microsoft SQL - Check memory	Memory usage
Microsoft SQL - Database size	Database size (KB)
Microsoft SQL - DB Agent Service	Check Microsoft SQL Agent service is started
Microsoft SQL - DB Browser	Check Microsoft SQL Browser service is started
Microsoft SQL - Generic SQL server statistics	SQL server stats
Microsoft SQL - List all DBs	Database enumeration
Microsoft SQL - List all lock types	Lock type enumeration
Microsoft SQL - List SQL cache types	Cache type enumeration
Microsoft SQL - Lock statistics	Lock statistics
Microsoft SQL - Log cache hit	Log cache hit ratio percentage
Microsoft SQL - Log flush time	Length of time to flush logs (ms)
Microsoft SQL - Log growths	Log growth
Microsoft SQL - Server buffer manager	Server buffer stats
Microsoft SQL - Server Latch/SuperLatch statistics	Microsoft SQL latch/superlatch stats
Microsoft SQL - Server Listener	Microsoft SQL Server response time on TCP port 1433
Microsoft SQL - Server Service	Microsoft SQL Server Service status
Microsoft SQL - Server wait statistics	Server wait stats
Microsoft SQL - SQL Server cache statistics	Microsoft SQL server cache stats
Microsoft SQL - Stats	SQL stats
Microsoft SQL - Time to connect	Time to connect to the Database (s)
Microsoft SQL - Transactions per second	Number of transactions per second for the database

Prerequisites

Ensure your Opsview Monitor version is at least 6.7.0. Check Opsview Release Notes for the latest version of Opsview Monitor.

Microsoft SQL Server Monitoring Setup and Configuration

Network Dependencies

TCP port 1433 needs to be open from the Opsview Monitor server to connect to the monitored host. For more details, see the Microsoft document Configure the Windows Firewall to Allow SQL Server Access

Creating a monitoring user

Create a new user, either use "Windows Authentication" or "SQL Server Authentication".
If using "SQL Server Authentication" remove "Enforce password expiration" and "user must change password at new login".
Populate the sections as below:
- SERVER ROLES: Select “public” and “sysadmin”.
- USER MAPPING: Leave as default.
- SECURABLES: Click “Search..”, select “All objects of the types..”, and then “Servers” and click OK. Then scroll down the lists to find the permission titled “View server state”, and check “Grant”.
- STATUS: Ensure “Grant” and “Enabled” are checked respectively.

Step 1: Add the Host Template

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

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

Step 2: Add and configure variables required for this host

Variable	Description
MSSQLCREDENTIALS	Used in authenticating to Microsoft SQL. Override the Username and Password with your credentials.
MSSQLDATABASE	One instance of this variable per database you wish to monitor, the value should be set to the database name.
WINCREDENTIALS	Used in authenticating to Microsoft Windows. Override the Username and Password with your credentials.