# Microsoft SQL Server Monitoring

Microsoft SQL (MSSQL) server is a relational database management system that efficiently stores, retrieves, and manages data. It is widely used across industries for its scalability and integration capabilities. It often serves as the backend database for enterprise applications such as SAP, where it plays a crucial role in handling vast amounts of business data and supporting mission-critical processes.

Monitoring the MSSQL server helps analyze the database server's usage, performance, and metrics to optimize application performance. Getting historical data is critical to measuring and analyzing performance trends to take quick actions and perform database tuning to improve operations.

### Prerequisites

Before configuring a Microsoft SQL server for monitoring in IT-Conductor, follow the instructions below.

#### Account Requirements

1. Create a new MSSQL monitoring account.
2. Grant the user the "sysadmin" role.

If you are unable to grant "sysadmin" permissions to the account, please grant the following permissions:

* Member of the "db\_datareader" role on the msdb system database
* Member of "SQLAgentReader" role on the msdb system database
* "VIEW ANY DEFINITION" server permission
* "ALTER TRACE" server permission (if you want to enable trace data)
* "VIEW SERVER STATE" and "VIEW DATABASE STATE" database permissions on all databases
* Member of the "db\_owner" role on the "tempdb" database
* "EXECUTE" on xp\_readerrorlog

{% hint style="info" %}
**Note:** 

* Perform these steps on the MSSQL server.
* Access to additional commands may be required if a custom configuration is involved.
  {% endhint %}

### Configure Microsoft SQL Server Monitoring in IT-Conductor <a href="#configure-mssql-server-monitoring-in-itconductor" id="configure-mssql-server-monitoring-in-itconductor"></a>

To configure Microsoft SQL server monitoring in IT-Conductor, follow the instructions below.

#### Add New Microsoft SQL Server

