Creating a Template SQL Sentry Database

Are you an MSP looking to onboard a new customer and need to have your custom alerts, settings, and configurations set up in their SentryOne environment? Are you a SentryOne user splitting your environment between countries or data centers but need to have consistent configurations across your installations?

If you have ever had to set up a second installation for SentryOne, you know that the settings and configurations you have modified do not have an easy way to be carried over from your primary environment. This blog post is intended to help you prepare for the need to set up a new SentryOne environment with your configurations intact.

Each environment requires a SentryOne repository database to store data. This database is created during the first installation; however, if you create a template database, you can use it in place of a brand-new database.

Alerts and Alerting Thresholds

The first configuration I will look at is alerting and alert thresholds. Although Advisory Conditions can be exported and imported, their rulesets are not carried over. This applies to General, Failsafe, and Audit conditions, as well as their Condition Settings.

Ensure all conditions are added to the Conditions pane with their appropriate action (as shown in the screenshot below). This is also a good time to remove conditions that are not used by default but added on a case-by-case basis.

Creating a Template SentryOne Database_Image 1

The following is an example of the Configured Actions List report (Reports > General > Configured Actions List) to see all the conditions you have configured and their associated actions.

Creating a Template SentryOne Database_Image 2

For conditions that require a Response Ruleset, ensure you have created and applied those to the respective conditions.

Creating a Template SentryOne Database_Image 3

For more information about Response Rulesets, please see the related SentryOne documentation.

Further information about alert tuning can be found in the following blog posts:

Settings for Collection and Retention

Other settings you might consider having default values for are collection and retention values. You might want to collect queries that are running faster than the default 5 seconds or, in some cases, this might be too sensitive for your needs. Changing your collection settings in the template database will ensure you are gathering the exact data you expected from the moment you add a target.

Creating a Template SentryOne Database_Image 4

Any category that ends in Source (e.g., Blocking SQL Source, Top SQL Source) will have a collection threshold. Those without source in the name (e.g., Blocking, Top SQL, Agent Jobs, SSRS reports) will have Max Runtime thresholds.

Learn more about configuring settings here.

You might also want to configure the retention values for your template database. The default retention for Top SQL, blocking, deadlocks, and tempdb data is 15 days. You can adjust this value on the Storage tab (Configuration > Global Settings > Storage), which is shown below.

Creating a Template SentryOne Database_Image 5

Users/Groups

The last configuration I want to mention is users/groups. Setting up a default distribution account that will be used for email alerts can help get alerting online much faster.

Creating a Template SentryOne Database_Image 6

Another method you might consider is creating groups that can be tied to alerts and users can be quickly added or removed from these groups rather than adding or removing those users from each alert.

In the example below, I have added myself to the On-Call group within SentryOne.

Creating a Template SentryOne Database_Image 7

Tie the group to the condition, rather than a user, as shown below.

Creating a Template SentryOne Database_Image 8

Going forward, anyone you add or remove from this group will receive the notification for a Send Email action as long as their email is specified and your SMTP settings are configured.

Learn more about Users and Groups here.

Implementation

Once you have configured these settings, take a full backup of the database. Prior to installing SentryOne on a customer environment, additional data center, or domain, restore this template database to the selected instance.

The following are scripts you can use to backup and restore the SentryOne database:

BACKUP DATABASE [SentryOne] TO DISK = N'C:\Backups\SentryOne.bak'

RESTORE DATABASE [SentryOne] FROM DISK = N'C:\Backups\SentryOne.bak'

Once restored, you will be able to connect to the new installation by accessing the Repository Connection Management menu.

If the version of SentryOne you are installing is the same as your template database, you will see a message letting you know you will be using an existing database. If the version being installed is a newer version, the installer will upgrade the template database, keeping your default settings intact.

THWACK - Symbolize TM, R, and C