Docs » Configure application receivers » Configure application receivers for hosts and servers » Microsoft SQL Server

Microsoft SQL Server 🔗

Description 🔗

The Splunk Distribution of OpenTelemetry Collector provides this integration as the Microsoft SQL Server monitor via the Smart Agent Receiver. This monitor sends metrics from Microsoft SQL Server instances. The monitor is based on the sqlserver plugin.

To use the monitor, you need to create login credentials in the Microsoft SQL Server host. To create this login, follow these steps:

  1. Log in as an administrator.

  2. Start an SQL client.

  3. Enter the following commands:

USE master;
GO
CREATE LOGIN [signalfxagent] WITH PASSWORD = '<YOUR PASSWORD HERE>';
GO
GRANT VIEW SERVER STATE TO [signalfxagent];
GO
GRANT VIEW ANY DEFINITION TO [signalfxagent];
GO

See mssqlserver for the monitor source.

Installation 🔗

This monitor is available in the SignalFx Smart Agent Receiver, which is part of the Splunk Distribution of OpenTelemetry Collector.

To install this integration:

  1. Deploy the Splunk Distribution of OpenTelemetry Collector to your host or container platform.

  2. Configure the monitor, as described in the next section.

Note: When using the sqlserver driver to connect to a Microsoft SQL server using Windows authentication, the DOMAIN\user format does not work. Use the hex value for \, which is %5C. For example, change DOMAIN\user to DOMAIN%5Cuser.

Configuration 🔗

The Splunk Distribution of OpenTelemetry Collector allows embedding a Smart Agent monitor configuration in an associated Smart Agent Receiver instance.

Note: Providing a Microsoft SQL Server monitor entry in your Smart Agent or Collector configuration is required for its use. Use the appropriate form for your agent type.

Smart Agent 🔗

To activate this monitor in the Smart Agent, add the following to your agent configuration:

monitors:  # All monitor config goes under this key
  - type: telegraf/sqlserver
    ...  # Additional config

See Smart Agent example configuration for an autogenerated example of a YAML configuration file, with default values where applicable.

Splunk Distribution of OpenTelemetry Collector 🔗

To activate this monitor in the Splunk Distribution of OpenTelemetry Collector, add the following to your agent configuration:

receivers:
  smartagent/sqlserver:
    type: telegraf/sqlserver
    ...  # Additional config

To complete the monitor activation, you must also include the smartagent/sqlserver receiver item in a metrics pipeline. To do this, add the receiver item to the service > pipelines > metrics > receivers section of your configuration file.

See configuration examples for specific use cases that show how the collector can integrate and complement existing environments.

Configuration settings 🔗

The following table shows the configuration options for the Microsoft SQL Server monitor:

Option Required Type Description
host yes string
port yes integer
userID no string UserID used to access the SQL Server instance.
password no string Password used to access the SQL Server instance.
appName no string The app name used by the monitor when connecting to the SQLServer. (default: signalfxagent)
queryVersion no integer The version of queries to use when accessing the cluster. Refer to the documentation for the Microsoft SQL Server Telegraf Plugin, provided by Influxdata. (default: 2)
azureDB no bool Whether the database is a Microsoft Azure database. (default: false)
excludedQueries no list of strings Queries to exclude. Possible values are PerformanceCounters, WaitStatsCategorized, DatabaseIO, DatabaseProperties, CPUHistory, DatabaseSize, DatabaseStats, MemoryClerk VolumeSpace, and PerformanceMetrics.
log no unsigned integer Log level to use when accessing the database (default: 1)

The following is an example of a Microsoft SQL Server monitor configuration:

monitors:
 - type: telegraf/sqlserver
   host: <host_name>
   port: 1433
   userID: <user_id>
   password: <password>
   appName: signalfxagent

Ensure that port and appName are always set to the values stated in this example.

Troubleshooting 🔗

In some Windows-based SQL Server instances, TCP/IP has been disabled by default. You might encounter this in a Microsoft Azure service instance. If you see error messages similar to Cannot read handshake packet: read tcp: wsarecv: An existing connection was forcibly closed by the remote host., you need to explicitly enable TCP/IP for the instance.

  1. Verify agent configurations are correct.

  2. In your SQL Server instance, enable TCP/IP. To do this, select Start > Administrative Tools > Computer Management.

  3. In the Computer Management sidebar, select Services and Applications > SQL Server Configuration Manager > SQL Server Network Configuration.

  4. Select Protocols for <YOUR SQL SERVER NAME>.

  5. In the protocol list to the right, right-click the TCP/IP protocol and select Enable.

Metrics 🔗

These are the metrics available for this integration.