November 28, 2010 at 6:18 am
I've 3 database stored into 3 database server.
It's
1. X DB in a A Server
2. Y DB in a B Server
3. Z DB in a C Server.
Let's say my distributed transaction as follow
1. X DB perform insert
2. Y DB perform insert
3. Z DB perform insert
If I'm gonna use this Service Broker ("SSB"). Once
1. Y DB perform insert
2. Y DB perform insert,
3. then Z DB cannot perform insert due to network down.
Did a Y DB, and Z DB is roll back?
Need help because I'm really beginner of this SSB. May be this SSB can enhance my current solutions for distributed transaction.
November 28, 2010 at 10:04 am
It's not clear how you are using service broker here. If you have an insert in db X on Server A, does this insert (direct or through trigger) put a message in a Q on server A? Or Server B?
If the insert into the Q cannot be completed, I believe that your transaction will roll back. If you insert into a Q on Server A, then the transaction is complete. As far as the receiver, it will have to pick up the data from the Q when it can.
The power is SSSB is that it can asynchronously power transactions, so that if a server is down, it can be guaranteed that the message will be delivered when the server comes back up.
November 28, 2010 at 5:25 pm
Steve Jones - SSC Editor (11/28/2010)
It's not clear how you are using service broker here. If you have an insert in db X on Server A, does this insert (direct or through trigger) put a message in a Q on server A? Or Server B?If the insert into the Q cannot be completed, I believe that your transaction will roll back. If you insert into a Q on Server A, then the transaction is complete. As far as the receiver, it will have to pick up the data from the Q when it can.
The power is SSSB is that it can asynchronously power transactions, so that if a server is down, it can be guaranteed that the message will be delivered when the server comes back up.
Hi Sir,
1. I've insert in DB X on Server A. This insert through trigger. This insert put message in a Q on Server A.
November 29, 2010 at 9:59 am
In this case, the queue that Y puts the next portion of the message into for the conversation for Z to pickup is sitting there, waiting for Z to come and get it, and will do so until the end of time.
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
November 29, 2010 at 10:18 am
Craig is correct. Service Broker guarantees delivery, but not timing. The transactions in the other servers should still be there.
December 23, 2010 at 7:31 pm
Sir,
Based on your instruction, I'm glad because my implementation of SQL Service Broker ("SSB") was worked. Really cool. Currently, I knew
1. How to using SSB to send message between 2 database in same instances and same machine (There's 1 machine is involved). Any database is offline, both database is not updated. Once both is online, this 2 database is updated respectively. Atomicity looks comply
2. How to using SSB to send message between 2 database across instances and across machine (There's 2 machine is involved). Any machine is offline, both database in both machine is not updated. Once both is online, this 2 database is updated respectively. Atomicity looks comply
Now, I want to involve 3 database on 3 different machine. I intent to do as follow
1. Any machine is offline, this 3 database is not updated. Once this 3 machine is online, this 3 database is updated respectively.
I can imagine the logic if 2 database is involve. But I can't imagine the logic if 3 database is involve.
Looking for help sir
December 26, 2010 at 4:51 am
In the 3 db scenario you simply send out 2 messages addressed to 2 services, all in a single transaction. SSB will either accept the both messages and you commit the transaction, or you will roll back the transaction if either fails to be accepted by SSB. The transaction is NOT over all 3 databases (nor is it over the 2 databases in the 2 db scenario). Instead the 1st transaction on server A only holds for as long as you deliver the message to SSB, a 2nd transaction is started to retrieve the message from SSB on server B and a 3rd transaction is started to retrieve the message from SSB on server C. SSB is a guaranteed message delivery mechanism, not a distributed transaction system. If anything fails, you will have to take corrective actions yourself (i.e. your code needs to be prepared to handle those situations). For example: if either of the 2 messages in the 3 db scenario can not be delivered to, say server C, SSB will simply return an error message to the service on server A. Your handler on server A will have to know to a do a corrective action on server A plus send a message to server B to have that do it's corrective action too. My point being: SSB does not perform the rollback, it only notifies your service a rollback action is needed by sending a "http://schemas.microsoft.com/SQL/ServiceBroker/Error" message, you need to implement it. You can write your handlers to handle all messages in a dialog within one and the same transaction per service, but you do risk having long running transactions if you do so (read on for info how to reduce the risks).
For the timely delivery: you can specify a maximum life time on the dialogs, using the LIFETIME parameter to BEGIN DIALOG: if the messages can't be delivered at either server within that time, SSB will return an error message to the service on server A, and again that service will have to do it's corrective action plus notify 'the other' db of the error. This way the transaction will -after the timeout period has expired- either have taken place on all 3 servers, or a start was made to do a rollback on all three of them. If you do choose to handle all messages in the same dialog in one transaction I suggest to always set some maximum time on your dialogs: if you don't, your transaction may end up uncommitted for eternity (or close to that). An alternative could be to use the BEGIN DIALOG TIMER command, this has the advantage the conversation is not ended when the timer expires, so more messages can still be sent. However, you need to implement the error message handler anyway because other errors may still occur that need to be handled too, so if you don't need to send more messages after the timer expires, you don't need to duplicate the same corrective code in a DialogTimer message handler.
December 26, 2010 at 6:50 pm
Sir,
Your explanation means impossible to implement
"Any machine is offline, this 3 database is not updated. Once this 3 machine is online, this 3 database is updated respectively"
Looking for help
December 27, 2010 at 10:25 am
First, what you are writing doesn't make sense. I suspect we have language issue, but things like "Your explanation means impossible to implement" and "Atomicity looks comply" don't tell us what you think will not work or what you don't understand. They are nonsense in English. Please write longer sentences to explain yourself or ask more clearly.
Service Broker delivers messages, not transactions. When the machines come back up, the message is guaranteed to be delivered, and then it can be processed.
If you have a Q on database 1, and you put a message in there, when it is received depends on how that works. You can have a program look to deliver that to another machine or be read from another machine, or go to another Q on another machine. If that machine is up, the message is delivered and can be processed. that can be updating something on the other machine, but that is not part of the original transaction. It could be ms later or days later if the second machine is down.
If you need to guarantee that a transaction takes place on the second machine, then you can use the DTC, but both machines must be up for a transaction to commit on both machines.
December 27, 2010 at 9:46 pm
Steve Jones - SSC Editor (12/27/2010)
First, what you are writing doesn't make sense. I suspect we have language issue, but things like "Your explanation means impossible to implement" and "Atomicity looks comply" don't tell us what you think will not work or what you don't understand. They are nonsense in English. Please write longer sentences to explain yourself or ask more clearly.Service Broker delivers messages, not transactions. When the machines come back up, the message is guaranteed to be delivered, and then it can be processed.
If you have a Q on database 1, and you put a message in there, when it is received depends on how that works. You can have a program look to deliver that to another machine or be read from another machine, or go to another Q on another machine. If that machine is up, the message is delivered and can be processed. that can be updating something on the other machine, but that is not part of the original transaction. It could be ms later or days later if the second machine is down.
If you need to guarantee that a transaction takes place on the second machine, then you can use the DTC, but both machines must be up for a transaction to commit on both machines.
Ok sir,
I'll improve my weaknesses. Your critic is most welcome.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply