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

Note:

• Perform these steps on the MSSQL server.

• Access to additional commands may be required if a custom configuration is involved.

Configure Microsoft SQL Server Monitoring in IT-Conductor

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

Add New Microsoft SQL Server

1. Visit service.itconductor.com and enter your login credentials.

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 1: Microsoft SQL Servers Actions Panel in Administrator's Dashboard

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

Figure 2: SQL Servers Actions Panel Page

1. Fill out all the necessary information in the New SQL Server wizard. Once completed, click to add the server.

Figure 3a: New SQL Server Wizard (New SQL Server Screen)

• 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 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.

1. Provide the account information in the New Account screen and click to complete the configuration.

Figure 3b: New SQL Server Wizard (New Account Screen)

• 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.

• 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.

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 to display the logs and troubleshoot. If everything is configured correctly, the status will change to In Progress.

8. Navigate to the service grid and verify if the system was added under the SQL Servers node.

Note: The system will appear in the service grid within 5-15 minutes.

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 4: SQL Servers View in Service Grid

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.

Note: All metrics will not show data when a server is unavailable.

To access a historical view of each of these metrics, click and a pop-up chart will be displayed.

Figure 5: Availability Chart in Service Grid

Agent

To check whether the MSSQL Agent is enabled or disabled, expand the Agent node.

Figure 6: Agent Jobs View in Service Grid

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

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

Databases

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 8: Databases View in Service Grid

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 to further examine the error logs in detail.

Figure 9: SQL Server Error Log Explorer

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

System Performance

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

Figure 11: System Performance View in Service Grid

• 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.