6. SQL Server資料庫監控 - 如何告警

一. 告警方式；

二. 選擇告警方式并配置

常用的告警方式大緻有：短信、郵件、應用程式 (beep提示，圖示提示，升窗提示等)，可是不能一直坐在電腦前看着應用程式，或者用腳本部署監控，根本沒有程式界面，是以通常用短信、郵件兩種方式告警。

一. 告警方式

1. 短信

用程式發短信的方式一般有這兩種：

(1) 硬體

需要1張SIM卡，1個SIM卡讀卡裝置 (比如：短信貓)，然後把裝置連接配接到電腦，應用程式根據裝置的軟體接口，傳參并發送短信。記得把SIM卡裝置放在信号好，無幹擾的地方；

如果有大量短信要發，1張SIM卡是不夠用的，而且發送過度頻繁，可能被營運商視為惡意短信，把SIM卡号加入黑名單，那麼就需要多張SIM卡甚至多個讀卡裝置。

顯示号碼為目前SIM卡号碼，大多供應商都支援DLL、HTTP等多種接口，當然也可以基于接口二次開發。

DLL接口方法參考：SmsManager.sendTextMessage(…)

(2) 第三方短信接口

有多種接口形式提供，比如：Web Service形式，HTTP形式，還有郵件接口：往[email protected]發個短小的郵件，這個郵件會以短信的形式轉發到手機上，等等。隻要往接口傳參數，告訴它發給誰，發什麼内容，就可以了。

顯示号碼為某個SIM卡号碼，或者為固定号碼 (如：106開頭的)，這取決于短信平台和營運商的實作方式，因為營運商發短信是不要卡的，直接可以發給目标号碼，而且可以顯示為固定的某個号碼。

Web Service接口位址參考：http://123.456.789.000/SmsManager.asmx?wsdl

Http接口位址參考：http://api.abc.xyz/sms/send.html

2. 郵件

凡是實作了SMTP協定的元件，都可以發送郵件。

在Windows環境下，有系統自帶的元件CDO (Collaboration Data Objects，以前叫OLE Messaging 或者Active Messaging)，是MAPI庫的COM封裝。不管是自己開發程式，使用VBS，還是SQL Server的SQL Mail/Database Mail，通常都是調用的這個元件。

SMTP協定要求的參數大緻如下：

SMTP Hostname: SMTP伺服器名，如mail.test.com或者IP

SMTP Port: SMTP服務端口，25

SMTP Username: 通過SMTP發送郵件用來驗證的使用者名, 如果不要求身份驗證，留白

SMTP Password: 通過SMTP發送郵件用來驗證的密碼, 如果不要求身份驗證，留白

不管是選擇硬體，還是第三方接口，都需要一個程式來調用，可以是監控工具、腳本、甚至資料庫。

(1) 監控工具/應用程式中，通常都留有短信接口的配置，配置接口位址即可；

(2) 在腳本中配置，Windows環境通常要借助OLE Automation；

OLE Automation後來改名叫Automation，是Windows上基于COM，用于腳本語言實作程序間通訊的機制，腳本如：VBS, SQL, Powershell，不包括BAT(BAT可以調用VBS)。

SQL Server中使用OLE Automation調用Web Service短信接口如下：

exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

declare @text_message nvarchar(180)
       ,@phone_number nvarchar(15)
       ,@soap_object  int
       ,@status       int
       ,@output       nvarchar(255)

set @text_message = N'Testing Mail'
set @phone_number = N'138000139000'

--Create MSSOAP.SoapClient object
exec @status=sp_OACreate 'MSSOAP.SoapClient', @soap_object out

--SmsManager is Web Service name
exec @status = sp_OAMethod @object, 'mssoapinit', null, 'http://123.456.789.000/SmsManager.asmx?wsdl', 'SmsManager'

--SendTextMessage is webservice method
exec @status = sp_OAMethod @object, 'SendTextMessage', @output OUT, @phone_number, @text_message

