Edit

sys.dm_os_cluster_properties (Transact-SQL)

Applies to: SQL Server

Returns one row with the current settings for the SQL Server cluster resource properties identified in this article. If you run this view on a stand-alone instance of SQL Server, it returns no data.

You can use these properties to set values that affect failure detection, failure response time, and the logging for monitoring the health status of the SQL Server failover cluster instance.

Column name Data type Nullable Description
VerboseLogging bigint Yes The logging level for the SQL Server failover cluster. Turn on verbose logging to provide extra details in the error logs for troubleshooting. One of the following values:

- 0: Logging is turned off (default)
- 1: Errors only
- 2: Errors and warnings

For more information, see ALTER SERVER CONFIGURATION.
SqlDumperDumpFlags bigint Yes SQLDumper dump flags determine the type of dump files generated by SQL Server. The default setting is 0.
SqlDumperDumpPath nvarchar(260) No The location where the SQLDumper utility generates the dump files.
SqlDumperDumpTimeOut bigint Yes The timeout value in milliseconds for the SQLDumper utility to generate a dump if SQL Server fails. The default value is 0.
FailureConditionLevel bigint Yes Sets the conditions under which the SQL Server failover cluster should fail or restart. The default value is 3. For a detailed explanation or to change the property settings, see Configure FailureConditionLevel property settings.
HealthCheckTimeout bigint Yes The timeout value for how long the SQL Server Database Engine resource DLL should wait for the server health information before it considers the instance of SQL Server as unresponsive. The timeout value is expressed in milliseconds. Default is 60000 (60,000).

For more information or to change this property setting, see Configure HealthCheckTimeout Property Settings.
ClusterConnectionOptions nvarchar(4000) Yes For more information about these options, see the Cluster connection options section.

Cluster connection options

Key Supported Values Description
Encrypt Mandatory, Strict, Optional Specifies how encryption to the availability group is enforced. If the server doesn't support encryption, the connection fails. If you set encryption to Mandatory, then TrustServerCertificate must be set to yes. If you set encryption to Strict, then TrustServerCertificate is ignored.

Note: This key value pair is required.
HostNameInCertificate Replica name or AG listener name Specifies the replica name or availability group listener name in the certificate that's used for encryption. This value must match the value in the Subject Alternative Name of the certificate. If the server name is listed in the certificate, then you can omit the HostNameInCertificate key-value pair. If the server name isn't listed in the certificate, then you must specify the HostNameInCertificate key-value pair with the server name.

Note: This key value pair is optional.
TrustServerCertificate Yes, No Set to yes to specify that the driver doesn't validate the server TLS/SSL certificate. If no, the driver validates the certificate. For more information, review TDS 8.0.

Note: This key value pair is optional.
ServerCertificate Path to your certificate If you don't want to use HostNameInCertificate, you can pass the path to your certificate. The cluster service account must have permission to read the certificate from the given location.

Note: This key value pair is optional.
CLUSTER_CONNECTION_OPTIONS Empty string ('') Clears the existing configuration and reverts to default encryption settings of Encrypt=Mandatory and TrustServerCertificate=Yes.

For more information, see CREATE AVAILABILITY GROUP and ALTER AVAILABILITY GROUP.

Permissions

SQL Server 2019 (15.x) and earlier versions require VIEW SERVER STATE permissions on the SQL Server failover cluster instance.

SQL Server 2022 (16.x) and later versions require VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

The following example uses sys.dm_os_cluster_properties to return the property settings for the SQL Server failover cluster resource.

SELECT VerboseLogging,
       SqlDumperDumpFlags,
       SqlDumperDumpPath,
       SqlDumperDumpTimeOut,
       FailureConditionLevel,
       HealthCheckTimeout
FROM sys.dm_os_cluster_properties;

Here's the result set.

VerboseLogging SqlDumperDumpFlags SqlDumperDumpPath SqlDumperDumpTimeOut FailureConditionLevel HealthCheckTimeout
0 0 NULL 0 3 60000
  • Last updated on