如何在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
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'\\.\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 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 ( [https://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 开始， 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]

GO

GRANT ALTER ANY EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT AUTHENTICATE SERVER TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT CONTROL SERVER TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT CREATE DDL EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT CREATE TRACE EVENT NOTIFICATION TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT CONTROL ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT IMPERSONATE ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]

GO

use [master]

GO

GRANT VIEW DEFINITION ON LOGIN::[ SQL_starting_account] TO [NT AUTHORITY\SYSTEM]

GO

 

By 微软亚太区全球技术支持中心 数据库技术支持组

对话微软原厂技术专家，手把手教会你 SQL Server性能调优: https://support.microsoft.com/gp/anxin_training2/zh-cn

给SQL Server来个全套体检，有病治病，无病强身：https://support.microsoft.com/gp/anxin_guide6/zh-cn

SQL Server技术支持大全，根治纠结您N久的疑难杂症：https://support.microsoft.com/gp/anxin_sql/zh-cn