Microsoft SQL Server receiver

The Microsoft SQL Server receiver collects metrics from a Microsoft SQL Server instance.

The Microsoft SQL Server receiver is a component of the OpenTelemetry Collector. It connects to a Microsoft SQL Server instance and supports metrics and logs pipelines.

The receiver works either by using the Windows Performance Counters, or by directly connecting to an instance and querying it. Windows Performance Counters are only available when running on Windows.

Microsoft SQL Server support starts from these minimum collector versions:

  • Splunk Distribution of the OpenTelemetry Collector (splunk-otel-collector) v0.154.0 or later
  • Community (OSS) version of the OpenTelemetry Collector (opentelemetry-collector-contrib) v0.154.0 or later
Tip: Run the Collector as an administrator in order to collect all performance counters for metrics.

Supported versions and platforms

Splunk Database Monitoring supports these Microsoft SQL Server versions and platforms:

  • Versions: 2016, 2017, 2019, 2022
  • Platforms: Azure Managed Instance, Azure SQL Database, AWS RDS, self-hosted

Prerequisites

  1. Create the following permissions:

    • VIEW SERVER PERFORMANCE STATE if you're using Microsoft SQL Server 2022 or later versions.

    • VIEW SERVER STATE if you're using previous versions.

    Depending on the tasks you want to perform you also need at least one of the following permissions:

    • CREATE DATABASE

    • ALTER ANY DATABASE

    • VIEW ANY DEFINITION

    Check with your database provider's documentation for details.

  2. Create a non-admin, read-only Microsoft SQL Server user for the receiver to use:

    CODE 
    CREATE LOGIN [otel-user] WITH PASSWORD = 'otel-user-password'; 
CREATE USER [otel-user] FOR LOGIN [otel-user]; 
GRANT VIEW SERVER STATE TO [otel-user]; 
GRANT VIEW ANY DEFINITION TO [otel-user];

Configure the receiver

Modify your OpenTelemetry Collector configuration file as follows.

  1. In the receivers: section, add sqlserver:
    Non-Windows

    Set receivers.sqlserver.server to the fully qualified domain name (FQDN) or IP address of your Microsoft SQL Server database service. For example, in the context of a Kubernetes internal DNS system, if the service name is mssql-server and the namespace is mssqltest, set receivers.sqlserver.server to mssql-server.mssqltest.svc.cluster.local.

    YAML 
    sqlserver:
  collection_interval: 10s
  username: otel-user
  password: otel-user-password
  server: your-server-name
  port: 1433
  resource_attributes:
    sqlserver.computer.name:
      enabled: true
    sqlserver.instance.name:
      enabled: true
  events:
    db.server.query_sample:
      enabled: true
    db.server.top_query:
      enabled: true
    Windows

    If you’re using a named instance on Windows, specify a computer and a named instance:

    YAML 
    sqlserver:
  collection_interval: 10s
  computer_name: CustomServer
  instance_name: CustomInstance
  resource_attributes:
    sqlserver.computer.name:
      enabled: true
    sqlserver.instance.name:
      enabled: true
  events:
    db.server.query_sample:
      enabled: true
    db.server.top_query:
      enabled: true
    Important:

    If you're using the Splunk Distribution of OpenTelemetry Collector, leave the following receiver settings at their default values:

    • collection_interval (Default: 10s)

    • query_sample_collection.max_rows_per_query (Default: 100)

    • top_query_collection.collection_interval (Default: 60s)

    • top_query_collection.max_query_sample_count (Default: 1000)

    These values support Database Monitoring without affecting the performance of the database or the collector. If you increase these values you might adversely affect the performance of your database or collector, and this could result in ingest throttling.

  2. In the exporters: section, add an instance of the OTLP/HTTP exporter named otlp_http/dbmon. Confirm that a signalfx exporter is already present in your configuration because the metrics/dbmon pipeline uses it.

    Note: If you deployed the Splunk Distribution of the OpenTelemetry Collector, the signalfx exporter is already present in the default configuration.
    YAML 
    otlp_http/dbmon:
  headers:
    X-SF-Token: your-splunk-access-token
    X-splunk-instrumentation-library: dbmon
  logs_endpoint: https://ingest.your-splunk-realm.observability.splunkcloud.com/v3/event
  sending_queue:
    batch:
      flush_timeout: 15s
      max_size: 10485760
      sizer: bytes

  3. In the service.pipelines: section, create a metrics pipeline named metrics/dbmon and a logs pipeline named logs/dbmon:

    YAML 
    metrics/dbmon:
  receivers:
    - sqlserver
  processors:
    - memory_limiter
    - batch
  exporters:
    - signalfx
logs/dbmon:
  receivers:
    - sqlserver
  processors:
    - memory_limiter
    - batch
  exporters:
    - otlp_http/dbmon
    Important: Use an identical list of processors for the metrics and logs/dbmon pipelines, and include these processors in the same order.

  4. Restart the collector to apply your configuration changes.

    The restart command varies depending on what platform you deployed the collector on and what tool you used to deploy it. Here are general examples of the restart command:

    Linux

    Linux with installer script:

    BASH 
    sudo systemctl restart splunk-otel-collector
    Windows

    Windows with installer script:

    BASH 
    stop-service splunk-otel-collector
start-service splunk-otel-collector
    Kubernetes

    Kubernetes with Helm:

    BASH 
    helm upgrade your-splunk-otel-collector splunk-otel-collector-chart/splunk-otel-collector -f your-override-values.yaml

    where splunk-otel-collector-chart is the name you gave to the Helm chart in the helm repo add command.

Your database instance should now be visible on APM > Database monitoring as well as on Infrastructure > Infrastructure monitoring if you have a Database Monitoring license. For troubleshooting, see Troubleshoot data collection .

