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 assqlcmd -S tcp:<HOST>\<INSTANCE> -U <ADMIN_LOGIN> -C, and run the following query. The-Coption trusts the instance’s certificate only for this lookup. Current versions ofsqlcmdotherwise 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
1when 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
sysadminfixed server role, and run the following statements:Replacecxdotwith 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 theVIEW SERVER PERFORMANCE STATEpermission. On those versions, grantVIEW SERVER STATEinstead. Themsdbuser lets the Collector read backup history. SQL Server allows access to the backup history by default. TheGRANT SELECTondbo.backupsetpreserves 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 thanmaster, 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:-
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 namedmssql, and${k8s_secret:sqlserver-credentials/password}with thepasswordkey of thesqlserver-credentialsSecret. To allow the Collector to read the Secret, add the pod’s namespace toapiServer.metadataServer.secretAllowlistin 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, addtls: {insecure_skip_verify: true}to the annotation, or configure a custom certificate authority as the encryption step describes. If the annotation setstls.ca_file, you must also allow that path undercollection.cxdot.integrations.discovery.kubernetes.annotation_restrictions.allowed_collector_file_pathsin your Collector Helm values. -
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 sameusernameandpassword. When you configure static targets, this integration instance collects from exactly those targets and stops collecting from annotated targets. The Collector setsserver.addressto the exact host string inendpoint, including its case. Use the same spelling and capitalization that your dashboards and monitors use in their filters, such as thehostvalue 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 sameserver.address, so a query that sums byserver.addresscounts 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. -
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_verifytotrueto accept any certificate. If the system that runs the Collector doesn’t trust the certificate authority that issued the server certificate, settls.ca_fileto a file that holds the authority’s certificate. Mount the file into the cluster Collector from a Secret withclusterCollector.extraVolumesandclusterCollector.extraVolumeMounts. For example:Every static target uses the sametlssettings. 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. Settingtls.min_versionto"1.0", withtls.insecure_skip_verifyset totruewhen 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 settls.server_name_overrideto the name in the certificate. The separate instance limits the override to targets whose certificates carry that name. -
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 undersqlserverand collect from static targets undersqlserver/external: -
Optional: Report the files and index usage of a database other than
masterfor 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, keepmaster. 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: -
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. Thetimeoutsetting limits how long a collection from one target can run. On an instance with a largemsdbbackup 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, raisetimeout, or raisebackup_collection_intervalso that the read runs less often. For example: -
Optional: Disable the integration. For example:
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:- 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.
-
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_PASSWORDenvironment variable. On a host, the integration collects only from the instances ininstances. The endpoint can namelocalhostor the host’s own name. The Collector reports the host from the endpoint asserver.address. Forlocalhostand other loopback endpoints, it reports the host’s name asserver.address. It also adds the host’s name ashost.nameto every metric. When the Collector runs as a Windows service, setSQLSERVER_PASSWORDas 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 inusername. -
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_verifytotrueto accept any certificate. When the endpoint islocalhostand the certificate names the host, settls.server_name_overrideto the name in the certificate. For example:
Validate
To validate the Microsoft SQL Server integration, follow these steps:-
In Live Telemetry Analyzer, filter for
__name__=cxdot.integration.target.healthandcxdot.integration.name=sqlserver. Confirm that each target reports1. Identify each target by itsserver.addressandserver.portattributes and, for host deployments, byhost.name. A target whose endpoint islocalhostor another loopback address reports the Collector host’s name asserver.address. -
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.
- 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
0forcxdot.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 port1433. - A static target reports
0forcxdot.integration.target.healthalthough 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, settls.ca_fileto a file that contains the certificate authority’s certificate, or settls.insecure_skip_verifytotrue. - A target reports
1forcxdot.integration.target.healthbut few other metrics arrive: The health metric confirms only that the Collector can sign in. Grant the loginVIEW SERVER PERFORMANCE STATE, orVIEW SERVER STATEon SQL Server 2019 and earlier. - File I/O metrics, such as
sqlserver.database.ioandsqlserver.database.latency, are missing while other metrics arrive: Grant the loginVIEW 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.countis missing: The Collector reports index usage only for user tables in the configured database. The configured database ismasterunless you specify another, andmasterusually has no user tables. Configure the database that holds your tables, and confirm that the login has a user in that database andVIEW ANY DEFINITION, without which SQL Server hides the indexes.sqlserver.database.backup.countis missing: Confirm that the login has a user inmsdbwithSELECTpermission ondbo.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.timeis always0: SQL Server counts that time only while Resource Governor limits the I/O of a resource pool. An instance without such a limit reports0. An edition without Resource Governor also reports0, including Express and Web editions and Standard editions earlier than SQL Server 2025.sqlserver.memory.grants.pending.countis always0: The counter counts queries waiting for workspace memory, so it stays0while the instance has enough workspace memory, regardless of the edition.sqlserver.replica.transaction_delay.timeis0on 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 report0.
Configuration reference
Configure one Microsoft SQL Server integration instance with the following settings. In Helm values, place these settings undercollection.cxdot.integrations.sqlserver. In a Collector
configuration file, place them under cxdot.integrations.sqlserver.
Optional settings
-
enabledType: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. -
usernameType:string. Optional. SQL Server login name that the Collector uses for every configured target. Required wheninstancesisn’t empty. Targets found through annotations use the credentials in their annotation instead. -
passwordType:string. Optional. Password forusername. Required wheninstancesisn’t empty. The Collector masks this value in diagnostic output, logs, and errors. -
tlsType: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. Setca_fileorca_pemto the CA that issued the server certificate when the system that runs the Collector doesn’t trust it, and setserver_name_overridewhen 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 setinsecure_skip_verifytotrueto accept any certificate. Setinsecuretotrueto 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.insecureType: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_verifyType: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_fileType: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_pemType:string. Optional. PEM-encoded content of the certificate authority certificate that issued the SQL Server certificate, as an alternative toca_file. -
tls.include_system_ca_certs_poolType:boolean. Optional. Default:false. Whether to trust the system certificate authority pool in addition toca_fileorca_pem. If true, the Collector trusts both sources. If false, the Collector trusts only the configured certificate authority. -
tls.server_name_overrideType:string. Optional. Host name that the SQL Server certificate must name, when it differs from the host in the target’s endpoint. -
tls.min_versionType:string. Optional. Default:1.2. Minimum TLS version that the Collector accepts. Allowed values:1.0,1.1,1.2,1.3. -
tls.max_versionType:string. Optional. Default:1.3. Maximum TLS version that the Collector accepts. Allowed values:1.0,1.1,1.2,1.3. -
tls.cipher_suitesType: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. -
databaseType: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 thanmaster, create a user for the SQL Server login in that database. Targets found through annotations use the database in their annotation instead. -
collection_intervalType:duration. Optional. Default:15s. How often the integration collects metrics from each SQL Server instance. -
timeoutType:duration. Optional. Default:10s. Maximum time allowed to collect metrics from a SQL Server instance during one interval. Set this value no greater thancollection_intervalso one collection doesn’t delay the next. A value of0sremoves the deadline, which lets an unresponsive target delay later collections and Collector shutdown. -
backup_collection_intervalType: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 everycollection_interval. A value no longer thancollection_intervalreads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds. -
index_usage_collection_intervalType:duration. Optional. Default:300s. How often the integration reads the index usage ofdatabaseon each configured instance, which grows with the number of indexes. Between reads, the integration reports the most recent counts everycollection_interval. A value no longer thancollection_intervalreads it on every collection. Targets found through annotations use the interval in their annotation, or 300 seconds. -
instancesType:array of object. Optional. Default:[]. SQL Server instances to monitor. Eachendpointuseshost:portformat, such assqlserver.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[].endpointType:string. Required.host:portof the target.