January 14, 2009 at 9:42 am
Write your own CLR procedure to send the UDP message and then use service broker to implement it. That way you can use a trigger to send a message to service broker to send the udp message to the clients.
January 14, 2009 at 9:43 am
foxjazz (1/14/2009)
I have triggers that call a stored procedure that sends a message using service broker.
Ok, I'm confused. You didn't mention anything about service broker before.
Wouldn't it be possible to have an afterinsert on this table to call a stored procedure that calls a clr function that messages the changed data to all the clients.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 9:43 am
I have looked into querynotification before and have done it this way as a test.
However there is a lot more overhead than just sending a udp message which is minimal effort, the overhead is in that sql server is "watching" for these notifications.
Although it is a great idea, but not as efficient as sending soemthing like:
udp.send(tablename, ipaddress, valuelistthatchanged)
Again, we aren't talking about frequent data changes, these are infrequent changes (upper level structural changes) dealing with less than 1000 records and are perfect for caching.
So if it's not so hard to send a udp message from sql server, how would you do it without creating a service to manage it?
January 14, 2009 at 9:45 am
Last time I tried writing my own clr to send udp messages, it wouldn't compile because of security reasons sql server imposed on the code. (no udp was allowed) I couldn't access the dll's that did the work.
January 14, 2009 at 10:04 am
GilaMonster (1/14/2009)
foxjazz (1/14/2009)
I have triggers that call a stored procedure that sends a message using service broker.Ok, I'm confused. You didn't mention anything about service broker before.
Wouldn't it be possible to have an afterinsert on this table to call a stored procedure that calls a clr function that messages the changed data to all the clients.
foxjazz learns to use quotes:
servicebroker I think in this case wouldn't be necessary due to the overhead involved.
In this case I would be using sevice broker to send a message, in turn to send another message?
My direct question I think is why does the clr (sql ) prevent us from using the windows.net tools like sockets or udp sendudpmessage? And since it does, are there prefered ways to handle this without querynotification, or servicebroker?
Polling the damn thing is the only solution I have seen that would work, but it still requires a trigger to throw data in a different table so the polling would not load the server.
polling in itself is a load because we have to write a service either on an app server or run it on sql server to manage the polling changes.
So am I missing something here, or did MS just crap the shoot?
January 14, 2009 at 10:09 am
Instead of rolling your own cache management, you might consider one of the cache management softwares that's available. One of our systems under development has just started this process. Here's a snapshot from the KMS:
Local Caching Packages:
Caching Application Block (Enterprise Library 4.1)
• Free
• Simple to implement
• In or out of process
• Supports only a single machine/application
• Phone/email support is only available via MS Premier support
NCache Express (can be used as a data provider for CAB)
• Free
• Simple to implement
• In or out of process
• ASP.Net Session state caching
• Supports only two servers
• Phone support is only available with the Enterprise version
Distributed Caching Packages
• NCache Enterprise
• ScaleOut StateServer
Both of these packages offer pretty much the same functionality. The main difference is that StateServer licensing can be significantly cheaper (especially for developer licenses, StateServer is free compared to $500 per machine).
There are several other distributed caching servers out there, however they lacked either functionality or support.
• MS Velocity - This is MS's caching server product it is current on CTP 2, no release date has been announced for V1. Too bleeding edge, is trying to replicate functionality that NCache and StateServer have had for several years.
• Gigaspaces - Mainly a Java product recently updated to support .Net, no support for ASP.Net session caching.
• SharedCache - Open source with no commercial support available, also a very young product.
• MemCache - No native Win32 port, no support for ASP.Net sessions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 12:50 pm
foxjazz (1/14/2009)
My direct question I think is why does the clr (sql ) prevent us from using the windows.net tools like sockets or udp sendudpmessage? And since it does, are there prefered ways to handle this without querynotification, or servicebroker?
Does it?
http://www.windows-tech.info/15/add0d369bf0c052a.php
It requires the unsafe permission set, but it seems to do kinda what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 12:54 pm
GilaMonster (1/14/2009)
foxjazz (1/14/2009)
My direct question I think is why does the clr (sql ) prevent us from using the windows.net tools like sockets or udp sendudpmessage? And since it does, are there prefered ways to handle this without querynotification, or servicebroker?Does it?
http://www.windows-tech.info/15/add0d369bf0c052a.php
It requires the unsafe permission set, but it seems to do kinda what you want.
Yea cool I think this is going to be a wrap.
January 14, 2009 at 1:02 pm
Just don't call that directly from within a trigger. I detailed all the reasons earlier in this thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2009 at 1:54 pm
I agree with Gail. If you go this way, call it via an asyncronous call using Service Broker. This way you know that the users update (insert/update/delete) is properly captured.
January 14, 2009 at 2:14 pm
I think this is a FRONT-END task. The APP that successfully change a table should send a "message" to all clients that way you keep those CLR procedures to minimum!
As suggested above there are many "caching" mechanisms available to choose from you prefer them to roll your own.
* Noel
January 14, 2009 at 2:31 pm
Lynn Pettis (1/14/2009)
I agree with Gail. If you go this way, call it via an asyncronous call using Service Broker. This way you know that the users update (insert/update/delete) is properly captured.
Ok, I think I am getting a better idea of what the architecture should look like.
We don't want triggers (bad)
we don't necessarilly need service broker because the application server that handles the updates could also handle the messages being sent to the active clients.
I would never think that it is a good idea for the front-end to handle sending messages to peers for a number of reasons. Many of those being firewall uncertainties on the clients, routing etc...
I really want to thank you guys for your time in thinkng about this. If I were to use a storedprocedure messagequeueing would be the best way to handle this function.
Oh, on other thinking, what if one of the data managers decides to update this stuff by hand, wouldn't triggering a message be the most sure fire way to go because that guy has authority to bypass the "client/server" solution and just add a few records on the fly if need be.
Maybe if that happens we slap him on the wrist and send an artificial message to the clients to refresh their data.
There are a lot of different ways to approach this it seems.
January 14, 2009 at 3:10 pm
I would never think that it is a good idea for the front-end to handle sending messages to peers for a number of reasons. Many of those being firewall uncertainties on the clients, routing etc...
If firewall issues can be a factor sending messages from the server to the client may be an issue as well 😉
... And you are correct there are many ways to approach this issue IMHO You should just try to keep it as simple as possible.
* Noel
January 15, 2009 at 11:38 pm
foxjazz (1/13/2009)
I have 6 tables tbl1,tbl2-6I need to write a program to determine if any changes were made to these tables.
Option 1, I can write a trigger that sends an msmq message on any change.
Option 2, I can use some sort of hash calculation to determine if it has changed last time I looked.
The goal is to notify users running a program via udp messages if a table changes, which these tables wouldn't change frequently. However when they do the program they run will receive the message and rebuild the generic lists that their static class contain.
Any suggestions, ideas etc...
Create DDL trigger to change track and send mail notification.
January 16, 2009 at 12:31 am
Paresh Prajapati (1/15/2009)
Create DDL trigger to change track and send mail notification.
Um...
DDL triggers fire when there's a change to the table structure, not the table's data (which is what foxjazz is interested in)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply