डेटाबेस मेल: Microsoft SQL सर्वर से सीधे मेल करना

बहुत से लोग जानते हैं कि 2005 के संस्करण से शुरू होकर, SQL सर्वर में ईमेल भेजने की अंतर्निहित क्षमता है, जिसे डेटाबेस प्रशासक अक्सर जरूरी अलर्ट भेजने के लिए उपयोग करते हैं, उदाहरण के लिए, जब अनुसूचित कार्य विफल हो जाते हैं। हालाँकि, कुछ ही लोग जानते हैं कि आप SQL सर्वर से सीधे SQL क्वेरीज़, फ़ंक्शंस और संग्रहीत कार्यविधियाँ भेज सकते हैं। और यदि आपने पहले ही SQL सर्वर में एक बार मेल सेट कर दिया है, तो एक पत्र भेजने में आपको बस एक मिनट लगेगा, और आप 15-20 मिनट में एक पूरे मेलिंग को व्यवस्थित कर सकते हैं। इस प्रणाली को डेटाबेस मेल (DBMail) कहा जाता है, और आज मैं इसके उपयोग के अनुभव को साझा करना चाहूंगा।

समायोजन

DBMail के साथ काम करने से पहले, आपको पहले सिस्टम को चालू करना होगा और उसमें एक ईमेल अकाउंट (SMTP) दर्ज करना होगा, जहाँ से पत्र भेजे जाएंगे। दुर्भाग्य से, सिस्टम को पता नहीं है कि आईएमएपी के साथ कैसे काम करना है, लेकिन अधिकांश मामलों में यह आवश्यक नहीं है।

SQL सर्वर व्यवस्थापक इंटरफ़ेस के माध्यम से DBMail की स्थापना बहुत अच्छी तरह से की गई है और "MS SQL सर्वर 2005 और बाद में डेटाबेस मेल की स्थापना" लेख में विस्तार से वर्णन किया गया है, इसलिए मैंने इसे दोहराया नहीं। मुझे लगता है कि यह विकल्प उन लोगों के लिए अधिक सुविधाजनक होगा जो पहली बार सिस्टम स्थापित कर रहे हैं।

मैं, बदले में, अब एक विकल्प दिखाऊंगा: SQL स्क्रिप्ट के माध्यम से इसे प्रोग्रामेटिक रूप से कॉन्फ़िगर कैसे करें। यह विकल्प इतना स्पष्ट नहीं है, लेकिन यह उन लोगों के लिए तेज और अधिक सुविधाजनक है, जिन्हें कई सर्वरों पर डीबीएमआईएल को कॉन्फ़िगर करने या सर्वर से सर्वर पर सेटिंग्स को स्थानांतरित करने की आवश्यकता है, इस मामले में यह केवल एक नए सर्वर पर इस स्क्रिप्ट को चलाने के लिए पर्याप्त होगा। यह महत्वपूर्ण है कि यह न भूलें कि निम्न स्क्रिप्ट का उपयोग करने के लिए आपको सर्वर में sysadmin समूह के सदस्य के रूप में लॉग इन करना होगा, अन्यथा सुरक्षा प्रणाली अधिकारों की कमी का संकेत देगी।

चलो सही बात पर आते हैं। यहाँ स्क्रिप्ट DBMail का उपयोग करने के लिए सर्वर तैयार कर रहा है:

--   Service broker -      -- ,  DBMail IF (SELECT is_broker_enabled FROM sys.databases WHERE [name] = 'msdb') = 0 ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK AFTER 10 SECONDS GO --    DBMail sp_configure 'Database Mail XPs', 1 GO RECONFIGURE GO 

अगला, जांचें कि क्या DBMail सेवा चल रही है:

 EXECUTE msdb.dbo.sysmail_help_status_sp 

और अगर यह नहीं चल रहा है (इसकी स्थिति "STARTED" नहीं है), तो इसे अनुरोध द्वारा चलाएं

 EXECUTE msdb.dbo.sysmail_start_sp 

