Supported Collector Type
Gateway
Supported Target Versions
MS SQL Server 2008 or higher
JDBC: 8.2.2.jre11
PrivilegeDescriptionExample
serveradminMinimum privilege to monitor MSSQL DBsys.dm_exec_query_stats

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
MSSQL Dashboard

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:

Configuration ParameterDescription
Connection Time-outMaximum 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 TypeConfigure the database at a secure end-point.
Default: In-secure.
A secure connection encrypts the data sent between the client and server.
Service PortDatabase port number.
Default: 1433.
The connection uses the specified port if you specify the port and the database name.
MSSQL DB Instance NameThe 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 TypeAuthentication method used to authenticate the databases.
Default: SQL Server Authentication.
If the credentials are Windows-based, use Windows NTLM Authentication.
Application TypeApplication type that identifies the adapter, such as MSSQL.
Do not change the default application type value.
Stored ProceduresGives 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.

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

Collector TypeTemplate Name
AgentMicrosoft 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
MSSQL Thread Count Free DotNet v4
GatewayAdvanced Cloud Database Template for MSSQL
Advanced Cloud MSSQL Database Access Methods Template
Advanced Cloud MSSQL Database Buffer Template
Advanced Cloud MSSQL Database Databases Template
Advanced Cloud MSSQL Database General Statistics Template
Advanced Cloud MSSQL Database Latches Template
Advanced Cloud MSSQL Database Locks Template
Advanced Cloud MSSQL Database Memory Manager Template
Advanced Cloud MSSQL Database Miscellaneous Template
Advanced Cloud MSSQL Database Resource Pool Stats Template
Advanced Cloud MSSQL Database SQL Statistics Template
Advanced Cloud MSSQL Database Transactions Template
G2 MSSQL Database Access Methods Template
G2 MSSQL Database Access Methods Template v2.0
G2 MSSQL Database Buffer Template
G2 MSSQL Database Buffer Template - v2.0
G2 MSSQL Database Databases Template
G2 MSSQL Database Databases Template - v2.0
G2 MSSQL Database General Statistics Template
G2 MSSQL Database General Statistics Template - v2.0
G2 MSSQL Database Latches Template
G2 MSSQL Database Latches Template - v2.0
G2 MSSQL Database Locks Template
G2 MSSQL Database Locks Template - v2.0
G2 MSSQL Database Memory Manager Template
G2 MSSQL Database Memory Manager Template - v2.0
G2 MSSQL Database Miscellaneous Template
G2 MSSQL Database Miscellaneous Template - v2.0
G2 MSSQL Database Resource pool Stats Template
G2 MSSQL Database Resource pool Stats Template - v2.0
G2 MSSQL Database SQL Statistics Template
G2 MSSQL Database SQL Statistics Template - v2.0
G2 MSSQL Database Transactions Time Template
G2 MSSQL Database Transactions Time Template - v2.0
G2 MSSQL Database Backup Template
G2 MSSQL Database Agent Jobs Template
G2 MSSQL Database CPU Template
G2 MSSQL Database AlwaysOn Template

Supported metrics

MetricMetric Display NameUnit
mssql.last.elapsed.time

The elapsed time in microseconds(but only accurate to milliseconds) for the recently executed plan.
Last elapsed timemicroseconds (micro sec)
mssql.sql.recompilations

The number of statement recompiles triggered per second.
Sql recompilationsper second(psec)
mssql.avg.lock.wait.time

The average amount of wait time (milliseconds) for each lock request.
Average lock wait timemilliseconds(ms)
mssql.batch.requests

The number of SQL batch requests received by the server.
Batch requestsper second(psec)
mssql.blocked.process

The number of currently blocked processes.
Blocked processes-
mssql.buffer.cache.hit.ratio

The percentage of pages found in the buffer pool without incurring a read from disk.
Buffer cache hit ratioPercentage(%)
mssql.buffer.lazy.writes

The number of buffers written by buffer manager lazy writer.
Buffer lazy writeswrites per sec(wps)
mssql.checkpoint.pages

The number of pages flushed to the disk per second by a checkpoint or other operations that require all dirty pages to be flushed.
Checkpoint pagesper second(psec)
mssql.connection.memory

The total amount of dynamic memory the server used for maintaining connections.
Connection Memorykilobytes(KB)
mssql.current.db.disk.used

The size of the current database in megabytes.
Current database disk space usedmegabytes(MB)
mssql.database.cache.memory

