1 / 38

Event Notifications with Service Broker

Learn about event notifications and service broker, implement a client/repository setup, and troubleshoot and monitor database events.

charlesaanderson
Download Presentation

Event Notifications with Service Broker

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. Event Notifications with Service Broker Phil Brammer

  2. Phil Brammer • Over 10 years’ experience in database technologies, data warehousing, ETL, on-call… • Started on Red Brick Data Warehouse. Ralph Kimball’s product. Inmon Who? • Worked with Teradata at PayPal • Operationally manages multi-terabyte instances • Dabbles a bit in SSIS – ssistalk.com • Microsoft MVP, SQL Server – 5th year

  3. Denali • Code name • Yukon, Katmai, Kilimanjaro • CTP 3 • microsoft.com/sql/

  4. Agenda • We will • learn about Event Notifications • go over Service Broker concepts • implement a client/repository setup • ask questions as we go through the session

  5. Background at BCBSNE • Say hi to Kevin Klein of Quest Software • BCBSNE uses Quest Spotlight for monitoring • Over 1,000 databases • Two databases over two terabytes in size • ETL very popular • 15-50 database change controls per week • Microsoft shop

  6. Challenges at BCBSNE • Compliance • When things go wrong, who changed what? • What kind of permissions changes happened? • Autogrowths are bad. • Shrinks are worse.

  7. Event Notifications • Similar to a server-side trace • User-configurable • Interfaces with Service Broker • Asynchronous

  8. Using Event Notifications • Compliance monitoring • Operational monitoring • Troubleshooting support

  9. Compliance Monitoring • Archive logins and logouts • Query permissions changes on an object • Capture impersonation activity

  10. Operational Monitoring • Near real-time data/log file auto growths • Shrinks too • Alert on blocking • Alert on deadlocks • Capture backup/restores • Log reindexing activity

  11. Troubleshooting Support • Determine if indexes were dropped recently

  12. Service Broker • Queuing model • Guaranteed message delivery • Asynchronous • Conversation based

  13. Setting up Event Notifications • Setup repository server first • We will need: • To ensure Service Broker is enabled in target database • Setup required tables to hold our data • Setup Service Broker components • Setup client next • Setup Event Notifications • Setup Service Broker components to talk to repository

  14. Setup Repository • Walk through setup

  15. Messages • Sent on a conversation • Bound to a message type

  16. Conversations • Belong to a conversation group • Dialog between two services • Provides exactly-once-in-order delivery • Watch out for fire and forget designs • Ended via two-way acknowledgement

  17. Message Types • Enforces message type validation • Well formed XML • No validation • Empty • Valid XML with Schema • CREATE MESSAGE TYPE [AuditData] AUTHORIZATION [dbo] VALIDATION = NONE

  18. Contracts • Defines which message types are allowed in a conversation • Message types in a contract must be included on both client and repository sides • Enforces who can send a message type on a contract • Initiator, Target, Any • CREATE CONTRACT [AuditDataContract] AUTHORIZATION [dbo] ([AuditData] SENT BY INITIATOR, [EndOfTimer] SENT BY INITIATOR)

  19. Queues • Destination for messages • Transactionally consistent • Survives restarts • Can activate a stored procedure one or many times • CREATE QUEUE [dbo].[TargetAuditQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [Audit].[prcWriteAuditData] , MAX_QUEUE_READERS = 50 , EXECUTE AS N'dbo' ) ON [PRIMARY]

  20. Activation Procedure • Initiated by a message landing on a queue • Activation can be turned on and off • Activation can spawn many iterations in parallel as load increases • Demo

  21. Services • Hooked up to a queue • Talks to another service • Bound to one or more contracts • CREATE SERVICE [AuditDataWriter] AUTHORIZATION dbo ON QUEUE dbo.TargetAuditQueue ([AuditContract]);

  22. Routes • Sets up a route for messages to understand where to go. • Can set it up many different ways. • We’ll use a transport route • Uses service name to tell message where to go • CREATE ROUTE [AuditRoute] WITH ADDRESS = N'TRANSPORT';

  23. Endpoints • A physical end point (i.e. TCP/IP port) • Authenticates connections • Belong to master database • CREATE ENDPOINT AuditEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 10050) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS NTLM );

  24. Volunteer Time • 10 volunteers

  25. Setup Clients • Walk through setup

  26. Client Event Notifications • First we need to set up Event Notifications (EN) • Four steps. • Create a queue • Create a service • Create a route • Create an Event Notification

  27. EN Queue • Same as in the previous setup. Just a plain ol’ queue. • CREATE QUEUE [dbo].[NotificationsQueue] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY] GO

  28. EN Service • CREATE SERVICE [NotificationsService] AUTHORIZATION [dbo] ON QUEUE [dbo].[NotificationsQueue] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO • Note the system contract

  29. EN Route • CREATE ROUTE [NotificationsRoute] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'NotificationsService' , ADDRESS = N'LOCAL' GO • Note how this is different from the last route we created

  30. Event Notification • This is where we setup the events to monitor • Points to a service (which points to a queue) • CREATE EVENT NOTIFICATION NotificationEvent ON SERVER FOR BLOCKED_PROCESS_REPORT, DEADLOCK_GRAPH, DDL_TABLE_EVENTS TO SERVICE 'NotificationsService', 'current database'; GO

  31. Event Notification Demo • Let’s see it work • How does it get to the repository?

  32. Getting data to repository • What will we need? • Stored procedure to process items in EN queue • Stored procedure to send data • Message Types to match repository • Contract to match repository • Another queue to receive messages back from repository • Another stored procedure to process items in received queue • A service • A route • Some back-end tables

  33. Client-Side Tables • Two tables required • AuditErrors to capture any errors • SessionConversations to keep track of current conversations that are in use

  34. Client-Side Stored Procedures • Review and implement stored procedures • prcProcessNotification • prcSendAuditData • prcProcessSenderAuditQueue

  35. Client-Side Service Broker • Similar to previous setups. • Review client-side Service Broker components

  36. Client-Side Implementation • Implement the changes to direct data to repository

  37. Questions • Any further questions?

  38. Resources • Event Notifications - http://msdn.microsoft.com/en-us/library/ms190427.aspx • Service Broker Architecture - http://technet.microsoft.com/en-us/library/ms166125.aspx • DDL Events for Event Notifications - http://msdn.microsoft.com/en-us/library/bb510452.aspx • Trace Events for Event Notifications - http://msdn.microsoft.com/en-us/library/ms190655.aspx • Fire and forget - http://rusanu.com/2006/04/06/fire-and-forget-good-for-the-military-but-not-for-service-broker-conversations/

More Related