|
|
You're in the
sql server notification services
group:Notification to WinForm programs in a LAN. Is there a Delivery Protocols for it?
sql server notification services:
I am monitoring one or more tables in a SQL DB via .Net WinForm programs developed in C# in the same LAN. As soon as the insert/update events happen, I need to get the notification from the SQL Server so that I can run a query to get the whatever new data inserted with in sub second. I may have up to 5 of the same monitoring programs in the LAN running at any given time. One option is to use Microsoft Message Queuing (MSMQ), which I do not how to do yet. Other option is to use SQL notification, but so far only available delievery protocols I found are SMTP and File delivery. That does not fit my application. Is there a Delivery Protocols by MS SQL Notification Service for trigging an eveny handler in a Window Form application? Your help would be greatly appreciated. --
Sorry I post the above message first then I found a thread was talking the
almost the same thing here http://www.sqlmonster.com/Uwe/Forum.aspx/sql- server-notification/178/How-do-I-create-subscriptions-from-a-machine-other Mr. Webb, I went to your website and download your SqlNSTestApp. Thank you very much for offering it to people. I will study it today. If I have questions, do you mind I ask you here? To the other people, I appologize to post this repeat topic here. --
Hi Y Zhang -
First of all, please feel free to call me Joe; we're all friends here. :) If you have any questions, suggestions, comments, or critiques about the SqlNSTestApp on my site, feel free to post them here. While I certainly wouldn't consider SqlNSTestApp to be "production ready", I think it does demonstrate how to use some of the SQLNS objects. It focuses on the subscription management application side of things (creating subscribers, subscriptions, etc, using the enumerations, etc) rather than creating custom delivery channels. So, I don't know that the thread you referenced actually addresses your original posting. The only 2 "out of the box" delivery protocols are the SMTP and File. You can certainly create your own using the SQLNS API. And finally, what do you mean by sub-second? Perhaps this thread will help "SQL NS to be or not to be" HTH... 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] Y Zhang via SQLMonster.com wrote:
> Sorry I post the above message first then I found a thread was talking the > almost the same thing here http://www.sqlmonster.com/Uwe/Forum.aspx/sql- > server-notification/178/How-do-I-create-subscriptions-from-a-machine-other > > Mr. Webb, I went to your website and download your SqlNSTestApp. Thank you > very much for offering it to people. I will study it today. If I have > questions, do you mind I ask you here? > > To the other people, I appologize to post this repeat topic here.
Hi Joe,
Thank you very much for your response. I studied your code. As you pointed, it is an example about how a subscriber and his subscription are set. It shows a very good way to handle this task. However, it does not have what I need, i.e. how to create an event-driven subscription for a Window Form application. Do you have or happen to know such an example available? If so, I would great appreciate the info. Let me explain my application more here so we can discuss ?To be or not to be? for each options. My DB in a SQL Server has about 20 tables. Some of them are updated every half second; some of them are once every hour or every day. The updating frequency is about not fixed and can be changed on the fly. Ideally, any of my Window Form C# data monitoring client programs in the same LAN should get a notice from the SQL server when insert/update events happen. Then it can do whatever query accordingly. However, since I have not found a way to get such a notice from the SQL server, right now each of my C# programs is polling from the server to get the data from every table every second. The polling is not a good solution. Why do I have poll every second? Each of the C# client programs is monitoring the server on the real time bases 24/7. If data in some records meet some limits, an operator at the C# client side will make a decision and send a command to update a command table in the server. A VB program in the server side will monitor this command table in real time and take some actions to control a group of machines (power generators) accordingly. If we had 2 seconds delay in this communication chain anywhere, we would have high chance to damage the $100 K power generators. Timing is very critical here. Now question is how we get a notification from the insert/update events in a timely manner assuming the LAN has enough bandwidth to transfer a tiny package in one ms or less whenever server or clients send such a package? Using SQLNS, on the SQL server side, we can use trigger to get these events and add code to execute some stored procedures. However, I need to learn how it works with the customized delivery protocol so that my Window Form C# program can receive the event. Another option is bypassing SQL server all together and establish a TCP/IP client/server communication between a VB program that is responsible to insert all original data into DB tables and all of the C# client programs. The VB program as a TCP/IP server can easily broadcast the insert/update/delete record events to all C# clients in the same LAN. The message itself can be very concise. Upon receiving the message, the C# client programs can take action accordingly. I have existing working code for TCP/IP client/server communication so implementing it will be easy. My application is a simple one and I hope to get a simple solution. I am eager to learn SQLNS too if it can be fast enough to send a very short notification to my application. Thank you. have a good weekend. YZ --
Sub-second processing it not available in NS. Events can come into the
system at any time but are batched for efficiency. When the Generator fires, it'll match the events and subscriptions and then batch the results for distribution. The shortest time period for each of those batches is 1 second so it could be up to 2 seconds before a notification reaches your desktop application. If a couple of seconds is sufficient for your needs, then maybe NS will work. If your application is that dependent on near-instantaneous notifications, I'm not sure that NS is the best solution. Maybe MSMQ or a custom app using the FileSystemWatcher for XML documents created by SQL Server would work? HTH... 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] Y Zhang via SQLMonster.com wrote:
> Hi Joe, > > Thank you very much for your response. I studied your code. As you pointed, > it is an example about how a subscriber and his subscription are set. It > shows a very good way to handle this task. However, it does not have what I > need, i.e. how to create an event-driven subscription for a Window Form > application. Do you have or happen to know such an example available? If > so, I would great appreciate the info. > > Let me explain my application more here so we can discuss ?To be or not to > be? for each options. > > My DB in a SQL Server has about 20 tables. Some of them are updated every > half second; some of them are once every hour or every day. The updating > frequency is about not fixed and can be changed on the fly. > > Ideally, any of my Window Form C# data monitoring client programs in the > same LAN should get a notice from the SQL server when insert/update events > happen. Then it can do whatever query accordingly. However, since I have > not found a way to get such a notice from the SQL server, right now each of > my C# programs is polling from the server to get the data from every table > every second. The polling is not a good solution. > > Why do I have poll every second? Each of the C# client programs is > monitoring the server on the real time bases 24/7. If data in some records > meet some limits, an operator at the C# client side will make a decision > and send a command to update a command table in the server. A VB program in > the server side will monitor this command table in real time and take some > actions to control a group of machines (power generators) accordingly. If > we had 2 seconds delay in this communication chain anywhere, we would have > high chance to damage the $100 K power generators. Timing is very critical > here. > > Now question is how we get a notification from the insert/update events in > a timely manner assuming the LAN has enough bandwidth to transfer a tiny > package in one ms or less whenever server or clients send such a package? > > Using SQLNS, on the SQL server side, we can use trigger to get these events > and add code to execute some stored procedures. However, I need to learn > how it works with the customized delivery protocol so that my Window Form > C# program can receive the event. > > Another option is bypassing SQL server all together and establish a TCP/IP > client/server communication between a VB program that is responsible to > insert all original data into DB tables and all of the C# client programs. > The VB program as a TCP/IP server can easily broadcast the > insert/update/delete record events to all C# clients in the same LAN. The > message itself can be very concise. Upon receiving the message, the C# > client programs can take action accordingly. I have existing working code > for TCP/IP client/server communication so implementing it will be easy. > > My application is a simple one and I hope to get a simple solution. I am > eager to learn SQLNS too if it can be fast enough to send a very short > notification to my application. > > Thank you. have a good weekend. > > YZ
Thank you very much for your advice. One second or more delivery time (from
the time record inserted/updated in a table to the time my C# application get notified for the event) is certainly not acceptable for my application. I will look into MSMQ right away. Again with my sincere appreciation for your help. --
Hi
I followed Y Zhang's case over several forumors, but I never found a conclusion. Would it not be a solution to create triggers that execute stored procedures - as sprocs can be created i nC# in SQL Server 2005 as of - and use the .NET framework combined with remoting to notify the WinClients? Am I totally of the wrong field or would this give Y Zhang ...and myself... the close to real-time updates on the clients? If you did come to a more direct solution using MSMQ I would appreciate a hint on that as well. ********* Torbjørn --
Thank you, I can see your point.
Then the next obvious alteernative in SQL Server 2005 would be Service Broker. May I ask if you know the complexity of creating a listener on the client-side, and wether that would improve the solution compared to the initial polling of the database from the client. *** TB [quoted text, click to view] "Joe Webb" <joew@webbtechsolutions.com> wrote in message > Sure, you can probably do that in SQL Server 2005. But, the risk there is > that you're putting code in a trigger that could take quite a bit of time > to execute. This will, of course, have adverse affects on performance. > > Triggers should be used judiciously to rollback, ensure referential > integrity, etc. I'd shy away from making .net remoting calls in them. > > Torbjørn Sigurdsen via SQLMonster.com wrote: >> Hi I followed Y Zhang's case over several forumors, but I never found a >> conclusion. Would it not be a solution to create triggers that execute >> stored procedures - as sprocs can be created i nC# in SQL Server 2005 as >> of >> - and use the .NET framework combined with remoting to notify the >> WinClients? >> >> Am I totally of the wrong field or would this give Y Zhang ...and >> myself... >> the close to real-time updates on the clients? >> >> If you did come to a more direct solution using MSMQ I would appreciate a >> hint on that as well. >> >> ********* >> Torbjørn >>
Sure, you can probably do that in SQL Server 2005. But, the risk there
is that you're putting code in a trigger that could take quite a bit of time to execute. This will, of course, have adverse affects on performance. Triggers should be used judiciously to rollback, ensure referential integrity, etc. I'd shy away from making .net remoting calls in them. HTH... 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] Torbjørn Sigurdsen via SQLMonster.com wrote:
> Hi > I followed Y Zhang's case over several forumors, but I never found a > conclusion. Would it not be a solution to create triggers that execute > stored procedures - as sprocs can be created i nC# in SQL Server 2005 as of > - and use the .NET framework combined with remoting to notify the > WinClients? > > Am I totally of the wrong field or would this give Y Zhang ...and myself... > the close to real-time updates on the clients? > > If you did come to a more direct solution using MSMQ I would appreciate a > hint on that as well. > > ********* > Torbjørn
Hi TS -
Here's an article that provides a good overview of Service Broker. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sqlsvcbroker.asp I suspect that'd be a good option to investigate. If you can, download the April CTP and try it out. HTH... 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] ts wrote:
> Thank you, I can see your point. > > Then the next obvious alteernative in SQL Server 2005 would be Service > Broker. May I ask if you know the complexity of creating a listener on the > client-side, and wether that would improve the solution compared to the > initial polling of the database from the client. > > *** > TB > > > "Joe Webb" <joew@webbtechsolutions.com> wrote in message > >>Sure, you can probably do that in SQL Server 2005. But, the risk there is >>that you're putting code in a trigger that could take quite a bit of time >>to execute. This will, of course, have adverse affects on performance. >> >>Triggers should be used judiciously to rollback, ensure referential >>integrity, etc. I'd shy away from making .net remoting calls in them. >> >>Torbjørn Sigurdsen via SQLMonster.com wrote: >> >>>Hi I followed Y Zhang's case over several forumors, but I never found a >>>conclusion. Would it not be a solution to create triggers that execute >>>stored procedures - as sprocs can be created i nC# in SQL Server 2005 as >>>of >>>- and use the .NET framework combined with remoting to notify the >>>WinClients? >>> >>>Am I totally of the wrong field or would this give Y Zhang ...and >>>myself... >>>the close to real-time updates on the clients? >>> >>>If you did come to a more direct solution using MSMQ I would appreciate a >>>hint on that as well. >>> >>>********* >>>Torbjørn >>> > >
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 |