Fourth SQL Server Agent metabase mail

This article is the fourth of the SQL Server Agent series, please refer to the original text for more information.

As mentioned in the previous series, SQL Server Agent jobs are composed of a series of job steps, each of which is executed by a separate type. SQL Server Agent also provides the ability to create alerts to send messages to a set operator as a notification. These notifications can be sent through Database Mail, Database Mail is built in to SQL Server, and SQL Server Agent is able to send and receive e-mail messages. In this article, you will learn how to configure Database Mail sending and receiving operations, as well as some basic troubleshooting steps for Database Mail.
A brief overview of Database Mail
In SQL Server 2000 and earlier versions, a messaging system called SQLMail is provided. SQLMail (which will eventually be removed, but still in SQL Server R2) uses the MAPI (Messaging Application programming Interface) API set to send and receive e-mail messages. This messaging system has several problems and limitations, including:
Lack of failover clustering support
A dependent MAPI interface is provided by another application (such as Microsoft Outlook)
-Dependent SQL Server service has a MAPI configuration file
Cannot resend the message if a failure occurs before the message is sent
-No MAPI version supported for SQL Server
Because of these limitations, SQL Server 2005 has designed a new messaging system. The new messaging system-Database Mail, which removes these limitations, greatly improves the reliability of SQL Server mail. Database Mail is fully supported for failover cluster configuration. Database Mail uses SMTP and mail server communication to eliminate any external dependencies supported by the Mail API. Database Mail uses the Service Broker queue internally to process requests for all messages, making the messaging system more powerful.
Database Mail is a standalone executable that can be called from the MSDB database with Transact-SQL and interacts directly with the SMTP mail server using security information stored in msdb.
Database Mail Configuration
Database Mail is not turned on by default (much like SQL Server Agent). Before you can configure and use the functionality that it provides, you must enable Database Mail. There are three ways to enable Database Mail:
Database Mail Configuration Wizard: This is a graphical wizard that enables Database Mail and configures the basic service project. This is what we will use in this article.
->sp_configure: With most server configuration options, you can use the sp_configure system stored procedure to enable or disable Database Mail. You specify "Database Mail XPs" (sp_configure advanced option) for 1 to enable DB Mail, or 0 to disable Database Mail.
Policy-based management
You can set the value of the facet "@DatabaseMailEnabled" to True, then add the policy for the facet configuration and apply the policy to the specific SQL Server. If you want to manage multiple servers, this is the best choice for extensibility (policy-based management is available in SQL Server 2008 and later)
To enable Database Mail using the Database Mail Configuration Wizard
To start to enable Database Mail, configure Database Mail with Database Mail, Management---SQL Server instance, local to SSMs connection, and then start the Database Mail Configuration Wizard, which displays the wizard introduction. Click Next and you will see the options available in the wizard, as shown in 4.1
Figure 4.1 Database Mail Configuration Wizard--Select configuration tasks
Because this is the first time that you run the Database Mail Configuration Wizard, you need to keep the Database Mail options setting. Click Next to prompt you to enable Database Mail, as shown in 4.2
Figure 4.2 Enabling Database Mail
Although security is not involved, you need members of the sysadmin server role to accomplish this task. Select Yes to run the sp_configure command that corresponds to the operation. You will see the New Profile dialog box. Enter a name and description for the profile, and then click Add next to the SMTP account list ... button, shown in 4.3
Figure 4.3 Creating a Database Mail account
The account name is for convenience, so use something that you can easily remember. The e-mail address will be displayed in the real email address of the sender of the message sent by SQL Server. The display name corresponds to the alias/display name of the sender. You can set up a reply e-mail message to receive all replies to SQL Server.
EXP: E-mail address is a, reply e-mail is B, send test message to C. C will receive an e-mail message, the sender will appear as "Display Name", and if C replies to the test message, B receives e-mail from C. As shown
The server name may be the DNS name or the IP address of the SMTP mail server used by the specific mail account. The port number defaults to 25, but your mail server may need to use a custom port number. Finally, you have three SMTP authentication options. You can use the Database Engine service credentials for Windows authentication (this uses the SMTP service as the service account for the Database Engine service (Mssqlservr.exe), using Basic authentication (user name and password), or anonymous authentication (which really means no authentication). If you don't know these settings, you can learn from your company's email administrator or Internet service provider (ISP). Most ISPs provide e-mail services that connect to your Internet.
Once you have selected the appropriate authentication option, click OK to complete the mail account configuration. You can create additional account associated mail profiles in the wizard. If the first account cannot connect to the mail server for some reason to send a message, the second account in the list will be used. You can have multiple accounts associated with a mail profile. Now, keep a single SMTP account, and then click Next. You will choose to make the mail profile, you have created a public profile (which allows all authorized Database Mail users to send mail using this mail configuration), or you can privatize it (in this case, you can only contact the profile from the msdb database using the user ID). Because this is the first file, set it as the default public profile (4.4).
Figure 4.4 Setting the database configuration file to public
Click Next and you are finished configuring the first database configuration file. However, you now face the Configure System Parameters dialog box, which allows you to view or change Database Mail system parameters (see Figure 4.5).
Figure 4.5 Database Mail system parameters
You can specify the following options:
Number of account retries
Number of retry attempts by the mail server to send e-mail
The number of times a database message tries to send an e-mail message before it discards and switches to the next e-mail account in the list of accounts in the given mail profile
Account retry delay (seconds)
The time interval (in seconds) that the mail server tries to send e-mail messages
-Max file size (bytes)
Maximum file size (in bytes) for email attachments sent by the mail server
You can send SQL Serverr query results (including other) as email attachments, so you might think it's more than you think. You can, however, have a limited attachment size by your mail server that will also override this setting.
Prohibited attachment file name extension
The file name extension that is forbidden when the mail server sends an e-mail attachment
This is primarily as a way to ensure that your messaging system does not propagate viruses through programs or scripts. Of course you can add or remove extensions.
Minimum lifetime of Database Mail executable (seconds)
There is overhead associated with starting up the Database Mail process (because it runs outside of the DB engine or SQL Server Proxy section). This setting determines how long Database Mail will remain running after SQL Server Agent sends the e-mail for the last time. If you frequently use Database Mail, maintaining a long running time can save you from re-querying SQL Server and cache information, such as account profiles.
Logging Level
Determining events written to the Database Mail event log
There are three possible logging levels-normal, extended, verbose. If you are troubleshooting a problem or want to see as many Database Mail information as possible, we recommend that you set the logging level to verbose. You can then review the Database Mail log (or the Sysmail_event_log view in the msdb database) in SSMs
Now leave the default settings, click Next, and then finish creating the mail profile and account (Figure 4.6).
Figure 4.6 Completing the Database Mail Configuration Wizard
Because if everything is OK, you will see similar figure 4.7, showing that the profile and account have been successfully created. If there is a mistake here for some reason, or if you later need to change your settings for mail creation, restart the Database Mail Configuration Wizard, in the first dialog box (4.1), select the second radio button labeled "Manage Database Mail accounts and profiles." "You will be able to modify everything you just entered, or create a new mail profile or mail account."
Figure 4.7 Database Mail Configuration Wizard
Next Step
When the wizard finishes, there are several steps to confirm that you have correctly configured Database Mail. First, you want to send a test message, and then you should review the Database Mail log.
send a test message
Under SSMs, right-click the Database Mail node and select "Send Test e-mail ..." and you'll see a dialog box like Figure 4.8. Send yourself a message to test whether the mail system is functioning properly.
Figure 4.8 Sending a test message
If the message was sent successfully, click OK in the Database Mail test e-mail dialog box that pops up. If you are not able to receive an email, click on "Troubleshooting" to find the appropriate troubleshooting guide through the online books.
If you receive a test message normally, you can send an alert message using the operators mentioned in the third section of this series.
View Database Mail logs
To view the Database Mail log, you can go directly from msdb. Sysmail_event_log the query in the view, or right-click Database Mail in SSMs and select the View Database Mail Log menu option. You will see the log file Viewer, where we only view the Database Mail log. If you have only successfully sent the message, you should see a message that the Database Mail process has started. If there is an error, as shown in 4.9, once you select an error message, the details about the error will be displayed at the bottom of the log screen.
Figure 4.9 Log File Viewer
If you still have difficulties, you can adjust the logging level in Database Mail to verbose. You can pass the sysmail_configure_sp stored procedure in SSMs or through the msdb database. In SSMs, right-click Database Mail, restart the Configuration Wizard, and select configuration tasks to view or change system parameters. "You'll see figure 4.5 again, and then modify the logging level to verbose. Click Next to complete the wizard, and then try to send the e-mail again. You will see all the available log information in the Database Mail log viewer.
EXP: E-mail address is a, reply e-mail is B, send test message to C. C will receive an e-mail message, the sender will appear as "Display Name", and if C replies to the test message, B receives e-mail from C. 4.10 is shown

4.10 Sending and replying to a test message
Next article
As you can see, Database Mail is fairly straightforward and flexible, as long as you have an SMTP mail server that communicates with SQL Server. Security restrictions on Database Mail allow you to configure who can send e-mail messages (via DatabaseMailUserRole in msdb), as well as restrict mail profiles for specific users. You can combine Database Mail and SQL Server Agent to send alerts and other help to automate the management of SQL Servers.
Now that you know the basics of jobs, alerts, operators, Database Mail, the next step is to get to the different types of entries in the SQL Server Agent error log to see how they can help you troubleshoot SQL Server Agent issues.

Fourth SQL Server Agent configuration Database Mail