DP-300 Microsoft Azure Database – Evaluate and implement an alert and notification strategy

1. 76, 79. create event notifications based on metrics

Hello. And in this section we’re going to evaluate and implement an alert and notification strategy. So we’re going to start on a virtual machine and we’re going to go into SQL Server Agent. So if I just open it, you can see that we’ve got a section for Alerts. Now. This isn’t for Azure SQL Database because you don’t have SQL Server. Agent. And this isn’t for the managed instance either, because you don’t have that section of alerts. So what we can do is create an event notification in SQL Server. So I’m going to write and click and go to New Alert. So I will enter a name for this new alert and I can select the type.

It could be an SQL Server event. So this is based on error number or severity. It could be a WMI event alert. This uses the Windows management instrumentation, so that’s what WMI is, to monitor events in SQL Server. Or you can use the SQL Server performance conditions. So this allows you to have a look at objects such as databases or general statistics, counters such as transactions or transaction per seconds or logins per second a particular instance. So this could be a database and alert. If the counter rises above, say, two, if there’s more than two logins per second, maybe something is wrong. Then we’ll go to the response page so we can execute a job.

So a job has a series of steps such as running TSQL commands. So it could be that if something happens, you might want to do something like shut down the computer, or you could add additional files or something like that. And you can notify operators. So we’re going to have a look later on in a later video about how to create notifications. And then in the Options section you can see that we can include the alert error text in Email or Pages pager is being a bit discontinued nowadays. So really email options you can put in an additional notification message. There are too many logins this second, and you can indicate a delay between the responses.

So maybe you just want it to be sent every minute as opposed to every time it happens. So if you have zero minutes and 0 second, that indicates that you want to respond. In other words, an alert for every single occurrence of this particular alert. So this is how you can create event notifications based on metrics. You can go into the SQL Server agent, right and click on the alert. Scott in New Alert, enter the alert. Enter the type of alert you want SQL Server Event Alert, Performance Condition Alert, or WMI Event Alert. Select the object, the counter, the login, and the condition, and then you can say what you want happening. You can notify Operators or execute jobs. And you’ve got a few other options as well.

2. 75. configure notifications for task success/failure/non-completion

Now in the previous video we created an alert, but I want to create an operator. So in other words, when the alert happens, I want a person to be notified. So we need to create an operator. Now, operators can be used with alerts, but they can also be used with jobs, and therefore it is also available in the managed instance. So whereas alerts are not available, operators are. So to create a new operator, I can just go and right and click and go new operator. So I can enter the person’s name, I can enter the email address, I can enter a pager email address, but I should point out that pages are being deprecated in SQL Server, so it might be removed in later versions and we can collect notifications page. Now how can I tie this operator to an alert or maybe a job? Well, this notifications page says I want this user to have notifications when a particular alert or a particular job happens.

So I can check email here, click okay, or I can go into the alert and in the response I can put the operator in for this particular alert. So I can do it either way. Now I need to do a bit more before I can send an email, and that is that I need to be able to enable database mail. Now this is not necessary on Azure SQL managed instance as it’s enabled by default, it’s not enabled by default on the virtual machine. So what I need to do is go into management and go into database mail. And I’m going to configure database mail. So we’ve got this splash page to start with, and then we’ve got these various things. I can set up a database mail by creating a new email profile, specifying the security and configuring the system parameters, and we do those in each particular order.

So we have database mail set up, then we create an account, and then we create a profile for the SQL Server Agent service account to use and to add the user to the database mail user role in the msdb database. So I click on Next and you can see the database mail feature is not available. Would you like to enable this feature? So the answer is yes. So this is my email and I need to have an SMTP account. This stands for simple Mail Transfer Protocol.

Basically it’s to be able to send and receive emails. So click. OK, so add. So I click Add and then I can specify the account name, the description, the outgoing mail server. And if you have set up an SMTP account previously, then this will be fairly well straightforward. If you don’t, then you might need some help from your mail provider. So you create a database mail account. So you set up a database mail to start with, then you create a database mail account, then you create a profile, and then you will need to set the profile as the default profile for the msdb database and you do that in manage profile security. So you say default profile for that particular profile.

So this is how you can set up an operator. You just right and click on operators and go to new operator and put in your details and say what alerts or jobs that operator is for. And then you need to set up database mail so it is in management database mail. You need to configure database mail and you need to enable or set up database mail to start with. Then set up an account, set up a profile which adds the user to the database mail user role and then set the profile as a default profile for the msdb database by managing the profile security and saying it is the default profile.

3. 46, 73. manage schedules and automate maintenance jobs

