INT324 SQL Server Notification Services: Design Patterns

1
INT324SQL Server Notification ServicesDesign
Patterns
Patrice Truong Program Manager, SQL
Server Microsoft Corporation
2
About This Talk

• This talk IS about
• Design Patterns for SQLNS applications
• Best Practices in building notification
  applications
• This talk IS NOT
• An introduction to SQL Server Notification
  Services (see Architecture Overview talk by Tete
  Mensa-Annan)
• A talk to learn how to develop notification
  applications (see my other talk on Application
  Development)
• A talk to learn how to optimize a sql match rule

3
Solving the Matching Problem

• Use SQL-like language to model queries
• Evaluate subscriptions for each incoming event
• Alert me when MSFT gt 50
• Scalability issues

Subscriptionsas queries
Subscriptionsas data

• Subscriptions stored as rows in a table
• Events stored as rows in a table
• Set-oriented operations with SQL JOINs

4
Agenda
FilteringDuplicateEvents
Minimizingnumber ofnotifications
Maintaininghistory
Expiringsubscriptions
Optimizingeventsprocessing
Dealing with ANDs ORs
5
Without Filtering Events

• Example news articles pushed as events every 5
  minutes

• SQLNS features
• Chronicle tables
• Events chronicle rules

New news batch Duplicate news article!
6
EC E-EH

• Events table
• All news articles
• Events Chronicle table
• New articles (dups filtered)
• Events History
• All news articles already seen in the system

• Event Chronicle Rule
• Adds new articles to EC (ECE-EH)
• Generator Rule
• Generates notifications out of the EC
• Updates EH with new articles

7
EC E-EH
First pass events arrive in the system
ECR adds new articles to EC
GR updates the EH with new events
8
EC E-EH
Second pass new batch with duplicate article
ECR adds new articles to EC
GR updates the EH with new events
9
EEC-EH
10
Minimizing the number of notifications

• Example Stock Notifications. Stock values change
  every few seconds. How do you minimize the number
  of notifications?

11
Without Application State
New notification!
New stock prices
12
High Watermark ConceptE gt S and E C
EventsChronicleRule

• Establishes events state
• Keep highest value for a stock
• Fires for every incoming event
• Always fires before other rules

• Matches incoming events with existing user
  subscriptions
• Generates a notification if
• StockValue(Event) gt Subscription
• StockValue(Event) Chronicle

SubscriptionRule
13
High Watermark In Action
Events Chronicle Rule Is it the highest value
for the day?
Match rule Generate a notification if EgtS and E
C
14
High Watermark In Action
Events Chronicle Rule Is it the highest value
for the day?
Match rule Generate a notification if EgtS and E
C
New stock prices
15
High Watermark
Highest value for the day
16
High Watermark
17
Reducing Volume Of Events

• Problem
• Volume of events is too important (eg. Bank
  account information, stock exchange)
• Restricted access to the source database
• How?
• Query the susbcriptions table to only retrieve
  events for which there are subscriptions
• Subscribed bank accounts
• Subscribed stock symbols
• SQLNS features
• SQL Server event provider
• Filter out new events (if needed)

18
Reducing Volume of EventsWith Query Access To
The Source
19
SQL Server Event ProviderNo Query Access To The
Source
Custom Event Provider Retrieves subscribed
accounts
SQL Provider PostQuery erases temp table
SQL Provider Query Query retrieves account
information from temp table, populates events
table
Night job creates snapshot of the database
NightJob
Custom Provider retrieves account information,
populates events table using SQLNS API
20
Optimizing Events Processing
21
Implementing History

• Per subscriber
• Across all subscription classes
• Example Send an SMS if number of allowed SMS
  has not been exceeded

SubscriberHistory

• Per notification class
• Example recent alerts

NotificationHistory

• Per subscription class
• Keep subscription-specific information
• Example Notify me only if the stock value is
  higher than the last time I was notified (for
  that stock)
• Example ExpirationDate per subscription

SubscriptionHistory
22
Subscriber History

• How?
• Create a subscriber chronicle table
• Contains per-subscriber information
• e.g. Last time the user was notified
• Number of notifications already sent
• Match rule
• generates notifications if a document was
  modified AFTER a user was notified
  (EC.LastModification gt SH.LastNotified)
• updates SH after it has fired with date of last
  notification
• Note
• It makes sense for all subscription classes to
  update the same shared subscriber history (number
  of notifications sent..)

23
Subscriber History
ECR Keeps date of last modification
1. Generates notifications
2. Updates the subscriber history
24
Subscription History Subscriptions w/ expiration
time

• At subscription time
• Delta ExpirationTime StartTime (in local
  time)
• Store Delta in subscription
• Build a subscriptions chronicle (exclusion list)
• With the expiration time (in UTC)
• When the scheduled rule fires
• On the first rule firing
• Set ExpirationTime in the subscription chronicle
• ExpirationTime UTCNow s.Delta
• Generate a notification when
• server time lt sh.expirationTime
• Subscriptions are automatically expired
  (excluded) by the match rule
• Physically delete expired subscriptions using
  SQLNS API

One time subscriptions with expirationtime
Recurringsubscriptionswithexpirationtime
25
Recurring subscriptionswith ExpirationDate
Subscription Compute Delta Expiration - Start
Match Rule Generate notification (UTCNow lt
sh.expirationDate)
Match Rule On First rule firing, create entry
in subscription chronicle with expiration date
26
Notifications History

• Scenario
• View recent alerts
• How ?
• Use the NSNotificationClassDistribution view!
• SubscriberId, DeviceName, DeliveryChannelName,
  SentTime, DeliveryStatusDescription,
  NotificationText, LinkNotificationId
• Watch out for aggressive vacuum settings!
• RetentionAge

27
Dealing with ANDs and ORsANDs

• Problem
• Arbitrary number of predicates linked with
  ANDs
• What you dont want
• To hard code the maximum number of predicates
  allowed
• Performance issues
• Impossible to pre-determine the number of
  conditions
• What you want
• A single rule that handles an arbitrary number of
  predicates
• A model that uses set-oriented operations

28
ANDsSubscriptions

• Break down user subscription into predicates
• 1 predicate 1 subscription row

• Generate a notification if all predicates are
  true (PCMPC)

29
ORsSubscriptions

• Break down user subscription into predicates
• 1 predicate 1 subscription row

• Generate a notification if at least one predicate
  is true (PCM 1)

30
ANDs ORsSubscriptions

• Break down user subscriptions into predicate
  groups
• Each predicate maps to 1 subscription row
• AND same predicate group
• OR new predicate group

31
ANDs and ORsEvents and Notifications

• Count predicates that are out of range (PCM)
• PGM true if PCM PC

• Generate a notification if, within one predicate
  group, all predicates are true (PCMPC)

32
(No Transcript)
33
(No Transcript)
34
ANDs and ORsSummary

• Variable number of arguments in the subscription
• Use SQLNS set-oriented features
• The SMA has to reduce a complex expression to its
  canonical form
• N number of ANDs
• Separated with ORs
• (A B) (C D)
• ? ( A C D) (B C D)

35
SQL Notification ServicesSummary

• Think ROWS!
• Learn how / when to use chronicles
• Filter out unnecessary data
• Talk to us about your application!

36
INT324Fill out the session evaluations!