EKG - Fixing Sitecore's Heartbeat


There are many mechanisms for monitoring the health of Sitecore, but Heartbeat.aspx is the simplest. It is easy to monitor with third party tools and requires no knowledge of technological underpinnings. With the release of Sitecore 9, however, things have gotten a bit more complicated. Heartbeat checks every database, even unused ones. this can lead to false negatives and problems monitoring site health in different environments. Fortunately, there is a solution!

Sitecore allows you to specify a list of excluded connections, so you can check only the required databases for a given environment. However, Sitecore does not provide a simple tool to get the status of every connection. Enter EKG: A tool that checks every connection, shows you the status of each database, and provides a pre-formatted Sitecore.Services.Heartbeat.ExcludeConnection config section to paste into a patch file in your App_Config.

 First, lets look at the important parts of the code. This is implemented as a legacy Forms page but should be easy enough to follow if you have only had exposure to MVC. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
//Loop through all of the Sitecore connection strings. 
foreach (ConnectionStringSettings connectionString in ConfigurationManager.ConnectionStrings)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(connectionString.ConnectionString))
        {
            SqlCommand sqlCommand1 = new SqlCommand("select * from sys.tables where 1=2", connection);
            sqlCommand1.CommandTimeout = 1;
            using (SqlCommand sqlCommand2 = sqlCommand1)
            {
                connection.Open();
                sqlCommand2.ExecuteNonQuery();
                connectionStatus.Add(connectionString.Name, true);
            }
        }
    }
    catch
    {
        connectionStatus.Add(connectionString.Name, false);
    }
}

This code iterates through the available connection strings, checks each one, and returns a database status report:

In addition, it returns a formatted XML section that can be placed into a patch file in your App_Config folder to exclude the failing databases from the Heartbeat check:

1
2
3
<settings>
<setting name="Sitecore.Services.Heartbeat.ExcludeConnection" value="LocalSqlServer|sessions|xconnect.collection|xconnect.collection.certificate|xdb.marketingautomation.operations.client| xdb.marketingautomation.operations.client.certificate|xdb.marketingautomation.reporting.client| xdb.marketingautomation.reporting.client.certificate|xdb.referencedata.client|xdb.referencedata.client.certificate| solr.search|PackageManagementServiceUrl|EXM.CryptographicKey|EXM.AuthenticationKey" />
settings>

 

There are a couple of caveats:

  1. This will need to be set for each environment.
  2. You need some understanding of what each database does, and whether it is supposed to be excluded.
  3. You will need to remember to change the Exclude Connection config if you make any changes to your environment. Failing to do so will give you false positives.

You can download the EKG.aspx source code here. It should be placed in the /sitecore/service folder. 

SEARCH ARTICLES