tables data changes question

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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?

  • 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.

  • 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?

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

  • 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.

  • 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

  • foxjazz (1/13/2009)


    I have 6 tables tbl1,tbl2-6

    I 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.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 33 total)

You must be logged in to reply to this topic. Login to reply