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.
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.
For conditions that require a Response Ruleset, ensure you have created and applied those to the respective conditions.
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:
- SQL Server Alert Tuning Basics with SentryOne
- SentryOne Tips & Tricks: Common Alert Tuning Examples
- SentryOne Tips & Tricks: Tuning Advisory Conditions
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.
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.
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.
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.
Tie the group to the condition, rather than a user, as shown below.
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: