1.18 Configuration Metrics

Configuration metrics consist of the following categories:

  • SQL Server Configuration

  • Registry Setting Configuration

  • Database Setting Configuration

1.18.1 SQL Server Configuration Metrics

The metrics in this category represent a Microsoft SQL Server installation. The metrics contain details of the product and version of the Microsoft SQL Server instance.

  • Table Name — MGMT_EMX_MSSQL_SQLSERVER

  • View Name — MGMT_EMX_MSSQL_SQLSERVER_VIEW

Default Collection Interval — Every 7 Days

Table 1-20 SQL Server Configuration Metrics

Metric Description

Server Name

Name of the SQL Server.

Clustered

Whether the server belongs to a cluster.

Package

Product installed.

  • 0 — Unknown

  • 1 — Office

Product

Installed product.

Version String

Installed version.

Operatingsystem Details

Operating system on which the installation is done.

Version Name

Installed version including revision.

HADR Enabled

Whether AlwaysOn high-availability disaster recovery is enabled.

Database Service Pack

Installed SQL Server service pack

Edition

Installed SQL Server edition.

1.18.2 Registry Setting Configuration Metrics

The metrics in this category contain the installation and run-time parameters of the SQL Server stored in the registry.

  • Table Name — MGMT_EMX_MSSQL_REGSETTING

  • View Name — MGMT_EMX_MSSQL_REGSETTING_VIEW

Default Collection Interval — Every 7 Days

Table 1-21 Registry Setting Configuration Metrics

Metric Description

Agent Log File

Path and file name for the Agent log.

Backup Directory

Location of the backup files directory.

Case Sensitive

Comparison method for multi-byte character data is either case-sensitive or not.

Error Log Path

Operating system path and file name to be used for the SQL Server error log.

Master DB Path

The full path and file name of the operating system file containing the master database.

NT Event Logging

Whether the SQL Server uses the Windows NT application log. If TRUE, the SQL Server sends all events to the Windows NT application log and the SQL Server error log. If FALSE, the SQL Server sends events only to the SQL Server error log.

Number of Processors

Number of CPUs available to the SQL Server on the server.

Perf Mon Mode

Operating system path and file name to be used for the SQL Server error log. Windows NT Performance Monitor polling behavior when the monitor is launched.

  • 0 — Continuous

  • 1 — On demand

Registered Organization

Company name supplied by the installer.

Registered Owner

User name supplied by the installer.

Replication Installed

TRUE when components supporting replication are installed.

RPC Encrypt

Whether RPC encryption is enabled.

SNMP

Whether Simple Network Management Protocol (SNMP) is installed on an instance of the SQL Server.

SNMP Current Version

Version of Simple Management Protocol (SNMP) currently installed on an instance of the SQL Server.

Sort Order

Character set used and ordering applied.

SQL Data Root

Default operating system directory implementing storage for SQL Server system user-defined databases.

TCP Port

TCP/IP Sockets Net-Libraries port number on an instance of the SQL Server.

1.18.3 Database Setting Configuration Metrics

The metrics in this category contain the settings for a database. These settings control the access to and the behavior of the database.

  • Table Name — MGMT_EMX_MSSQL_DBSETTING

  • View Name — MGMT_EMX_MSSQL_DBSETTING_VIEW

Default Collection Interval — Every 7 Days

Table 1-22 Database Setting Configuration Metrics

Metric Description

Database Name (key column)

Database name.

Offline

Whether the database is online. Also, whether the database is unavailable, or is being made unavailable, for use by authorized users.

Recovery Type

Whether the comparison method for multi-byte character data is case-sensitive or not. Type of recovery model that a database will use:

  • Value — 0

    Description — Simple

    Explanation — The database can be recovered only to the last full database backup or last differential backup.

  • Value — 1

    Description — Bulk Logged

    Explanation — Logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery.

  • Value — 2

    Description — Full

    Explanation — Database backups and transaction log backups provide full recoverability from media failure. All operations are fully logged, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data.

  • Value — 3

    Description — Unknown

    Explanation — The recovery type is not known.

AutoClose

Whether the database is closed and its resources are freed when no user connection accesses the database.

AutoCreateStatistics

Whether the optimizer directs automatic creation of supporting data statistics as required.

AutoShrink

Whether operating system files maintaining table and index data are evaluated for downward resizing when the server periodically checks for unused space.

AutoUpdateStatistics

Whether the optimizer directs the automatic rebuilding of statistics.

CursorCloseOnCommit

Whether cursors are closed when a transaction is completed.

DataSpaceUsage

Amount of space in use and reserved for use of data in megabytes.

IndexSpaceUsage

Amount of space for the index in megabytes.

DB Owner UseOnly

Whether only users with the database ownership privilege can access the database.

SingleUser Mode

Whether only one user can access the database at a given time.

ReadOnly

Whether the database is read-only.

DefaultCursor

Whether cursors declared in a batch are created with local scope.

SelectIntoBulkCopy

Whether non-logged operations are allowed.

TruncateLogOnChekpoint

Whether the SQL Server removes log entries referencing committed transactions when activity on the databases forces a dirty page write.

Member of an Availability Group

Whether the database is a member of an AlwaysOn availability group.

1.18.4 HADR Availability Group Databases Metrics

The metrics in this category provide information about which databases are members of which AlwaysOn high-availability disaster recovery availability groups.

Default Collection Interval — Every 30 Minutes]

Table 1-23 HADR Availability Group Databases Metrics

Metric Description

Database ID

Database unique ID.

Group ID

Availability group unique ID.

Database Name

Database name.

Group Name

Availability group name.