MSSQL database is a Relational Database Management System (RDBMS) that is designed to store, analyze and retrieve data efficiently.

In Linux/Unix, a Java-based agent is used. In Windows, you can use a .NET-based agent (preferred) or a Java-based agent. The agent runs the queries periodically to collect metrics/detailed DB information. Two types of monitoring are supported for MSSQL:

Time-Series Metric Monitoring (TSMM) - TSMM collects metrics that can be used to visualize the performance of your MSSQL DB on our dashboard. An extensive coverage includes multiple data points from the following categories:

MSSQL Service Stats
MSSQL Server Stats
MSSQL Temp Stats
MSSQL Perf Stats
MSSQL MemUtilization
MSSQL Connection Stats
MSSQL Space Stats
MSSQL IO Stats

Detailed DB Monitoring (DDBM) - DDBM gives you deeper insight into the performance of your MSSQL DB by capturing currently running queries, sessions, locks, DB I/O Stats, etc. The metrics that are to be captured can be checked from this configuration page and the detailed DB Monitoring module can be accessed by clicking on Configurations => Actions => DB Monitoring. Following metric groups are captured as part of detailed DB monitoring:

MSSQL Wait Stats
MSSQL Blocking Stats
MSSQL DB Service Stats
MSSQL Temp DB Stats
MSSQL Locks Stats
MSSQL Session Stats
MSSQL IO By File Stats
MSSQL Job Stats
MSSQL Job History Stats
MSSQL Session Wait Stats
MSSQL DB Connection Stats
MSSQL Report Stats
MSSQL Dead Lock Stats
MSSQL Actual Execution Plan

Platform(s) Supported:

Ubuntu (Version 16 ,20)
RHEL (Version: 5,6,7)
Windows Server Version (8,10,12,16,17,19)

MSSQL Versions Supported:

MSSQL Database Versions 12 and above

Pre-Requisites:

Ensure that the communication from the machine on which the agent is installed to DB Server is established. For establishing the connection, follow the below steps:
- Go to SQL Server Manager
- Go to SQL Server Services and configure SQL server Protocols and enable the TCP/IP, Shared Memory fields.
SQL Server Authentication: If the SQL Server Agent is installed on the Linux platform, the SQL Server Authentication should be enabled for provided SQL user credentials. Click on the Authentication mode drop-down and select the type of server authentication.
Windows Authentication: If the SQL Server Agent is installed on the Windows platform, then SQL Server Authentication or Window Authentication will work for monitoring.
Permissions required to see MSSQL Stats and Execution Plans: User accounts used for mapping should have the following permissions to see MSSQL Stats and Execution plans:
- Map the user to the “master” DB and default schema to “sys”.
- For the current database server, provide the explicit grant for “Connect SQL”.
- Users should be granted “VIEW” permission for “server state” (view any db, view any definition & view server state).
For monitoring your Oracle database, a user with above mentioned permissions is required. You can use any existing user or create a new user 'cavisson' using the following commands:

USE [master]

    GO

    CREATE USER [cavisson] FOR LOGIN [cavisson]

    GO

    USE [master]

    GO

    ALTER USER [cavisson] WITH DEFAULT_SCHEMA=[sys]

    GO

    use [master]

    GO

    GRANT VIEW SERVER STATE TO [cavisson]

    GO.

Note: Here the username is cavisson. In the above query, View is the permission, Master is the database and Sys is the schema.

Configurations Steps:

To successfully configure MSSQL database monitoring, you are required to fill the following fields:

Host Name/IP: It provides the hostname/IP of the machine on which the MSSQL database resides.
Port: The port on which the connection will be made along with the hostname/IP. The default port value is 1433.
Username/Password: It provides the username and password where MSSQL DB is setup.
Authentication Mode: The authentication mode provided is Windows/SQL. It is an optional field.

Notes:

You have the option to select the tier/server on which the monitors have to be applied via the following section on the configuration page:
It will show detailed information in a tabular form about the configurations done manually or present by default in the Data Source being monitored.

Configuration of Time Series Metric Monitors:

All the metric groups have the following fields in common, which are optional:
1. Frequency: It is the time interval in which the monitor is to be runed. Eg: In every 10 sec.
2. Other Arguments: It is for enabling debug mode or passing any extra argument in JSON.

Deploy Monitors

You also have the option to deploy monitors using Cavisson Agent. The following fields needs to be filled to deploy:

Tier: In this, the monitors which can be deployed can be viewed. Select the required monitors from the drop-down list.
Server: Servers can be selected as per the user's requirement, i.e. with monitors a user requires all servers, any server or a specified server. Users can select any one of the following:
- All: It is used to select all the severs.
- Any: It is used to select any servers from the given list.
- Specified: It is used to select specific servers from the given list.

Validation

To validate the successful configuration of your MSSQL DB monitors, go to the monitor’s homepage and you will be able to see a corresponding section for MSSQL. Click on the monitor section to view a list of all the configured monitors, their corresponding metric category, and their status (Running, Disabled, Failed). To validate detailed DB monitoring, check for the configured database under DB Monitoring icon on the dashboard.