if @status <> 0
begin
   exec sp_OAGetErrorInfo @soap_object   
   select @soap_object
end
else
begin
   select @output
end

--Destroy MSSOAP.SoapClient object
exec @status = sp_OADestroy @soap_object
GO

對于HTTP, DLL接口，和SOAP接口類似，用OLE Automation也都可以調用，主要差別就是在CreateObject() 時。

以VBS為例，調用HTTP, DLL時CreateObject()如下：

Dim http 
Set http = CreateObject("Msxml2.XMLHTTP") 

Dim dll
Set dll = CreateObject("工程名.類名")

(1) 監控工具/應用程式中，通常都留有SMTP配置項，配置SMTP參數即可；

VBS發送郵件如下：

Dim ns
ns = "http://schemas.microsoft.com/cdo/configuration/"

Dim title, content
title = "db_maint_alert"
content = ""
content = content&"Hi All,"
content = content&chr(13)&chr(10)
content = content&" "
content = content&chr(13)&chr(10)
content = content&"----test mail----"
Msgbox('~1~')

Set cm = CreateObject("CDO.Message")
cm.from = "[email protected]"    
cm.to = "[email protected]"
cm.cc = "[email protected]"
cm.subject = title
cm.textbody = content
'cm.AddAttachment ""
Msgbox('~2~')

'sendusing: 1 = pickup, 2 = port
'smtpauthenticate: 0 = anonymous,1 = common,2 = NTLM
'smtpusessl: 0 = no,1 = yes
With cm.configuration.fields
    .item(ns & "sendusing") = 2
    .item(ns & "smtpserver") = "xxx.xxx.xxx.xxx"
    .item(ns & "smtpserverport") = 25
    .item(ns & "smtpauthenticate") = 1
    .item(ns & "sendusername") = "[email protected]"
    .item(ns & "sendpassword") = "*****************"
    .item(ns & "smtpconnectiontimeout") = 10
    .item(ns & "smtpusessl") = 0
    .update
End With
Msgbox('~3~')

cm.send
Set cm = nothing
Msgbox('~success~')

SQL Server 2000發送郵件如下：

SQL Server 2000有SQL Mail，不過必須要同伺服器上安裝一個實作了MAPI的郵件程式，如：OUTLOOK，因為SQL Mail需要借用郵件應用程式的MAPI來發送郵件，配置起來不太友善，是以使用類似上面VBS的OLE Automation方法。

use master;
if OBJECT_ID('sp_SendDatabaseMail') is not null 
    drop proc sp_SendDatabaseMail
go

CREATE PROCEDURE sp_SendDatabaseMail
        @recipients varchar(8000), --'[email protected]; [email protected];'
        @Subject    varchar(400) = '',
        @HtmlBody   varchar(8000) = ''
as
Declare @From varchar(100)
Declare @To varchar(100)
Declare @Bcc varchar(500)
Declare @AddAttachment varchar(100)
Declare @object int
Declare @hr int
Declare @source varchar(255) 
Declare @description varchar(500) 
Declare @output varchar(1000)

    set @From = '[email protected]'
    set @To = @recipients
    set @Bcc = ''
    set @AddAttachment = ''
    --set @HtmlBody= '<body><h1><font color=Red>' +@HtmlBody+'</font></h1></body>'

    EXEC @hr = sp_OACreate 'CDO.Message', @object OUT
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' 
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'xxx.xxx.xxx.xxx' 
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserverport").Value','25' 
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1' 
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value','[email protected]' 
    EXEC @hr = sp_OASetProperty @object, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value','*****************' 
    EXEC @hr = sp_OAMethod @object, 'Configuration.Fields.Update', null 
    EXEC @hr = sp_OASetProperty @object, 'To', @To 
    EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc 
    EXEC @hr = sp_OASetProperty @object, 'From', @From 
    EXEC @hr = sp_OASetProperty @object, 'Subject', @Subject 
    EXEC @hr = sp_OASetProperty @object, 'HtmlBody', @HtmlBody 