1. Visit [service.itconductor.com](https://service.itconductor.com/) and enter your login credentials.&#x20;
2. Navigate to **Dashboards → Administrator** to access the **Administrator's Dashboard**.
3. Locate the **Microsoft SQL Servers** actions panel and click the title to access the complete list.

<figure><img src="/files/f0goJcjngm5xrhJgWLvn" alt=""><figcaption><p>Figure 1: Microsoft SQL Servers Actions Panel in Administrator's Dashboard</p></figcaption></figure>

4. Click the **New SQL Server** button to start adding a new server for monitoring.

<figure><img src="/files/JOR2D8gk2BszOOqMac1Y" alt=""><figcaption><p>Figure 2: SQL Servers Actions Panel Page</p></figcaption></figure>

5. Fill out all the necessary information in the **New SQL Server** wizard. Once completed, click <img src="/files/oqn8RL2g7n684ORCwtgx" alt="" data-size="line"> to add the server.

<figure><img src="/files/9sxWKbofyfJvvdepwV3t" alt=""><figcaption><p>Figure 3a: New SQL Server Wizard (New SQL Server Screen)</p></figcaption></figure>

* **Description** - refers to any relevant information about the server being added.
* **Organization** - refers to an administrative structure that defines objects with a common goal or purpose. If you previously created an organization, please select it.
* **Role -** refers to the environment where the server will be used.
* **Site** - refers to a logical object that describes a particular area or location, depending on the context in which it is used.
* **Gateway** - allows communication between the customer's site network and the IT-Conductor cloud platform. Select the previously configured gateway from the dropdown menu. See [Gateway Setup](/user-guide/setup/gateway-setup.md) for more details.
* **Host** - refers to the computer or server where the MSSQL Server instance is running.
* **Port** - refers to the port number through which the server communicates with IT-Conductor. This is typically set to 1433.
* **Encrypt** - enables encryption of data exchanged between the server and IT-Conductor.
* **SSL Protocol** - specifies the version of the SSL protocol used to secure the connection between the server and IT-Conductor.
* **Trust Certificate** - enables establishing a trust or secure connection between the server and IT-Conductor.

6. Provide the account information in the **New Account** screen and click <img src="/files/oqn8RL2g7n684ORCwtgx" alt="" data-size="line"> to complete the configuration.

<figure><img src="/files/CImAhOQXgx1xfzY7zFlF" alt=""><figcaption><p>Figure 3b: New SQL Server Wizard (New Account Screen)</p></figcaption></figure>

* **Description** - refers to any relevant information about the user account being added.
* **Application** - refers to the name given to the server being added.
* **Person** - refers to the name of the user who is adding the system.
* **User Name** - refers to the user created aligned with the [Account Requirements](#account-requirements).
* **Password** - refers to the password of the previously created user.
* **Re-type Password** - refers to the same password provided in the **Password** field.

7\. Verify if the system was added to the **Microsoft SQL Servers** actions panel and check its status.

{% hint style="info" %}
**Note:** The status will remain on **Ready** for a few minutes. Refresh the panel if needed. If there are configuration issues, such as incorrect connection parameters or an invalid account, the status will remain **Ready**. Click <img src="/files/V4avIC3c5nuIWtovUdqK" alt="" data-size="line"> to display the logs and troubleshoot. If everything is configured correctly, the status will change to **In Progress.**
{% endhint %}

8\. Navigate to the service grid and verify if the system was added under the **SQL Servers** nod&#x65;**.**

{% hint style="info" %}
**Note:** The system will appear in the service grid within 5-15 minutes.
{% endhint %}

### Monitor **Microsoft SQL Server in IT-Conductor**

To view the availability and performance metrics of a SQL server, locate the **SQL Servers** node in the service grid.

<figure><img src="/files/12McVGieXKgvCgJJDNVN" alt=""><figcaption><p>Figure 4: SQL Servers View in Service Grid</p></figcaption></figure>

#### **Microsoft SQL Server Key Metrics**

* **Availability** - refers to the operational state and accessibility of the SQL server.
* **Connection Failures** - refer to the events where attempts to establish a connection are unsuccessful.
* **Heartbeat** - refers to the periodic signal sent to the system, enabling real-time detection of server downtime.
* **Missing Account** - refers to the absence of a required user account or resource within the SQL server.
* **Retriever Failures** - refer to the errors or issues encountered during the retrieval process of essential data or information within the SQL server.

{% hint style="info" %}
**Note:** All metrics will not show data when a server is unavailable.
{% endhint %}

To access a historical view of each of these metrics, click <img src="/files/8kk1LNlXUUUHPixKaGkW" alt="" data-size="line"> and a pop-up chart will be displayed.

<figure><img src="/files/sLz2u36z5GBNEP0XMDJN" alt=""><figcaption><p>Figure 5: Availability Chart in Service Grid</p></figcaption></figure>

#### **Agent**

To check whether the MSSQL Agent is enabled or disabled, expand the **Agent** node.&#x20;

![Figure 6: Agent Jobs View in Service Grid](/files/DECrDxxYBBMtfNUumCiO)

You may receive notifications for failed jobs to avoid missing backups or critical tasks and identify which step failed.

#### **Backup**

To identify backup files that may contain errors, expand the **Backup** node and locate the **Errors** node.

![Figure 7: Backup View in Service Grid](/files/Snx49lj6xG0k2jFVKlv3)

These errors are primarily due to a problem with the database while the backup is running.

#### **Databases**&#x20;

To view the status and active connections of each database, expand the **Databases** node. You may further expand the **DB Files** node to view the used spaces and statuses of the database files.

<figure><img src="/files/eynHo06U556ncR6PspsC" alt=""><figcaption><p>Figure 8: Databases View in Service Grid</p></figcaption></figure>

#### **Error Log**

To view the error logs in the SQL server, expand the **Error Log** node. Then click the **Errors** node. You will be redirected to the Error Log Explorer, as shown in Figure 9. Click <img src="/files/upVne0JX1B7WOxWXisqQ" alt="" data-size="line"> to further examine the error logs in detail.

<figure><img src="/files/8xvzYG2prqOn7kV9XdnB" alt=""><figcaption><p>Figure 9: SQL Server Error Log Explorer</p></figcaption></figure>

#### **Linked Servers**

To view the status of the linked server, expand the **Linked Server** node. Then click on the arrow to see the individual **Availability** statuses of the linked servers.

![Figure 10: Linked Servers View in Service Grid](/files/Ynn48xN0WBLJ7KXPiXH6)

#### **System Performance**&#x20;

To view real-time metrics regarding the system performance of the MSSQL server, expand the **System Performance** node.

<figure><img src="/files/p54kALkoZbfvM80FHNJl" alt=""><figcaption><p>Figure 11: System Performance View in Service Grid</p></figcaption></figure>

* **Active Transactions** - refers to the number of transactions currently being processed by the SQL server. Monitoring this metric helps administrators understand the workload on the server and identify potential bottlenecks.
* **Batch Requests Rate** - indicates the rate at which the SQL Server processes batch requests. It reflects the server's overall workload and helps assess its performance.
* **Free Memory** - represents the available memory on the SQL Server that is not currently being used. Monitoring free memory helps optimize server performance and identify memory-related issues.
* **Lock Timeouts** - occur when a transaction attempts to acquire a lock on a resource but cannot do so within a specified time period. Monitoring lock timeouts helps identify contention issues and optimize database performance.
* **Lock Wait Time** - measures the amount of time a transaction spends waiting for another transaction to release a lock. Monitoring lock wait time helps identify potential performance bottlenecks and optimize query execution.
* **Log Cache Hit Ratio** - measures the efficiency of the SQL Server's transaction log cache by indicating the percentage of log records found in the cache. A high hit ratio indicates efficient cache usage.
* **Log Used** - represents the percentage of the transaction log that is currently being used. Monitoring log usage helps manage log file growth and ensure sufficient transaction logging space.
* **Processes Blocked** - indicates the number of processes currently blocked by other processes and unable to proceed. Monitoring this metric helps identify and resolve blocking issues impacting database performance.
* **SQL Compilations** - refer to the number of times SQL Server compiles a Transact-SQL statement into an execution plan. Monitoring SQL compilations helps optimize query performance and identify inefficient queries.
* **SQL Re-Compilations** - indicates the number of times SQL Server recompiles a previously compiled execution plan. Monitoring re-compilations helps identify query performance issues and optimize query execution.
* **Update Conflict Ratio** - measures the percentage of update operations that encounter conflicts, such as deadlock or blocking. Monitoring this metric helps identify and resolve concurrency issues in the database.
* **User Connections** - represent the number of user connections established with the SQL Server. Monitoring user connections helps manage server resources and ensure optimal performance for connected users.
* **SQL Statement Elapsed Time** - measures the total time to execute an SQL statement, including parsing, compilation, execution, and fetching results. Monitoring statement elapsed time helps identify performance-intensive queries and optimize their execution.

### Video

{% embed url="<https://youtu.be/cZ-yO1HpsAU?si=W2x2x-evlenmdt0H>" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.itconductor.com/user-guide/monitoring/database/microsoft-sql-server.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.