|
|
You're in the
sql server notification services
group:SQL NS to be or not to be
sql server notification services:
I am monitoring a table in a DB, as soon as the data changes, I need to get the notification for the new data inserted, (with in sub second) and then my monitor object calls a web service to deliver the data for their final destination. I may have 2-3 monitors. Once monitor deliver successfully he updates the last delivery table with the time stamp. One solution can be to have a NT service with a file watcher. DB trigger drops a file which in turn activate the file watcher which wakes up monitor to go get the newest data and do the rest etc. Other option I was studying was to use SQL notification, but so far I seen for my scenario it does not add any value, and found it to be configuration heavy, it is not simplifying the design in any way. It is adding more moving parts and surely will affect the performance in negative way. It is doing exactly the same thing with more configurations and less flexibility. Using NS does not save us from developing much of the code, yes, it does make a NT service for me but it is trivial any way... All the above comment for NS is specifically for my scenario, I know it does a lot, and good for many, it just seem too much for MY SCENARIO .... I may be off base as I am just started reading about NS, like to know your thoughts on it ... Thanks for your time
As you mentioned, SQLNS is not a tool for every situation. There are
some scanarios where a custom developed solution may be more advantageous. Your's may very well be one of those scenarios. However, many developers find that SQLNS gives offers them a huge advantage or jumpstart on the development process. SQLNS is designed to provide some of the plumbing that they have either not already thought about or would have to code by hand. Things such as scheduled and event-based subscriptions, handling multiple time zones, retries on failed deliveries, multiple delivery protocols, scale-out deployments, combining multiple notifications destine for a single recipient into one notification, etc. SQLNS is very flexible, but that comes with a price; it must be explicitly configured. But once you've done that a couple of times, it's really straight forward and the samples provided with installation provide a good launch point. Should you use SQLNS or roll your own solution?? I don't know. But some questions to consider: 1) How many subscribers will you have? 2) How many notifications can you expect per day/hour/minute? 3) How many events are expected to enter the system? 4) Who are the subscribers (employees, customers, anyone in the world)? 5) Are the subscribers in different time zones? 6) What flexibility would you like to offer subscribers (scheduled delivery, digest delivery)? 7) What delivery mechanisms are important to you? 8) Will you need to be able to spread the load among multiple servers? 9) Are the other similar notification applications that could be created for the same set of users? If you want to notify a loan officer when the loan amount exceeds a predefined amount, use alerts and sql mail. If you want to notify thousands of customers when a hotfix to your software has been released, consider SQLNS. HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] sleepyjoe wrote:
> Would appreciate your comments. Here is the scenario: > > I am monitoring a table in a DB, as soon as the data changes, I need to > get the notification for the new data inserted, (with in sub second) > and then my monitor object calls a web service to deliver > the data for their final destination. I may have 2-3 monitors. Once > monitor deliver successfully he updates the last delivery table with > the time stamp. > > One solution can be to have a NT service with a file watcher. DB > trigger drops a file which in turn activate the file watcher which > wakes up monitor to go get the newest data and do the rest etc. > > Other option I was studying was to use SQL notification, but so far I > seen for my scenario it does not add any value, and found it to be > configuration heavy, it is not simplifying the design in any way. It is > adding more moving parts and surely will affect the performance in > negative way. It is doing exactly the same thing with more > configurations and less flexibility. Using NS does not save us from > developing much of the code, yes, it does make a NT service for me but > it is trivial any way... > > All the above comment for NS is specifically for my scenario, I know it > does a lot, and good for many, it just seem too much for MY SCENARIO > ... > > I may be off base as I am just started reading about NS, like to know > your thoughts on it ... > > Thanks for your time
Thanks Joe for your quick reply, actually I already ordered your book
and waiting to receive it, meanwhile if you can answer some more questions. 1) How many subscribers will you have? A:I have two subscribers, and they are web services sitting on the same network, in same time zone. 2) How many notifications can you expect per day/hour/minute? A:Range may very ... I am expecting 100's per min to 50 a day. 3) How many events are expected to enter the system? A:normally 50 - 60 a day but at the system start it may be 100's a minute. 4) Who are the subscribers (employees, customers, anyone in the world)? A:Two web services. 5) Are the subscribers in different time zones? A:They are in same time zone. 6) What flexibility would you like to offer subscribers (scheduled delivery, digest delivery)? A: delivery should be with in seconds. 7) What delivery mechanisms are important to you? A:I guess it will be custom delivery as these web services will be called and data is delivered in a XML format. XML may be created using SQL for XML AUTO syntax. 8) Will you need to be able to spread the load among multiple servers? A:not necessary Other questions are Can i call a web service directly? web service returns me true or false as a flag to indicate that they received the data, can this return data be put on DB by NS or it is redundent with NS as last delivery etc will be taken care of NS. Or Should i have a custom application which is called by the NS and then that application will call the WS and put back the status back into DB. My two different subscribers want to monitor different status in DB though, One NS Application can satisfy two different subscriber or do i have to have two different instances etc. Joe, thanks again for your time, i really appriciate your help.
Thanks for ordering the book!! :) I hope you find it worthwhile! Please
feel free to email me with comments/suggestions/critisms/etc. Your answers and additional questions are very interesting. Can you share a bit more about what the 2 web services are doing for you? Generally speaking, 100's of events and notifications per minute is no problem for SQLNS; it's very scalable. However, if you only have 2 subscribers, SQLNS may be a bit of an overkill. To send notifications to web services would require that you create your own custom delivery protocol. That shouldn't be too much of a problem. Something else to consider is that to get a delivery within seconds, you'd have to decrease the quantum and that will of course has some impact on performance. HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] sleepyjoe wrote:
> Thanks Joe for your quick reply, actually I already ordered your book > and waiting to receive it, meanwhile if you can answer some more > questions. > > 1) How many subscribers will you have? > > A:I have two subscribers, and they are web services sitting on the same > network, in same time zone. > > 2) How many notifications can you expect per day/hour/minute? > > A:Range may very ... I am expecting 100's per min to 50 a day. > > 3) How many events are expected to enter the system? > > A:normally 50 - 60 a day but at the system start it may be 100's a > minute. > > 4) Who are the subscribers (employees, customers, anyone in the world)? > > A:Two web services. > > 5) Are the subscribers in different time zones? > > A:They are in same time zone. > > 6) What flexibility would you like to offer subscribers (scheduled > delivery, digest delivery)? > > A: delivery should be with in seconds. > > 7) What delivery mechanisms are important to you? > > > A:I guess it will be custom delivery as these web services will be > called and data is delivered in a XML format. XML may be created using > SQL for XML AUTO syntax. > > 8) Will you need to be able to spread the load among multiple servers? > > > A:not necessary > > Other questions are Can i call a web service directly? web service > returns me true or false as a flag to indicate that they received the > data, can this return data be put on DB by NS or it is redundent with > NS as last delivery etc will be taken care of NS. > > Or Should i have a custom application which is called by the NS and > then that application will call the WS and put back the status back > into DB. > > My two different subscribers want to monitor different status in DB > though, One NS Application can satisfy two different subscriber or do i > have to have two different instances etc. > Joe, thanks again for your time, i really appriciate your help.
These web services are part of larger document management system, these
web services will in fact start down loading some documents, images after the notification. The requirement is to have notification running with in sub seconds. The quantum number you mentioned make me believe that SQL NS is a polling system it does not actually a event driven system as such. Sure, events are generated but they are delivered based on Quantum number. Is this assumption is right?
Well, I would not go so far as to say that SQLNS is polling based.
Here's what happens...Events are entered into the system in real-time (or polled into the system depending on your event provider) and are batched for efficiency. Consider the stock sample app as an example. You could have several 1000's of events entering the system almost instantly - say through a single XML document that contains every stock on the NYSE. The file system watcher event provider will pick up the document immediately. Rather than firing a notification immediately for every stock, SQLNS batches these events and processes them as a single unit. The generator component is fired periodically (at its quantum) and matches the events to the subscriptions and produces notifications. The notifications are batched in a similar manner and are picked up by the distributor for formatting and passing on to the delivery channel. You can adjust the frequency at which the generator and distributor check for work, but you cannot get it down to the sub-second level. Even checking once per second would cause the matching query to be execute that often and thus adding somewhat of a load on the system. So if sub-second processing is that important for you, SQLNS is probably not the best solution. Additionally you have 2 very well defined subscribers rather than a multitude of different subscriptions that you'd need to deal with so again, SQLNS may be a bit overqualified. Just my 2 cents here....consider your alternatives and see which one is the best. You may find that even though it's not quite an ideal fit for SQLNS, it may turn out to be the best alternative. Maybe not though... Whatever you decide, would you share your decision with the group so we can learn? HTH... Joe Webb ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] sleepyjoe wrote:
> These web services are part of larger document management system, these > web services will in fact start down loading some documents, images > after the notification. > > The requirement is to have notification running with in sub seconds. > The quantum number you mentioned make me believe that SQL NS is a > polling system it does not actually a event driven system as such. > Sure, events are generated but they are delivered based on Quantum > number. Is this assumption is right?
sure, i have not given up on NS. Santa will be bringing your book, and
i will be educating my self on NS. Other thing worries me for my simple scenario is those extra database, so many extra tables and stored procedures of NS. Seems like debugging and maintainence will not be very easy. Thanks again for your help.
Joe, I just wanted to mention a design pattern that essentially works around
the limitations you describe below, although at the cost of some complexity. We have the need of an NS application that will run "continuously" as long as there are rows in a queue table that need to be processed. We implmented this first with a Scheduled Event Provider. But after a while (in production, of course) the queue grew and the call to Run() exceeded the five-minute limit imposed by NS as part of using IScheuledEventProvider. So we hit upon this scheme, with assistance from MS posters on this board: Have Run() spawn a thread bound to a worker method that processes the queue, then have Run() return immediately. So, you never hit the five-minute limit, and you worker method just loops on the queue and returns (and dies) when it is drained. This is great, but not enough (at least this showed up in QA), because NS continues to call run on the Schedule period set in the ADF, which means concurrency problems if the last call in RunWorker() hasn't returned before the next call to ran spawns a new thread and new call to RunWorker(). So, the second essential part of this pattern is a semaphore that guards access to the call to spawn the RunWorker() thread, which is only released by RunWorker() when it's done and about to return (as the very last thing it does before returning). This we implemented just with Interlocked. So, not unsophisticated to understand, but actually all this is not a lot of code in ..NET. This has been working great in production for about 6 months now. And it has the single great advantage of turning NS into a real-time "run until done" system if you have some outside queue your EventProvider must consume as the definition of your event. [quoted text, click to view] "Joe Webb" wrote:
> Well, I would not go so far as to say that SQLNS is polling based. > > Here's what happens...Events are entered into the system in real-time > (or polled into the system depending on your event provider) and are > batched for efficiency. Consider the stock sample app as an example. You > could have several 1000's of events entering the system almost instantly > - say through a single XML document that contains every stock on the > NYSE. The file system watcher event provider will pick up the document > immediately. Rather than firing a notification immediately for every > stock, SQLNS batches these events and processes them as a single unit. > The generator component is fired periodically (at its quantum) and > matches the events to the subscriptions and produces notifications. The > notifications are batched in a similar manner and are picked up by the > distributor for formatting and passing on to the delivery channel. > > You can adjust the frequency at which the generator and distributor > check for work, but you cannot get it down to the sub-second level. Even > checking once per second would cause the matching query to be execute > that often and thus adding somewhat of a load on the system. So if > sub-second processing is that important for you, SQLNS is probably not > the best solution. Additionally you have 2 very well defined > subscribers rather than a multitude of different subscriptions that > you'd need to deal with so again, SQLNS may be a bit overqualified. > > Just my 2 cents here....consider your alternatives and see which one is > the best. You may find that even though it's not quite an ideal fit for > SQLNS, it may turn out to be the best alternative. Maybe not though... > Whatever you decide, would you share your decision with the group so we > can learn? > > > HTH... > Joe Webb > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > sleepyjoe wrote: > > These web services are part of larger document management system, these > > web services will in fact start down loading some documents, images > > after the notification. > > > > The requirement is to have notification running with in sub seconds. > > The quantum number you mentioned make me believe that SQL NS is a > > polling system it does not actually a event driven system as such. > > Sure, events are generated but they are delivered based on Quantum > > number. Is this assumption is right? > >
Ah very nice! And you described it well, makes perfect sense.
Thanks for sharing!! :) Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] MarkSW wrote:
> Joe, I just wanted to mention a design pattern that essentially works around > the limitations you describe below, although at the cost of some complexity. > > We have the need of an NS application that will run "continuously" as long > as there are rows in a queue table that need to be processed. We implmented > this first with a Scheduled Event Provider. But after a while (in > production, of course) the queue grew and the call to Run() exceeded the > five-minute limit imposed by NS as part of using IScheuledEventProvider. So > we hit upon this scheme, with assistance from MS posters on this board: > > Have Run() spawn a thread bound to a worker method that processes the queue, > then have Run() return immediately. So, you never hit the five-minute limit, > and you worker method just loops on the queue and returns (and dies) when it > is drained. > > This is great, but not enough (at least this showed up in QA), because NS > continues to call run on the Schedule period set in the ADF, which means > concurrency problems if the last call in RunWorker() hasn't returned before > the next call to ran spawns a new thread and new call to RunWorker(). So, > the second essential part of this pattern is a semaphore that guards access > to the call to spawn the RunWorker() thread, which is only released by > RunWorker() when it's done and about to return (as the very last thing it > does before returning). This we implemented just with Interlocked. So, not > unsophisticated to understand, but actually all this is not a lot of code in > .NET. > > This has been working great in production for about 6 months now. And it > has the single great advantage of turning NS into a real-time "run until > done" system if you have some outside queue your EventProvider must consume > as the definition of your event. > > > "Joe Webb" wrote: > > >>Well, I would not go so far as to say that SQLNS is polling based. >> >>Here's what happens...Events are entered into the system in real-time >>(or polled into the system depending on your event provider) and are >>batched for efficiency. Consider the stock sample app as an example. You >>could have several 1000's of events entering the system almost instantly >>- say through a single XML document that contains every stock on the >>NYSE. The file system watcher event provider will pick up the document >>immediately. Rather than firing a notification immediately for every >>stock, SQLNS batches these events and processes them as a single unit. >>The generator component is fired periodically (at its quantum) and >>matches the events to the subscriptions and produces notifications. The >>notifications are batched in a similar manner and are picked up by the >>distributor for formatting and passing on to the delivery channel. >> >>You can adjust the frequency at which the generator and distributor >>check for work, but you cannot get it down to the sub-second level. Even >>checking once per second would cause the matching query to be execute >>that often and thus adding somewhat of a load on the system. So if >>sub-second processing is that important for you, SQLNS is probably not >>the best solution. Additionally you have 2 very well defined >>subscribers rather than a multitude of different subscriptions that >>you'd need to deal with so again, SQLNS may be a bit overqualified. >> >>Just my 2 cents here....consider your alternatives and see which one is >>the best. You may find that even though it's not quite an ideal fit for >>SQLNS, it may turn out to be the best alternative. Maybe not though... >>Whatever you decide, would you share your decision with the group so we >>can learn? >> >> >>HTH... >>Joe Webb >> >>~~~ >>Get up to speed quickly with SQLNS >>http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 >> >> >> >>sleepyjoe wrote: >> >>>These web services are part of larger document management system, these >>>web services will in fact start down loading some documents, images >>>after the notification. >>> >>>The requirement is to have notification running with in sub seconds. >>>The quantum number you mentioned make me believe that SQL NS is a >>>polling system it does not actually a event driven system as such. >>>Sure, events are generated but they are delivered based on Quantum >>>number. Is this assumption is right? >>>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |