sqlserverreceiver

Microsoft SQL Server Receiver

Status
Stability	development: logs
	beta: metrics
Distributions	contrib
Issues
Code Owners	@StefanKurek, @sincejune, @crobert-1 | Seeking more code owners!

The sqlserver receiver grabs metrics/logs about a Microsoft SQL Server instance. The receiver works by either using the Windows Performance Counters, or by directly connecting to the instance and querying it. Windows Performance Counters are only available when running on Windows.

Make sure to run the collector as administrator in order to collect all performance counters for metrics.

Configuration

The following settings are optional:

• collection_interval (default = 10s): The interval at which metrics should be emitted by this receiver.
• instance_name (optional): The instance name identifies the specific SQL Server instance being monitored. If unspecified, metrics will be scraped from all instances. If configured, the computer_name must also be set when running on Windows.

Direct connection options (optional, but all must be specified to enable):

• 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 SQL Server instance to connect to.
• port: Port of the SQL Server instance to connect to.

For finer control over the direct connection use the datasource, a.k.a. the "connection string", instead. Note: it can't be used in conjunction with the username, password, server and port options.

Windows-specific options:

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

Top-Query collection specific options (only useful when top-query collection are enabled):

• lookback_time (optional, example = 60, default = 2 * collection_interval): The time window (in second) in which to query for top queries.
  • Queries that were finished execution outside the lookback window are not included in the collection. Increasing the lookback window (in seconds) will be useful for capturing long-running queries.
• max_query_sample_count (optional, example = 5000, default = 1000): The maximum number of records to fetch in a single run.
• top_query_count: (optional, example = 100, default = 200): The maximum number of active queries to report (to the next consumer) in a single run.
• enabled: (optional, default = false): Enable collection of top queries.
  • e.g. sqlserver receiver will fetch 1000 (value: max_query_sample_count) queries from database and report the top 200 (value: top_query_count) which used the most CPU time.

Query sample collection related options (only useful when query sample is enabled)

• max_rows_per_query: (optional, default = 100) use this to limit rows returned by the sampling query.
• enabled: (optional, default = false): Enable collection of sample queries. Example:

    receivers:
      sqlserver:
        collection_interval: 10s
      sqlserver/1:
        collection_interval: 5s
        username: sa
        password: securepassword
        server: 0.0.0.0
        port: 1433

When a named instance is used on Windows, a computer name and an instance name must be specified. Example with named instance:

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

The full list of settings exposed for this receiver are documented in config.go with detailed sample configurations in testdata/config.yaml.

Top query collection enabled:

    receivers:
      sqlserver:
        collection_interval: 5s
        username: sa
        password: securepassword
        server: 0.0.0.0
        port: 1433
        top_query_collection:
          enabled: true
          lookback_time: 60
          max_query_sample_count: 1000
          top_query_count: 200
        query_sample_collection:
          enabled: true
          max_rows_per_query: 1450
          

Metrics

Details about the metrics produced by this receiver can be found in documentation.md

Logs

Details about the logs produced by this receiver can be found in logs-documentation.md

Known issues

SQL Server docker users may run into an issue that the collector fails to parse certificate from server due to x509: negative serial number. That's because we adopted Go 1.23 starting from contrib v0.121.0:

Before Go 1.23, ParseCertificate accepted certificates with negative serial numbers. This behavior can be restored by including "x509negativeserial=1" in the GODEBUG environment variable. references:

1. https://pkg.go.dev/crypto/x509#ParseCertificate
2. microsoft/mssql-docker#895