Monitoring MS SQL

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft. Monitoring the MS SQL database can help database administrators to:

• Identify the issues in the database.
• Check metric graphs to view the monitoring updates.
• Take proactive steps to resolve issues before it affects the end-users.

Prerequisites

• Database and port(1433) can be accessed by the gateway.
• serveradmin privilege to monitor the database. For more information, see Monitoring Privileges.
• A gateway management profile mapped to a resource.
• Create credentials with type as Database before assigning a template to a resource. The Port, Connection TimeOut, and Is Secure values are not used while creating credentials.

Supported version

• MS SQL Server 2008 or higher.
• JDBC(Java Database Connectivity): 8.2.2.jre11.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support MS SQL server agent-based or agentless monitoring.

Discovery using an agent

The installed agent auto-discovers the MS SQL database and collects the following metric information:

• Database storage information
• SQL server instances - Information about all SQL Server instances
• Log files utilization in percentage
• Data files utilization in percentage
• Log files free space in KB
• Data files free space in KB
• Database backup information
• Jobs-SQL enabled jobs status
• Processes related to SQL server information
• Services related to SQL server information

You can also apply agent-based templates to initiate MS SQL monitoring.

Discovery using the gateway

The gateway establishes a connection to the database using Java Database Connectivity (JDBC) Java API and collects monitoring metrics using SQL queries. To monitor the MS SQL database, install gateway version 5.0.0 or later. For Windows authentication support, install gateway version 7.0.0 or later.

Use one of the following mechanisms to discover MS SQL servers to add them to your inventory:

• WMI
• SSH
• SNMP

Optionally, add a database server manually to the infrastructure inventory.

To add a resource manually to the inventory:

1. Select Infrastructure.
2. Click the Settings > Resource > Add.
3. From the Add Device window, provide the required details such as Device Type and Management Profile.
4. Click Save.

Prepare the resource to start monitoring

To monitor MS SQL:

1. Associate appropriate database credentials to the discovered database.
2. Assign one or more database-specific global monitors or templates on the device.
   Optionally, create custom metrics or monitors using SQL queries and assign them to the database.

To track monitoring of MS SQL database in your cloud environment, see Monitoring Cloud Database using Gateway.

Global templates and metrics

Global templates

Global metrics

Create custom metrics

An administrator can use one of the following methods to create custom metrics using SQL query:

Using SQL query

To create a metric using SQL query for MS SQL monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to find the day since the last backup of the database, use:

SELECT DATEDIFF(D, MAX(BACKUP_FINISH_DATE), GETDATE()) AS DAYS_SINCE_LAST_BACKUP\nFROM MSDB.DBO.BACKUPSET\nWHERE TYPE = 'D'

For more information, see Manage Metrics.

Using stored procedures

The system-defined stored procedures used in SQL server is a group of SQL requests saved into the database. The stored procedures also accept and execute according to the arguments passed into the procedures. For example,

database_name,mirroring_state::EXEC sp_dbmmonitorresults @dbname@ @mssql.storedprocedure.param1@ @mssql.storedprocedure.param2@

If you want to monitor the MS SQL server using stored procedures, perform the following:

Step 1: Create metrics
Create metrics to understand the performance and availability of your MS SQL server. Add stored procedures query while creating metrics. For more information, see Managing Metrics.

SQL query syntax:

sp_dbmmonitorresults database_name, rows_to_return, update_status

If there are no column values, use the syntax:

EXEC sp_dbmmonitorresults @dbname@ @mssql.storedprocedure.param1@ @mssql.storedprocedure.param2@

For example,

EXEC sp_dbmmonitorresults msdb, 0, 0;

The @dbname@ attribute is optional.

Step 2: Create monitors
Add MS SQL stored procedure parameters and select the parameters to be passed into the procedures while creating monitors. For more information, see Manage Monitors.
The parameters added in the configuration parameters are aligned with the arguments provided in the SQL query while creating the metric.

Step 3: Create template
Create gateway-based templates to add the stored procedure monitors. For more information, see Create Templates.

Step 4: Assign templates
After creating a template, assign them to the resources. For more information, see Assign Templates.

Assign templates from setup

Assign MSSQL templates to one or more resources for a selected client and modify the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.

Assign templates at the device level

Applying MSSQL templates at the device level helps assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.

Template configuration parameters:

Assign template from device management policies

Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Creating Policies.

View resource metrics

The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the desired database resource name > resource Details > Metrics.

Troubleshooting

Verify the following if the graphs are not plotted correctly:

• The resource is accessible and Telnet is established for the IP address and port.
• Valid credentials are used for the resource.

If you experience MSSQL monitoring issues, follow these troubleshooting steps:

1. Verify server and gateway connectivity using Ping.

ping <IP Address>

Example:

ping 12.26.105.173

2. Verify Telnet-to-server listening port connectivity.

telnet <IP Address> <Port>

Example:

telnet 172.26.105.173 1433

3. Execute the following commands from GCLI to verify the database connectivity:

gcli

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

Example:

db mssql 172.26.105.173 root Pass@123 5432 master 15000 10000 insecure "select * from msdb.dbo.suspect_pages"

From gateway version 5.3.0, use the following format to execute the command along with the result-set:

db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>

Example:

db mssql 172.26.105.173 root Pass@123 5432 master 15000 10000 insecure Yes "select * from msdb.dbo.suspect_pages"