The memory currently used by the server for database cache.
Database cache memorykilobytesilo(KB)
mssql.database.page.reads

The number of issued physical database page reads.
Page readsper second(psec)
mssql.database.page.writes

The number of issued physical database page writes.
Database Page writeswrites per sec(wps)
mssql.db.data.file.size

The cumulative size of all the data files in the database.
Data File Sizekilobytes(KB)
mssql.db.transactions

The number of transactions started for the database.
Db transactionsper second(psec)
mssql.free.memory

The amount of memory currently not used by the server.
Free Memorykilobytes(KB)
mssql.full.scans

The number of unrestricted full scans.
Full scansper second(psec)
mssql.latch.waits

The number of latch requests that are not granted and had to wait before getting a grant.
Latch waitsper second(psec)
mssql.lock.deadlocks

The number of lock requests that resulted in a deadlock.
Deadlocksper second(psec)
mssql.lock.memory

The total amount of dynamic memory the server uses for locks.
Lock Memorykilobytes(KB)
mssql.lock.timeouts

The number of lock requests that timed out including requests for no wait locks.
Lock timeoutsper second(psec)
mssql.lock.waits

The number of lock requests not executed and caused the caller to wait before granting the lock.
Lock waitsper second(psec)
mssql.log.flushes

The number of log flushes.
Log Flushesper second(psec)
mssql.login.count

Monitors the logins in the database.
Login Count-
mssql.longest.transaction time

The time (in seconds) since the start of the active transactions that are longer than any current transaction.
Longest transaction timeseconds (s)
mssql.memory.grants.pending

The current number of processes waiting for a workspace memory grant.
Memory Grants Pending-
mssql.memory.grantspending

The total number of processes waiting to acquire a workspace memory grant.
MemoryGrantsPending-
mssql.optimizer.memory

The total amount of dynamic memory used by the server for query optimization.
Optimizer Memorykilobytes(KB)
mssql.page.life.expectancy

The number of seconds a page stays in the buffer pool without references.
MSSQL PageLifeExpectancyseconds (s)
mssql.page.splits

The number of page splits per second that occur as a result of the overflowing index pages.
Page splitsper second(psec)
mssql.process.count

Monitors the current processes running in the database.
Process Count-
mssql.resource.pool.disk.read.io

The number of read operations from the disk in the last second.
Resource pool disk read IOread operations per sec(rops)
mssql.resource.pool.disk.write.io

The number of bytes written to the disk in the last second.
Resource pool disk write IOwrites per sec(wps)
mssql.resource.pool.used.memory

The amount of memory used, in kilobytes (KB), for the resource pool.
Resource pool used memorykilobytes (KB)
mssql.sql.cache.memory

Total amount of dynamic memory the used by the server for the dynamic SQL cache.
SQL Cache Memorykilobytes(KB)
mssql.sql.compilations

The number of SQL compilations.
Sql compilationsper second(psec)
mssql.total.server.memory

The total amount of dynamic memory currently consumed by the server.
Total server memorykilobytes(KB)
mssql.user.connections

The number of users connected to the system.
User connections-
mssql_db_backup_daysSinceLastFullBackup

Last successful full backup status for DB and log file backup.
MSSQL DB Backup Days Since Last Full Backupdays
mssql_db_backup_daysSinceLastLogBackup

Last successful log backup status for DB and log file backup.
MSSQL DB Backup Days Since Last Log Backupdays
mssql_db_backup_daysSinceLastDifferentialBackup

Last successful differential backup status for DB and log file backup.
MSSQL DB Backup Days Since Last Differential Backupdays
mssql_db_agentJobsStatus

To check agent jobs.
MSSQL DB Agent Jobs Status-
mssql_db_alwaysOn_DBAvailability_SynchronizationHealth

To check database availability - SynchronizationHealth.
MSSQL AlwaysOn DataBase Availability Synchronization Health-
mssql_db_alwaysOn_AG_SynchronizationHealth

To check group availability - SynchronizationHealth.
MSSQL AlwaysOn Availability Group Synchronization Health-
mssql_db_alwaysOn_listener_state

To check listener state.
MSSQL AlwaysOn Listener State-
mssql_cpuBusy

To check Cpu Busy state.
MSSQL CPU Busymicroseconds
mssql_cpuIdle

To check CpuIdle.
MSSQL CPU IDLEmicroseconds
mssql_cpuIoBusy

To check CpuIoBusy.
MSSQL CPU IoBusymicroseconds