Introduction to SQL Server Notification Services

1. Introduction to SQL Server Notification Services By: Randy M. Franklin, Jr. Wednesday, October 22, 2014

2. What Notification Services Is Not • Not a replacement for SQL Agent Mail • Not a replacement for SQL Mail • Not a replacement for Reporting Services, Crystal Reports or other reporting platforms

3. What Notification Services Is • A framework for building and deploying notification applications • A scalable solution for sending alerts to subscribers • A framework for empowering users to manage their own subscriptions • A replacement for parameterized reporting jobs

4. Target Devices • E-mail addresses • SMS devices • Instant messaging clients • Voicemail • .NET alerts • Fax • RSS

5. SMTP E-mails File .NET Alerts Partners for Notification Delivery Services Custom outputs can be designed using .NET applications Delivery Protocols Standard Protocols Additional Protocols

6. How the Application Produces Notifications • Subscribers create subscriptions related to the application. • The application collects events. • Notification Services matches subscriptions to events. • When an event and subscription match, Notification Services generates a notification. • Notification Services then formats the notification and sends it to a the device specified in the subscription.

7. Anatomy of an NS Application • Each instance runs as a Windows Service or is hosted in another process • One SQL Server database for the instance • One SQL Server database per Notification Application

8. Events Batches of data collected for notifications Examples: Shipped next movie in a subscriber’s rental queue New vendor created in AP system Machine finished running tests Information Stored In The Databases

9. Events Event providers capture and store the event data Standard Providers: File System Watcher Event Provider SQL Server Event Provider Analysis Services Event Provider Information Stored In The Databases

10. Events Notifications Defines Fields contained in the resulting message Formatter used Information Stored In The Databases

11. Events Notifications Generator Settings Which computer runs the generator How many threads can be used Each application has one generator Information Stored In The Databases

12. Events Notifications Generator Distributor Governs Notification formatting Delivery Information Stored In The Databases

13. Events Notifications Generator Distributor Settings Which computer runs the distributor It’s possible to run distributors on multiple computers per app Thread pool size Quantum duration how frequently an idle distributor checks for work items Information Stored In The Databases

14. Events Notifications Generator Distributor Subscribers Subscriber data includes Subscriber ID Info about his devices E-mail addresses Text messaging addresses Voicemail number Information Stored In The Databases

15. Events Notifications Generator Distributor Subscribers Subscriptions Subscription data includes Fields subscriber is interested in Which device receives the notifications Information Stored In The Databases

16. Developing an NS Application • XML defines an instance config file (ICF) • XML defines an application definition file (ADF) • Applications using the standard Event Providers and Content Formatters can use SSMS or any other XML/text editor • Use Visual Studio 2005 • for ease of development • When developing non-hosted event providers or formatters

17. Demonstration