SQL Server 2000 Notification Services Bart De Smet

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

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

Let’s show you first

What are SQL Server Notification Services? Notification applications defined

Notification Applications Definition Web News ERP CRM Notification Application My Device of choice Personalized and timely information sent to any device = what people want

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

Notification Applications A high-level view Subscribers Subscriptions (millions) Source System s Events Match Notifications (millions) Subscriber Devices and Systems

What are SQL Server Notification Services? Introducing Notification Services

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

Notification Services releases NS 2. 0 SP 1: 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

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, multilanguage, scheduled subscriptions, fall behind, retry logic, multi-device…

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

Notification Services Explained Instances versus applications

Instances and applications Definition 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

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

Instances and applications Scalability 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

Instances and Applications Declaration & 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

The ICF and ADF

Notification Services Explained Development cycle

Development cycle XML Files Windows Service SQL Server SQL-NS Compiler Databases

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

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

Compilation and deployment

Notification Services Explained Architectural overview

The big picture Subscription Management Application SQL Server Data Change s External Delivery Event Provider Generator Distributor

Subscription Management Application Data Change s Event Provider Subscribers Subscriber Devices Subscriptions Generator Notifications Distributor External Delivery

Subscription Management App Allows subscribers to manage subscriptions API in SQL-NS Managed: ASP. NET, VB. NET, C#, etc Unmanaged: ASP, VB 6, C++, etc

Managing subscriptions

Event Providers Subscription Management Application Data Change s Event Provider File System Watcher SQL Server provider Custom EP Event Provider Host Subscribers Subscriber Devices Subscriptions Generator Notifications Distributor External Delivery

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

Built-in Event Providers XML event file Data Source XML File System Watcher Provider Event Provider Host Events Query Post. Query Schedule SQL Server provider Changes Event Provider Host Events

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

Adding event providers

Generator Subscription Management Application Data Change s Event Provider File System Watcher SQL Server provider Custom EP Event Provider Host Subscribers Subscriber Devices Subscriptions Generator SQL Match Rule Executes Stored Procs Notifications Distributor External Delivery

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

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

Writing the rules

Distributor Subscription Management Application Data Change s Event Provider File System Watcher SQL Server provider Custom EP Event Provider Host Subscribers Subscriber Devices Subscriptions Notifications Generator SQL Match Rule Executes Stored Procs External Delivery Distributor XSLT CF XSLT Email File Http. Ext. NET Alerts * Custom CF Custom DP

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

Content Formatting Format raw SQL Notification data Language-Country. Region and Device. Type Specified per Notification. Class 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

Delivery Richness v Rich Formatting Choice of content formatting v Device specific (mail, SMS, IM, etc) Language specific (localized notifications) v (XSLT or custom content formatter) v Combine Digest notifications sent to the same user v v 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 v Same ‘Multicast’ notifications message to many people v Format once, deliver to many v v Weather report for the Brussels area Traffic on E 40

Get it delivered

The picture that tells it all. . . Subscription Management Application Data Change s Event Provider File System Watcher SQL Server provider Custom EP Event Provider Host Subscribers Subscriber Devices Subscriptions Notifications Generator SQL Match Rule Executes Stored Procs External Delivery Distributor XSLT CF XSLT Email File Http. Ext. NET Alerts * Custom CF Custom DP

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

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

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

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

Summary Useful resources, books, summary, Q&A

Resources Information and download www. microsoft. com/sql/ns Tech. Net http: //www. microsoft. com/technet/prodtech nol/sql/2000/evaluate/sqlnsto. mspx Books online http: //www. microsoft. com/sql/ns/techinfo/pr oductdoc/default. asp. NET Alerts (30 -days free trial) https: //www. netservicesmanager. com/wiza rd/default. aspx

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

Summary Notification Services is a platform Enables rapid development of robust scalable Notification Applications Easy to use Declarative Programming Model Free addition for SQL Server 2000 licensees Get prepared for next releases

Q&A

Developer & IT Pro Days February 1 and 2 at Heysel, Brussels Opening Keynote by Bill Gates +30 Pure Technical Sessions Separate Tracks For: Developers IT Professionals Special Community Happening Early Bird (till 26 December): EUR 249 Full Price: EUR 299 http: //www. dev-itprodays. be