How to create custom health checks in SQL Server instances

In various scenarios, DBAs need to write T-SQL queries to check numerous states of their respected SQL Server instances. These queries can be run separately or with a fairly large amount of time invested in optimizing and modifying each one to run as a single power-query that will check a variety of conditions. On top of the time invested in creating such a script, additional time will be required to modify it and add new checks which can cause the whole script to break. To address this concern comes ApexSQL Manage. In addition to default rules incorporated in the tool for SQL Server best practices and health checks and predefined FixSQL queries, there is also an option to create custom rules with user-defined FixSQL that can be run if the rule result indicates an issue. Creating custom rules allows for a variety of health check scenarios that can be tailored to meet specific user environments. The main benefit of this kind of SQL Server instance management is centralization for each instance. Health check rules can be applied on multiple instances and results can be shown for all or individual SQL Server instances:

Behind the scenes

The majority of pre-written rules in ApexSQL Manage are using SQL Server Management Objects (SMO) to gather information on the SQL Server instance. SMO is a collection of objects that are designed by Microsoft for programmatic management of SQL Server instance. SMO is used to build customized SQL Server management applications and is a cornerstone for ApexSQL Manage. Properties gathered on the SQL Server state through SMO are used by the tool to cross-reference the received information with the SQL Server best practices. To the receiving end the user is shown the end result in ApexSQL Manage as Pass or Fail, depending on the SQL Server state, as well as advice on how to remedy the detected issue or a T-SQL script which can be executed from the tool.

Detailed information on SQL Server Management Objects, how it works, and which classes and properties can be used to further customize rules in ApexSQL Manage can be found in the following Microsoft documentation:

• Overview (SMO)
• SMO Object Model Diagram
• Information Class
• SERVERPROPERTY (Transact-SQL)

How to write custom rules

To write custom rules in ApexSQL Manage, go to the Health tab and click the Manage button. This will open the Rule management window:

Once here, click on the Add button to open the Create new rule window.

In the Details tab, several fields require user side input.

• Name – The name of the rule
• Description – Brief or detailed description of what the purpose of the rule is
• Object type – Object type can be set to Server or Database. From the coding standpoint, it doesn’t matter whether the Server or Database is selected as Object type. The only reason to use Database as Object type is clearer grouping of rules. It is recommended to always use the Server as the Object type.
• Category – Category this rule is best suited for. Detailed information on available categories can be found in How to perform health checks on SQL Server instances with ApexSQL Manage article
• Language – Language in which the rule was written. Supported languages are C# and Visual Basic

For this example, a custom rule will be created to check if the recovery model for all databases on a SQL Server instance is set to FULL recovery.

Object type is set to Server, Category is set to Databases and selected language is C#:

Next, proceed to the Condition tab. This is where custom rules can be specified e.g. the code that will check various user-specified conditions. In this example, a condition to check if the Recovery model is set to FULL will be used. Details regarding this condition can be found in Example 1:

Once the condition has been set, go to the Violation tab. This tab provides options for specifying the level of Severity and Advice for the rule.

Severity can be set to:

• High
• Medium
• Low

Since these are all user-specified areas, it is up to the user to determine the severity level and appropriate advice for the detected issue. For this example, the Severity will be set to High and Advice will be to Set database to Full Recovery model:

When the severity level and advice have been specified, go to the SQL Server tab. This tab is used to specify the version of the SQL Server instance against which the rule will be executed. The outcome when the rule is executed is depended on the type of rule and differences between different versions of SQL Servers. If a custom rule is executed without specifying any SQL Server version, the outcome will show no results. This also applies to the scenario when the rule is executed without specifying the correct SQL Server version. For this example, all SQL Server versions will be checked. Click on the OK button to create the rule:

Once the rule has been created, it will be shown in the appropriate category. All user-created rules are colored dark red for easier management:

Clarification and examples of custom rules

As mentioned previously, ApexSQL Manage is using SQL Server Management Objects (SMO) for rule execution. All rules are executed based on ActiveObject to which an SMO object is attributed.

ActiveObject is what governs the rule execution and it can be:

• Server – when the rule applies to the instance level
• Database – when the rule applies to the database level

Quick tip: ApexSQL Manage is a server management tool and as such the primary focus of the tool is at the server level. Executing custom rules for single or specific databases is not recommended.

The following examples will provide detailed information on how a condition can be written and executed in ApexSQL Manage.

Example 1:

When the rule is executed it goes through a verification process to check if certain conditions (specified by the user) have been met.

In this example, the rule is going to use SMO to check if the recovery model of a single database is set to full.

DatabaseOptions is a SQL Server Management Object class and RecoveryModel is the property of the class. This property is stored in ActiveObject and is used by the tool to check the condition:

// If recovery model is not Full - Best practice is to set RecoveryModel to Full
if(ActiveObject.DatabaseOptions.RecoveryModel != RecoveryModel.Full)

It is important to note, the aforementioned code is written as an example only and in this scenario, the rule would apply to only one database, but ApexSQL Manage is primarily intended to work with the whole SQL Server instance and as such it is not possible to select only one database.

In a scenario where the condition is not met (e.g. not in accordance with best practice) RaiseViolation (); can be initiated as a signal that a discrepancy with best practice has been found.

As a response to the discrepancy, it is possible to add a FixSQL – which is a query that will be offered to the user as an option to bring the situation in line with best practice.

For this particular scenario, the whole code would go as following:

