Rolling back a distribution
-
Here's another one:
When distributing large transactions to a remote server over a not-so-fast connection, what happens if the distribution is disturbed in the middle?
Does the distribution rollback?
is ther any way of configuring this behaviour?
Any help would be appreciated on this one.
Thanks,
- Avi
-
Thats right. Its a transaction like any other.
Andy
-
How would I access this transaction?
what would be the syntax (i.e. "rollback tran <tranName>"...)
Also, how are these transactions' attributes configured (or are they hard-coded)?
I tried checking the status of one target-table while an entire distribution was still taking place (i.e. - not yet committed), and even though the transaction hadn't yet completed, the destination table already had the changes in it.
Am I missing something cardinal here?
Thanks,
- Avi
-
No:-) Couple different layers here, will explain to the extent that I understand! First layer is the transaction within the publisher. Log reader will NOT process it and add it to the distribution database until it's committed. Next is that the act of getting the completed transaction from the publisher and posted into the distribution database is treated like a transaction (can't tell exactly since it processes the log directly) but essentially it will not mark the log entry as completed until it's been added to the distribution db. When the transaction from the publisher is written to the distribution db that operation is logged (like any other write) and may be rolled back if an error occurs (out of disk space for instance). When the distribution agent runs it uses CommitBatchThreshold and CommitBatchSize to determine how much of the current transaction it should post to the subscriber at a time. This allows more responsiveness on both distribution db and subscriber db - otherwise very large transactions might take hours to post depending on the number of rows changed and the speed of the link. I suspect that the batch size setting is what allows you to see data on the subscriber before all changes are complete. Finally, all transactions on the subscriber are logged so that the change from distribution can be rolled back if needed.
In the case of a not-so-fast connection you probably want to go with a small batchsize/commit, otherwise if something happens the entire thing will rollback and you start trying to send all over again. Other than tuning it a little the distribution agent handles the rest so if the agent fails for some reason all you need to do is restart it, it handles the transactional part for you.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply