Setting up Database Mail in MS SQL Server 2005 and later
Everything described in this post will be relevant for Microsoft SQL Server 2005 and higher versions (2008, 2008 R2, Denali). Also, within the framework of this post, the phrase “SQL Server” will designate only Microsoft SQL Server, excluding MySQL, PostgreSQL, FireBird and other DBMS, 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, by its Database Engine component). The main difference between Database Mail and SQLMail is that the latter used a third-party client (Microsoft Outlook) to send messages, which was supposed to be installed on the machine with SQL Server, and Database Mail itself communicates with the mail server via SMTP protocol.
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 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, except for the Express Edition ( proof ), however, here is a detailed description of how this component can be included.
The second thing without which Database Mail will not work is the mail server accessible from the machine with SQL Server and the account on it from which the letters will be sent. In my example, mail.ru will perform the role of such a server.
And the third is membership in the sysadmin role with your SQL Server account, since only members of this role can configure.
First of all, let's connect to our SQL Server using SQL Server Management Studio. Naturally, all configuration steps can be performed using predefined stored procedures, but I don’t strive to make as many requests as possible, especially if there are handy wizards for that.
Open the Management branch, select Database Mail, click on it with the right mouse button and select “Configure Database Mail”. The wizard’s welcome screen can be immediately checked with the checkbox “Never again show me this rubbish”, since there is no useful information on it.
And now, we are already facing a choice. Now we can: configure Database Mail, change profiles and Database Mail accounts, change profile security, and finally change the system configuration. Since we didn’t use Database Mail before - we don’t have anything to change, select the first item “Set up Database Mail” and click “Next”.
And here is the first surprise. SQL Server helpfully reports that in fact such a feature as Database Mail is disabled and specifies - do we want to enable it? Since this is 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 outstanding jobs (job) and add one account (Account) to it - for which we will click the “Add” button (to the right of the currently empty list of credentials). records).
Before writing a post, I registered TestDBMail@mail.ru mail just for experiments. Naturally, all these fields you will need to fill out "by yourself". For example, our internal mail server does not require authorization to send emails and, accordingly, you can select the “Anonymous authentication” option. The display name on this screen is what will appear in the “From” field of the received letter, and the Account name is the internal name of the SQL Server account.
Please note that at this stage there is no possibility of checking the connection to the server and there is no possibility to send a test letter - i.e. if an error is made while filling, it may take some time to find it. 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 didn’t pay attention to before - why do I need to create several profiles?
Each msdb user included in the DatabaseMailUserRole role can be assigned a profile. Or even a few. To do this, check the box "Access". Default Profile - if set to “Yes”, when using the stored procedure sp_send_dbmail, the default profile name can be omitted, the name of the profile marked Default for this user will be inserted there.
However, I will return to the Public Profiles tab and set the default = "Yes" and Public = "Yes" attributes for the newly 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 customize:
Feel free to click "Finish".
SQL Server will quickly finish the configuration and will show which items and for what reason it could not be completed (if something did not succeed). Making sure that everything is in order, we close the window.
Check that the Database Mail settings are correct. Go back to Management, right-click Database Mail and select the “Send Test E-Mail” item.
As we can see, the newly created profile has already been 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 am 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? Good, SQL Server is absolutely don't give a damn about incoming emails, he can't read them). And I press 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 fellows, who will unsuccessfully check mail, but they will not see the necessary letter.
First, look at the Database Mail magazine. In SSMS, go to Management, right-click Database Mail and select Database Mail Log there. Perhaps you just incorrectly specified the server connection settings - strangely enough, a rather common error.
The second thing to check is that Service Broker is enabled in the msdb database. That it is used to send messages.
Create a new query and execute there:
If the return value is different from one, Service Broker is disabled. You can enable it like this:
If the Service Broker is turned on, all settings are made 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 see typical errors in the Microsoft help.
We assume that now Database Mail has been set up 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 something is wrong with it (or, on the contrary, that everything is fine with it).
SQL Server may send a notification that the task was completed successfully (or vice versa - it ended with an error). More precisely, this can be done by the SQL Server Agent, in fact, performing the tasks. In order for him to do this, you need to do the following.
First of all - create an operator. That is, the person receiving the notice. 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 e-mail 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 a customized Database Mail profile, a notification operator, and 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 the checkbox "Enable Mail Profile"
Immediately after this, the Mail System and Mail Profile elements become active. In Mail System we can choose Database Mail, or as malicious SQL Mail necrophils (but it must be configured separately), in the Mail Profile we can choose which Database Mail profile SQL Server Agent will use to send messages. This can be a public profile, or a private profile, but in this case, the Windows user, under which the SQL Server Agent is running, must have a login in SQL Server, as well as a user in msdb, which is a member of the DatabaseMailUserRole role. Not wanting to bother with profiles, I agree with the choice of a public profile and press OK.
Everything. Now, be sure to restart the SQL Server Agent. This can be done from SSMS, or from SQL Server Configuration Manager. Choose for yourself - where it is more convenient for you.
After restarting, we’ll check that everything is set up correctly and messages really come. To do this, I do a job, trying to create a backup of one of the databases on a deliberately non-existent path.
I go to the properties of the created task, go to the Notifications tab, put the E-mail checkbox and select the newly created operator named Database Administrator. I leave the third column unchanged - there is “When the job fails”, i.e. I will receive notifications only if the task ends with an error. However, there you can choose two more options - when the task is completed successfully, or when the task is completed at all somehow - with an error, or without errors.
Settings are made, we start the task:
Well, the path is naturally not found, the backup is not made. Checking the mailbox:
The letter is delivered. In it you can see:
I hope this information was at least someone useful and maybe even interesting. If anyone has an interest, I can write about other ways to use Database Mail. In particular: inside the maintenance plans, to create alerts (Alerts), as well as about the stored procedure sp_send_dbmail, used to send emails using Database Mail.
On Habré there is another post dedicated to setting Database Mail. It says how to configure Database Mail using scripts.
What is this thing, 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, by its Database Engine component). The main difference between Database Mail and SQLMail is that the latter used a third-party client (Microsoft Outlook) to send messages, which was supposed to be installed on the machine with SQL Server, and Database Mail itself communicates with the mail server via SMTP protocol.
What is it 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’s mail: how many and what products were sold, which customer made the highest bid, which customers are in arrears and so on. In other words - the results of any query can be sent using Database Mail;
- the database administrator wants to receive by mail information about which tasks (job) or maintenance plans ended with an error (or, on the contrary, completed successfully);
- the database administrator wants to receive by mail information about errors of a certain level of severity, 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 very close to the size of the hard disk and it is time to shake the boss for the purchase of a new equipment;
- the database administrator wants to get something else in the mail.
')
What do we need?
So, first of all, we need SQL Server 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, except for the Express Edition ( proof ), however, here is a detailed description of how this component can be included.
The second thing without which Database Mail will not work is the mail server accessible from the machine with SQL Server and the account on it from which the letters will be sent. In my example, mail.ru will perform the role of such a server.
And the third is membership in the sysadmin role with your SQL Server account, since only members of this role can configure.
Go
First of all, let's connect to our SQL Server using SQL Server Management Studio. Naturally, all configuration steps can be performed using predefined stored procedures, but I don’t strive to make as many requests as possible, especially if there are handy wizards for that.
Open the Management branch, select Database Mail, click on it with the right mouse button and select “Configure Database Mail”. The wizard’s welcome screen can be immediately checked with the checkbox “Never again show me this rubbish”, since there is no useful information on it.
And now, we are already facing a choice. Now we can: configure Database Mail, change profiles and Database Mail accounts, change profile security, and finally change the system configuration. Since we didn’t use Database Mail before - we don’t have anything to change, select the first item “Set up Database Mail” and click “Next”.
And here is the first surprise. SQL Server helpfully reports that in fact such a feature as Database Mail is disabled and specifies - do we want to enable it? Since this is 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 outstanding jobs (job) and add one account (Account) to it - for which we will click the “Add” button (to the right of the currently empty list of credentials). records).
Before writing a post, I registered TestDBMail@mail.ru mail just for experiments. Naturally, all these fields you will need to fill out "by yourself". For example, our internal mail server does not require authorization to send emails and, accordingly, you can select the “Anonymous authentication” option. The display name on this screen is what will appear in the “From” field of the received letter, and the Account name is the internal name of the SQL Server account.
Please note that at this stage there is no possibility of checking the connection to the server and there is no possibility to send a test letter - i.e. if an error is made while filling, it may take some time to find it. 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 didn’t pay attention to before - why do I need to create several profiles?
Each msdb user included in the DatabaseMailUserRole role can be assigned a profile. Or even a few. To do this, check the box "Access". Default Profile - if set to “Yes”, when using the stored procedure sp_send_dbmail, the default profile name can be omitted, the name of the profile marked Default for this user will be inserted there.
However, I will return to the Public Profiles tab and set the default = "Yes" and Public = "Yes" attributes for the newly 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 customize:
- Account Retry Attempts - the number of attempts to send a letter using a specific account (remember that we can add several accounts to the profile? Here, SQL Server will first try to send an email on behalf of an account with priority 1 as many times as we specify, and then , if the letter does not go away - it will go through lower priority accounts)
- Account Retry Delay (seconds) - these are the exact seconds SQL Server will wait before trying again to send an email
- Maximum File Size (Bytes) - SQL Server can add attachment files to email. This parameter can limit the size of such an attachment.
- Prohibited Attachment File Extensions - prohibited permissions for attachments so that the user cannot generate and send a .vbs file, for example
- Database Mail Executable Minimum Lifetime (seconds) - Database Mail is a separate DatabaseMail file (90-110) .exe - this parameter determines after what period of time SQL Server will “beat” the running process in the absence of 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 configuration and will show which items and for what reason it could not be completed (if something did not succeed). Making sure that everything is in order, we close the window.
Check that the Database Mail settings are correct. Go back to Management, right-click Database Mail and select the “Send Test E-Mail” item.
As we can see, the newly created profile has already been 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 am 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? Good, SQL Server is absolutely don't give a damn about incoming emails, he can't read them). And I press 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 fellows, who will unsuccessfully check mail, but they will not see the necessary letter.
First, look at the Database Mail magazine. In SSMS, go to Management, right-click Database Mail and select Database Mail Log there. Perhaps you just incorrectly specified the server connection settings - strangely enough, a rather common error.
The second thing to check is that Service Broker is enabled in the msdb database. That it is used to send messages.
Create a new query and execute there:
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb'If the return value is different from one, Service Broker is disabled. You can enable it like this:
ALTER DATABASE msdb SET ENABLE_BROKERIf the Service Broker is turned on, all settings are made 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 see typical errors in the Microsoft help.
We assume that now Database Mail has been set up 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 something is wrong with it (or, on the contrary, that everything is fine with it).
SQL Server may send a notification that the task was completed successfully (or vice versa - it ended with an error). More precisely, this can be done by the SQL Server Agent, in fact, performing the tasks. In order for him to do this, you need to do the following.
First of all - create an operator. That is, the person receiving the notice. 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 e-mail 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 a customized Database Mail profile, a notification operator, and 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 the checkbox "Enable Mail Profile"
Immediately after this, the Mail System and Mail Profile elements become active. In Mail System we can choose Database Mail, or as malicious SQL Mail necrophils (but it must be configured separately), in the Mail Profile we can choose which Database Mail profile SQL Server Agent will use to send messages. This can be a public profile, or a private profile, but in this case, the Windows user, under which the SQL Server Agent is running, must have a login in SQL Server, as well as a user in msdb, which is a member of the DatabaseMailUserRole role. Not wanting to bother with profiles, I agree with the choice of a public profile and press OK.
Everything. Now, be sure to restart the SQL Server Agent. This can be done from SSMS, or from SQL Server Configuration Manager. Choose for yourself - where it is more convenient for you.
After restarting, we’ll check that everything is set up correctly and messages really come. To do this, I do a job, trying to create a backup of one of the databases on a deliberately non-existent path.
I go to the properties of the created task, go to the Notifications tab, put the E-mail checkbox and select the newly created operator named Database Administrator. I leave the third column unchanged - there is “When the job fails”, i.e. I will receive notifications only if the task ends with an error. However, there you can choose two more options - when the task is completed successfully, or when the task is completed at all somehow - with an error, or without errors.
Settings are made, we start the task:
Well, the path is naturally not found, the backup is not made. Checking the mailbox:
The letter is delivered. In it you can see:
- When and which task was performed (JOB RUN: 'Backup With Errors' was run on 11/19/2011 at 19:35:12)
- How long a task was performed before an error was detected (DURATION: 0 hours, 0 minutes, 1 seconds)
- The result of the execution (STATUS: Failed) - although, in fact, with the successful completion of the task, the letter would not have come
- A message from the event log of this job (MESSAGES: The job failed. The job was invoked by User KATE-HOME \ rancid. The last step was run 1 (test).), Which includes the name of the user who performed the task
Happy end
I hope this information was at least someone useful and maybe even interesting. If anyone has an interest, I can write about other ways to use Database Mail. In particular: inside the maintenance plans, to create alerts (Alerts), as well as about the stored procedure sp_send_dbmail, used to send emails using Database Mail.
PS
On Habré there is another post dedicated to setting Database Mail. It says how to configure Database Mail using scripts.
Source: https://habr.com/ru/post/132902/
All Articles