Home Database Mysql Tutorial 事件通知(Event Notification)实践

事件通知(Event Notification)实践

Jun 07, 2016 pm 04:42 PM
broker notification event notification

事件通知(EventNotification)实践问题描述作为DBA,我们常常需要在SQLServer实例或数据库级别上跟踪正在发生的事件。有没有方法跟踪这些修改而不会太影响SQLSe

事件通知(Event Notification)实践

 

问题描述

作为DBA,我们常常需要在SQL Server实例或数据库级别上跟踪正在发生的事件。有没有方法跟踪这些修改而不会太影响SQL Server性能呢?

 

解决方案

SQL Server 2005及其以后版本提供了事件通知(Event Notification)机制,来跟踪发生在数据库或实例级别上的事件或修改。这其实也可以通过DDL触发器或这SQL跟踪来实现,但是事件通知有异步相应事件和运行在事务范围之外的优点,因此能作为数据库应用程序的一部分,捕获预定义的事件,而无需占用分配给事务的资源。

 

事件通知是一个使用DDL触发器或SQL跟踪的可编程的替代方案,执行相应不同的DDL语句、SQL跟踪、Service Broker事件(像QUEUE_ACTIVATION或BROKER_QUEUE_DISABLED),然后以XML格式发送信息到SQL Server Service Broker服务。换句话说,当创建一个通知,SQL Server跟踪预定义的事件,并将发生的事件写入到SSB服务,然后异步地从SSB队列接收信息。

 

步骤一:

首先检查Service Broker是否在数据库级别被启用,如果没有,启用它。然后创建一个SSB队列,SSB服务将会用这个队列去存储消息到服务。该服务使用内置的契约(契约定义了一个能发送到SSB服务的消息类型),,专用于事件通知(Event Notification)。注意:你需要排他访问数据去执行修改数据库命令。

 

--Check if the database is enabled for Service Broker --If not then enable it IF EXISTS (SELECT * FROM sys.databases WHERE name = 'AdventureWorks2012' AND is_broker_enabled = 0) ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER; GO USE AdventureWorks2012 GO --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 which tracked information will be sent CREATE SERVICE [//AdventureWorks2012/EventNotificationService] ON QUEUE dbo.EventNotificationQueue ([]) GO --Check if the service is created or not SELECT * FROM sys.services WHERE name = '//AdventureWorks2012/EventNotificationService' GO

 

步骤二:

创建两个数据库级别的通知。首先,,当有一个创建表命令执行时,将发出通知;然后,当有一个修改表命令执行时,将发出通知。也可以创建一个通知事件组;例如,你可以创建一个单一的通知DDL_TABLE_EVENTS去跟踪像创建、修改、删除表的所有事件。

 

--Create a notification to track create table command CREATE EVENT NOTIFICATION NotifyCREATETABLEEvents ON DATABASE FOR CREATE_TABLE TO SERVICE '//AdventureWorks2012/EventNotificationService' , 'current database' GO --Create a notification to track alter table command CREATE EVENT NOTIFICATION NotifyALTERTABLEEvents ON DATABASE FOR ALTER_TABLE TO SERVICE '//AdventureWorks2012/EventNotificationService' , 'current database' GO --Check if both the above notifications created or not SELECT * FROM sys.event_notifications WHERE name IN ('NotifyCREATETABLEEvents','NotifyALTERTABLEEvents') GO

 

步骤三:

创建一个服务器级别的通知,当一个错误在SQL Server实例级别触发时,该通知即被触发。可以查看sys.server_event_notifications目录视图查看通知是否存在于该服务器上。

 

--Create a notification to error occuring at server level CREATE EVENT NOTIFICATION NotifyERROREvents ON SERVER WITH FAN_IN FOR ERRORLOG TO SERVICE '//AdventureWorks2012/EventNotificationService', 'current database' GO --Check if the above notification was created or not SELECT * FROM sys.server_event_notifications WHERE name IN ('NotifyERROREvents') GO

 

步骤四:

验证刚才建立的事件通知是否在正常工作。在这个脚本里,先创建一个表,然后修改它,它将会被数据库级别的事件通知捕获到,并且我使用了RAISERROR(WITH LOG从句需要被服务器级别事件通知捕获)在SQL Server里触发一个错误,该错误将会被上一个服务器级别的事件通知捕获到。

 

--Generate a create table event CREATE TABLE ABC ( COL1 INT, COL2 INT ) GO --Generate an alter table event ALTER TABLE ABC ADD COL3 INT GO --Generate a server level event RAISERROR (N'Generating error for Event Notification testing...', 16, 1) WITH LOG GO --Review if the events were tracked in queue SELECT CAST(message_body AS XML) AS message_in_xml FROM dbo.EventNotificationQueue GO

 

步骤五:

事件通知以XML格式发送捕获到的信息到SSB服务;可以查询队列去看到捕获的信息,但是你需要用RECEIVE命令从队列接收消息,如下所示,处理它们并从队列中移除。使用RECEIVE命令你可以设置在一次接受的记录的数量。在这个脚本中,我使用TOP (1)命令接受第一行队列中的消息,并显示它的内容。事件通知以XML格式发送消息(注意:我们使用创建服务的内置契约,它定义了只有XML数据能被写入到服务),因此我转换消息体到XML数据类型。因为我使用了TOP (1)从句在RECEIVE命令,因为队列中有3条记录,我运行了下面的命令3次。查询的结果如下图。也可以使用一个循环的结构去从队列读取所有的记录,而不用运行这个脚本多次。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

See all articles