---
title: "Database - Microsoft SQL Opspack"
description: ""
url: "https://docs.itrsgroup.com/docs/opsview/6.11.7/opspacks/database-microsoft-sql-opspack/index.html"
---
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

Opsview Monitor contains all the important service checks to make sure your Microsoft SQL Server is up and running. Service Checks for Microsoft SQL Server 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  |

## Setup and Configuration

### Network Dependencies

TCP port 1433 needs to be open to allow 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](http://support.microsoft.com/kb/287932)

### 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.

### Add the Host Template

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

![Add host template](https://docscdn.itrsgroup.com/opsview/images/opspacks-database-microsoft-sql-add_microsoft-sql_host.png)


**Note**


For more information, refer to the documentation on [Adding Host Templates to Hosts](https://docs.itrsgroup.com/docs/opsview/6.11.7/host/index.html#host-templates).



### 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.        |

![Add variables](https://docscdn.itrsgroup.com/opsview/images/opspacks-database-microsoft-sql-add_microsoft-sql_variables.png)
![Add variables](https://docscdn.itrsgroup.com/opsview/images/opspacks-database-microsoft-sql-add_microsoft-sql_variables2.png)
![Add variables](https://docscdn.itrsgroup.com/opsview/images/opspacks-database-microsoft-sql-add_microsoft-sql_variables3.png)


**Note**


For more information, refer to the documentation on [Adding Variables to Hosts](https://docs.itrsgroup.com/docs/opsview/6.11.7/adding-a-new-variable/index.html#adding-a-variable-to-a-host).



### Apply Changes

Apply Changes and the system will then be monitored:

![View Output](https://docscdn.itrsgroup.com/opsview/images/opspacks-database-microsoft-sql-output.png)

## Plugin help


**Secure Args**


The `check_mssql_database.py` plugin uses the [Secure Args](https://docs.itrsgroup.com/docs/opsview/6.11.7/configuration/secure-arguments/index.html) feature for argument passing.




