How to configure SQL CoPilot to monitor your instances.
SQL CoPilot now comes with monitoring added at no extra cost!
This article shows you how to quickly get it configured. You can be monitoring your instances in a couple of minutes.
You should already have imported your SQL Server instances into SQL CoPilot. If not, go to Getting Started to find out how to do this.
Before You Start
SQL CoPilot is a desktop application designed to assist the busy DBA with investigations into perfomance and other issues and to generate estate-wide reports of best practice, system health and more.
SQL CoPilot is not a dedicated monitoring tool - this feature has been added on request from users, but is not the primary use case for the application. Whereas a dedicated tool runs on a server and monitors servers 24/7, SQL CoPilot performs its monitoring as a background task in the application. You MUST keep the application running if you want to receive alerts for any issues.
Also, the monitoring is paused by default when SQL CoPilot is started. Use the button at the top left of the screen to start it.
If you are impatient to get started, you can use the quick setup function. You can always refine things afterwards, but quick setup is a good way to get started.
Go to the Configuration -> Setup Monitoring screen, drop down the Tags list and select one or more tags to monitor - see below:
Click Apply and the tags you selected are associated with all alerts.
You can click on any of the alerts to see the default severity and thresholds that have been used.
You don't need to do anything else. Click the button in the toolbar at top left and the monitoring will start.
If you want to change the severity or thresholds for any alerts or add more tags, read on.
Change the Defaults
Lets change the severity of the Low Disk Space alert to Critical and add another Warning when free space is below 20%.
Click on the Low Disk Space alert, then click on the row underneath showing the tag, severity and threshold. Change the severity to Critical and click Save.
Now click the Add button at the bottom of the screen. In the dialog select the tag, select a severity of Warning, and change the threshold to 20. Click Add to close the dialog. You have now setup the alert so that a Critical alert is raised when disk space drops below 5% free, but only a warning when it drops below 20%. You can add similar alerts with different thresholds for other tags.
If you want to receive emails when an alert is triggered, goto the Configuration -> Email Configuration screen, enter your SMTP server and the From and To email addresses and click Update. You will need an SMTP server in your company that does not require authentication. You may need your system administrators to add your PC to an approved list for it to work.
Now go to Configuration -> Setup Monitoring and go through each alert to be emailed, ticking the "Send Email" radio button.
Please note the following before using email
In addition to the points in "Before You Start" at the top of this page, please consider the following:
- If multiple users run SQL CoPilot with monitoring enabled and send emails to a distribution group, people in that group will receive duplicate emails;
- If you configure a single instance of SQL CoPilot to monitor your servers and email alerts to multiple recipients, you must ensure that all recipients are licensed users.
Monitoring in Action
So what happens when the monitoring spots an issue?
Two things happen simultaneously. A popup message shows at the top right of the screen. This disappears after 5 seconds. Also, colored badges appear in the toolbar (one for each severity), with a number showing how many alerts have been raised. Click on a badge to view all the alerts.
There are 2 ways to view the alerts. In the Monitoring menu you have the option of an Alert Dashboard and an Alert List. The dashboard displays all instances, color-coded by severity. The list shows all alerts. Clicking on an alert opens a tab for the instance and takes you to the most relevant screen.
How are Alerts Cleared?
The simple answer is - when they are no longer active!
There is no way currently to clear an alert, although it is now possible to acknowledge alerts to prevent them from appearing in the list.
Some alerts have a threshold, such as the Failed Job alert, that defines how far back in job history to go, while others, like Database Offline, will be active until the database is no longer offline.
If you get an alert because of a high severity error in the log and it doesn't go away, there are a couple of possible reasons for that. Either the error keeps occurring, or you are not cycling the error log nightly. Both of these need to be looked at.
Over To You
Start using the monitoring functionality. You will probably think of things that could be improved, or of some alerts you would like added. If so, please contact us. We are always looking for new ideas.