SQL Server 2000 Notification Services

1. SQL Server 2000Notification Services Bart De Smet [MVP] info@bartdesmet.net

2. Agenda • What is SQL Server Notification Services? • Notification applications defined • Introducing Notification Services • Notification Services Explained • Instances versus applications • Development cycle • Architectural overview • Troubleshooting and tips • Demos, demos, demos

3. Let’s show you first

4. What are SQL Server Notification Services? Notification applications defined

5. Notification ApplicationsDefinition Web NotificationApplication My Device of choice News ERP CRM Personalized and timely information sent to any device = what people want

6. Notification applicationsTimely delivery of information Activity Generation Routing End Points • Stock feeds • News services • Custom applications • LOB applications • Etc. • SQL Server Notification Services • .NET Alerts • LCS • SMS Aggregators • Exchange (SMTP) • Outlook • Windows Messenger • Messenger for Mac • Pocket PC • Smart Phone • SMS capable phone Information flow A solution may use some or all of these parts

7. Notifications(millions) Events Notification ApplicationsA high-level view Subscribers Subscriptions(millions) Source Systems Subscriber Devices and Systems Match

8. What are SQL Server Notification Services? Introducing Notification Services

9. SQL Server 2000 NS Platform • A platform for developing and deploying rich and highly scalable notification applications • A declarative programming model • Based on XML and T-SQL • An efficient and scalable execution engine • Based on SQL Server and .NET Framework • Developer writes Notification Application • Notification Services hosts and executes it

10. Notification Services releases • NS 2.0 SP1: Web download for SQL 2000 • http://www.microsoft.com/sql/ns • Free addition to SQL 2000 licensees • Standard and Enterprise Editions available • Future: SQL Server 2005 • Integrated component (cf. installation) • Management tools support • Analysis Services event provider • Programming model enhancements • Smooth upgrade from NS 2.0

11. Developer benefits • Build and deploy applications quickly • Reduce time-to-market • Build your application within a rich Notification Services framework • Focus on application, not basic services • Performance and Richness • “Internet scale” matching: millions of events against millions of subscriptions • Queue management, time zones, multi-language, scheduled subscriptions, fall behind, retry logic, multi-device…

12. Administrator benefits • Notification Services applications are easy to deploy and manage • Predictable, reliable tools • Deployment can be automated • Use standard mechanisms • Windows performance counters, Event log, SQL reporting stored procedures • Proven, tested reliability • Rigorous testing at Microsoft • Patterns and practices used over and over in real applications

13. Notification Services Explained Instances versus applications

14. Instances and applicationsDefinition • Instance • A single named configuration of Notification Services • Shared repository for subscriber data (not subscriptions!) • Application • An individual functional entity for collecting events, matching them with subscriptions, and delivering notifications • Part of an instance

15. Application 1 E S N Application 2 E S N Subscribers Instances and applicationsDefinition • One instance can host multiple applications • Applications in an instance share subscribers • Applications have their own events, subscriptions, and notifications

16. Instances and applicationsScalability • Multiple instances can exist on the same machine • Separate security and trust boundaries • Each instance can be a different version of Notification Services • Each instance has its own Windows Service • An instance can span multiple machines • Scale out • Each machine runs the Windows Service • Various roles can be spread across machines

17. Instances and ApplicationsDeclaration & configuration • “Instance Configuration File” (ICF) • Defines an instance • SQL Server used by the instance • Applications • Protocols and Delivery Channels available • “Application Definition File” (ADF) • Defines the schema, logic, and topology of a single application • Both are XML documents

18. The ICF and ADF

19. Notification Services Explained Development cycle

20. Development cycle Windows Service XML Files SQL ServerDatabases SQL-NS Compiler

21. Nscontrol.exe • SQL Server Notification Services Prompt • Used to create, register, enable instances and applications • No “Visual Studio .NET project” (!)

22. Development process Design • Define events, subscriptions, notifications • Design matching logic • Implement application core • Test with sample data Prototype Connect • Add event providers and delivery protocols • Build subscription management interface Deploy • Move application to production hardware • Implement monitoring and maintenance procedures

23. Compilation and deployment

24. Notification Services Explained Architectural overview

25. Subscription Management Application Event Provider Generator Distributor The big picture SQL Server Data Changes External Delivery

26. Subscribers Subscriber Devices Subscriptions Notifications Subscription Management Application Events Subscription Management Data Changes External Delivery Event Provider Generator Distributor

27. Subscription Management App • Allows subscribersto manage subscriptions • API in SQL-NS • Managed: ASP.NET, VB.NET, C#, etc • Unmanaged: ASP, VB6, C++, etc

28. Managing subscriptions

29. Subscribers Subscriber Devices Subscriptions Notifications Subscription Management Application Events EventProviderHost File System Watcher SQL Server provider Custom EP Event Providers Data Changes External Delivery Event Provider Generator Distributor

30. Event Providers • Event Providers collect data changes and submits them as Events in Event Batches • Notification Applications have one or more Event Providers • Notification Services ships with two standard providers • File System Watcher Event Provider (testing) • SQL Server Event Provider

31. SQL Server provider EventProviderHost Built-in Event Providers XML File System Watcher Provider EventProviderHost XMLevent file Events QueryPostQuerySchedule DataSource Events Changes

32. Custom Event Providers • Hosted Event Provider • Run in-proc with Notification Services Windows Service • Less custom code by leveraging Initialize, Run, Terminate, Schedule • Starts and Stops with Notification Services Windows Service • Independent Event Provider • Can run on alternate platform or remote system • Starts and Stops independently from Notification Services Windows Service • Three Event Submission Methods • Event Object • XMLLoader Object • Event Submission SQL Server Stored Procedures

33. Adding event providers

34. Subscribers Subscriber Devices Subscriptions Notifications Subscription Management Application Events Generator Data Changes External Delivery Event Provider Generator Distributor SQLMatchRule Executes Stored Procs EventProviderHost File System Watcher SQL Server provider Custom EP

35. Subscribers Subscriber Devices Subscriptions Generator Events Notifications Generator • Matches Events and Subscriptions to generate Notifications • Write “match rules” with full power of SQL (Transact-SQL, sp, transactions…)

36. Rules Classification Eventchronicle rules • Maintain Event state • One per event class • Fire for each incoming event • Always fired first • Example: Latest stock prices Subscriptionevent rules • Match subscriptions with events • Multiple per Subscription Class • Executed when the generator fires • Fired before scheduled rules • Example: Notify when MSFT crosses $70 • Match subscriptions with events • Multiple per Subscription Class • Time Zones • Recurrence • Example: Send MSFT price at 1PM Mon-Fri Subscriptionscheduledrules

37. Writing the rules

38. XSLT XSLT CF Email File HttpExt Custom CF .NET Alerts* Custom DP Subscribers Subscriber Devices Subscriptions Notifications Subscription Management Application Events Distributor Data Changes External Delivery Event Provider Generator Distributor EventProviderHost File System Watcher SQLMatchRule Executes Stored Procs SQL Server provider Custom EP

39. Distributor • Periodically scans for notification batches • Formats raw notification data to message body • Assembles formatted notifications into protocol packets • Delivers the protocol packet • Handles delivery failures

40. Content Formatting • Format raw SQL Notification data • Language-CountryRegion and DeviceType • Specified per NotificationClass • Stock notifications are formatted differently from Flight notifications • Built-in XSLT Content Formatter • Applies a XSLT to Notifications • Custom Content Formatter • Written as Managed Class

41. Delivery Richness RichFormatting • Choice of content formatting (XSLT or custom content formatter) • Device specific (mail, SMS, IM, etc) • Language specific (localized notifications) Digest notifications • Combine notifications sent to the same user • Send me a daily summary of my portfolio • Send me a summary of all the documents that have been modified since the last time I connected ‘Multicast’ notifications • Same message to many people • Format once, deliver to many • Weather report for the Brussels area • Traffic on E40

42. Get it delivered

43. XSLT XSLT CF Email File HttpExt Custom CF .NET Alerts* Custom DP Subscribers Subscriber Devices Subscriptions Notifications Subscription Management Application Events The picture that tells it all... Data Changes External Delivery Event Provider Generator Distributor EventProviderHost File System Watcher SQLMatchRule Executes Stored Procs SQL Server provider Custom EP

44. Troubleshooting and tips About debugging, manageability, performance, scalability and deployment

45. Detecting Problems • Event log • Shows error and warning messages • Check and doublecheck permissions • Stored Procedure Richness • Views installed at runtime • Manual submission of events • Management op subscriptions • Performance Counters • Provide throughput data for various parts of the NS pipeline

46. NS generates notifications faster than any protocol can deliver! NS scales with the hardware platform Building For Performance SQL Server • Data, logs, tempdb on dedicated disks • Pre-allocate database to prevent auto-grow • Disk subsystem vs. # CPU Subscribers, Subscriptions • Single / Multi-Threaded • Bulk load using multiple clients Generator • Beware of rule complexity; Install indexes on tables • ThreadPoolSize and parallel rule firing • Optimize here! Distributors • Use multiple machines • Notification batch size

47. SQL SQL SQL SQL SQL NS NS NS Single server Highly scalable distribution Separate DB NS Highly available Deployment Scenarios

48. Summary Useful resources, books, summary, Q&A

49. Resources • Information and downloadwww.microsoft.com/sql/ns • TechNethttp://www.microsoft.com/technet/prodtechnol/sql/2000/evaluate/sqlnsto.mspx • Books onlinehttp://www.microsoft.com/sql/ns/techinfo/productdoc/default.asp • .NET Alerts (30-days free trial)https://www.netservicesmanager.com/wizard/default.aspx

50. Notification Services Books • Microsoft SQL Server 2000 Notification Services (Shyam Pather) • The Rational Guide to SQL Server Notification Services (Joe Webb)