Monitoring Microsoft SQL Server

The sensor supports the following operating systems:

• Windows
• Linux

The sensor supports the following versions of Microsoft SQL Server:

• SQL Server 2016
• SQL Server 2017
• SQL Server 2019
• SQL Server 2022

The sensor also supports monitoring Microsoft SQL Server Always On Availability Groups (AG) for high availability and disaster recovery configurations.

The following table shows the latest supported version and support policy:

For more information about the support policy, see Support strategy for sensors.

For this technology, Instana supports client-side tracing for the following languages and runtimes:

• Node.js
• Java
• .Net Framework
• Go
• PHP

You must install the Instana host agent on the same system in which Microsoft SQL Server is installed. To install the Instana host agent on Windows, see the Installing the host agent on Windows topic. To install the Instana host agent on Linux, see the Installing the host agent on Linux topic.

After you install the Instana agent, it enables the Microsoft SQL Server sensor by default. You can choose how the Instana agent connects to the SQL Server as follows:

• SQL Server authentication: If you provide the instance, username, password, and port (clear-text) in the agent configuration file *instanaAgentDir*/etc/instana/configuration.yaml as shown in the following configuration, the agent connects to the SQL Server by using the SQL Server authentication:

  Note:

  The local monitoring sensor design is updated to support multiple database instances that use the SQL Server authentication. As a result, all the local monitoring configurations must now be defined under the local tag. However, the design remains compatible with the existing configuration for monitoring single database instance, which continues to be supported.

  com.instana.plugin.mssql:
    local: # multiple configurations supported
      - instance: 'MSSQLSERVER'  # instance name of MsSQL Server
        user: 'mssql'
        password: 'password'
        port: '1433'
        poll_rate: 60 # seconds.
        top_queries_poll_rate: 120 # seconds.
   

• Windows Authentication: If you leave the configuration section in the agent configuration file commented out or leave the username and password empty, the agent tries to connect using Windows Authentication. When Windows Authentication is used, the agent authenticates with the credentials.

• poll_rate: Specify the poll rate. By default, the value is 1 second.

• top_queries_poll_rate: Specify the poll rate for most used queries. By default, the value is 60 seconds.

  When the Windows Authentication is used, ensure that your account has appropriate permissions as described in the Required server or database permissions section.

For the Instana agent to securely connect to your MS SQL Server, configure the agent configuration files<agent_install_dir>/etc/instana/configuration.yaml as follows:

If you are running the Microsoft SQL database and the Instana agent on a Windows system, ensure that the TCP/IP protocol is enabled and the SQL Server Browser service is running.

If you want to specify a custom port that the sensor must use to monitor the instance (other than the default port 1433), specify the port field in the agent configuration file:

com.instana.plugin.mssql:
  port: 2529    # custom Microsoft SQL TCP port
 

Instana queries dynamic management views (DMV) on SQL Server by using the user credentials specified in the agent configuration file. Ensure that the SQL user specified in the agent configuration file has the VIEW SERVER STATE and VIEW DATABASE STATE permissions. For more information on these permissions, see System dynamic management views.

To collect disk volume and auto-growth metrics, the monitoring login must also be a member of the sysadmin fixed server role. This requirement exists because sys.master_files, which is used to enumerate database files for these metrics, does not return any rows to non-sysadmin logins. This membership requirement applies to both Windows Authentication and SQL Server Authentication.

If disk volume and auto-growth metrics are not required, sysadmin membership is not needed.

For Microsoft SQL Server Always On Availability Groups (AG), the sensor creates a separate AG entity that collects aggregate metrics and configuration data for the availability group and its replicas.