November 19, 2011 at 16:54Setting up Database Mail in MS SQL Server 2005 and later
Everything described in this post will be relevant for Microsoft SQL Server 2005 and older versions (2008, 2008 R2, Denali). Also, in the framework of this post, the phrase “SQL Server” will mean only Microsoft SQL Server, excluding MySQL, PostgreSQL, FireBird and other DBMSs, which, theoretically, can be called SQL Server.
Database Mail appeared in SQL Server 2005, replacing the SQLMail component. Database Mail (as previously SQLMail) is used to send email messages by SQL Server itself (more precisely, its Database Engine component). The fundamental difference between Database Mail and SQLMail is that the latter used a third-party client (Microsoft Outlook) to send messages, which had to be installed on the machine with SQL Server, and Database Mail itself communicates with the mail server via SMTP.
In fact, everyone decides for himself whether he needs it or not. Examples of use include the following:
So, first of all, we need SQL Server version 2005 or later. I have SQL Server 2008 R2, but, in this case, there will be no difference in settings. Database Mail, according to Microsoft, is available in all editions, with the exception of Express Edition ( proof ), however, here is a detailed description of how this component can be enabled.
The second thing Database Mail will not work without is a mail server accessible from the machine with SQL Server and an account on it from which letters will be sent. In my example, the role of such a server will be performed by mail.ru.
And the third is membership in the sysadmin role for your account in SQL Server, since only members of this role can configure it.
First of all, connect to our SQL Server using SQL Server Management Studio. Naturally, all the configuration steps can be performed using predefined stored procedures, but I don’t try to do whatever is possible for the queries, especially if there are convenient wizards for this.
We open the Management branch, select the Database Mail item, poke it with the right mouse button and select "Configure Database Mail". The wizard’s welcome screen can immediately be checked with a daw “Never show me this rubbish again”, since there is no useful information on it.
And now, we are already facing a choice. Now we can: configure Database Mail, change the profiles and accounts of Database Mail, change the security of profiles and, finally, change the system configuration. Since we have not used Database Mail before - we have nothing to change so far, select the first item “Set up Database Mail” and click “Next”.
And here comes the first surprise. SQL Server helpfully reports that, in fact, such a feature as Database Mail is disabled and clarifies - do we want to enable it? Since this is precisely why we came here, we answer “Yes” and get to the next screen.
Let's create a new profile with the name My First DBMail Profile - it will be used to send mail about unfulfilled tasks (jobs) and add one account to it - for this, click the "Add" button (to the right of the list of accounts that is still empty records).
Before writing a post, I registered TestDBMail@mail.ru just for experimentation. Naturally, you will need to fill in all these fields "for yourself." For example, our internal mail server does not require authorization to send letters and, accordingly, you can select "Anonymous authentication". The Display Name on this screen is what will appear in the “From” field of the received message, and Account name is the internal name of the account on SQL Server.
Please note that at this stage there is no way to check the connection to the server and there is no way to send a test letter - i.e. if a mistake is made during the filling, its search may take some time. Be careful.
So, we have a profile, an account has been added to it. Click "Next" and see what's next.
Looking at this screen, and especially at the Private Profiles tab, there should be a question that I did not pay attention to before - why do I need the ability to create multiple profiles.
Each msdb user included in the DatabaseMailUserRole role can be assigned a profile. Or even a few. To do this, install the “Access” checkbox. Default Profile - if “Yes” is set, when using the sp_send_dbmail stored procedure, the profile name can be omitted by default, the name of the profile marked with Default for this user will be substituted there.
However, I will return to the Public Profiles tab and set the attributes Default = “Yes” and Public = “Yes” for the freshly created profile. Now this profile can be used by all msdb users included in the DatabaseMailUserRole role (and users of the sysadmin server role). After clicking “Next” we get to the penultimate screen of the setup wizard.
Here we can configure:
Feel free to click "Finish".
SQL Server will quickly finish the setup and show which items and for what reason it failed to complete (if something failed). After making sure that everything is in order, we close the window.
Check that the settings for Database Mail are correct. Again, go to Management, right-click on Database Mail and select "Send Test E-Mail".
As we can see, the profile you just created is already selected as the profile, the "Subject" and "Message text" fields are automatically filled. Naturally, all this can be changed, but the default values suit me. It remains only to specify the address to which the letter will go, without further ado - I’m writing the same address from which SQL Server will send the letter (please forgive my laziness - why create two mailboxes when one is more than enough? Fortunately, SQL Server don't give a damn about incoming letters, he can't read them). And I click the “Send Test E-Mail” button.
... after 15 seconds, in the incoming:
Bingo! Letter came. In fact, we can assume that the task is completed. But here I will make a small digression for the poor fellow who will unsuccessfully check mail, but will not see the necessary letter.
First, check out Database Mail. In SSMS, go to Management, right-click in Database Mail and select Database Mail Log there. Perhaps you just incorrectly specified the parameters for connecting to the server - this, oddly enough, is a pretty common mistake.
The second thing to check is that Service Broker is included in the msdb database. It is he who is used to send messages.
Create a new query and run there: If the return value is different from one, Service Broker is turned off. You can enable it like this:
If Service Broker is turned on, all the settings are done correctly - what the hell is not joking - check the mail again. No letter? Poke into the “Troubleshoot” button, which is in the window that appears after clicking the “Send Test E-Mail” button and look for typical errors in the help from Microsoft.
We will assume that we have now configured Database Mail and a test letter has arrived.
And then the fun begins. Now we can finally use Database Mail to receive notifications from SQL Server that it has something wrong (or vice versa that everything is fine with it).
SQL Server may send a notification that the task completed successfully (or vice versa - failed). More precisely, this can be done by the SQL Server Agent, which actually performs tasks. In order for him to be able to do this, the following must be done.
First of all, create an operator. That is, the person receiving the notifications. To do this, we open the SQL Server Agent, select Operators and poke "New operator"
In the window that appears, fill in the name (I have this Database Administrator) and the email address to which notifications will come (I specify the same long-suffering mailbox on mail.ru). Click "OK" and the operator will be created in a split second.
Now we have the configured Database Mail profile, the operator receiving the notification, it remains to configure the SQL Server Agent. To do this, click on it (SQL Server Agent) with the right mouse button, select the Properties item and go to the Alert System tab.
Here we put a daw "Enable Mail Profile"
Immediately after this, the Mail System and Mail Profile items become active. In the Mail System we can choose Database Mail, or as the evil necrophiles of SQL Mail (but it needs to be configured separately), in the Mail Profile we can choose which Database Mail profile will be used by SQL Server Agent to send messages. This can be a public profile or private, but in this case, the Windows user running SQL Server Agent must have a login in SQL Server, as well as a user in msdb, which is a DatabaseMailUserRole. Not wanting to bother with profiles, I agree with the choice of public profile and click "OK."
All. Now you must restart the SQL Server Agent. You can do this from SSMS, or from SQL Server Configuration Manager. Choose for yourself - from where it is more convenient for you.
After the restart, check that everything is configured correctly and that the messages really arrive. To do this, I am doing a job trying to backup one of the databases on a knownly non-existent path.
I go to the properties of the created task, go to the Notifications tab, check the E-mail checkbox and select the recently created operator named Database Administrator. I leave the third column unchanged - it says “When the job fails”, i.e. I will receive notifications only if the task ends in error. However, there you can choose two more options - when the task will complete successfully, or when the task will complete at least somehow - with an error, or without errors.
Settings are made, we start the task:
Great, the path, of course not found, backup not done. Checking the mailbox:
Email delivered. In it you can see:
I hope this information was at least useful to someone and perhaps even interesting. If anyone gets interested, I can write about other ways to use Database Mail. In particular: inside service plans, to create alerts (Alerts), as well as the stored procedure sp_send_dbmail, used to send emails using Database Mail.
On a habr there was one more post devoted to setup Database Mail. It says how to configure Database Mail using scripts.
What kind of thing is Database Mail?
Database Mail appeared in SQL Server 2005, replacing the SQLMail component. Database Mail (as previously SQLMail) is used to send email messages by SQL Server itself (more precisely, its Database Engine component). The fundamental difference between Database Mail and SQLMail is that the latter used a third-party client (Microsoft Outlook) to send messages, which had to be installed on the machine with SQL Server, and Database Mail itself communicates with the mail server via SMTP.
What is this for?
In fact, everyone decides for himself whether he needs it or not. Examples of use include the following:
- every morning, the manager wants to receive the results of yesterday by mail: how many and what products were sold, which client made the largest request, which clients are in arrears, etc. In other words, the results of any query can be sent using Database Mail;
- the database administrator wants to receive information on the mail about which tasks (jobs) or service plans have completed with an error (or vice versa, completed successfully);
- the database administrator wants to receive information on mail about errors of a certain severity level, or with a certain number that occurred on the server;
- the database administrator wants to receive information by mail that the size of the database is close to the size of the hard drive and it’s time to shake the boss for the purchase of new equipment;
- the database administrator wants to receive something else by mail.
What do we need?
So, first of all, we need SQL Server version 2005 or later. I have SQL Server 2008 R2, but, in this case, there will be no difference in settings. Database Mail, according to Microsoft, is available in all editions, with the exception of Express Edition ( proof ), however, here is a detailed description of how this component can be enabled.
The second thing Database Mail will not work without is a mail server accessible from the machine with SQL Server and an account on it from which letters will be sent. In my example, the role of such a server will be performed by mail.ru.
And the third is membership in the sysadmin role for your account in SQL Server, since only members of this role can configure it.
Go
First of all, connect to our SQL Server using SQL Server Management Studio. Naturally, all the configuration steps can be performed using predefined stored procedures, but I don’t try to do whatever is possible for the queries, especially if there are convenient wizards for this.
We open the Management branch, select the Database Mail item, poke it with the right mouse button and select "Configure Database Mail". The wizard’s welcome screen can immediately be checked with a daw “Never show me this rubbish again”, since there is no useful information on it.
And now, we are already facing a choice. Now we can: configure Database Mail, change the profiles and accounts of Database Mail, change the security of profiles and, finally, change the system configuration. Since we have not used Database Mail before - we have nothing to change so far, select the first item “Set up Database Mail” and click “Next”.
And here comes the first surprise. SQL Server helpfully reports that, in fact, such a feature as Database Mail is disabled and clarifies - do we want to enable it? Since this is precisely why we came here, we answer “Yes” and get to the next screen.
Let's create a new profile with the name My First DBMail Profile - it will be used to send mail about unfulfilled tasks (jobs) and add one account to it - for this, click the "Add" button (to the right of the list of accounts that is still empty records).
Before writing a post, I registered TestDBMail@mail.ru just for experimentation. Naturally, you will need to fill in all these fields "for yourself." For example, our internal mail server does not require authorization to send letters and, accordingly, you can select "Anonymous authentication". The Display Name on this screen is what will appear in the “From” field of the received message, and Account name is the internal name of the account on SQL Server.
Please note that at this stage there is no way to check the connection to the server and there is no way to send a test letter - i.e. if a mistake is made during the filling, its search may take some time. Be careful.
So, we have a profile, an account has been added to it. Click "Next" and see what's next.
Looking at this screen, and especially at the Private Profiles tab, there should be a question that I did not pay attention to before - why do I need the ability to create multiple profiles.
Each msdb user included in the DatabaseMailUserRole role can be assigned a profile. Or even a few. To do this, install the “Access” checkbox. Default Profile - if “Yes” is set, when using the sp_send_dbmail stored procedure, the profile name can be omitted by default, the name of the profile marked with Default for this user will be substituted there.
However, I will return to the Public Profiles tab and set the attributes Default = “Yes” and Public = “Yes” for the freshly created profile. Now this profile can be used by all msdb users included in the DatabaseMailUserRole role (and users of the sysadmin server role). After clicking “Next” we get to the penultimate screen of the setup wizard.
Here we can configure:
- Account Retry Attempts - the number of attempts to send an email using a specific account (remember that we can add several accounts to the profile? Here, first, SQL Server will try to send an email on behalf of an account with priority 1 as many times as we specify, and then if the letter still doesn’t go away, it will sort through lower priority accounts)
- Account Retry Delay (seconds) - exactly how many seconds SQL Server will wait before retrying to send an email
- Maximum File Size (Bytes) - SQL Server can add attachments to a message. You can limit the size of such an attachment with this parameter.
- Prohibited Attachment File Extensions - forbidden permissions for attachments so that the user cannot generate and send the .vbs file, for example
- Database Mail Executable Minimum Lifetime (seconds) - Database Mail is a separate DatabaseMail (90-110) .exe file - this parameter determines how long SQL Server will “kill” a running process when there is no activity
- Logging Level - this parameter determines how much information will be written to the log when Database Mail is running.
Feel free to click "Finish".
SQL Server will quickly finish the setup and show which items and for what reason it failed to complete (if something failed). After making sure that everything is in order, we close the window.
Check that the settings for Database Mail are correct. Again, go to Management, right-click on Database Mail and select "Send Test E-Mail".
As we can see, the profile you just created is already selected as the profile, the "Subject" and "Message text" fields are automatically filled. Naturally, all this can be changed, but the default values suit me. It remains only to specify the address to which the letter will go, without further ado - I’m writing the same address from which SQL Server will send the letter (please forgive my laziness - why create two mailboxes when one is more than enough? Fortunately, SQL Server don't give a damn about incoming letters, he can't read them). And I click the “Send Test E-Mail” button.
... after 15 seconds, in the incoming:
Bingo! Letter came. In fact, we can assume that the task is completed. But here I will make a small digression for the poor fellow who will unsuccessfully check mail, but will not see the necessary letter.
First, check out Database Mail. In SSMS, go to Management, right-click in Database Mail and select Database Mail Log there. Perhaps you just incorrectly specified the parameters for connecting to the server - this, oddly enough, is a pretty common mistake.
The second thing to check is that Service Broker is included in the msdb database. It is he who is used to send messages.
Create a new query and run there: If the return value is different from one, Service Broker is turned off. You can enable it like this:
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb'ALTER DATABASE msdb SET ENABLE_BROKERIf Service Broker is turned on, all the settings are done correctly - what the hell is not joking - check the mail again. No letter? Poke into the “Troubleshoot” button, which is in the window that appears after clicking the “Send Test E-Mail” button and look for typical errors in the help from Microsoft.
We will assume that we have now configured Database Mail and a test letter has arrived.
And then, then what?
And then the fun begins. Now we can finally use Database Mail to receive notifications from SQL Server that it has something wrong (or vice versa that everything is fine with it).
SQL Server may send a notification that the task completed successfully (or vice versa - failed). More precisely, this can be done by the SQL Server Agent, which actually performs tasks. In order for him to be able to do this, the following must be done.
First of all, create an operator. That is, the person receiving the notifications. To do this, we open the SQL Server Agent, select Operators and poke "New operator"
In the window that appears, fill in the name (I have this Database Administrator) and the email address to which notifications will come (I specify the same long-suffering mailbox on mail.ru). Click "OK" and the operator will be created in a split second.
Now we have the configured Database Mail profile, the operator receiving the notification, it remains to configure the SQL Server Agent. To do this, click on it (SQL Server Agent) with the right mouse button, select the Properties item and go to the Alert System tab.
Here we put a daw "Enable Mail Profile"
Immediately after this, the Mail System and Mail Profile items become active. In the Mail System we can choose Database Mail, or as the evil necrophiles of SQL Mail (but it needs to be configured separately), in the Mail Profile we can choose which Database Mail profile will be used by SQL Server Agent to send messages. This can be a public profile or private, but in this case, the Windows user running SQL Server Agent must have a login in SQL Server, as well as a user in msdb, which is a DatabaseMailUserRole. Not wanting to bother with profiles, I agree with the choice of public profile and click "OK."
All. Now you must restart the SQL Server Agent. You can do this from SSMS, or from SQL Server Configuration Manager. Choose for yourself - from where it is more convenient for you.
After the restart, check that everything is configured correctly and that the messages really arrive. To do this, I am doing a job trying to backup one of the databases on a knownly non-existent path.
I go to the properties of the created task, go to the Notifications tab, check the E-mail checkbox and select the recently created operator named Database Administrator. I leave the third column unchanged - it says “When the job fails”, i.e. I will receive notifications only if the task ends in error. However, there you can choose two more options - when the task will complete successfully, or when the task will complete at least somehow - with an error, or without errors.
Settings are made, we start the task:
Great, the path, of course not found, backup not done. Checking the mailbox:
Email delivered. In it you can see:
- When and what task was performed (JOB RUN: 'Backup With Errors' was run on 11/19/2011 at 19:35:12)
- How long did the job run before the error was detected (DURATION: 0 hours, 0 minutes, 1 seconds)
- Final result (STATUS: Failed) - although, in fact, if the task was completed successfully, the letter would not have arrived
- A message from the event log for this job (MESSAGES: The job failed. The Job was invoked by User KATE-HOME \ rancid. The last step to run was step 1 (test).), Including the name of the user who completed the job
Happy end
I hope this information was at least useful to someone and perhaps even interesting. If anyone gets interested, I can write about other ways to use Database Mail. In particular: inside service plans, to create alerts (Alerts), as well as the stored procedure sp_send_dbmail, used to send emails using Database Mail.
PS
On a habr there was one more post devoted to setup Database Mail. It says how to configure Database Mail using scripts.