My name is Richard Douglas. And I am a subject matter expert for SQL Server. My job today is to help you make configuring Spotlight on SQL Server a super easy task. Having a 24/7 monitoring tool is a great asset to any DBA. And to make the best use of that tool, you need to ensure it knows what to do while you're not looking at the pretty spinny things.
In a previous video in the series, I walked you through the process of how you can configure the thresholds of over 140 alarms that are available out of the box with Spotlight. If you haven't seen that video, I suggest you go back and take a quick look at that now. This video is a natural progression from configuring alarms and allows you to create complex rules really easily once that alarm threshold has been triggered.
As I said a moment ago, it is really easy to create powerful alarms inside of Spotlight on SQL Server. And so all we need to do is go over to the Configure menu and then click on the Alarm Actions icon. This opens up a new window for us. We simply click on New to create a new rule. In this case, I'll call this Video.
And this section here is where we select the conditions for the Alarm Actions rule. So there's lots of different options in here. They're all fairly straightforward and self-explanatory.
So for example, I can say The alarm is. And I can choose down here the kind of alarm. So I can go through and say, I want to do this particular action rule for all of the SQL alarms. Or it may be that I wish a subset of them.
So for example, earlier, in a different video, we spoke about the backup alarms, for example. We also went through and we mentioned some of the SQL Agent alarms. Now if you do nothing else, I recommend that you go and enable a rule for this one here which is the SQL Agent's Jobs Failed. This means that we could set up an email alert-- which I'll show you how to do that in a second-- and this will fire no matter if you have database mail set up on any of your other servers. It will automatically send it to the distributee of your choice.
So in this example, what we're going to do is we're just going to say, let's choose all the SQL Server alarms. Normally, we want to be a lot more specific than this. But this is just a demonstration.
Then we can add extra action rules in as well. So we can say, and the severity is. So we don't want to know about the low and medium alarms. We only want to know about the high severity errors.
And then we could say, and The connection tag is. So inside of Spotlight, when you're creating your connections, one of the things you can do is you can add a tag. And so in one of the examples, I created a connection which had the tag of production.
So what I can do here is I can logically group my connections together and say, these are all production instances, so I only want to know about production instances. Development instances can go to somebody else in this scenario. So we click on OK.
So it can also have things like The date is, The day of the week is, The time of day is between. Now these options here, The day of the week and The time of day is between, are very, very important if you have DBAs who work out of hours because what this allows you to do is to have a separate action for in hours and a separate action for out of hours-- which means, when you come to the second section here, you can say, send an email to and send this to a different recipient depending on the time of day that this alarm actually breaches the threshold, which is a very, very powerful thing. And as you can see, I've just made a couple of ticks here and I've managed them set up quite a powerful rule.
So let's look at the day of the week. So you see I can just set it for certain days. So it might be I want it to be Saturday and Sunday for my out of hours. Time of day doesn't really matter. It's just going to be for the whole of the Saturday and Sunday.
And to send the email recipients, all I would do in here is put in their email address, or what I prefer to do is to tell people to create distribution lists in Exchange or whatever your mail platform might be. It's also a good idea for me to point out at this point that this is a very configurable message.
So if you had a help desk ticketing platform that allows you to send emails in to create help desk requests, then what you can do is you can format this email in such a way that it adheres to your help desk program. And what it will do is it will create help desk tickets on your behalf. Now the first time you do this, you'll need to click here to modify the settings. And this basically just sets up how the diagnostic server is going to connect to your SMTP server so it can send out emails. So as I said, you only need to do this the first time, and it will use these settings moving forward.
So as you can see, it's a very, very simple way of performing very, very powerful actions. Now instead of sending an email, one of the things I might want to do is to perform some kind of corrective action. So, for example, we had the alarm