This describes how to discover and monitor the Microsoft SQL Server (MSSQL) database.
Introduction
The objective of this document is to help users, who are completely new, to create query based (MSSQL) custom monitors development in the OpsRamp. This document provides a complete user guide to implement sql query based metrics and create monitors and templates. Also explain prerequisites, limitations, Troubleshooting steps and FAQs which are related to MSSQL DB Monitoring.
Note
The MSSQL Query-Based Monitoring is completely related to Classic Application Monitoring and does not pertain to the SDK app.
What is MSSQL Database Monitoring?
OpsRamp provides the MSSQL Database Monitoring, It offers readily available performance metrics, enabling users to effortlessly visualize the health and availability of their MSSQL Database environment. With a simple login to the OpsRamp portal, Users can access and view the status updates and performance metrics specific to MSSQL servers.
How MSSQL Database Monitoring works
This monitoring functionality operates to executing sql queries on target MSSQL servers via a Gateway using JDBC connections. However, users must adhere to specific prerequisites outlined in the following sections, which will be elaborated upon below.
Prerequisites
The database and port 1433 are accessible to the gateway.
A gateway management profile mapped to a resource.
End device credentials must be assigned on End device (Credentials of type SSH ), before assigning a template to a resource.
Database/instance credentials must be assigned on End device (Credentials of type Database ), before assigning a template to a resource. The Port, Connection TimeOut, and Is Secure parameters fields in credentials are not considered, instead those values will be taken from Monitor’s configuration parameters.
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:
Select Infrastructure and click Resources.
Select the Settings icon on the top-right and click Add.
On the Add Device page, enter the Device Type and any other information you want to enter.
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.
OpsRamp comes with a predefined set of MSSQL metrics. However, for users who want any customization and the ability to create their own monitoring solutions, we offered UI-based support for creating MSSQL metrics.
To prepare MSSQL metric, Users must return max one or two fields from the query. Please follow below usecases along with examples to understand how to write queries to monitor MSSQL server environment.
How to Prepare MSSQL metric:
Metric with no components: If user want to prepare metric query for the direct value without any components then user should return only one column from the query result. Here the value will be consider as metric value.
Examples:
To monitor total count of the MSSQL Databases.
Query - Select COUNT(*) as DBCount FROM sys.databases
Above metric query output is the count value of the Number of Databases, Here our framework should consider the value as metric value.
To monitor count of database corruption (suspect db pages count).
Query - select count(*) from msdb.dbo.suspect_pages WITH (NOLOCK)
Above metric query output is the count value of the suspect db pages, Here our framework should consider the value as metric value.
Metric with multiple components: If user want to prepare metric query for multiple component based metrics then user should return to two columns from the query result. Here column1 will be considers as component name and collumn2 will be consider as metric value.
Examples:
To monitor active users count of the each MSSQL Database.
Query - SELECT DB_NAME(sP.dbid) AS database_name, COUNT(sP.spid) AS total_active_user_connections FROM sys.sysprocesses sP GROUP BY DB_NAME(sP.dbid) ORDER BY 1
Here framework should consider first column database_name as component name and second column total_active_user_connections as metric value.
To monitor status of the each MSSQL Database.
Query - select name, state FROM sys.databases
Here framework should consider first column database_name as component name and second column state as metric value.
Note
- Users must need to follow these usecases while preparing queries for DB query based monitoring.
- If User prepare metric query for multiple components, Then the OpsRamp framework showcase component names like this format <InstanceName-ComponentName> or <Port_InstanceName-ComponentName> (Port number added if target environment having multiple instances with different ports).
- Metric query returns max two columns only. If it returns one column consider as metric value & If it returns two columns, first column as componentName and another column as metric value.
- Metric value could be number only, If it returns string-type values then must use enumarated mappings otherwise graphs are not plotted.
How to calculate/format metric values with example queries:
To calculate metric values like utilization, memory usage conversions (like GB to MB), time conversions (like seconds to milliseconds)..etc., follow below examples for better understanding.
Examples:
- This query monitors how many minutes have passed since the last full backup (type = 'D') of each database.
Query - select database_name, DATEDIFF(MINUTE, MAX(backup_finish_date), GETDATE()) AS minutes_since_last_backup from msdb.dbo.backupset where type = 'D' group by database_name
- This query returns the number of days the SQL Server instance has been running (i.e., its uptime).
Query - select DATEDIFF(DAY, sqlserver_start_time, getdate()) as uptime from sys.dm_os_sys_info
- This query returns a list of .ndf files, showing their physical file paths (OSFileName) and the percentage of space they are using for databases in a SQL Server instance. The results are ordered by the used space percentage in descending order, showing the most utilized files first.
Query - SELECT mf.physical_name as OSFileName, CONVERT(DECIMAL(10,2),((dovs.total_bytes - dovs.available_bytes)/dovs.total_bytes)*100) AS UsedSpacePercentage FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs WHERE mf.type_desc = 'ROWS' AND mf.name LIKE '%.ndf' ORDER BY UsedSpacePercentage DESC
How to handle enumerated mappings & Alerting:
Enumerated Map:
OpsRamp monitoring framework exclusively supports integer-type metric values. Therefore, users are required to return metric values as integers rather than strings or other data types. However, if users wish to return metric values as strings, an option for enum mappings is available. Below, you’ll find a few examples providing further clarification.
Examples:
To monitor the state of each database in MSSQL, it's important to note that each databases have direct possible states. In such cases, users must prepare a query to directly return the state value for each database, as illustrated below:
Query - Select NAME, STATE from sys.databases
When creating a metric, users are required to select the Datapoint Value Conversion option as Enumerated Map and provide string value mappings for each possible state (Integer value to string value), as demonstrated below:
Additionally, there is an option to enable Use formatted value in Alerts & Graphs. If users opt to enable this feature, the enumerated mapping values provided will affect Alerts & Graphs. Otherwise, these values will not be reflected anywhere. Please find below screenshots having enum mapping reflections in monitoring data.
If User provide string value mappings for each possible state (i.e. string-type values to integer-type values) as demonstrated below:
The above configuration will be reflected in monitoring data like below:
Alerting:
If the metric has an enumerated map enabled, users must follow the instructions below to select critical and warning alert conditions for alert creation.
As outlined above, the prepared query returns the metric value as a string type. In accordance with this, users need to add N-number of enum maps for each possible state to different integer values.
To enable critical or warning alerts, users must take into account the possible string-type values returned from the query.
Refer to the screenshot below for guidance on enabling critical alerts if the enumerated map (string type to integer type) is enabled.
Refer to the screenshot below for guidance on enabling critical alerts if the enumerated map (integer type to string type) is enabled.
How to Create a MSSQL Metric:
Login to OpsRamp platform and Click on ‘Setup’ from top menu and expand ‘Monitoring’ from the side bar menu and click on ‘Metrics’.
Click on +Add to create a new metric.
Configure the parameters on the Create Metric page.
Metric Scope: Select either Service Provider Metric or Partner or Client Metric. Based on your access level and role, you might see this menu slightly different. If you have chosen Partner or Client Metric you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Adapter Type: Select Application from this dropdown.
Application Type: Select MS SQL from this dropdown.
Name: Provide a meaningful metric name.
Tag Name: It will be filled automatically with the same metric name.
Display Name: Provide a meaningful metric display name.
Description: Provide an elaborate description about this metric.
SQL Query: Provide the prepared MS SQL query. There is no need to provide semicolon at the end of the query.
Data Point type: Choose a suitable type from this dropdown. It has the following 8 pre-defined types. For this example, will go with Gauge.
Counter Delta: It calculates delta on top of metric value.
Counter Delta = (Current poll value - Prev poll value)
Note: If the result is less than zero then it returns zero.
Counter Rate: It calculates rate on top of metric value.
Counter Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
Note: If the result is less than zero then it returns zero.
Derive Delta: No support in both agent and gateway
Derive Rate: No support in both agent and gateway
Gauge: It returns direct metric value, which is returning from the script.
Rate: It calculates rate on top of metric value.
Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
Note: If the result is less than zero then it returns negative value.
Delta: It calculates delta on top of metric value.
Delta = (Current poll value - Prev poll value)
Note: If the result is less than zero then it returns negative value.
None: Same as Gauge
Units: Choose a suitable unit from this dropdown. For status related metrics doesn’t required any units. So go ahead with None.
Unit Multiplication Factor: Value to multiply the metric by. As this is status monitor, it doesn’t required this factor value. So go ahead with default value 1.0.
Datapoint value conversion: Choose a suitable option from any of the below two.
Value = Choose this option (As shown in below) when no conversion required on the metric value. This is the default value for “Datapoint value conversion” dropdown.
Enumerated Map = Choose this option when enumeration based conversion is required.
For status-related metrics, it’s common for queries to return string-type values. However, our monitoring graphs only support integer values. Therefore, users need to provide mappings for all possible string-type values, as illustrated below:
Sometimes If the query returns direct integer values to the metric but still the user wants to enable enumerated mapping then provide an enumerated map in reverse order. In this case the mapped strings displayed in the metric graph info page and alerts.
Example:
1- Running,
0 - Stopped
Based on user requirements, select the “Alerts” and “Graph” checkboxes accordingly. When both checkboxes are selected, Graphs as well as Alert subject and details page will display integer values alongside enumeration mappings. Additionally, these mappings will appear in the “Get Latest Metric Value” page against the metric.
Metric Processing: Choose any of below suitable option based on requirement.
Graph - Select if graph only needed, but no alerting is needed
Notification - Select if alerting is only needed, but no graphing is needed
Graph and Notification - Select if both graphing and alerting is needed
None - Select if both graphing and alerting is not needed.
For Notification, Graph, and Notification, below options will populate dynamically:
How to create MSSQL Monitor:
Login to OpsRamp platform and Click on ‘Setup’ from top menu and expand ‘Monitoring’ from the side bar menu and click on ‘Monitors’.
Click on +Add to create a new monitor.
On the Create a Monitor page, configure the following parameters:
Monitor Scope: Select either ‘Service Provider Monitor’ or ‘Partner or Client Monitor’. Based on your access level and role, you might see this menu slightly different. If you have chosen to create the script at Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Adapter Type: Select “Application” from this dropdown
Application Type: Select MS SQL from this dropdown
Name: Provide a meaningful monitor name.
Description: Provide an elaborate description about this monitor.
Metrics: Click on Add and select all the metrics, which you created in the previous section. (NOTE: Make sure you select the right MS SQL metrics)
Configuration Parameters: By default, you will see below 4 configuration parameters:
collector.application.type – Proceed with the default values, i.e MSSQL
connection.timeout.ms – Proceed with the default value, i.e 15000 ms (If require, you can increase it but it should be well within the monitor frequency/poll time)
mssql.service.port - Proceed with the default value, i.e 1433 (If target MSSQL environment running on different port then change it to right port number)
mssql.service.transport - Select correct secure/insecure transport type.
mssql.dbname.list - Provide database instance name here.
If target MSSQL environment have multiple database instances running on same port - provide input like InstanceName1, InstanceName2, …etc.
If target MSSQL environment have multiple database instances and each one running on different ports - provide input like InstanceName1:Port1, InstanceName2:Port2,….etc.
mssql.authentication.type – Select SQL Server Authentication / Windows NTLM Authentication. (If target environment configured with default windows authentication then select Windows NTLM Authentication or If target environment configured with SQL authentication then select SQL Server Authentication).
SSL Monitoring Configuration Parameters:
If you are using a trusted certificate on the end device to establish the connection, then we must add below Configuration Parameters -
mssql.service.transport - set this to True
use.ssl.certificate - Select Yes / No, If target MSSQL environment configure the database to use ssl certificate then select Yes otherwise No.
mssql.certificate.hostname - It specifies the host name of the server. The value of this property must match the subject property of the certificate.
How to Create a MS SQL Monitoring Template
Login to OpsRamp platform and Click on ‘Setup’ from top menu and expand ‘Monitoring’ from the side bar menu and click on ‘Templates’.
Click on +Add to create a new template.
The following steps are to configure on ‘Monitor Template’ screen:
Select Template Scope: Select either ‘Service Provider Template’ or ‘Partner or Client Templates’. Based on your access level and role, you might see this menu slightly different. If you have chosen to create Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Collector Type: Choose Gateway (Note - DB Query based monitoring supports Gateway collector type only).
Monitor Type: For Gateway, select Monitors radio button.
Applicable For: Always select Device
Template Name: Provide a meaningful template name.
Description: Provide an elaborate description about this template.
Leave remaining fields as is and go bottom, Click on +Add against Monitors.
Select Frequency: Select monitoring frequency based on metric requirement
Monitor Definition: Choose monitor which you created in the previous section
When To Alert: Proceed with the default option. i.e Breach of a Threshold
Then Click on Add and then Save the template.
Assign Template
Login to OpsRamp platform and Choose Partner and Client
Click on ‘Resources’ under ‘Infrastructure’ from top menu
Look for target device ip/hostname and click on Monitors
Click on ‘Assign Templates’ and select ‘Collector Type’ and look for Template and Assign.
User need to provide parameters based on their MS SQL configuration. By default it will show default ones like below:Multiple instances with different ports:User must need to select right authentication type based on mssql environment. Here we have authentication type radio buttons (either SQL Server Authentication or Windows NTLM Authentication).
MSSQL Metric - Stored Procedure Based
These are a collection of SQL queries and additional programming logic (like loops, conditions, etc.) that are saved in the database. Stored procedures can accept parameters, return results, and execute complex operations in a controlled manner.
OpsRamp comes with a predefined set of MSSQL metrics. However, For users who want to use MSSQL Stored Procedure in the MSSQL metric, we offer UI-based support for creating MSSQL metrics with Stored Procedures.
To prepare MSSQL metric, Users must return max one or two fields from the stored procedure. Follow below use cases along with examples to understand how to prepare MSSQL metrics with stored procedures to monitor MSSQL server environment.
How to Prepare MSSQL metric:
Stored Procedure as metric query with select columns: selecting few columns against the procedure with zero parameters which contains multiple columns list in the query result
Example: To monitor size of the MSSQL Databases.
Query - exec sp_databases
Stored Procedure as metric query with multiple componentNames: selecting two columns from the result against the procedure with single parameter for query condition which contains multiple columns list in the query result
Example: To monitor state of the provided MSSQL Database.
Query - state,state_desc::EXEC dbo.sample @param1
Stored Procedure as metric query with no components: selecting few columns against the procedure with single parameter which contains single columns list in the query result
Example: To monitor state of the provided MSSQL Database.
Query - state::EXEC dbo.sample @dbname@
Note
Users must need to follow these usecases while preparing queries (with stored procedures) for DB query based monitoring.
If User prepare metric query for multiple components, Then the OpsRamp framework showcase component names like this format or <Port_InstanceName-ComponentName> (Port number added if target environment having multiple instances with different ports).
Metric query returns max two columns only. If it returns one column consider as metric value & If it returns two columns, first column as componentName and another column as metric value.
Metric value could be number only, If it returns string-type values then must use enumarated mappings otherwise graphs are not plotted.
How to calculate/format metric values with example queries:
To calculate metric values like utilization, memory usage conversions (like GB to MB), time conversions (like seconds to milliseconds)..etc. Please follow below examples for better understanding.
Examples:
To monitor Database backup status in minutes
Query - select database_name, DATEDIFF(MINUTE, MAX(backup_finish_date), GETDATE()) AS minutes_since_last_backup from msdb.dbo.backupset where type = ‘D’ group by database_name
To monitors the uptime(in Days) of sql server.
Query - select DATEDIFF(DAY, sqlserver_start_time, getdate()) as uptime from sys.dm_os_sys_info
To monitors the space utilization of the ndf file in MSSQL environment.
Query - SELECT mf.physical_name as OSFileName, CONVERT(DECIMAL(10,2),((dovs.total_bytes - dovs.available_bytes)/dovs.total_bytes)*100) AS UsedSpacePercentage FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs WHERE mf.type_desc = ‘ROWS’ AND mf.name LIKE ‘%.ndf’ ORDER BY UsedSpacePercentage DESC
How to handle enum mappings & Alerting:
Enumerated Map:
OpsRamp monitoring framework exclusively supports integer-type metric values. Therefore, users are required to return metric values as integers rather than strings or other data types. However, if users wish to return metric values as strings, an option for enum mappings is available. Below, you’ll find a few examples providing further clarification.
Examples:
To monitor the state of each database in MSSQL, it’s important to note that each databases have direct possible states. In such cases, users must prepare a query to directly return the state value for each database, as illustrated below:
Query - Select NAME, STATE from sys.databases
When creating a metric, users are required to select the Datapoint Value Conversion option as Enumerated Map and provide string value mappings for each possible state (Integer value to string value), as demonstrated below:
Additionally, there is an option to enable Use formatted value in Alerts & Graphs. If users opt to enable this feature, the enumerated mapping values provided will affect Alerts & Graphs. Otherwise, these values will not be reflected anywhere. Please find below screenshots having enum mapping reflections in monitoring data.
If User provide string value mappings for each possible state (i.e. string-type values to integer-type values) as demonstrated below:
The above configuration will be reflected in monitoring data like below:
Alerting:
If the metric has an enumerated map enabled, users must follow the instructions below to select critical and warning alert conditions for alert creation.
As outlined above, the prepared query returns the metric value as a string type. In accordance with this, users need to add N-number of enum maps for each possible state to different integer values.
To enable critical or warning alerts, users must take into account the possible string-type values returned from the query.
Refer to the screenshot below for guidance on enabling critical alerts if the enumerated map (string type to integer type) is enabled:
Refer to the screenshot below for guidance on enabling critical alerts if the enumerated map (integer type to string type) is enabled.
How to Create a MS SQL Metric
Login to OpsRamp platform and Click on Setup from top menu and expand Monitoring from the side bar menu and click on Metrics.
Click on +Add to create a new metric.
Configure the following parameters on the Create Metric page:
Metric Scope: Select either Service Provider Metric or Partner or Client Metric. Based on your access level and role, you might see this menu slightly different. If you have chosen Partner or Client Metric you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Adapter Type: Select Application from this dropdown.
Application Type: Select MS SQL from this dropdown.
Name: Provide a meaningful metric name.
Tag Name: It will be filled automatically with the same metric name.
Display Name: Provide a meaningful metric display name.
Description: Provide an elaborate description about this metric.
SQL Query: Provide the prepared MS SQL query. There is no need to provide semicolon at the end of the query.
Data Point type: Choose a suitable type from this dropdown. It has the following 8 pre-defined types. For this example, will go with Gauge.
Counter Delta: It calculates delta on top of metric value.
Counter Delta = (Current poll value - Prev poll value)
Note: If the result is less than zero then it returns zero.
Counter Rate: It calculates rate on top of metric value.
Counter Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
Note: If the result is less than zero then it returns zero.
Derive Delta: No support in both agent and gateway
Derive Rate: No support in both agent and gateway
Gauge: It returns direct metric value, which is returning from the script.
Rate: It calculates rate on top of metric value.
Rate = (Current poll value - Prev poll value) / (Current poll time - Prev poll time)
Note: If the result is less than zero then it returns negative value.
Delta: It calculates delta on top of metric value.
Delta = (Current poll value - Prev poll value)
Note: If the result is less than zero then it returns negative value.
None: Same as Gauge
Units: Choose a suitable unit from this dropdown. For status related metrics doesn’t required any units. So go ahead with None.
Unit Multiplication Factor: Value to multiply the metric by. As this is status monitor, it doesn’t required this factor value. So go ahead with default value 1.0.
Datapoint value conversion: Choose a suitable option from any of the below two.
Value = Choose this option (As shown in below) when no conversion required on the metric value. This is the default value for “Datapoint value conversion” dropdown.
Enumerated Map = Choose this option when enumeration based conversion is required.
For status-related metrics, it’s common for queries to return string-type values. However, our monitoring graphs only support integer values. Therefore, users need to provide mappings for all possible string-type values, as illustrated below:
Sometimes If the query returns direct integer values to the metric but still the user wants to enable enumerated mapping then provide an enumerated map in reverse order. In this case the mapped strings displayed in the metric graph info page and alerts.
Example:
1- Running,
0 - Stopped
Based on user requirements, select the “Alerts” and “Graph” checkboxes accordingly. When both checkboxes are selected, Graphs as well as Alert subject and details page will display integer values alongside enumeration mappings. Additionally, these mappings will appear in the “Get Latest Metric Value” page against the metric.
Metric Processing: Choose any of below suitable option based on requirement.
Graph - Select if graph only needed, but no alerting is needed
Notification - Select if alerting is only needed, but no graphing is needed
Graph and Notification - Select if both graphing and alerting is needed
None - Select if both graphing and alerting is not needed.
For Notification, Graph, and Notification, below options will populate dynamically:
How to Create a MS SQL Monitor
Login to OpsRamp platform and Click on ‘Setup’ from top menu and expand ‘Monitoring’ from the side bar menu and click on ‘Monitors’.
Click on +Add to create a new monitor.
The following steps are to configure on ‘Create A Monitor’ screen
Monitor Scope: Select either ‘Service Provider Monitor’ or ‘Partner or Client Monitor’. Based on your access level and role, you might see this menu slightly different. If you have chosen to create the script at Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Adapter Type: Select “Application” from this dropdown
Application Type Select “MS SQL” from this dropdown
Name: Provide a meaningful monitor name.
Description: Provide an elaborate description about this monitor.
Metrics: Click on Add and select metric, which you created in the previous section. (NOTE: Make sure you select the right MSSQL metric which is having Stored Procedure only)
Configuration Parameters:
collector.application.type – Proceed with the default values, i.e MSSQL
connection.timeout.ms – Proceed with the default value, i.e 15000 ms (If require, you can increase it but it should be well within the monitor frequency/poll time)
mssql.service.port - Proceed with the default value, i.e 1433 (If target MSSQL environment running on different port then change it to right port number)
mssql.service.transport - Select correct secure/insecure transport type.
mssql.dbname.list - Provide database instance name here.
If target MSSQL environment have multiple database instances running on same port - provide input like InstanceName1, InstanceName2, …etc.
If target MSSQL environment have multiple database instances and each one running on different ports - provide input like InstanceName1:Port1, InstanceName2:Port2,….etc.
mssql.authentication.type – Select SQL Server Authentication / Windows NTLM Authentication. (If target environment configured with default windows authentication then select Windows NTLM Authentication or If target environment configured with SQL authentication then select SQL Server Authentication)
mssql.storedprocedure - Proceed with the default value, i.e TRUE
Stored Procedure Configuration Parameters:
Stored Procedure as metric query with select columns: selecting few columns against the procedure with zero parameters which contains multiple columns list in the query result.
Example: To monitor size of the MSSQL Databases.
Query - exec sp_databases
Here we are supporting the columns range from 1 to 10 in the output of the stored procedure, User must mention stored procedure params and considerable columns from the query result like below
Note
User need to match the param count to actual fields count.
mssql.storedprocedure.param1 - User need to provide the Actual FieldName of the Param1.
mssql.storedprocedure.param2 - User need to provide the Actual FieldName of the Param2.
Stored Procedure as metric query with multiple component Names: selecting two columns from the result against the procedure with single parameter for query condition which contains multiple columns list in the query result.
Example: To monitor state of the provided MSSQL Database.
Query - state,state_desc::EXEC dbo.sample @param1
mssql.storedprocedure.param1 - To provide the Input database name of the Param1, the provided input value will replaced in the query and formatted like state,state_desc::EXEC dbo.sample test.
The user can configure the database name while applying the template or anytime.
Stored Procedure as metric query with no components: selecting few columns against the procedure with single parameter which contains multiple columns list in the query result.
Example: To monitor state of the provided MSSQL Database.
Query - state::EXEC dbo.sample @dbname@
mssql.storedprocedure.param1 - To provide the Input database name of the Param1. The provided input value will replaced in the query and formatted like State::EXEC dbo.sample test
If we are using a trusted certificate on the end device to establish the connection, then we need to add below Configuration Parameters:
- mssql.service.transport - set this to True
- use.ssl.certificate - Select Yes / No, If target MSSQL environment configure the database to use ssl certificate then select Yes otherwise No.
- mssql.certificate.hostname - It specifies the host name of the server. The value of this property must match the subject property of the certificate.
How to create MSSQL Database Monitoring Template
Login to OpsRamp platform and Click on Setup from top menu and expand Monitoring from the side bar menu and click on Templates.
Click on +Add to create a new template.
The following steps are to configure Monitor Template screen
Select Template Scope: Select either ‘Service Provider Template’ or ‘Partner or Client Templates’. Based on your access level and role, you might see this menu slightly different. If you have chosen to create Partner or Client level you will be prompted to choose a Partner/Client from contextual drop down which is dynamically populated.
Collector Type: Choose Gateway (Note - DB Query based monitoring supports Gateway collector type only).
Monitor Type: For Gateway, select Monitors radio button.
Applicable For: Always select Device
Template Name: Provide a meaningful template name.
Description: Provide an elaborate description about this template.
Leave remaining fields as is and go bottom, Click on +Add against Monitors.
Select Frequency: Select monitoring frequency based on metric requirement
Monitor Definition: Choose monitor which you created in the previous section
When To Alert: Proceed with the default option. i.e Breach of a Threshold
Then Click on Add and then Save the template.
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:
Configuration Parameter
Description
Connection Time-out
Maximum time to connect to the database. Default: 15000 milliseconds. The driver waits for the specified time before timing out due to connection failure.
Service Transport Type
Configure the database at a secure end-point. Default: In-secure. A secure connection encrypts the data sent between the client and server.
Service Port
Database port number. Default: 1433. The connection uses the specified port if you specify the port and the database name.
MSSQL DB Instance Name
The name of the database to connect to. The default name is root.
If you have multiple instances with different ports, use the following syntax:
Instance1:Port1,Instance2:Port2
Note: Do not consider the service port configuration value here.
If you have a single instance, use the following syntax:
“Instance1”
Note: Consider the port from the service port configuration for this case.
If you have multiple databases with the same port, use the following syntax:
“Database1, Database2, Database3”
Note: Consider the port from the service port configuration for this case.
Authentication Type
Authentication method used to authenticate the databases. Default: SQL Server Authentication. If the credentials are Windows-based, use Windows NTLM Authentication.
Application Type
Application type that identifies the adapter, such as MSSQL. Do not change the default application type value.
Stored Procedures
Gives the latest, server status using the monitor configured 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.
Points to be considered
Gateway collector type only supports MSSQL query based monitoring.
To assign the right MSSQL database credentials (which are having proper priviliges to execute metric queries) on the target MSSQL server.
User must provide proper priviliges to execute metric queries
MSSQL DB Port must be able to telnet from the Gateway.
If the user has instance level authentication, Need to assign all credential sets on the target MSSQL server. OpsRamp Gateway will take care to connect the different instances of the MSSQL server.
Need to provide an correct authentication type SQL Server Authentication or Windows NTLM Authentication to connect target MSSQL environment.
To provide right transport type like secure or insecure
While preparing sql query, User should not consider frequently changed fields as component names (like mssql databaseName, userName, ..etc) or any combinations (like databaseName_sessionId, tableName+userId, …etc).
If any database connectivity issues occur from the Gateway, the Framework generates alerts such as ‘mssql.auth.error.’ However, it does not generate alerts for query syntax errors, empty data, or access denied issues.
Troubleshooting
Usecase 1 - Failed to get latest metric values/Gateway is offline
When user applied an MSSQL monitoring templates on devices and encounter the message “Failed to get latest metric values/Gateway is offline” while fetching latest metric data, Follow the steps below:
Navigate to the Overview section of the Device( Infrastructure » Resources » Search using IP or Device Name ) and on that device, ensure that the Gateway is online.
Identify whether a template is global or customer written.
Please review any alerts associated with the template on the Overview page of the Device (or) navigate to Command Center » Alerts page and filter using the specific server name or Ip address. We have static alert format with the metricName is mssql.auth.error and the component name like .
Check key points related to MSSQL monitoring.
Access Gateway:
We can access the gateway using the following two methods
- Login to the gateway using ssh
ssh username@gateway-ip
- Login to the gateway using browser console
Click on the browser console, then it will redirect to the following page, Give the necessary fields and launch the consoleCheck database server IpAddress & port connectivity
Login to the gateway
Refer Step-5 Access gateway
Type ping and press enter, then telnet like below:
ping telnet
Connect to GCLI terminal in gateway
Classic Gateway:
Login to the gateway
Refer Step-5: Access gateway
Type gcli and press enter, then gcli terminal will open
Nextgen Gateway:
Login to the gateway
Refer Step-5 Access gateway
Execute command kubectl exec -it nextgen-gw-0 -c vprobe -n – bash
Note: Here we need to replace the if applicable
otherwise execute:
kubectl exec -it nextgen-gw-0 -c vprobe – bash
Type gcli and press enter, then gcli terminal will open
Access MSSQL logs.
By default the OpsRamp gateway capturing few error logs, If user want to enable all available logs which are related to MSSQL then follow below steps.
Access gcli mode by running: gcli
Refer Step-7: Connect to GCLI terminal in gateway
Use this command to enable debug logs: flag add mssql.log on 30
Exit gcli prompt and run this command to observe logs: sudo tail -100f /var/log/app/vprobe.log
In Nextgen Gateway, If User want to copy files from the vprobe container to the gateway cli, execute the following command after exiting the container
kubectl cp /nextgen-gw-0:<source_path> <dest_path> -c vprobe
Example: kubectl cp nextgen-gw-0:/var/log/app/vprobe.log /home/gateway-admin/vprobe.log -c vprobe
Download files from the gateway cli.
Execute MSSQL query manually from the GCLI on the target machine.
Access gcli mode by running: gcli
Refer Step-7: Connect to GCLI terminal in gateway
Execute sql query via gcli prompt with SQL Authentication:
db mssql 15000 10000 insecure <Yes/No> <“Query”>
Example:
Execute sql query via gcli prompt with Windows Authentication:
syntax of the command:
db mssqldb :windows 15000 10000 insecure <Yes/No> <“Query”>
Example:Check If the query output returns more than 2 columns then refer How to Prepare MSSQL metric query & If manual query execution returns any MSSQL exceptions then refer MSSQL Exceptions.
If you are unable to determine the root cause of the issue from the logs or query output, or if different exceptions occur when executing an MSSQL query, please raise a case and attach the relevant logs and manual query output.
Usecase 2 - Graph data is not populating for specific or all metrics
Validate whether the metric is retrieving data from end device by checking latest snapshot data., if not refer to Usecase1 - Failed to get latest metric values (or) Gateway is offline.
Check if the graph is enabled or not at metric level. If its enabled, check whether the data got from latest snapshot data is a string. If it’s a string, then check if Enum Mapping is defined for that string at metric leve.
If Enum mapping is not defined for that particular string and it’s a global template, then raise a case while attaching screenshots of latest snapshot data, Enum Mapping defined at Metric.
If Enum mapping is not defined for that particular string and it’s a customer written template, then suggest customer to edit the metric and add this new state in State Descriptions field.
Usecase 3 - User is observing gaps in metric graphs
This issue might be due to the following reasons:
Gateway going offline at that time
The device is not reachable and port connectivity issues.
There may be no data available for the metric on the device at that time.
Check the debug level logs to cross verify if Gateway was offline at that time.
If you do not find any logs related to those, then raise a case while attaching logs, to analyse query output at those specific times when graph is not populating.
Usecase 4 - Alerts not getting generated on resource for particular metric.
Check latest snapshot data to see if we are retrieving any data from device for that metric and also verify the thresholds defined for the metric.
If the latest snapshot data is also not coming for that metric, then execute the command or script manually on device, to see if any data exists for that metric.
Usecase 5 - Alerts generated do not align with the defined alert thresholds
Alert thresholds follow a hierarchical order, starting from the component level and moving up to the template level. Each level can override the thresholds set at the previous level. Component-level settings override those set at the device level, and device-level settings take precedence over template-level settings.
This hierarchy ensures that monitoring configurations can be finely tuned at various levels of the system, allowing for granular control over alerting parameters. This approach enables more precise and effective management of alerts tailored to the specific needs of each level.
Graph data is not populating for stored procedure related metrics
If user have stored procedure related metrics then need to enable stored procedure option in MSSQL monitoring configuration params.
FAQs
Why latest snapshot data is not available
Ans: Refer - Usecase1 - Failed to get latest metric values / Gateway is offline
Why graphs are not reflecting
Ans: Check Points to be considered & Usecase2 - Graph data is not populating for specific or all metrics
What are the possible mssql exceptions?
Below are few possible exceptions while connecting MSSQL server.
Login failed for user ‘username’
Cannot open database “database_name” requested by the login.
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
The connection is already open.
The connection is closed.
These exceptions may occure while manually executed MSSQL query via Gateway GCLI prompt
To resolve these type of issues, please request the user to try the following solutions:
Check whether the MSSQL database instance is up and running.
Ensure the SQL Server is configured to accept remote connections, and the necessary services are running.
Verify that firewall rules allow traffic on the SQL Server port (default is 1433).
Ensure that the client machine(Gateway) has the necessary privileges and is authorized to access the specified database instance.
Check SQL Server logs and event logs for more detailed error information.
Refer to Points to be considered.
Where to check OpsRamp supported MSSQL Metrics
Customers should check the Recommended Templates page within the public documentation.
If the required monitoring support is not found on these pages and it’s a generic request applicable beyond the customer’s specific needs, then only submit a case to support team for Request for Enhancement (RFE).
However, if the monitoring requirement is specific to the customer’s needs, then customers need to develop their own script by following this documentation.
How to identify whether a template is global or customer written?
The details available in the links below only pertain to Global Templates and Metrics.
Latest metric snapshot data is not getting from the template.
Refer to Usecase1 - Failed to get latest metric values / Gateway is offline.
What steps should an user follow if they want to create their own query based custom metric for database monitoring?
Ans: Users can refer to the Create Custom Metrics guide available in the public documentation.
This guide provides comprehensive instructions on how to develop query based custom metrics.
Can customer create query based metrics using Agent collector type?
Ans: No, Query-based metrics only work with collector type Gateway, not with Agent.
How to plot Graph for String values like health or status metrics?
Ans: To plot graph for state or status-related metrics returned as strings, utilize the Enum Mapping option.
Why metrics and monitor changes are not getting reflected in template (latest metric data or graphs or alerts)?
Ans: Refer - How to update & add metrics to Monitor & Monitor to template
User wants to exclude monitoring for some components of metric. How can they achieve this?
Ans: If User wants to exclude monitoring some components of metric, then suggest them to use Component Filters option of RSE.
By using these component filters, you can monitor specific components or ignore unwanted components from monitoring.
For MSSQL monitoring, should SSH credentials assigned on the target device or on the Gateway device?
Ans: In MSSQL monitoring, No need to assign the SSH login credentials on the target device, not on the Gateway device.
Authentication error alerts (Dtabase Related) observed on device Overview page.
Ans: If it’s MSSQL query based template, make sure that MSSQL Database Type credentials are assigned in device’s credentials tab.
Observed any MSSQL Query execution errors on device like permissions error
Ans: Assigned Database Type credentials must have sufficient permissions to execute the query on the device.
User wants to know the queries used for template
Ans: Go to the Setup page→ Navigate to Monitoring→ Click on Metrics→Search the exact metric based on scope→Open the metric definition→ Monitoring query available on the metric definition
Can a user assign version 1 and version 2 , 3 , 4 etc of a template on the same device?
Ans: No, User cannot assign version 1 and version 2, 3, 4, etc., of a template to the same device simultaneously. In nearly all cases, the metrics present in version 1 (v1) will also be included in version 2 (v2) or later versions. The later versions typically include additional metrics, enhancements to existing metrics or methodologies, and bug fixes. Therefore, we recommend always using the latest version of the template to ensure that users benefit from these improvements and new features.
Supported Templates
Collector Type
Template Name
Agent
Microsoft SQL 2000 - Performance Counters DotNet v4.
Microsoft SQL 2005 - Performance Counters DotNet v4
Microsoft SQL 2008 DotNet v4 - Performance Counters
Microsoft SQL 2012 Advanced Performance Counters DotNet v4
Microsoft SQL 2012 DotNet v4 - Performance Counters
Microsoft SQL 2012 WMI DotNet v4 - Performance Counters
Microsoft SQL 2014 DotNet v4 - Performance Counters
Microsoft SQL 2016 - Performance Counters DotNet v4
Microsoft SQL Server 2017 DotNet v4 - Performance Counters
Microsoft SQL 2019 DotNet v4
Microsoft SQL Performance Counters DotNet v4
MSSQL - Data and Log Space Size - Config Parameters DotNet v4