अब आपको पत्र भेजने के लिए एक एसएमटीपी खाता बनाना होगा, मेलिंग सूचियों के लिए एक व्यवस्थापक प्रोफ़ाइल बनाना होगा और अपने एसएमटीपी खाते को इस प्रोफ़ाइल से जोड़ना होगा। मान लीजिए कि MySite.ru साइट व्यवस्थापक को अपनी साइट के पंजीकृत उपयोगकर्ताओं के लिए एक मेलिंग सूची व्यवस्थित करने की आवश्यकता है, और इसके लिए वह smtp.mysite.ru सर्वर पर अपने admin@mysite.ru मेलबॉक्स का उपयोग करता है।

 --  SMTP-    EXECUTE msdb.dbo.sysmail_add_account_sp --   @account_name = 'admin@mysite.ru', --    @description = N'  admin@mysite.ru', --   @email_address = 'admin@mysite.ru', -- ,      ":" @display_name = N' MySite.ru', -- ,        --    ,   "no-reply" @replyto_address = 'no-reply@please.no-reply', --   IP- SMTP- @mailserver_name = 'smtp.mysite.ru', --  SMTP-,  25 @port = 25, --  .       --        @username = 'admin', --     @password = 'MyPassword', --  SSL  ,  SMTP-   SSL @enable_ssl = 1; --      EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MySite Admin Mailer'; --  SMTP-    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MySite Admin Mailer', @account_name = 'admin@mysite.ru', --   SMTP-   @sequence_number = 1; --        DatabaseMailUserRole  MSDB EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'MySite Admin Mailer', @principal_id = 0, @is_default = 1; 

विश्वसनीयता के लिए, दो अलग-अलग मेल सेवाओं के लिए एसएमटीपी खातों की एक जोड़ी बनाने और उन्हें प्रोफ़ाइल से जोड़ने की सिफारिश की जाती है। यह आपको एसएमटीपी सर्वर में से एक के साथ संचार की अनुपस्थिति में भी एक महत्वपूर्ण पत्र भेजने की अनुमति देगा। इस स्थिति में, प्राथमिकता वाले SMTP खाते के लिए, sysmail_add_profileaccount_sp प्रक्रिया का @fterence_number पैरामीटर बैकअप खाते के लिए 1 (ऊपर देखें) होना चाहिए, पैरामीटर 2 होना चाहिए।

निजी डोमेन मेलबॉक्स से बहुत बड़े मेल की सिफारिश की जाती है। जब आप सार्वजनिक डोमेन मेलबॉक्स (मेलबॉक्स से yandex.ru, mail.ru, gmail.com, आदि) पर भेजने का प्रयास करते हैं, तो एसएमटीपी सर्वर आपको स्पैमर मान सकते हैं और मेलिंग सूची या संपूर्ण मेलबॉक्स को भी ब्लॉक कर सकते हैं।

फ़ायरवॉल

एक अलग प्रोग्राम SQL सर्वर को सीधे मेल भेजने के साथ सौदा करेगा। SQL सर्वर के विभिन्न संस्करणों में, इसे "DatabaseMail90.exe" या "DatabaseMail.exe" कहा जाता है और यह "C: \ Program Files \ Microsoft SQL Server \ ... \ MSSQL \ Binn \" फ़ोल्डर में डिफ़ॉल्ट रूप से स्थित होता है। यह महत्वपूर्ण है कि फ़ायरवॉल (फ़ायरवॉल) में इसके लिए आउटगोइंग ट्रैफ़िक की अनुमति न भूलें।

परीक्षण पत्र

यह कोशिश करने का समय है, सब ठीक है। Sysadmin समूह का कोई भी उपयोगकर्ता, MSDB डेटाबेस का मालिक (db_owner), या DatabaseMailUserRole भूमिका वाला उपयोगकर्ता एक परीक्षण पत्र भेज सकता है। उपयोगकर्ता के लिए DatabaseMailUserRole भूमिका जोड़ने के लिए, मानक sp_addrolemember प्रक्रिया का उपयोग किया जाता है:

 sp_addrolemember @rolename = 'DatabaseMailUserRole', @membername = '<_>'; 

अब एक परीक्षण पत्र भेजें:

 EXEC msdb.dbo.sp_send_dbmail --       @profile_name = 'MySite Admin Mailer', --   @recipients = 'friend@mysite.ru', --   @body = N'  SQL Server Database Mail', --  @subject = N' ', --        SQL- @query = 'SELECT TOP 10 name FROM sys.objects'; 

Sp_send_dbmail प्रक्रिया के अतिरिक्त पैरामीटर MSDN पर इसके विवरण में पाए जा सकते हैं।

