September 25, 2012 at 9:24 am
Hi,
I have a customer with webservice that creates users on his database.
On the same database server we have another SW and want to synchronize the users' table.
As we can't change his webservice we added a trigger on the users' table to insert a row on a table (that works as a queue) so worker processes can read from that queue and send the users to the other database. Currently the database is on the same server but in the future it will be on another server on another network, so the sync is made via c# code.
The webservice writes directly on the Users' table and the trigger on the SyncEntities' table, with a FOR UPDATE, INSERT.
Is the FOR UPDATE, INSERT executed in the same transaction as is the insert in the Users' table?
Should we user AFTER UPDATE, INSERT in this case so the Users' table is released while the SyncEntities' table is being written?
We have a major problem with this since the worker process reads and updates the SyncEntities' table (makes a row lock) and then reads the Users' table to get the data to send to the other database (in this step there's no locks from the worker, the worker only locks when reading a row and updating it from the SyncEntities table).
Thanks,
Pedro
September 25, 2012 at 9:38 am
The trigger is part of the transaction. The AFTER won't release the locks for you.
You would be better served by using a real queue here with Service Broker. Insert the data from the trigger into the queue, which will be extremely quick. Then you can activate your C# code from the Service Broker Queue.
September 25, 2012 at 10:08 am
Steve Jones - SSC Editor (9/25/2012)
The trigger is part of the transaction. The AFTER won't release the locks for you.You would be better served by using a real queue here with Service Broker. Insert the data from the trigger into the queue, which will be extremely quick. Then you can activate your C# code from the Service Broker Queue.
I've never used Service Broker.. Is it available on Standard Edition?
Seems really simple to use (saw some examples of using queues)...
Thanks,
Pedro
September 25, 2012 at 2:48 pm
http://msdn.microsoft.com/en-us/library/cc645993.aspx
Should be in all editions.
You don't have to use Service Broker, but you want transactions as tight as possible. If you really want the queue on another machine, use a local table to catch the data in a trigger and another process to grab the data and move it to another instance.
September 25, 2012 at 3:29 pm
PiMané (9/25/2012)
The webservice writes directly on the Users' table and the trigger on the SyncEntities' table, with a FOR UPDATE, INSERT.Is the FOR UPDATE, INSERT executed in the same transaction as is the insert in the Users' table?
Should we user AFTER UPDATE, INSERT in this case so the Users' table is released while the SyncEntities' table is being written?
Yes, the transaction is held open for the entirety of the trigger as well. FOR is just an indicator, AFTER is the default type (change is made, trigger is run). The only other option for triggers is INSTEAD OF, which overrides the actual operation and you have to perform the real operations in the trigger itself. There's reasons for both but in general AFTER will be the one you'll want unless you're doing some serious data cleanup and transformations before inclusion.
We have a major problem with this since the worker process reads and updates the SyncEntities' table (makes a row lock) and then reads the Users' table to get the data to send to the other database (in this step there's no locks from the worker, the worker only locks when reading a row and updating it from the SyncEntities table).
Why are you not passing everything the sync needs to the queue table? It will shared lock the entry in the users table if it has to read the data. In general you should pass everything you need in one shot over to the worker pickup so you can stay out of the rest of the system while the worker... well... works. Service Broker, Manual Queue Table, deposit to external queue, whatever, it should be an encapsulated work request. Some of this is for modularity but in this case you already had all the data available to pass to the Sync, so why not make it immediately available to it without further joins?
I also would recommend Service Broker here, however, as this is one of the things it was basically built for, which is asynchronous data transfers. It also has the bonus of being internally controlled in the database during point in time restores so you don't have to attempt to resync the external application too (depending on how it's built).
Service Broker is available in all editions except Express (which is limited in that Express can't talk to Express, you need a standard+ in the middle somewhere). Its complexity mostly derives from the fact that most database people aren't familiar with message queue lingo, and endpoint setups can really make you crazy if you go cross server with the queue, which is the primary use for it so you'll end up there too.
There's a series of workbench articles by Adam Machanic that walk through basic service broker setup that I can't recommend enough. I'd start there:
http://www.simple-talk.com/sql/learn-sql-server/service-broker-foundations-workbench/
http://www.simple-talk.com/sql/learn-sql-server/service-broker-advanced-basics-workbench/
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply