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 user:

   This is a basic example of how to create a non-admin, read-only Microsoft SQL Server user:

   CREATE LOGIN [splunk-otel-collector] WITH PASSWORD = '<SECURE_PASSWORD>'; 
   CREATE USER [splunk-otel-collector] FOR LOGIN [splunk-otel-collector]; 
   GRANT VIEW SERVER STATE TO [splunk-otel-collector]; 
   GRANT VIEW ANY DEFINITION TO [splunk-otel-collector];

Get started

Follow these steps to configure and activate the receiver:

1. Deploy the collector.

2. Configure the receiver.

3. Restart the collector.

Deploy the collector

Deploy the Splunk Distribution of the OpenTelemetry Collector on your host or container platform:

• Install the Collector for Linux with the installer script

• Install the Collector for Windows with the installer script

• Install the Collector for Kubernetes using Helm

Configure the receiver

Configure the Microsoft SQL Server receiver, sqlserver, in the Splunk Distribution of the OpenTelemetry Collector that you deployed on your host or container platform:

1. Add sqlserver to the receivers section of your Splunk Distribution of the OpenTelemetry Collector configuration file. For details on sqlserver parameters, see the OpenTelemetry Microsoft SQL Server receiver README.

   Tip: You are responsible for keeping your sqlserver configuration compatible with any updates to this receiver. Updates are typically announced on its README page, so the best practice is to monitor this page for updates you might need to make to your configuration.
   Tip: The example below shows a password as clear text, but 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.

   Sample configuration

   receivers:
     sqlserver:
       collection_interval: 10s
       username: sa
       password: password123
       server: mssql-server.mssqltest.svc.cluster.local
       port: 1433
       resource_attributes:
         sqlserver.computer.name:
           enabled: true
         sqlserver.instance.name:
           enabled: true

   Sample configuration for Windows (configure a named instance)

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

   receivers:
     sqlserver:
       collection_interval: 10s
       computer_name: CustomServer
       instance_name: CustomInstance
       resource_attributes:
         sqlserver.computer.name:
           enabled: true
         sqlserver.instance.name:
           enabled: true

2. 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 metrics and resource attributes

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

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

   receivers:
       sqlserver:
         collection_interval: 10s
         username: sa
         password: securepassword
         server: 0.0.0.0
         port: 1433
         resource_attributes:
           sqlserver.instance.name:
             enabled: true

   Use datasource to specify your connection string

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

   receivers:
       sqlserver:
         datasource: sqlserver://username:password@host/instance?param1=value&param2=value
         server: 127.0.0.1
         trusted_connection: true
   

   For more information:

   • See the configuration that enables built-in content at SQL Server discovery yaml in GitHub.

   • See the list of default and optional metrics at SQL server default and optional metrics in GitHub.

3. Configure advanced settings.

4. Add sqlserver to the collector's metrics pipeline, which is within the service section of the collector's configuration file:

   service:
     pipelines:
       metrics:
         receivers:
           - sqlserver

5. Restart the collector.

Restart the collector

The command to restart the Splunk Distribution of the OpenTelemetry Collector varies depending on what platform you deployed it on and what tool you used to deploy it, but here are general examples of the restart command:

• Linux with installer script:

  sudo systemctl restart splunk-otel-collector

• Windows with installer script:

  Stop-Service splunk-otel-collector
  Start-Service splunk-otel-collector

• Kubernetes with Helm:

  sudo systemctl restart splunk-otel-collector

Settings

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:

Metrics

The following metrics, resource attributes, and attributes, are available.

receivers:
  samplereceiver:
    metrics:
      metric-one:
        enabled: true
      metric-two:
        enabled: false

receivers:
  hostmetrics:
    scrapers:
      process:
        metrics:
          process.cpu.utilization:
            enabled: true

Troubleshooting