sql server可以支持wmi alert，因此我們可以使用wmi event 來監控sql server中的某些事件發生，并在此時出發sql server alert，指定執行我們需要的語句。 以下是WMI alert的一個示例腳本： IF OBJECT_ID(‘DeadlockEvents’, ‘U’) IS NOT NULL BEGIN DROP TABL
sql server可以支持wmi alert，因此我們可以使用wmi event 來監控sql server中的某些事件發生，并在此時出發sql server alert，指定執行我們需要的語句。
以下是WMI alert的一個示例腳本：
IF OBJECT_ID(‘DeadlockEvents’, ‘U’) IS NOT NULL
BEGIN
??? DROP TABLE DeadlockEvents ;
END ;
GO
CREATE TABLE DeadlockEvents
??? (AlertTime DATETIME, DeadlockGraph XML) ;
GO
— Add a job for the alert to run.
EXEC? msdb.dbo.sp_add_job @job_name=N’Capture Deadlock Graph’,
??? @enabled=1,
??? @description=N’Job for responding to DEADLOCK_GRAPH events’ ;
GO
— Add a jobstep that inserts the current time and the deadlock graph into
— the DeadlockEvents table.
EXEC msdb.dbo.sp_add_jobstep
??? @job_name = N’Capture Deadlock Graph’,
??? @step_name=N’Insert graph into LogEvents’,
??? @step_id=1,
??? @on_success_action=1,
??? @on_fail_action=2,
??? @subsystem=N’TSQL’,
??? @command= N’INSERT INTO DeadlockEvents
??????????????? (AlertTime, DeadlockGraph)
??????????????? VALUES (getdate(), N”$(ESCAPE_SQUOTE(WMI(TextData))))’,
??? @database_name=N’AdventureWorks’ ;
GO
— Set the job server for the job to the current instance of SQL Server.
EXEC msdb.dbo.sp_add_jobserver @job_name = N’Capture Deadlock Graph’ ;
GO
— Add an alert that responds to all DEADLOCK_GRAPH events for
— the default instance. To monitor deadlocks for a different instance,
— change MSSQLSERVER to the name of the instance.
EXEC msdb.dbo.sp_add_alert @name=N’Respond to DEADLOCK_GRAPH’,
@wmi_namespace=N’. ootmicrosoftSqlServerServerEventsMSSQLSERVER’,
??? @wmi_query=N’SELECT * FROM DEADLOCK_GRAPH’,
??? @job_name=’Capture Deadlock Graph’ ;
GO
?
這個腳本是用WMI事件來監控SQL Server有沒有deadlock 發生，如果發生了deadlock，就調用一個job，把相關的內如寫入我們事先創建好的表中。
?
在SSMS 里面用圖形界面創建WMI alert如下：
這兩種方式都需要指定SQL Server WMI的namespace。
我們可以從這里得到當前SQL實例的WMI namespace的路徑:
?
那么，創建WMI alert的過程究竟是怎樣的呢？
SQL Server的WMI event provider 包含在Sqlwep100.dll 中。在我們創建WMI alert時，WMI service需要先將Sqlwep100.dll 裝載并且初始化。
當SQL Server 調用WMI query的時候，會有一個對應的WMIPRVSE.exe 被啟動。
WMIPRVSE.exe 啟動后，裝載Sqlwep100.dll并且做provider 初始化，初始化包含以下幾個過程：
a.??? sqlwep100!CSQLServerEventProvider::Initialize? –開始初始化
?b. sqlwep100!CSQLServerEventProvider::HrConnectToSQL—連接SQL Server master database
c. sqlwep100!CSQLServerEventProvider::FIsServiceBrokerEnabled –檢查 MSDB上”broker enabled”有沒有啟用
d. sqlwep100!CSBDeployment::CreateDeploymentIfNecessary –創建WMI alert 在SQL Server中所需要的對象。
?
對于我們文中的實例，需要創建以下對象：
IF NOT EXISTS(select * from sys.service_queues where name=‘WMIEventProviderNotificationQueue’) CREATE QUEUE WMIEventProviderNotificationQueue;
IF NOT EXISTS(select * from sys.services where name=‘SQL/Notifications/ProcessWMIEventProviderNotification/v1.0’) CREATE SERVICE [SQL/Notifications/ProcessWMIEventProviderNotification/v1.0] ON QUEUE WMIEventProviderNotificationQueue( [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification] );
IF NOT EXISTS(select * from sys.server_event_notifications where name=‘SQLWEP_RECHECK_SUBSCRIPTIONS’) CREATE EVENT NOTIFICATION SQLWEP_RECHECK_SUBSCRIPTIONS ON SERVER WITH FAN_IN FOR ALTER_LOGIN, DROP_LOGIN, ALTER_USER, DROP_USER, ADD_ROLE_MEMBER, DROP_ROLE_MEMBER, ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER, DENY_SERVER, REVOKE_SERVER, DENY_DATABASE, REVOKE_DATABASE TO SERVICE ‘SQL/Notifications/ProcessWMIEventProviderNotification/v1.0’, ‘current database’;
SELECT service_broker_guid FROM sys.databases WHERE name=‘msdb’
從windows的task manager中我們可以觀察到，SQL Server對應的WMIPRVSE.exe 的啟動用戶是system. 從SQL Server 2008 開始， builtindministrator 用戶組默認已經從SQL Server的login中移除了。 在上述列出的a, b, c, d 四個步驟中，b, d 兩個步驟都可能會遇到權限問題。
b.??? 連接SQL Server master database—我們需要將NT AUTHORITYSYSTEM 加入到SQL login中，并且grant “public”用戶組
c.?????? MSDB的”broker enabled” 啟用：
?
d.????? 執行創建對象的腳本需要給用戶NT AUTHORITYSYSTEM賦予以下權限：
use [master]
GO
GRANT ALTER ANY EVENT NOTIFICATION TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT AUTHENTICATE SERVER TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT CONTROL SERVER TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT CREATE DDL EVENT NOTIFICATION TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT CREATE TRACE EVENT NOTIFICATION TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT VIEW ANY DEFINITION TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT CONTROL ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT IMPERSONATE ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITYSYSTEM]
GO
use [master]
GO
GRANT VIEW DEFINITION ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITYSYSTEM]
GO