यदि कुछ गलत है, तो पहले आपको पत्र की स्थिति को देखना होगा:

 SELECT * FROM msdb.dbo.sysmail_allitems 

और फिर लॉग को देखें:

 SELECT * FROM msdb.dbo.sysmail_event_log 

MSDN लेख, "समस्या निवारण डेटाबेस मेल", साथ ही लेख, "DBAs के लिए SQL सर्वर कार्य: समस्या निवारण डेटाबेस मेल " में सबसे आम समस्याओं को संबोधित किया जाता है।

निम्नलिखित एसक्यूएल क्वेरी के साथ सफलतापूर्वक भेजे गए पत्र देखे जा सकते हैं:

 SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems 

मेलिंग सूची

अब मान लें कि MySite.ru साइट के प्रशासक को उन लोगों का चयन करना होगा जो अपनी साइट के उपयोगकर्ताओं की तालिका से एक वर्ष से अधिक समय तक साइट पर नहीं आए हैं और उन्हें निमंत्रण भेजते हैं। हम विशेष रूप से वास्तविक जीवन की स्थिति को डीबीएमएल और एसक्यूएल की अधिक विशेषताओं को प्रदर्शित करने के लिए लेते हैं, जिसमें कर्सर और लूप शामिल हैं। कार्य को जटिल करने के लिए, हम कुछ और शर्तें जोड़ते हैं:

• उन लोगों को पत्र नहीं भेजे जाने चाहिए जिन्होंने पंजीकरण के दौरान संकेत दिया था कि वे पत्र प्राप्त नहीं करना चाहते थे। और भी अधिक जटिलता के लिए, हम उन लोगों की पहचान करेंगे जो एक अलग SQL क्वेरी का उपयोग लूप में करते हैं।
• पत्र HTML प्रारूप में होना चाहिए।
• पत्रों को 3 सेकंड के अंतराल के साथ भेजा जाना चाहिए ताकि हमारे एसएमटीपी सर्वर को अधिभार न डालें।
• SQL स्क्रिप्ट चलाने के तुरंत बाद वितरण शुरू नहीं होना चाहिए, लेकिन, कहें, 3 बजे।

 --     mysite USE mysite GO --  :  , , --     ,   DECLARE @user_id int, @user_name nvarchar(255), @last_login_date smalldatetime, @email_address varchar(255); --  @body       HTML DECLARE @body nvarchar(MAX); --  @no_mail   ,    DECLARE @no_mail int; --    ,      --        FAST_FORWARD DECLARE users CURSOR LOCAL FAST_FORWARD FOR SELECT id, name, last_login_date, email_address FROM users WHERE user_role IN (3,4,5) AND account_state = 2 AND email_address IS NOT NULL AND DATEDIFF(day, last_login_date, GETDATE()) > 365 ORDER BY id --      ,   3   WAITFOR TIME '03:00:00' --   users OPEN users --       FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address --      WHILE @@FETCH_STATUS = 0 BEGIN -- ,       SET @no_mail = (SELECT id FROM users WHERE id = @user_id AND allow_mail = 0) --  ,      IF @no_mail IS NOT NULL BEGIN PRINT N' ' + @user_name + N'   .' FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address CONTINUE END PRINT N'   ' + @email_address + N' ...' --    SET @body = N' <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> </head> <body> <p><img style="float:right;" src="http://mysite.ru/images/logo.png"/></p> <p>, ' + @user_name + N'!</p> <p>     MySite.ru.       ,  ,            .</p> <p> !</p> <p> ,   MySite.ru</p> </body> </html>'; --   EXEC msdb.dbo.sp_send_dbmail @recipients = @email_address, @subject = N'     MySite.ru', @body = @body, --      'HTML',  'TEXT' @body_format = 'HTML', --        --@file_attachments ='C:\attachment.jpg', --        --@copy_recipients ='me@gmail.com', -- "Blind copy"  "carbon copy" -    , --         --@blind_copy_recipients ='me2@gmail.com', --        @profile_name = 'MySite Admin Mailer'; --    3   ,    SMTP- WAITFOR DELAY '00:00:03'; --         FETCH NEXT FROM users INTO @user_id, @user_name, @last_login_date, @email_address END --     CLOSE users GO 

सब कुछ तैयार है। आप आराम करने जा सकते हैं।