Microsoft SQL Server

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, enable the protocol if it’s disabled, confirm that Listen All on the Protocol tab is Yes, which makes the IPAll settings apply, and on the IP Addresses tab under IPAll, 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 for this lookup only, because current versions of sqlcmd reject the certificate that SQL Server generates for itself.
  SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID;
  
• 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:
  New-NetFirewallRule -DisplayName 'SQL Server 1433' -Direction Inbound -Protocol TCP -LocalPort 1433 -Action Allow
  
• 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 it. To see which mode an instance uses, run the following query, which returns 1 when the instance allows only Windows authentication:
  SELECT SERVERPROPERTY('IsIntegratedSecurityOnly');
  
  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:
  USE master;
  IF SUSER_ID('cxdot') IS NULL CREATE LOGIN cxdot WITH PASSWORD = '<PASSWORD>';
  GRANT VIEW SERVER PERFORMANCE STATE TO cxdot;
  GRANT VIEW ANY DEFINITION TO cxdot;
  
  USE msdb;
  IF DATABASE_PRINCIPAL_ID('cxdot') IS NULL CREATE USER cxdot FOR LOGIN cxdot;
  GRANT SELECT ON dbo.backupset TO cxdot;
  
  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 that access by default, and the user keeps it working on an instance that removes the default. Give the Collector a login of its own rather than one that another tool, such as the Datadog Agent, also signs in with. The Collector signs in at every collection, so a wrong or rotated password fails several sign-ins a minute on each instance. For a login created with its password policy enforced (CHECK_POLICY = ON), that locks the login, and with it every tool that shares it. 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:
  USE <DATABASE>;
  IF DATABASE_PRINCIPAL_ID('cxdot') IS NULL CREATE USER cxdot FOR LOGIN cxdot;
  
  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. To configure the Microsoft SQL Server integration, follow these steps:

1. Annotate the pods or Services that run SQL Server. The CXDOT Collector collects from pods and Services that carry autodiscovery annotations for SQL Server. Each annotation carries the endpoint and credentials for its own target, so different instances can use different logins. An annotated target also takes its database and its backup and index usage intervals from its annotation, and encrypts its connection as its annotation specifies, never as the tls setting in your Helm values. Metrics from discovered pods arrive with that pod’s Kubernetes metadata attached. For more information, see autodiscovery.
2. Optional: Configure static targets instead of annotated targets, such as SQL Server instances running outside your Kubernetes cluster. 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:
   clusterCollector:
     extraEnv:
       - name: SQLSERVER_PASSWORD
         valueFrom:
           secretKeyRef:
             name: sqlserver-credentials
             key: password
   
   config:
     integrations:
       sqlserver:
         instances:
           - endpoint: mssql.example.com:1433
         username: cxdot
         password: "${env:SQLSERVER_PASSWORD}"
   
   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 reports each target’s server.address as you spell the host in endpoint, including its case. Spell it as the dashboards and monitors you bring over filter on it, 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:
   config:
     integrations:
       sqlserver:
         instances:
           - endpoint: mssql.example.com:1433
         username: cxdot
         password: "${env:SQLSERVER_PASSWORD}"
         tls:
           ca_file: /etc/sqlserver-ca/ca.crt
   
   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 name a host by an alias or by an availability group listener name while the server certificate names the host itself. For such a target, set tls.server_name_override to the name in the certificate, in an instance of the integration of its own, so that the override applies only to the 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:
   config:
     integrations:
       sqlserver: {}
       sqlserver/external:
         instances:
           - endpoint: mssql.example.com:1433
         username: cxdot
         password: "${env:SQLSERVER_PASSWORD}"
   
5. Optional: Report the files and index usage of a database other than master for static targets. The Collector reports server-wide metrics, and the state and backup count of every database, whatever this setting is. 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, and a database the replica doesn’t allow reading fails the switch, so the files and index usage are missing and the Collector logs an error. For example:
   config:
     integrations:
       sqlserver:
         database: sales
   
6. Optional: Change how often the Collector reads backup counts and index usage from static targets. Both run on the first collection and then every five minutes by default, independently of collection_interval, because the cost of each read grows with the backup history and the number of indexes. Between reads, the Collector reports the most recent values at every collection. A read that fails runs again at the next interval. The timeout setting bounds one collection of a target as a whole. On an instance with a large msdb backup history, the backup read can take most of it, and the metrics the Collector reads after it are missing from that collection. On such an instance, raise timeout, or raise backup_collection_interval so that the read runs less often. For example:
   config:
     integrations:
       sqlserver:
         backup_collection_interval: 15m
         index_usage_collection_interval: 10m
   
7. Optional: Disable the integration. For example:
   config:
     integrations:
       sqlserver:
         enabled: false
   

​

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, identified by its server.address and server.port attributes. A target dialed at 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:
   sum by ("server.address", "server.port") (rate({"sqlserver.batch.request.count"}[$__rate_interval]))
   
   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 the certificate authority that issued it, 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 the user tables of the configured database, which is master unless you configure another, and master usually has none. 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, so an instance with no such limit reports 0, as does an edition without Resource Governor, which Express and Web editions lack, and Standard edition before 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, whatever 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 config.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 statically 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 statically 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 statically configured target. Server-wide metrics and the state and backups of every database are reported whatever this is set to. 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. A value of 0s disables the timeout.
• backup_collection_interval Type: duration. Optional. Default: 300s. How often the integration reads the backup history of each statically 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 statically 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.