--add attachment
    if @AddAttachment<>''
    EXEC @hr = sp_OAMethod @object, 'AddAttachment',NULL,@AddAttachment
    IF @hr <>0 
    select @hr 
        BEGIN 
            EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT 
            IF @hr = 0 
                BEGIN 
                    SELECT @output = ' Source: ' + @source 
                    PRINT @output 
                    SELECT @output = ' Description: ' + @description 
                    PRINT @output 
                END 
            ELSE 
                BEGIN 
                    PRINT ' sp_OAGetErrorInfo failed.' 
                    RETURN 
                END 
        END 

--send mail
EXEC @hr = sp_OAMethod @object, 'Send', NULL 
IF @hr <>0 
    select @hr 
        BEGIN 
            EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT 
            IF @hr = 0 
                BEGIN 
                    SELECT @output = ' Source: ' + @source 
                    PRINT @output 
                    SELECT @output = ' Description: ' + @description 
                    PRINT @output 
                END 
            ELSE 
                BEGIN 
                    PRINT ' sp_OAGetErrorInfo failed.' 
                    RETURN 
                END 
end
PRINT 'Send Success!!!' 

--destroy object
EXEC @hr = sp_OADestroy @object

調用上面這個SP來發郵件：

EXEC sp_SendDatabaseMail 
        @recipients = '[email protected]; [email protected];', 
        @body = 'This is a testing mail',
        @HtmlBody = 'Testing Database Mail'

SQL Server 2005起，使用Database Mail，腳本如下：

--1. 啟用database mail
use master
GO
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Database mail XPs',1
reconfigure
GO

--2. 添加account
exec msdb..sysmail_add_account_sp
        @account_name            = 'SqlAlert'                -- mail account
       ,@email_address           = '[email protected]'       -- sendmail address
       ,@display_name            = 'SqlAlert'                -- sendusername
       ,@replyto_address         = null
       ,@description             = null
       ,@mailserver_name         = '***,***,***,***'         -- SMTP Address
       ,@mailserver_type         = 'SMTP'                    -- SQL 2005 only support SMTP
       ,@port                    = 25                        -- port
       --,@username                = '*********@test.com'    -- account
       --,@password                = '******************'    -- pwd
       ,@use_default_credentials = 0
       ,@enable_ssl              = 0                         --is ssl enabled on SMTP server
       ,@account_id              = null

--3. 添加profile 
exec msdb..sysmail_add_profile_sp 
            @profile_name = 'SqlAlert'         -- profile name 
           ,@description  = 'dba mail profile' -- profile description 
           ,@profile_id   = null

--4. 關聯account and profile 
exec msdb..sysmail_add_profileaccount_sp  
            @profile_name    = 'SqlAlert'     -- profile name 
           ,@account_name    = 'SqlAlert'     -- account name
           ,@sequence_number = 1              -- account order in profile  
                                         
--5. 發送database mail
EXEC msdb.dbo.sp_send_dbmail 
        @profile_name = 'SqlAlert',
        @recipients = '[email protected]; [email protected];', 
        @body = 'This is a testing mail',
        @subject = 'Testing Database Mail';
GO

注意：SMTP伺服器的配置，比如：是否使用smtp使用者驗證，SSL是否開啟，必須要和服務端一緻，否則無法發送郵件。

其他

(1) 告警的次數：被告警的問題也許正在進行中，告警還在反複頻繁發送，尤其用腳本輪詢時，注意設定次數和發送間隔；

(2) 告警的曆史記錄：短信或者郵件告警，最好都在資料庫中留一份記錄；

(3) 字元編碼：如果應用程式/接口不支援中文，可以把中文轉成UTF-8的字元編碼發送，然後再解析回來。

6. SQL Server資料庫監控 - 如何告警

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述