SQL Server Monitoring - Database configuration

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To get a quick overview of the databases on the instance and if they have configurations that should be changed.

Report

The report has 2 tables where the upper one are showing the databases on the instance, as well a few but critical and important settings, and where the lower table are showing the database scoped configurations.

When right-clicking on a row there is the possibility to go to the drill-through page “Database configuration history”.

The following settings well be highlighted with green, yellow or red background depending on their settings:

Setting	Description	Background color
State	Databases not online	Yellow
Standby	Databases in standby mode	Yellow
Read only	Databases in read only mode	Yellow
Collation	Databases not configured with the same collation as the instance	Yellow
Comp.	Databases running at a lower compatibility level than the instance	Yellow
Recovery	User databases not configured to be using Full recovery model. There can be situations where it is acceptable to have a production database in Simple or Bulk Logged but these situations are rare	Yellow
RCSI	Databases not configured to not have RCSI enabled	Yellow
Page verify	Databases not configured to be using Checksum as Page verify method	Red
Trustworthy	Database configured to have Trustworthy enabled	Yellow
Always On group	Database not on primary	Yellow
Auto close	Databases configured to be using Auto close	Red
Auto shrink	Databases configured to be using Auto shrink	Red
Auto update stats. async.	Databases configured to be using Auto update stats. async.	Yellow
Param. forced	Database configured to use forced parameterization	Yellow
Encrypted	Databases configured to be Encrypted	Green
Mixed page alloc.	Database configured to use mixed page allocation	Yellow
Delayed dura.	Databases configured to be using Delayed durability	Yellow

History – drill-through page

The Database configuration history page list all the configuration changes made on the database in question, standard and scoped.

Sources

This feature queries the DMVs sys.databases and sys.database_scoped_configurations which returns data for the databases on the instance.

Model

The model for the report uses the facts [Database scoped configurations], [Database state] and [Database state history] and the dimensions [Database] and [Database scoped configuration].

Service setup

The feature uses the service ‘fhsmSPDatabaseState’ which can be configured using the stored procedure fhsmSPControl.

Parameter

There is no parameter to be configured for this service.

Schedule

The default schedule for the feature is every hour (3.600 seconds).

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPDatabaseState',
	@Name = 'Database state',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPDatabaseState',
	@Name = 'Database state',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 3600,
	@FromTime = '00:00:00',
	@ToTime = '23:59:59',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmDatabaseState is 1.825 days (5 years).

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmDatabaseState',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmDatabaseState ',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 1825;

SQL Server Monitoring – Database configuration