Advanced configurations

Enable built-in content

Splunk Observability Cloud provides built-in dashboards with charts that give you immediate visibility into the technologies and services being used in your environment. Learn more at Monitor the Collector with Splunk Observability Cloud’s built-in dashboards.

CAUTION: For the Microsoft SQL Server receiver out-of-the-box content to work properly, you need to explicitly activate and deactivate specific metrics and resource attributes in your configuration file:
Enable certain resource attributes

Some resource attributes, such as sqlserver.instance.name, are deactivated by default.

To activate them, specify the option in your configuration file:

YAML 
sqlserver:
  collection_interval: 10s
  username: otel-user
  password: otel-user-password
  server: 0.0.0.0
  port: 1433
  resource_attributes:
    sqlserver.instance.name:
      enabled: true
Enable optional metrics

Set metrics.metric-name.enabled to true. For example:

YAML 
sqlserver:
  metrics:
    sqlserver.user.connection.count:
      enabled: true
Use datasource to specify your connection string

Use this option to define the connection string used when connecting to the database:

YAML 
sqlserver:
  datasource: sqlserver://otel-user:otel-user-password@host/instance?param1=value&param2=value
  server: 127.0.0.1
  trusted_connection: true
Identify database instances uniquely

Use service.instance.id, service.name, and service.namespace resource attributes to distinguish Microsoft SQL Server instances when the generated identifier doesn't identify each instance uniquely in your deployment.

To identify database instances by using a combination of service.name, service.namespace, and override_value, use Splunk Distribution of the OpenTelemetry Collector (splunk-otel-collector) version 0.155.0 or higher or Community (OSS) version of the OpenTelemetry Collector (opentelemetry-collector-contrib) version 0.155.0 or higher.

For direct connections, the receiver sets service.instance.id from the configured host and port. Microsoft SQL Server named instances aren't included in the generated value. To include a named instance in the identifier, set override_value for service.instance.id or set service.name.

For Windows Performance Counters deployments, service.instance.id isn't populated. Activate service.name and service.namespace and set override_value to distinguish instances.

If you collect metrics and logs, add the resource attributes under both metrics.resource_attributes and logs.resource_attributes. For example:

Direct connect example
YAML 
sqlserver:
  server: your-server-name
  port: 1433
  username: otel-user
  password: otel-user-password
  metrics:
    resource_attributes:
      service.instance.id:
        enabled: true
        override_value: your-server-name:1433
      service.name:
        enabled: true
        override_value: fraud-db
      service.namespace:
        enabled: true
        override_value: playground-cluster-1
  logs:
    resource_attributes:
      service.instance.id:
        enabled: true
        override_value: your-server-name:1433
      service.name:
        enabled: true
        override_value: fraud-db
      service.namespace:
        enabled: true
        override_value: playground-cluster-1
Datasource connection string
YAML 
receivers:
  sqlserver:
    datasource: "your-server-name,1433;user id=otel-user;password=otel-user-password"
    metrics:
      resource_attributes:
        service.name:
          enabled: true
          override_value: fraud-db
        service.namespace:
          enabled: true
          override_value: playground-cluster-1
    logs:
      resource_attributes:
        service.name:
          enabled: true
          override_value: fraud-db
        service.namespace:
          enabled: true
          override_value: playground-cluster-1
Note: Set override_value only when the resource attribute is enabled.
Note: service.instance.id is populated only when the receiver connects directly to SQL Server (server/port or datasource configured). It is not populated when collecting metrics via Windows Performance Counters alone. For Performance Counters deployments, use service.name and service.namespace overrides to distinguish instances.
Note: service.instance.id uses host:port only. The SQL Server instance name (e.g. SQLEXPRESS in myserver\SQLEXPRESS,5000) is not part of service.instance.id. Use service.name or override_value on service.instance.id if instance-level disambiguation is required.
Use secure credentials
You can store your password securely through your choice of secret management software and simply reference it through an environment variable or an external file.

Set up APM correlation

See Correlate database queries with Splunk APM traces.

Settings reference

General optional settings:

  • collection_interval: The interval at which the receiver emits metrics. Default: 10s.

  • instance_name: The name of the specific SQL Server instance to monitor. If unspecified, metrics are scraped from all instances. If configured, you must also set computer_name when running on Windows.

Direct connection optional settings:

  • username: The username used to connect to the SQL Server instance.

  • password: The password used to connect to the SQL Server instance.

  • server: IP address or hostname of the SQL Server instance to connect to.

  • port: Port of the SQL Server instance to connect to.

  • datasource: Use this option to specify the direct connection using a string. It can't be used in conjunction with the username, password, server and port options. For more information refer to the example and to Microsoft's connection string descriptions in the Microsoft SQL documentation.

Windows-specific optional settings:

  • computer_name: The computer name identifies the SQL Server name or IP address of the computer being monitored. If specified, instance_name is also required. This option is ignored in non-Windows environments.

The following table describes all configuration settings for the Microsoft SQL Server receiver:

Note: If you’re planning to collect Microsoft SQL Server data from Azure-managed platforms, deactivate the sqlserver.database.count metric because it isn't supported in the current version of the receiver. Example on how to deactivate it:
CODE 
sqlserver:
  metrics:
    sqlserver.database.count:
      enabled: false

All configuration options for this receiver:

included

https://raw.githubusercontent.com/splunk/collector-config-tools/main/cfg-metadata/receiver/sqlserver.yaml

Metrics reference

Important: For the latest list of available configurable metrics, see the documentation on GitHub.

Metrics, attributes, and resource attributes reported by this receiver:

included

https://raw.githubusercontent.com/splunk/collector-config-tools/main/metric-metadata/sqlserverreceiver.yaml