【监控笔记】【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')

 

 

 【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

 

 

 

 

 【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;

 

参考：https://www.cnblogs.com/gaizai/p/3473553.html