【监控笔记】【1.5】事件通知(event Notification)
关键词:DDL监控
【监控笔记】【1.5】事件通知(event Notification)
注意,只能通过删除新建来修改事件。
【1】概念
事件通知是特殊类型的数据库对象,用于将有关服务器和数据库实践的信息发送到 Service Broker服务。
执行事件通知可对各种T-SQL数据定义语言DDL语句和SQL跟踪事件作出响应,采取的响应方式是将这些事件的相关信息发送到Service Broker服务。
【2】设计事件通知
必须定义通知的作用域、引发事件通知的T-SQL语句或语句组!
(1)定义通知作用域:
用户可以将事件通知指定为响应针对【当前数据库】或【sql server实例中的所有对象执行的语句】。
针对Queue_activation 和 Broker_queue_disabled 事件指定的时间通知的作用域限定为单个队列。并发所有事件都可以在任何作用域级别执行。
比如Create_database 事件仅可以在服务器实例级别执行。
相反,针对Alter_table 时间创建的时间通知可以通过编程,针对数据库或服务器实例中的所有表执行。
(2)指定T-SQL语句或语句组:
可以创建时间通知响应以下内容
特定的DDL语句、SQL跟踪事件或Service Broker事件。 预定义的DDL语句组或SQL跟踪事件组。
(3)选择特定的DDL语句及SQL跟踪引发事件通知
事件可以设计为运行特定的T-SQL语句或存储过程之后执行,如后面例子中事件通知在alter——table事件之后执行。
相关引发事件通知的列表可以参考相关事件,还可以查询 sys.event_notification_event_types 目录视图获取。
注意,SQL跟踪事件仅可在服务器实例作用域执行。
(4)选定特定的Service Broker 事件引发事件通知
事件通知可以设计为在 Queue_activation 或 Broker_Queue_disabled Service Broker事件之后激发。
当队列有消息要处理时,将发生Queue_Activation事件。
但队列状态设置为 Off 时,将发生Broker_Queue_Disabled 事件。
(5)选择预定义的DDL语句组引发事件通知
事件通知可以在任何属于预定义想死事件组的T-SQL事件运行之后执行。
如果要使事件通知在CREATE/ALTER/DROP TABLE语句之后执行,可以在 create event notificatio 语句中执行for ddl_tables_events.
执行create event notification后,事件组将添加到sys.events目录视图中。
(6)选择预定义的SQL跟踪事件组引发事件通知
如果要使时间通知在任何与锁定相关的跟踪事件(包括Lock_deadlock、lock_deadLock_chain、lock_escalation 和 deadLock_graph 事件)之后执行。
则可以在create event notification 语句中指定for trc_locks.
(7)实现事件通知
若要实现事件通知,必须先创建目标服务以接收时间通知,然后在创建事件通知。
(8)创建目标服务
无需创建 Service Broker 启动服务,因为 Service Broker 包含以下特定的事件通知消息类型和约定。
http://schemas.microsoft.com/SQL/Notifications/PostEventNotification
接收事件通知的目标服务必须使用此预先存在的约定。
创建目标服务:《1》创建队列以接收消息 《2》在引用事件通知约定的队列上创建服务
《3》创建服务路由,以定义Service Broker 将服务消息发送到的地址。对于指向同一数据库中的服务的事件通知,请指定Address='local'.
(9)创建事件通知
create event notification .. drop event notification. ... 若是要修改,需要先删除,再创建。
【3】事件通知实践
【3.1】检查Service Broker是否在数据库级别被启用
如果没有,启用它。然后创建一个SSB队列。后续需要用它来存放事件通知信息。
use test go if exists( select 1 from sys.databases where name = 'test' and is_broker_enabled=0 ) begin alter database test set enable_broker; end --create a queue which will hold the tracked information create queue dbo.eventNotificationQueue go --check if the queue is created or not select * from sys.service_queues where name = 'eventNotificationQueue'; go --create a service on whick tracked information will be sent create service [//test] on queue dbo.eventNotificationQueue([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) go --check if the service is created or not select * from sys.services where name = '//test' go
【3.2】定义数据库级别的DDL事件通知
--Create_database_event_ddl.sql
--create a notification to track ddl command use test go create event notification Notify_DDLTABLE_Event on database for DDL_TABLE_EVENTS to Service '//test','current database' go /* create event notification Notify_CREATETABLE_Event on database for Create_table to Service '//test','current database' go create event notification Notify_ALTERTABLE_Event on database for Alter_table to Service '//test','current database' go create event notification Notify_DROPTABLE_Event on database for drop_table to Service '//test','current database' go */ select * from sys.event_notifications
【3.3】创建服务器实例级别的事件通知
--Create_server_event_notifications.sql
use test go --create a notification to error occuring at server level create event notification Notifi_ERROR_Event on server with fan_in for errorlog to service '//test','current database' go --check if the above notification was created or not select * from sys.server_event_notifications where name in ('Notifi_ERROR_Event')
![](https://img2018.cnblogs.com/blog/1302413/201905/1302413-20190529200450683-1833316952.png)
【3.4】测试操作,事件通知信息查看(实现DDL监控)
use test go --generate a create table event create table test1_1( id int, num int ); go --generate an alter table event alter table test1_1 add num1 int; go --generate a server level event raiserror(N'Generating error for Event Notification testing..',16,1) with log; go --generate drop table event drop table test1_1; go --review if the events were tracked in queue select cast(message_body as xml) as messages_in_xml from dbo.eventNotificationQueue go
点开一看,很详细
格式化成表
--select *,identity(int,1,1) as rn into #temp1 from test.dbo.eventNotificationQueue declare @data xml declare @h int select @data=cast(message_body as xml) from #temp1 where rn=4
--DML --declare @str varchar(1000) --select @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)') as EventType , --@data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') PostTime , --@data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(128)') SPID , --@data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)') ServerName , --@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)') LoginName , --@data.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)') UserName , --@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)') DatabaseName , --@data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'varchar(128)') SchemaName , --@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(128)') ObjectName , --@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)') ObjectType , --@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(128)') TSQLCommand --server event declare @str varchar(1000) select @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)') as EventType , @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'varchar(128)') PostTime , @data.value('(/EVENT_INSTANCE/SPID)[1]', 'varchar(128)') SPID , @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'varchar(128)') ServerName , @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)') LoginName , @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)') DatabaseName , @data.value('(/EVENT_INSTANCE/HostName)[1]', 'varchar(128)') HostName , @data.value('(/EVENT_INSTANCE/SessionLoginName)[1]', 'varchar(128)') SessionLoginName , @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(128)') ObjectType , @data.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(128)') TSQLCommand
![](https://img2018.cnblogs.com/blog/1302413/201905/1302413-20190530190116839-1364027802.png)
【3.5】按顺序删除
use test go --drop all objects --drop event notification Notify_CREATETABLE_Event on database; --drop event notification Notify_ALTERTABLE_Event on database; --drop event notification Notify_DROPTABLE_Event on database; drop event notification Notify_DDLTABLE_Event on database; drop event notification Notifi_ERROR_Event on server; drop service [//test]; drop queue dbo.eventNotificationQueue;