January 9, 2010 at 7:32 am
im wondering which is more reliable when using cross database transactions.
eg 1 Mirroring:
Server 1 is the principal server.
It has 2 databases, customers and orders.
Both databases are mirrored to Server 2.
The front end application generates multi database transactions, where a user can update the customer details, and click a button to generate a new order identical to the previous order, all in one go.
Both databases are mirrored. If i have a failure, is there a chance the two databases could be out of sync?
What about the same example, but for log shipping?
edit: my terminology "cross database transactions" may be incorrect and what i mean is to keep referential integrity between databases, and also in a scenario where two databases are updated within the one transaction.
January 9, 2010 at 7:47 am
as mirroring is at the transaction level, whereas log shipping is at the transaction log level, mirroring would be the better bet. In this instance I would run mirroring in synchronous, high safety mode, then you know if its committed on the primary its committed on the secondary as well.
Presumably the application ensures transactional integrity by either committing everything or rolling back everything so log shipping is workable if backups are run at the same time, but you cannot guarantee every log will be copied across with each run of the copy job, so you may have to restore to an older point in time. therefore I would have a time lag in the restores to allow for that possibility.
---------------------------------------------------------------------
January 9, 2010 at 7:55 am
A time lag is a good idea, but it increases the potential amount of data loss, am i correct in this assumption?
Thanks for the reply. Clarifying that mirroring is at the tran level where logshipping is at the tran log level cleared up a log of questions for me.
January 9, 2010 at 9:27 am
winston Smith (1/9/2010)
A time lag is a good idea, but it increases the potential amount of data loss, am i correct in this assumption?
you only put a delay into the restore phase of logshipping, not the backup and copy phases, so no there is no increase in potential data loss, it gives you an opportunity to use the standby to roll back errors, unintentional deletes etc, or in this case as its not restored up to the minute, restore both your databases to an earlier point than the latest tran log.
logshipping itself does have a higher potential for data loss than mirroring.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply