Skip to main content
The Microsoft SQL Server integration requires CXDOT Collector 1.13.0 or greater. Microsoft SQL Server is a relational database management system. Use the Microsoft SQL Server integration to collect throughput, memory, lock, transaction log, and database file metrics from SQL Server instances running in your environment. The Microsoft SQL Server integration supports SQL Server 2017 or greater.

Supported telemetry types

The Microsoft SQL Server integration supports these telemetry types:

Prerequisites

The Microsoft SQL Server integration has the following prerequisites:
  • Find the TCP port of each SQL Server instance. The Collector connects to a host and port, and doesn’t resolve named instances through SQL Server Browser. A default instance listens on port 1433. A named instance often uses a dynamic port, so configure it to listen on a fixed port. In SQL Server Configuration Manager, open the TCP/IP properties of the instance’s network configuration and enable the protocol if it’s disabled. On the Protocol tab, confirm that Listen All is Yes so that the IPAll settings apply. Under IPAll on the IP Addresses tab, clear TCP Dynamic Ports, set TCP Port, and restart the instance. To find the port of an instance, connect to it over TCP as an administrator with a SQL client, such as sqlcmd -S tcp:<HOST>\<INSTANCE> -U <ADMIN_LOGIN> -C, and run the following query. The -C option trusts the instance’s certificate only for this lookup. Current versions of sqlcmd otherwise reject the certificate that SQL Server generates for itself.
  • Make that port reachable from the CXDOT Collector. On Windows, allow it inbound through Windows Firewall. For example, run the following command in an elevated PowerShell session on the SQL Server host:
  • Enable SQL Server authentication on each instance. The Collector signs in with a login name and password, so an instance that allows only Windows authentication refuses the connection. To see which mode an instance uses, run the following query, which returns 1 when the instance allows only Windows authentication:
    To change the authentication mode, see change server authentication mode. The change takes effect only after you restart the instance, so plan it for a maintenance window.
  • Create a SQL Server login for the CXDOT Collector. On each SQL Server instance, connect as a login that can create logins and database users and grant server and database permissions, such as a member of the sysadmin fixed server role, and run the following statements:
    Replace cxdot with the login name and <PASSWORD> with a password for your environment. The statements skip a login or user that already exists, so you can run them again to add a grant. SQL Server 2019 and earlier don’t have the VIEW SERVER PERFORMANCE STATE permission. On those versions, grant VIEW SERVER STATE instead. The msdb user lets the Collector read backup history. SQL Server allows access to the backup history by default. The GRANT SELECT on dbo.backupset preserves that access on an instance where the default access has been removed. Give the Collector a login of its own rather than one that another monitoring tool also uses. The Collector signs in at every collection, so a wrong or rotated password causes several failed sign-ins a minute on each instance. For a login created with its password policy enforced (CHECK_POLICY = ON), those repeated failures lock the login and prevent every tool that shares it from signing in. Test the password with a SQL client before you configure it. If you configure the integration to report files and index usage for a database other than master, also create a user for the login in that database:
    Replace <DATABASE> with the configured database.

Configure

The Microsoft SQL Server integration is enabled by default. It collects metrics only from the SQL Server instances you identify, through annotations on their pods and Services or through a list of static targets. Follow the instructions for your CXDOT Collector deployment: the Helm chart on Kubernetes or direct installation on each SQL Server host.

Configure on Kubernetes

To configure the Microsoft SQL Server integration on Kubernetes, follow these steps:
  1. To collect from SQL Server pods in Kubernetes, add a CXDOT integration annotation to the pod template. The annotation key must end with the container name, and its value can reference credentials stored in a Kubernetes Secret in the same namespace. For example, add the following annotation to your SQL Server workload:
    The Collector replaces ${target.host} with the pod IP address, ${target.port.mssql} with the container port named mssql, and ${k8s_secret:sqlserver-credentials/password} with the password key of the sqlserver-credentials Secret. To allow the Collector to read the Secret, add the pod’s namespace to apiServer.metadataServer.secretAllowlist in the Helm values for your CXDOT Collector. The Collector verifies the server certificate for an annotated target as it does for a static target. To accept a self-signed certificate generated by SQL Server, add tls: {insecure_skip_verify: true} to the annotation, or configure a custom certificate authority as the encryption step describes. If the annotation sets tls.ca_file, you must also allow that path under collection.cxdot.integrations.discovery.kubernetes.annotation_restrictions.allowed_collector_file_paths in your Collector Helm values.
  2. Optional: To collect from SQL Server instances running outside your Kubernetes cluster, configure static targets instead of annotated targets. Supply the password through your deployment’s secret management and expose it on the cluster-tier CXDOT Collector through clusterCollector.extraEnv. For example, add the following to the Helm values file for your CXDOT Collector:
    Every static target uses the same username and password. When you configure static targets, this integration instance collects from exactly those targets and stops collecting from annotated targets. The Collector sets server.address to the exact host string in endpoint, including its case. Use the same spelling and capitalization that your dashboards and monitors use in their filters, such as the host value in a Datadog annotation. Collect each SQL Server instance from exactly one Collector. One Collector can list every instance on a host, or on several hosts. Two Collectors that collect the same instance report it twice under the same server.address, so a query that sums by server.address counts the instance twice. In an Always On availability group, list each replica by its own host name rather than by the group’s listener, which follows the primary replica.
  3. Optional: Configure encryption for static targets. By default, the Collector encrypts each connection and verifies that a certificate authority it trusts issued the server certificate for the host name in the endpoint. SQL Server generates a certificate for itself when none is configured, and that certificate can’t pass verification. SQL Server 2017 and earlier generate a new one at every start, and about half of them carry a negative serial number, which the Collector accepts. Install a certificate issued for the server’s host name on each instance, or set tls.insecure_skip_verify to true to accept any certificate. If the system that runs the Collector doesn’t trust the certificate authority that issued the server certificate, set tls.ca_file to a file that holds the authority’s certificate. Mount the file into the cluster Collector from a Secret with clusterCollector.extraVolumes and clusterCollector.extraVolumeMounts. For example:
    Every static target uses the same tls settings. The Collector accepts TLS 1.2 or later by default, so a server that can’t negotiate TLS 1.2, such as one that hasn’t received the update that adds it, fails the handshake. Setting tls.min_version to "1.0", with tls.insecure_skip_verify set to true when the server certificate can’t be verified, might let the Collector connect to such a server. The durable fix is to enable TLS 1.2 on the server. An endpoint can use a host alias or an availability group listener name while the server certificate names the host itself. For such a target, create a separate integration instance and set tls.server_name_override to the name in the certificate. The separate instance limits the override to targets whose certificates carry that name.
  4. Optional: Collect from annotated targets and static targets at the same time by adding a second instance of the integration. A key in the form sqlserver/<NAME> defines another instance with its own settings. For example, the following Helm values keep annotated targets under sqlserver and collect from static targets under sqlserver/external:
  5. Optional: Report the files and index usage of a database other than master for static targets. Regardless of this setting, the Collector reports server-wide metrics and the state and backup count of every database. On a secondary replica of an Always On availability group whose databases aren’t readable, keep master. The Collector switches to the configured database at every collection. If the replica doesn’t allow the Collector to read that database, the switch fails, the file and index usage metrics are missing, and the Collector logs an error. For example:
  6. Optional: Change how often the Collector reads backup counts and index usage from static targets. Both reads run during the first collection and then every five minutes by default, independently of collection_interval, because their cost grows with the backup history and number of indexes. Between reads, the Collector reports the most recent values at every collection. If a read fails, the Collector retries it at the next interval. The timeout setting limits how long a collection from one target can run. On an instance with a large msdb backup history, the backup read can consume most of the timeout. Metrics that the Collector reads afterward are then missing from that collection. On such an instance, raise timeout, or raise backup_collection_interval so that the read runs less often. For example:
  7. Optional: Disable the integration. For example:
Metrics from each discovered pod include that pod’s Kubernetes metadata. For more information, see autodiscovery.

Configure on a host

To configure the Microsoft SQL Server integration on a host outside Kubernetes, such as a Windows server that runs SQL Server, follow these steps:
  1. Install the CXDOT Collector on each SQL Server host. For the operating system versions that the Collector supports, see install the CXDOT Collector on Windows or install the CXDOT Collector on Linux.
  2. List the local instance as a static target. Run the Collector in host mode. For example, add the following to the Collector configuration file:
    The example reads the password from the Collector process’s SQLSERVER_PASSWORD environment variable. On a host, the integration collects only from the instances in instances. The endpoint can name localhost or the host’s own name. The Collector reports the host from the endpoint as server.address. For localhost and other loopback endpoints, it reports the host’s name as server.address. It also adds the host’s name as host.name to every metric. When the Collector runs as a Windows service, set SQLSERVER_PASSWORD as a machine-scoped environment variable and restart the service. The service doesn’t inherit variables from an interactive session. For example, from an elevated PowerShell session:
    The Collector never signs in as the Windows account that runs it. It signs in with the SQL Server login in username.
  3. Optional: Configure encryption. As on Kubernetes, the Collector verifies the server certificate by default, and the certificate that SQL Server generates for itself can’t pass verification. Install a certificate issued for the host’s name, or set tls.insecure_skip_verify to true to accept any certificate. When the endpoint is localhost and the certificate names the host, set tls.server_name_override to the name in the certificate. For example:

Validate

To validate the Microsoft SQL Server integration, follow these steps:
  1. In Live Telemetry Analyzer, filter for __name__=cxdot.integration.target.health and cxdot.integration.name=sqlserver. Confirm that each target reports 1. Identify each target by its server.address and server.port attributes and, for host deployments, by host.name. A target whose endpoint is localhost or another loopback address reports the Collector host’s name as server.address.
  2. In Metrics Explorer, run the following query while the SQL Server instances handle client requests:
    Confirm that the query returns a time series for each target.
  3. Optional: Open the Overview dashboard that the integration provides, which shows server health, throughput, memory, file I/O, buffer cache, database state, and blocking. Confirm that its panels show data for your targets.

Troubleshooting

  • A target reports 0 for cxdot.integration.target.health: The Collector can’t connect and sign in to the instance. Confirm that the endpoint names the instance’s TCP port, that the instance allows SQL Server authentication, and that the login name and password are correct. A named instance that listens on a dynamic port isn’t reachable at port 1433.
  • A static target reports 0 for cxdot.integration.target.health although its endpoint and login are correct: The Collector rejects the server certificate. By default, it verifies the certificate of every static target, and the certificate that SQL Server generates for itself fails verification. Install a certificate issued for the host name in the endpoint, set tls.ca_file to a file that contains the certificate authority’s certificate, or set tls.insecure_skip_verify to true.
  • A target reports 1 for cxdot.integration.target.health but few other metrics arrive: The health metric confirms only that the Collector can sign in. Grant the login VIEW SERVER PERFORMANCE STATE, or VIEW SERVER STATE on SQL Server 2019 and earlier.
  • File I/O metrics, such as sqlserver.database.io and sqlserver.database.latency, are missing while other metrics arrive: Grant the login VIEW ANY DEFINITION. Without that permission, SQL Server hides the files from the login without an error, and the Collector reports no time series for them.
  • sqlserver.index.operation.count is missing: The Collector reports index usage only for user tables in the configured database. The configured database is master unless you specify another, and master usually has no user tables. Configure the database that holds your tables, and confirm that the login has a user in that database and VIEW ANY DEFINITION, without which SQL Server hides the indexes.
  • sqlserver.database.backup.count is missing: Confirm that the login has a user in msdb with SELECT permission on dbo.backupset. The Collector reads backup counts on its first collection and then every five minutes by default, so a new backup can take that long to appear in the count.
  • sqlserver.database.io_queue.time is always 0: SQL Server counts that time only while Resource Governor limits the I/O of a resource pool. An instance without such a limit reports 0. An edition without Resource Governor also reports 0, including Express and Web editions and Standard editions earlier than SQL Server 2025.
  • sqlserver.memory.grants.pending.count is always 0: The counter counts queries waiting for workspace memory, so it stays 0 while the instance has enough workspace memory, regardless of the edition.
  • sqlserver.replica.transaction_delay.time is 0 on a replica: The primary replica of an availability group reports the delay in committing to its secondary replicas. A secondary replica and an instance outside an availability group report 0.

Configuration reference

Configure one Microsoft SQL Server integration instance with the following settings. In Helm values, place these settings under collection.cxdot.integrations.sqlserver. In a Collector configuration file, place them under cxdot.integrations.sqlserver.

Optional settings

  • enabled Type: boolean. Optional. Default: true. Whether to run the SQL Server integration. If true, the Collector collects SQL Server metrics. If false, the Collector doesn’t run this integration.
  • username Type: string. Optional. SQL Server login name that the Collector uses for every configured target. Required when instances isn’t empty. Targets found through annotations use the credentials in their annotation instead.
  • password Type: string. Optional. Password for username. Required when instances isn’t empty. The Collector masks this value in diagnostic output, logs, and errors.
  • tls Type: object. Optional. Transport Layer Security (TLS) settings for connections to the configured targets. By default, the Collector encrypts the whole connection and verifies that a trusted certificate authority (CA) issued the server certificate for the target’s host name. Set ca_file or ca_pem to the CA that issued the server certificate when the system that runs the Collector doesn’t trust it, and set server_name_override when the certificate names a host other than the one in the endpoint. The certificate that SQL Server generates for itself when none is configured can’t pass verification: install a certificate issued for the server’s host name, or set insecure_skip_verify to true to accept any certificate. Set insecure to true to encrypt only the sign-in, the least that SQL Server accepts, without verifying the certificate. Client certificates aren’t supported. Targets found through annotations use the encryption settings in their annotation’s connection string instead.
  • tls.insecure Type: boolean. Optional. Default: false. Whether to encrypt only the sign-in. If true, the Collector encrypts the sign-in, the least that SQL Server accepts, without verifying the server certificate, and sends the rest of the connection unencrypted unless the server requires encryption. If false, the Collector encrypts the whole connection.
  • tls.insecure_skip_verify Type: boolean. Optional. Default: false. Whether to skip verification of the SQL Server certificate. If true, the connection is encrypted but isn’t authenticated and is vulnerable to man-in-the-middle attacks. If false, the Collector verifies the certificate.
  • tls.ca_file Type: string. Optional. Path to a PEM-encoded file with the certificate authority (CA) certificate that issued the SQL Server certificate. If omitted, the Collector verifies the certificate against the system certificate authority pool.
  • tls.ca_pem Type: string. Optional. PEM-encoded content of the certificate authority certificate that issued the SQL Server certificate, as an alternative to ca_file.
  • tls.include_system_ca_certs_pool Type: boolean. Optional. Default: false. Whether to trust the system certificate authority pool in addition to ca_file or ca_pem. If true, the Collector trusts both sources. If false, the Collector trusts only the configured certificate authority.
  • tls.server_name_override Type: string. Optional. Host name that the SQL Server certificate must name, when it differs from the host in the target’s endpoint.
  • tls.min_version Type: string. Optional. Default: 1.2. Minimum TLS version that the Collector accepts. Allowed values: 1.0, 1.1, 1.2, 1.3.
  • tls.max_version Type: string. Optional. Default: 1.3. Maximum TLS version that the Collector accepts. Allowed values: 1.0, 1.1, 1.2, 1.3.
  • tls.cipher_suites Type: array of string. Optional. Default: []. TLS cipher suites that the Collector can use with TLS 1.2 and earlier, in preference order. An empty list uses the secure defaults of the Go TLS library. For supported names, see https://go.dev/src/crypto/tls/cipher_suites.go.
  • database Type: string. Optional. Default: master. Database whose files and index usage the Collector reports for every configured target. Server-wide metrics and the state and backups of every database are reported whatever this is set to. For a database other than master, create a user for the SQL Server login in that database. Targets found through annotations use the database in their annotation instead.
  • collection_interval Type: duration. Optional. Default: 15s. How often the integration collects metrics from each SQL Server instance.
  • timeout Type: duration. Optional. Default: 10s. Maximum time allowed to collect metrics from a SQL Server instance during one interval. Set this value no greater than collection_interval so one collection doesn’t delay the next. A value of 0s removes the deadline, which lets an unresponsive target delay later collections and Collector shutdown.
  • backup_collection_interval Type: duration. Optional. Default: 300s. How often the integration reads the backup history of each configured instance. The backup history grows with every backup the server records, so it’s read less often than the other metrics. Between reads, the integration reports the most recent backup counts every collection_interval. A value no longer than collection_interval reads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds.
  • index_usage_collection_interval Type: duration. Optional. Default: 300s. How often the integration reads the index usage of database on each configured instance, which grows with the number of indexes. Between reads, the integration reports the most recent counts every collection_interval. A value no longer than collection_interval reads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds.
  • instances Type: array of object. Optional. Default: []. SQL Server instances to monitor. Each endpoint uses host:port format, such as sqlserver.default.svc:1433. When this list contains an instance, the integration monitors only the listed instances and disables discovery through annotations for this integration instance. When the list is empty, the integration monitors targets supplied through SQL Server annotations, each with the credentials in its annotation.
  • instances[].endpoint Type: string. Required. host:port of the target.