On Tue, 22 May 2012, James Harper <james.harper@bendigoit.com.au> wrote:
vg's 0, 1, and 2 are on different sets of spindles to give higher IOPS. The problem is that the 4 snapshots need to be created atomically or else I'm going to get my database and transaction logs out of sync as there could be a few ms between the snapshot of lv2 and lv3.
If a database is designed correctly shouldn't it be possible to be consistent even if the snapshots are at different times?
The purpose of the log is that logged transactions can be replayed if they didn't get to the main data store. So if you snapshot the database filesystem and then snapshot the log a bit later then the DB server should just replay the newer transactions and everything should be fine - according to my knowledge of databases.
Am I wrong in my understanding of databases?
You mmight be wrong if you hadn't qualified your statement with "if designed correctly :) But my example was illustrative... it would also be possible for filegroups from the same db to be scattered across 10 different logical volumes, and any inconsistency between them would be disastrous. In addition, MSSQL is a bit funny wrt transaction logs. You can replay the backed up logs against a restored backup of a database that you took, provided you have an unbroken sequence of logfile backups, but you can't replay the current logfile against a previous live copy of the database. MS Exchange is much better in this regard and works as you describe with zero-loss-recovery provided the database or the logfiles survive the disaster, but I think it still relies on the action of a restore of the database to be able to replay the logs - you can't just pick up where you left off if one of the volumes suddenly does a time warp. James