Skip to content

Latest commit

 

History

History

sqlserver

README.md

Microsoft SQL Server Check

SQL server Graph

Overview

The SQL Server integration tracks the performance of your SQL Server instances. It collects metrics for number of user connections, rate of SQL compilations, and more.

Enable Database Monitoring (DBM) for enhanced insight into query performance and database health. In addition to the standard integration, Datadog DBM provides query-level metrics, live and historical query snapshots, wait event analysis, database load, query explain plans, and blocking query insights.

SQL Server 2012, 2014, 2016, 2017, 2019, and 2022 are supported.

Setup

This page describes the SQL Server Agent standard integration. If you are looking for the Database Monitoring product for SQL Server, see Datadog Database Monitoring.

Installation

The SQL Server check is included in the Datadog Agent package. No additional installation is necessary on your SQL Server instances.

Make sure that your SQL Server instance supports SQL Server authentication by enabling "SQL Server and Windows Authentication mode" in the server properties:

Server Properties -> Security -> SQL Server and Windows Authentication mode

Prerequisite

Note: To install Database Monitoring for SQL Server, select your hosting solution on the documentation site for instructions.

Supported versions of SQL Server for the SQL Server check are the same as for Database Monitoring. Visit the Setting up SQL Server page to see the currently supported versions under the Self-hosted heading.

Proceed with the following steps in this guide only if you are installing the standard integration alone.

  1. Create a read-only login to connect to your server:

        CREATE LOGIN datadog WITH PASSWORD = '<PASSWORD>';
    CREATE USER datadog FOR LOGIN datadog;
    GRANT SELECT on sys.dm_os_performance_counters to datadog;
    GRANT VIEW SERVER STATE to datadog;

    To collect file size metrics per database, ensure the user you created (datadog) has connect permission access to your databases by running:

        GRANT CONNECT ANY DATABASE to datadog;

  2. (Required for AlwaysOn and sys.master_files metrics) To gather AlwaysOn and sys.master_files metrics, grant the following additional permission:

        GRANT VIEW ANY DEFINITION to datadog;

Configuration

Host

To configure this check for an Agent running on a host:

  1. Edit the sqlserver.d/conf.yaml file, in the conf.d/ folder at the root of your Agent's configuration directory. See the sample sqlserver.d/conf.yaml for all available configuration options:

    init_config:

instances:
  - host: "<SQL_HOST>,<SQL_PORT>"
    username: datadog
    password: "<YOUR_PASSWORD>"
    connector: odbc # alternative is 'adodbapi'
    driver: SQL Server

    If you use port autodiscovery, use 0 for SQL_PORT. See the example check configuration for a comprehensive description of all options, including how to use custom queries to create your own metrics.

    Note: The (default) provider SQLOLEDB is being deprecated. To use the newer MSOLEDBSQL provider, set the adoprovider variable to MSOLEDBSQL19 in your sqlserver.d/conf.yaml file after having downloaded the new provider from Microsoft. If you're using MSOLEDBSQL version 18 or lower, set the adoprovider variable to MSOLEDBSQL instead. It is also possible to use the Windows Authentication and not specify the username/password with:

    connection_string: "Trusted_Connection=yes"

  2. Restart the Agent.

Linux

Extra configuration steps are required to get the SQL Server integration running on a Linux host:

  1. Install an ODBC SQL Server driver, for example the Microsoft ODBC driver or the FreeTDS driver.
  2. Copy the odbc.ini and odbcinst.ini files into the /opt/datadog-agent/embedded/etc folder.
  3. Configure the conf.yaml file to use the odbc connector and specify the proper driver as indicated in the odbcinst.ini file.
Log collection

Available for Agent versions >6.0

  1. Collecting logs is disabled by default in the Datadog Agent, enable it in your datadog.yaml file:

    logs_enabled: true

  2. Add this configuration block to your sqlserver.d/conf.yaml file to start collecting your SQL Server logs:

    logs:
  - type: file
    encoding: utf-16-le
    path: "<LOG_FILE_PATH>"
    source: sqlserver
    service: "<SERVICE_NAME>"

    Change the path and service parameter values based on your environment. See the sample sqlserver.d/conf.yaml for all available configuration options.

  3. Restart the Agent.

Containerized

For containerized environments, see the Autodiscovery Integration Templates for guidance on applying the parameters below.

Metric collection
Parameter Value
<INTEGRATION_NAME> sqlserver
<INIT_CONFIG> blank or {}
<INSTANCE_CONFIG> {"host": "%%host%%,%%port%%", "username": "datadog", "password": "<UNIQUEPASSWORD>", "connector": "odbc", "driver": "FreeTDS"}

See Autodiscovery template variables for details on passing <UNIQUEPASSWORD> as an environment variable instead of a label.

Log collection

Available for Agent versions >6.0

Collecting logs is disabled by default in the Datadog Agent. To enable it, see Kubernetes Log Collection.

Parameter Value
<LOG_CONFIG> {"source": "sqlserver", "service": "sqlserver"}

Validation

Run the Agent's status subcommand and look for sqlserver under the Checks section.

Data Collected

Metrics

See metadata.csv for a list of metrics provided by this check.

Most of these metrics come from your SQL Server's sys.dm_os_performance_counters table.

Events

The SQL server check does not include any events.

Service Checks

See service_checks.json for a list of service checks provided by this integration.

Troubleshooting

Need help? Contact Datadog support.

If you are running the Agent on an ARM aarch64 processor, there is a known issue starting in version 14.0.0 of this check, which is bundled with Agent version 7.48.0. A Python dependency fails to load, and you'll see the following message when running the Agent's status subcommand:

Loading Errors
  ==============
    sqlserver
    ---------
      Core Check Loader:
        Check sqlserver not found in Catalog
      JMX Check Loader:
        check is not a jmx check, or unable to determine if it's so
      Python Check Loader:
        unable to import module 'sqlserver': No module named 'sqlserver'

This is fixed in version 15.2.0 of the check and in Agent versions 7.49.1 and above.

Further Reading