MSSQL database mail Series 1 (Environment setup)

I. Preface

Database mail is an enterprise solution that sends emails through Microsoft SQL Server 2005 database engine. For example, when I was sleeping at home, I also wanted to pay close attention to my company database, so my laptop was at risk of being burned out, open the net send or email sent to me by the listener server. When there is an email, Gmail prompts me. then you can quickly check whether something is wrong. Of course, you can also use database mail to send some ads or other information in MSSQL like our customers. there is no need to worry about the development time of the Upper-layer development group system, which reduces development costs and database mail is based on the concept of queue. Therefore, unsent mail is in the queue, wait for the next schedule. In this regard, you can focus on the concepts of database mail and service broker.

Ii. Database mail features

Reliability

In MSSQL 2005, you do not need to use extended mapi to send emails like the solution in SQL mail 2000. You do not need to install the mapi client to send emails, he uses the SMTP protocol to send emails.

To fully demonstrate database emails, I have set up my own POP3 and SMTP environments.

First, let's take a look at SMTP.

SMTP (Simple Mail Transfer Protocol): controls how emails are sent to the target server over the Internet. SMTP receives and sends emails between servers.

That is to say, we send and receive emails through SMTP In the TCP/IP cluster, so SMTP is the transmission carrier of our emails.

POP3: Post Office Protocol 3 is the standard protocol for email retrieval. The POP3 Protocol controls the connection between the POP3 email client and the server that stores the email. The POP3 Service uses the POP3 protocol to retrieve emails from the mail server to the POP3 email client.

That is to say, POP3 downloads emails sent from SMTP to our email client, such as Gmail or outlook.

Let's take a look

Step1-3: The someone@example.com client is surfing the internet, and he sent an email that was carried through the SMTP protocol, the ISP resolved the domain of the email and then transferred to the server of example.com.

Step 4: continue to use SMTP to transmit our email.

Step 5: send to the specified shipping directory of the domain in SMTP virtual service in IIS. The default path you see is shown in the figure.

Step 6: POP3 provides three authentication methods for you to log on to your mailbox, such as Active Directory, anonymous, and password authentication.

Step 7: If the verification succeeds, you can use POP3 to download the email to your client.

Well, in order to better continue the following courses, you need the following environment:

DB: ms SQL Server 2005 (I am using the Enterprise Edition)

OS: Windows 2003 (two machines, I use virtual machines for bridging), install the POP3 Service, remember to put the dishes, or use the system's ISO to install the service. 2008, you can add features directly. haha. by the way, you must enable the SMTP service. SMTP and POP3 must be used on a server for conditional reasons.

1. Enable the SMTP service: Choose Start> RUN> Net start SMTPSVC> success.

2. Open the POP3 Service: Start Menu->Program-> Management tools-> POP3 service.

Right-click the POP3 Service and choose connection:

Enter the name of your computer. If it is used by default, it will display Terry as I do.

3. In the name of your computer (my computer is Terry), create a new domain and enter "mssql.com ).

4. Select "mssql.com", add the mailbox, and add two mailboxes, One and Two respectively. Set the password by yourself.

In fact, the mailbox name is 1@mssql.com, and 2@mssql.com

Now, our

POP3 server: Terry (my computer name)

SMTP server: Terry (Mine too)

Those who have played outlook know that setting these two accounts and the mail client account password can receive and send emails.

5. Configure the client machine. Now both POP3 and SMTP servers are configured:

Click other settings in account settings and select

Then you can send an email like a 2@mssql.com. the client can also perform the same settings. If everything is normal, congratulations, you should be able to get the email. if not, ask in the message. as for why not use exchange, it is mainly convenient to configure.

The 2nd point of reliability is that multiple SMTP servers can be added in the database mail configuration, and multiple accounts can be added. That is to say, if the first SMTP server fails, it will be pushed to the next one for execution.

Flexibility

You can directly use sp_send_dbmail to send database emails.

Multiple database email configuration files can be used

You can set up multiple accounts to send messages, such as 1@mssql.com, 2@mssql.com

Security

You can control the attachment size, file extension, and permission control in SQL Server (you can send emails only when you have databasemailuserrole in MSDB)

Iii. Conclusion

Database mail is still a lot of help. First, we can use database mail to regularly notify some DBAs who are going out to view their corresponding system information, then we can use database emails to send emails to our customers in batches in some businesses, and finally send emails to DBAs as warnings. in short, you can imagine a lot of scenarios. this article describes the environment configuration and the specific content of database emails.