if(ActiveObject.DatabaseOptions.RecoveryModel != RecoveryModel.Full)
    {
        FixSQL = string.Format("ALTER DATABASE [{0}] SET RECOVERY FULL", ActiveObject.Name);
        RaiseViolation();
    }

Running this code will only give results for the first database on the selected SQL Server instance. To encompass all databases on a SQL Server instance it is required to expand this code further. The best approach is to check if all databases meet the requirements e.g. if the database recovery model is set to FULL. Condition for a custom rule that will apply to all databases is as follows:

List<string> databasesRecovery = new List<string>();

        bool errorExists = false;
        
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        
        foreach (var database in ActiveObject.Server.Databases.Where(x => x.DatabaseOptions.RecoveryModel != RecoveryModel.Full))
        {
            databasesRecovery.Add(database.Name);
        }
        
        if (databasesRecovery.Count() > 0)
        {
            foreach (string database in databasesRecovery)
            {
                sb.AppendFormat(string.Format(@"ALTER DATABASE [{0}] SET RECOVERY FULL;", database));
                sb.AppendLine("\nGO\n");
                errorExists = true;
            }
        
            if (errorExists)
            {
                FixSQL = sb.ToString();
                RaiseViolation();
            }
        }

FixSQL that would be presented to the user in this scenario would look like this:

ALTER DATABASE [AdventureWorksDW] SET RECOVERY FULL;
GO
ALTER DATABASE [master] SET RECOVERY FULL;
GO
ALTER DATABASE [msdb] SET RECOVERY FULL;
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL;
GO
ALTER DATABASE [WideWorldImporters] SET RECOVERY FULL;
GO

By adding this piece of code:

...
    if(!database.IsSystemObject)
    {
    databasesRecovery.Add(database.Name);
    }
...

Instead of:

databasesRecovery.Add(database.Name);

FixSQL would not contain system databases and would read as follows:

ALTER DATABASE [AdventureWorksDW] SET RECOVERY FULL;
GO
ALTER DATABASE [WideWorldImporters] SET RECOVERY FULL;
GO

In a scenario when Database is selected as the Object type, the first database is taken from the SQL Server instance to read the server data.

ActiveObject would be the database and the list of all databases on the server would be obtained as ActiveObject.Server.Databases

In a scenario when Server is selected as the Object type, a list of all databases on the SQL Server instance would be obtained as ActiveObject.Server.Databases (as in the scenario with Database as Object type) or shorten ActiveObject.Databases. ActiveObject.Server is an SMO object that is supplemented with additional properties from ApexSQL Manage. To reduce the possibility of error in the code it is recommended to use ActiveObject.Server.Databases.

Example 2:

There are also simple SQL Server instance checks that can only offer advice instead of a specific query to fix the detected issue because it is not possible to write a query for these types of scenarios. Scenarios, where it is not possible to write a query are the ones that require intervention from the user side, for example: hardware limitations, weak passwords, locations of filegroups, etc.

In this example, the rule is going to check the response time of TempDB. If the response time is under 20ms, the rule result is considered as Pass. If the response time is greater, the advice is to check the performance for selected SQL Server instance:

//This rule demonstrates how to check whether the response time (writing and reading) TempDB is no greater than 20 ms
var Time = ActiveObject.Server.Databases.Tempdb.AvgResponseTime.Values.ToList();
var writeTime = Time[0];
var readTime = Time[1];
if(writeTime > 20 || readTime > 20)
{
FixSQL = "Response time for TempDB is greater than 20 ms, check SQL Server performance";
    RaiseViolation();
}

In this scenario, FixSQL serves to advise the user on how to correct this issue.

Example 3:

Not all rules are written to use SMO properties. Due to the limitations of the data that can be obtained from the SMO, several special queries are used to obtain the desired data. This feature is not enabled for users, but these checks can be found in default rules.

For example, the rule for checking free hard drive space is using the property HddSpace to collect the data on total and free space on hard-drive.

public List<string> HddSpace
    {
        get
        {
            using (SqlConnection cnn = new SqlConnection(ConnectionString))
            {
                string querry = @"SELECT distinct(volume_mount_point), 
  					total_bytes/1048576 as Size_in_MB, 
  					available_bytes/1048576 as Free_in_MB
FROM sys.master_files AS f CROSS APPLY 
  					sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576, 
  					available_bytes/1048576 order by 1";

                cnn.Open();
                SqlCommand command = new SqlCommand(querry, cnn);
                SqlDataReader reader = command.ExecuteReader();
                List<string> list = new List<string>();

                while (reader.Read())
                {
                    list.Add(reader[1].ToString());
                    list.Add(reader[2].ToString());
                }

                return list;
            }
        }
    }

The rule checks if there is less than 20% free space on the HDD. If it is less than 20%, the rule is FAIL and advice is given:

long hddSpace = Convert.ToInt64(ActiveObject.Server.freeHD [0]);
    long hddFreeSpace = Convert.ToInt64(ActiveObject.Server.freeHD [1]);
    
    if(((hddFreeSpace * 100) / hddSpace) < 20)
    {
    FixSQL = "Free disk space is approaching the 20%, after it may affect SQL Server performance";
    RaiseViolation();
    }

Summary

In summary, instead of executing a large number of scripts multiple times to check the state of SQL Server instances, custom rules written in ApexSQL Manage provide a faster and centralized approach of SQL Server management where all managed instances can be monitored and detected issues addressed from a single point.