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.

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.

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. Make sure your SQL Server instance is listening on a specific fixed port. By default, named instances and SQL Server Express are configured for dynamic ports. See Microsoft's documentation for more details.

  3. (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

    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 MSOLEDBSQL in your sqlserver.d/conf.yaml file after having downloaded the new provider from Microsoft. 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.

Further Reading