May 31, 2006 at 10:52 am
Well all you smart SQL 2005 Gurus (hehehe is that enough sucking up for now?)
I have a situation where I "THINK" I'm going to try and use Service Broker and I'd like to bounce the idea off some people and see if it sticks.
Here's the situation:
1 Customer Master table, shared between MANY servers. All the servers need the abilty to add/update/delete Customers and have those changes made on any of the servers propagated.
I thought about doing a merge replication scenario but I'm not a big fan of the triggers and having not used Merge replication before and with all the negative things I've read about it I don't really want to go down that road
I thought about creating my own version of replication where I would basically add/update/delete on the server the user is currently on and then when it commits the transaction that I would log in a table the servers the change need to applied to and have an external webservice pull from that table and do the changes... blah blah blah
and then I started investigating Service Broker as a solution. I'm interested in the whole queue idea and like that fact it does not force me to use triggers or create my own replication solution.
Does anyone have any experience/feedback/suggestions on whether or not Service Broker should work for what I'm trying to do? Any other methods I should be investigating? Keep in mind all sites need to be active and have the ability to add/change/delete customers whether the other sites are alive or not.
Thanks
June 6, 2006 at 6:29 am
The problem you might run into is writing to the queue from multiple servers. Each would have to have the service running.
Then once a message is posted to the queue you would have to have a reference somewhere that told you all servers that need updating.
If I was going to use Service Broker for this one thing I would consider is making one server/database the master. All the other servers would use service broker to update the master. The service broker on the master would then propogate all the changes out to the "registered" child servers. This is a hard implementation of service broker (however doable) and I would consider going with merge replication.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply