SQL Server offers an integrated alarming system that informs you by email when something goes wrong. In the past this helped us find problems early on and react quickly – before we lost data. Let us look how we can activate this feature in SQL Server.
Activate Database Mail
Open up SQL Server Management Studio and expand the Management folder. Then right-click on Database Mail and select Configure Database Mail:
The configuration wizard starts with an info screen. If you do not want to see it every time you change your configuration, you can disable it with the checkbox at the bottom:
This wizard allows you to create new configurations (1) and check your existing ones (2). We want to create a new one and go with the first option:
The Database Mail feature gets installed when you run the wizard for the first time:
We can now create a new mail profile. Give it a name and then add a SMTP account:
Here you can add all the configuration for your SMTP account. You probably want to activate the secure connection (SSL) option:
When your SMTP account is complete, you can continue with the wizard and make your new profile the default one:
I like to increase the retry attempts to something higher than the default value. This improves the chances of sending your mail if your mail server is temporarily unavailable:
Check if everything looks right before you end the wizard:
The wizard now creates your configuration and should report everything as successful:
Test your mail configuration
The first thing you should do after setting up a mail configuration is to check if it works. You find in the same context menu on the Database Mail feature an entry to send a test mail:
You can add an email address for the recipient and add you message:
In a few seconds you should get an email from your SQL Server. If this is the case, proceed to activate the alarming. If you do not get an email, start the troubleshooting by checking your mail configuration.
Activate the alarm system
To configure the alarming, we need to open the context menu of the SQL Server Agent and open the Properties entry:
In the entry for Alarm we can enable the mail profile and use our newly created profile with Database Mail:
Create an operator
The operator is the person or email account that will be alarmed when an error happens. You can create an operator in the context menu of the Operators folder of SQL Server Agent:
You need to give the operator a name and set its e-mail address:
Define the Alerts you want to receive
The final step is to create a set of alarms you want to receive by email. For that you can create an alarm in the context menu of the Alarms folder of SQL Server Agent:
We need to give the alarm a name and decide which database (or all) it should match:
For the severity we can select one of the pre-defined problems:
On the response page we need to select our operator and define how we want to reach them (by e-mail or pager):
The options page allows us to define a time period between two alerts:
Repeat the steps to create an alarm for all errors you want to get an email.
Parting thoughts
We have to take a many steps to set-up Database Mail and the different parts of the alert system. But as soon as it is in place, SQL Server will inform us about the important problems that happen with our databases. That will save us a lot of time later on when we get an alert right away and not after weeks of destroying your database.