Collect data from Microsoft SQL Server

Configure the Splunk Distribution of OpenTelemetry Collector to collect metrics and events from your Microsoft SQL Server instance.

1. Deploy a supported OpenTelemetry collector if it's not already deployed.

   • To deploy the Splunk Distribution of OpenTelemetry Collector:

     1. See Get started with the Splunk Distribution of the OpenTelemetry Collector.
     2. Upgrade the collector to a supported version.

   • To deploy the community (OSS) version of the OpenTelemetry Collector:

     1. See the GitHub repository opentelemetry-collector-contrib.
     2. Upgrade the collector to a supported version.
2. Set up a baseline configuration.
   1. Configure sqlserver receiver to collect infrastructure metrics from your Microsoft SQL Server instance.

      The following example contains the minimum required configuration for metrics, but you can add other options:

      • If you're using the Splunk Distribution of OpenTelemetry Collector, see Microsoft SQL Server receiver.

      • If you're using the community (OSS) version of the OpenTelemetry Collector, see the opentelemetry-collector-contrib sqlserver README.

      YAMLCopy

      receivers sqlserver: server: your-server-name port: 1433 username: your-username password: your-password resource_attributes: sqlserver.instance.name: enabled: true

      receivers
        sqlserver:
          server: your-server-name
          port: 1433
          username: your-username
          password: your-password
          resource_attributes:
            sqlserver.instance.name:
              enabled: true

      Important:

      In the Splunk Distribution of OpenTelemetry Collector, the following sqlserver options have default values that are sufficient for database monitoring needs:

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

      The Splunk Distribution of OpenTelemetry Collector uses carefully selected default values to 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. Confirm your exporter configuration.
   3. Confirm your metrics pipeline configuration.

      You must create a metrics pipeline (or update an existing one) in order for you to get database platform metrics into Splunk Observability Cloud. Metrics work in synergy with the database platform events pipeline you'll set up below.

3. Enable Database Monitoring:
   1. Add the following lines to your sqlserver receiver configuration:
      YAMLCopy

      receivers sqlserver: ... # ADD to ENABLE Database Monitoring events: db.server.query_sample: enabled: true db.server.top_query: enabled: true

      receivers
        sqlserver:
          ...    
          # ADD to ENABLE Database Monitoring
          events:
            db.server.query_sample:
              enabled: true
            db.server.top_query:
              enabled: true

   2. Add an additional exporter, named dbmon, with the configuration shown in the codeblock below.
      Note: The dbmon exporter sends OpenTelemetry-formatted logs to your Splunk Observability Cloud event endpoint rather than to its metrics endpoint.

      YAMLCopy

      # Exporters define where the telemetry data is sent to exporters: # Exports dbmon events as logs otlphttp/dbmon: headers: X-SF-Token: your-splunk-access-token X-splunk-instrumentation-library: dbmon logs_endpoint: https://ingest.your-splunk-realm.signalfx.com/v3/event sending_queue: batch: flush_timeout: 15s max_size: 10485760 # 10 MiB sizer: bytes

      # Exporters define where the telemetry data is sent to
        exporters:
          # Exports dbmon events as logs
          otlphttp/dbmon:
            headers:
              X-SF-Token: your-splunk-access-token
              X-splunk-instrumentation-library: dbmon
            logs_endpoint: https://ingest.your-splunk-realm.signalfx.com/v3/event
            sending_queue:
              batch:
                flush_timeout: 15s
                max_size: 10485760 # 10 MiB
                sizer: bytes

   3. Add a new logs pipeline named dbmon, with the following components:
      Important:

      About the processors section:

      Use identical processors for both your metrics and your logs/dbmon pipelines, and include these processors in the same order. For example, if you use memory_limiter, and k8sattributes, batch processors in your metrics pipeline, use the same combination and order for your logs/dbmon pipeline.

      YAMLCopy

      service: pipelines: … logs/dbmon: receivers: - sqlserver processors: - memory_limiter - batch exporters: - otlphttp/dbmon

      service:
        pipelines:
      	…
          logs/dbmon:
            receivers:
              - sqlserver
            processors:
              - memory_limiter
              - batch
            exporters:
              - otlphttp/dbmon

   4. Restart your collector.
      The command to restart the collector varies depending on which collector you deployed, which platform you deployed it on, and what tool you used to deploy it. For the Splunk Distribution of the OpenTelemetry Collector deployed on Kubernetes platforms with Helm, the restart command is:

      BASHCopy

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

      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 (in other words, your command was helm repo add splunk-otel-collector-chart https://signalfx.github.io/splunk-otel-collector-chart ).