In this video we’re going to have a look at how we can create a job and manage schedules for regular maintenance jobs. So again, this is for the VM and SQL managed instance as it uses SQL Server Agent, which as your SQL Database doesn’t. So let’s go into the job section of SQL Server right? And click. And new job. So we give this a job name. So, new job. You can also give it an owner and a category. So next we have the steps. So this is what it actually does. So we got this step list and we have name type on Success and on Failure. So we click New and you can see this is our first step.

So job steps, you can see there could be PowerShell, things to do with replication, SSIs packages, operating system and TSQL commands. So this is my first step and I can select what database I want it to run against. And I’m just going to do a very simple command. Now what we can do is have a look at Pass and make sure that the syntax is correct. In the advanced section it says what to do on Success.

So on Success you can go to the next step or you can quit the job reporting success. Now you can have multiple retry attempts. It could be that the results you’re trying to connect to is not a line. For instance, so maybe I want to have two retry attempts at ten minute intervals. On failure, then you can quit the job reporting failure. Alternatively, if you don’t really care whether it fails or doesn’t, then you go to the next step. So entirely your choice. So you can see there are a few additional things there.

So this is how it gets translated. So first step is TSQL script. If successful, go to the next step, if failed, then go to the job reporting failure. And we can then add a second step and so on. And we can move these steps up and down. So we can insert a new step, we can edit a step, we can delete a step and self off. Let’s have a look at the schedules. So this is when the job is going to run. So it could be one time job, it could be recurring. Now in a virtual machine, you can get it to start automatically when SQL Server Agent starts. Now.

SQL Server Agent always runs on Azure SQL managed instance, so I wouldn’t do that. So I’m going to choose a recurring schedule and you can see, you can make it enabled or not. You can choose the frequency daily, weekly or monthly. You can say I want it to reoccur on the 14th of every month, or the second Wednesday of every month, or I want it to reoccur every two days. So just because it says daily, doesn’t actually mean it will happen.

Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, it’s Sunday. You can say I want it to be measured in days, the frequency, but I want it to reoccur every two days. I want it to reoccur every two weeks on a Wednesday and a Sunday. I want it to reoccur at a particular time. So maybe let’s recur at twelve noon and 34 minutes. Or maybe I want it to recur every hour, every minute, every second. I want it to have a start date and an end date. Or you could say no end date. So this bit up here is only for if it’s a one time recurrence. Everything else is if it’s reoccurring.

So this is my job schedule. So we have pick at the bottom. Pick is there if you have previously created a schedule and there are some which are built in, so you can see reoccurs every day, every 10 minutes between these particular times. So I could say I want it to happen like that. I can edit it, I can remove it. We’ve got notifications, so we’ve had a look at notifications before. So we have email so we can email somebody when the job fails, succeeds or is completed. We can delete the job automatically when the job succeeds. So maybe you just want it to happen, say Sunday morning, and when it’s completed, delete the job.

You can see that the pager option is deprecated, so I wouldn’t choose it. And then we can target the local server or multiple servers. So this is how we can create a regular maintenance job. We just go to jobs and we say what it is, the steps, when it is, the schedule, who to notify, and is it the local server. Once we’ve done that, then we have a new job and we can go back into it. We can start the job at a particular step. We can stop the job if it’s already running. We can go to properties and have a look at the job as well. So this is how we can create jobs in SQL Server Agent. Now you can also do this in TSQL and one good thing about these particular dialog boxes is that quite often there is a script there now you can see the script isn’t there for all of the things so if you find that you have scripted something you can always click on script and see what the output is.

Let’s have a look at it what it is for schedules. So first of all, we need to be in the msdb database because that is where all of your schedules are stored and then you can execute the stores procedure SPAD schedule. So require swings like schedule name, frequency, type so one for once, four for daily, eight for weekly, 16 for monthly, like a day of the month. 32 is monthly also, but this is for say, the second Sunday and 64 when Escrow agent services start and 128 when the computer is idle.

So you can see this is quite busy. If you think that’s complex, the frequency interval even more complex. I’m not going to go into that there. And here we have the start time. But the important thing is SP Ad schedule adds a schedule. We can attach a schedule to a job with spat schedule. And you can see we have the job name and the schedule name. And to view the schedules, we can go to the table called Sys Schedules notices null dot in it. We have to be in the msdb database for this. So here we can see the schedule. So there’s the my job schedule that I’ve previously created. And you can see the frequency type eight. So that indicates weekly.

And it’s got several more things as well. And when jobs have run, you can see them in the Job Activity Monitor. So if I start this job, which to be honest is a very straightforward job, it’s just select a table and go to the Job Activity Monitor and view Job Activity. There we can see this job. So you can see when it was last run, when it’s going to be next run, is it runnable, is it scheduled, is it enabled? And what’s its current status? In this case, idle. So you can see this by going to SQL Server Agent, the Job Activity Monitor, and then just right and click it and go to View Job Activity. So this is what you need to know about jobs and schedules.

4. 78. create alerts for server configuration changes

In this video we’re going to track what server configuration changes have been made and we can do this with something called the default Trace enabled. So what I can do is just go SP configure, default trace enabled, but unfortunately it says it doesn’t exist or it may be an advanced option. So what does this mean? Well let’s have a look at all of the options that we, we’ve got. And you can see here they are in alphabetical order and we’re looking at SP underscore configure and you can see there are 28 options and default Trace enabled is not one of them.

Now what happens is that the computer hides the advanced options. If you want to actually see them then we have to go SP configure show advanced options and it can go from a zero to a one. At the moment it is zero. So I’m just going to run that. Notice we need go between all of these. So let’s have a look at it. And you can see, no, nothing’s happened but there was a clue in the message that we got run the reconfigure statement in store. So it’s no good just saying that this is what I intend to happen.

I intend to for sure advanced options to go from a zero to a one. What I actually want to do is actually commit that value to change it from the config value, which is what I’ve done, to the run value, in other words, the current figure. So I need to reconfigure. So now I’ve reconfigured. Let’s have a look at SP configure. And you can see there used to be 28 items. There are now 90 items in my particular version. Your version might be different and that includes default Trace enabled. Now you probably don’t have it enabled. If you don’t, then all you have to do is spconfigure default Trace enabled, comma one and then reconfigure. So now when we look at it, if it wasn’t previously enabled then it is now. So what does this do? Well this allows me to trace what changes have been made. So to do this we can go to the server. Now notice we’re not going to any of the databases, we’re going to the server and we’re going to right and click on it and go to reports, standard reports and configuration changes. History now needs, as I say, because this is to do with the server.

This doesn’t work in Azure SQL database, it’s for virtual machines and it’s for managed instances. And here you can see that we have got some of the changes. So it’s the changes recorded by the default trace. So unless you got the default Trace enabled then you won’t be seeing anything. And here you can see what I’ve got. So a couple of minutes ago I enabled the default trace enabled, otherwise none of this would be showing. And then I showed options and showed options again and enabled the default trace enabled again and you can see all of this but that’s only because previously I have configured the default trace enabled to be on or value of one.

So initially this was completely blank and now it is just tracking what I’m doing. So if I go show advanced options to zero and then refresh this then again we now know that show advanced options becomes zero from one. So this is how we can monitor for server changes configuration. We have to enable the default trace enabled which is advanced options. You need to show the advanced options first and then you can just right and click on the server go to reports standard reports configuration changes history and then you can see what server configuration changes there have been.

5. 77. split and filter event notifications for Azure resources

Now, in a previous video we had a look at how we can use metrics and I said that you can’t add filters or splitting for SQL database also said these are the various metrics that you can use. Now, you can’t have filter or splitting for the managed instance either. And we had a quick look at the seven different metrics you can use for managed instance. However, you can add filters and splitting for virtual machines. So here I’m in the metrics part of a virtual machine and I can add a metric and you can see all of the various categories and there are an awful lot of them.

So they’re basically CPUs credits, data disk, data read and write inbound, flaws, network, in and out, operating system, disk, premium, data disk cache and virtual machines. Now, you’ll notice some of them have got this symbol next to them. This symbol indicates that it can support filtering and grouping. So what I’m going to do is have a look at the data disk read byte per second, so that has this symbol next to it. So here are the figures. So, 6. 8 bytes per second and we can see this in a line chart as well. But what we can do is we can split this up. So we don’t just have the one data disk on a virtual machine, we have multiple ones.

So I’m going to apply splitting and it’s probably best if I just show this in a grid to start with. So I can apply splitting of the L-U-N that is a logical unit number and you can see how it splits. So we’ve got these five disks and we’ve got different averages. And I can’t sort by the AUM, but I can sort by the averages maximum to minimum or vice versa. So that is an example of splitting. So it’s going further down into a particular metric.

So if I was to look at this in the line chart, you can see that we have got all of these different disks and I can hover over each one and you can see that particular logical unit number. Now, what I can also do is then apply filtering. Maybe I want to say, well, I don’t really want to see zero, two and one, so I’ll add a filter as well. So Aun is equal to or is not equal to or starts with, so it’s equal to. And I want to see numbers three and four only. So I’m going check three and four. So there we can see, we’ve now just got numbers three and four. So this is the ad filtering and add splitting part of the metric. You can only do this with virtual machines and you can only do this if you have a particular metric with this little symbol next to it. So that supports filtering and grouping.