1 / 14

Empower SQL Server with Event Alerts and Notifications

Learn to create, manage, and respond to SQL Server Agent alerts and event notifications for efficient system monitoring and management. This guide covers defining alerts, setting conditions, and responding through jobs or operators. Utilize Service Broker for event processing and XQuery for message extraction. Take your SQL Server skills to the next level with actionable insights.

levana
Télécharger la présentation

Empower SQL Server with Event Alerts and Notifications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Make SQL Server to talk you! Antonios Chatzipavlis Software Architect , Development Evangelist, IT Consultant MCT, MCITP, MCPD, MCSD, MCDBA, MCSA, MCTS, MCAD, MCP, OCA MVP on SQL SERVER

  2. Objectives • SQL Server Agent Alert System • Event Notifications

  3. What Is an Alert? • Predefined response to an event • Triggered by jobs, performance conditions, or application log events • Can notify an operator or start a specified job

  4. Create an Alert Use SQL Server Management Studio or “sp_add_alert” • Specify • Alert name • Condition (Event, WMI trigger, performance condition) that triggers the alert • Response – notify an operator, start a job • EXEC dbo.sp_add_alert • @name = N'Test Alert', • @message_id = 55001, @severity = 0, @notification_message = N'Error 55001 has occurred. The database will be backed up...', • @job_name = N'Back up the AdventureWorks Database' ;

  5. Responding to Alerts Either • Launch a Job • Use an existing job • Create a new job Or • Notify an Operator • Schedule responders for different parts of the day or week • E-mail, pager, or net send

  6. Using Alerts DEMO

  7. Overview of Event Notifications • Messages containing event data DDL Events Trace Events DML Events Database and Server Events • Sent to an event processing service by usingService Broker A message type and contract are predefined You must create a queue, a service, and a route

  8. Creating Event Notifications • Define the Event Notification • Specify the Scope • Specify the Event • Specify the Service CREATE EVENT NOTIFICATION NotifyALTER_T1 ON DATABASE FOR ALTER_TABLE TO SERVICE '//Adventure-Works.com/ArchiveService' , '8140a771-3c4b-4479-8ac0-81008ab17984';

  9. Processing Event Notifications • Receive the message • Extract event data by using XQuery DECLARE @cmdnvarchar(1000) DECLARE @posttimenvarchar(24) DECLARE @spidnvarchar(6) SET @cmd = @messagebody.value ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(100)') SET @messagebody.value ('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(24)') SET @spid = @messagebody.value ('(/EVENT_INSTANCE/SPID)[1]','nvarchar(6)') DECLARE @messageTypeName NVARCHAR(256), @messageBody XML ;RECEIVE TOP(1) @messageTypeName = message_type_name, @messageBody = message_body FROM dbo.NotifyQueue; IF @@ROWCOUNT = 0 RETURN

  10. Managing Event Notifications • Viewing event notifications and queues • Deleting event notifications SELECT name FROM sys.event_notifications SELECT definition FROM sys.service_queues DROP EVENT NOTIFICATION UpdateStats ON SERVER

  11. Implementing Event Notifications DEMO

  12. Q & A

  13. My Blogs http://www.autoexec.gr/blogs/antonch http://www.dotnetzone.gr/cs/blogs/antonch

  14. Thank you

More Related