如何在SQL Server中添加WMI alert

SQL Server可以支持WMI alert，因此我们可以使用WMI event 来监控SQL Server中的某些事件发生，并在此时出发SQL Server alert，指定执行我们需要的语句。

以下是WMI alert的一个示例脚本：IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL

BEGIN

&">nbsp;   DROP TABLE DeadlockEvents ;

END ;

GOCREATE 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'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',

    @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 databasec. sqlwep100!CSQLServerEventProvider::FIsServiceBrokerEnabled –检查 MSDB上”broker enabled”有没有启用d. sqlwep100!CSB
Deployment::Create
DeploymentIfNecessary –创建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 A
LTER_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 开始， builtin\administrator 用户组默认已经从SQL Server的login中移除了。 在上述列出的a, b, c, d 四个步骤中，b, d 两个步骤都可能会遇到权限问题。b.    连接SQL Server master database—我们需要将NT AUTHORITY\SYSTEM 加入到SQL login中，并且grant “public”用户组c.       MSDB的”broker enabled” 启用： d.      执行创建对象的脚本需要给用户NT AUTHORITY\SYSTEM赋予以下权限：use [master]GOGRANT ALTER ANY EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT AUTHENTICATE SERVER TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT CONTROL SERVER TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT CREATE DDL EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT CREATE TRACE EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT VIEW ANY DEFINITION TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT CONTROL ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT IMPERSONATE ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]GOuse [master]GOGRANT VIEW DEFINITION ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]GO