MS SQL

This describes how to discover and monitor the Microsoft SQL Server (MSSQL) database.

Prerequisites

• The database and port 1433 are accessible to the gateway.
• A gateway management profile mapped to a resource.
• Credentials of type Database before assigning a template to a resource. The Port, Connection TimeOut, and Is Secure parameters are not used when creating credentials.

Discovery

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

Discover 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 apply agent-based templates to initiate MS SQL monitoring.

Discover using the Gateway

To monitor the database, install gateway version 5.0.0 or later. For Windows authentication support, install gateway version 7.0.0 or later.

The gateway establishes an MS SQL database connection using the Java Database Connectivity (JDBC) Java API and collects monitoring metrics with SQL queries.

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

• WMI
• SSH
• SNMP

You can also manually add a database server to the infrastructure inventory:

1. Select Infrastructure and click Resources.
2. Select the Settings icon on the top-right and click Add.
3. On the Add Device page, enter the Device Type and any other information you want to enter.
4. Click Save.

To start monitoring, associate valid database credentials with the discovered database and 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 database monitoring in your cloud environment, see Monitoring Cloud Database using Gateway.

Create Custom Metrics

To create custom metrics based on SQL queries:

1. Navigate to Setup > Monitoring > Metrics.
2. Click +Add, which displays the CREATE METRIC dialog. In addition to the generic information you want to enter when defining a metric, specify the following MSSQL-specific parameters.
3. For Adapter Type, select Application.
4. For Application Type, select MS SQL. This displays the MSSQL-specific dialog for entering queries as metrics.
5. In the SQL Query field, enter the SQL query to generate the metric.

For example, to create a metric that represents the day since the last database backup:

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

The SQL server, system-defined stored procedures are a group of SQL requests saved in the database. The stored procedures also accept arguments passed to the procedures. For example,

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

Define stored procedures in the target server.

If you want to monitor the MS SQL server using stored procedures, add a stored procedures query.

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@

The @dbname@ attribute is optional.

After you define MSSQL metrics, define the monitor, adding MS SQL stored procedure parameters and specifying the parameters to be passed into the procedures.

Assign Templates

Assign Templates from Setup

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

Assign Templates at the Device level

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

Template configuration parameters:

Assign template from device management policies

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 monitoring output in a graphical format. To view the output, click Infrastructure > Resources > MSSQL > 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 Telnet connectivity:

       ping <IP Address>
       telnet <IP Address> <Port>
       

2. Verify database connectivity:

   Gateway versions before 5.3.0:

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

   Gateway versions 5.3.0 and above:

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

   Note: If you are using windows based credentials, then instead of <Database Name/InstanceName> mention as <instanceName>:windows in the above query.

Supported Templates

Supported metrics