Transaction Log writes...

  • swjohnson

    Hall of Fame

    Points: 3254

    We have a new director of IT and he has a very strong Oracle background and worked for them at one point in his career.

    We were discussing our SQL Server disaster recovery plan the other day and he was pleased with what he saw except for one thing. The only thing he was worried about about was the sanctity/integrity of the transation logs. He says Oracle has the ability to write to two transation logs (each on a different server) at the same time. His fear is that the drive that contains the tran log goes down while a write is being made to it, thus giving us an inconsistent tran log and more importantly missing data.

    My question, does SQL Server 2K have this capability or does a third party product give it this capability?

    Thanks

  • Rodrigo Acosta

    SSC-Insane

    Points: 21261

    In sql you can add more log files but they are treated as one log. When one file fills up, then it changes to the other.

    To avoid loosing the log in a disk failure, you must backup the log.

  • Jonathan

    SSC-Insane

    Points: 20427

    quote:


    We have a new director of IT and he has a very strong Oracle background and worked for them at one point in his career.

    We were discussing our SQL Server disaster recovery plan the other day and he was pleased with what he saw except for one thing. The only thing he was worried about about was the sanctity/integrity of the transation logs. He says Oracle has the ability to write to two transation logs (each on a different server) at the same time. His fear is that the drive that contains the tran log goes down while a write is being made to it, thus giving us an inconsistent tran log and more importantly missing data.

    My question, does SQL Server 2K have this capability or does a third party product give it this capability?

    Thanks


    SQL Server data will remain consistent after recovering from such a disaster. The recovery process will roll back any transactions that were not complettely written to the log. If you have write-caching enabled, make sure that the cache is fault-tolerant and battery-backed-up, and know how to recover by taking this into account.

    If he's just worried about "the drive that contains the tran log" going down, mirror the drive.

    --Jonathan

    Edited by - jonathan on 09/26/2003 10:36:01 AM



    --Jonathan

  • swjohnson

    Hall of Fame

    Points: 3254

    Thanks for you thoughts.

    From what I can determing here is what Oracle calls this featue--Multiplexing: "Oracle provides the capability to multiplex an instance's online redo log files to safeguard against damage to its online redo log files. When multiplexing online redo log files, LGWR concurrently writes the same redo log information to multiple identical online redo log files, thereby eliminating a single point of redo log failure. "

    Basically, the Transaction Writer in SQL writes the transaction into two different LDF files which reside on two different drives.

    This prevents a single point of failure on the transaction logs because it is written to two log files on different drive arrays. As such, in this scenario, you could continue writing transaction to the second set of log files and remain online and operational.

    Mirroring does not solve the problem. If you have a failure of the drive/drive array, how can it write to the drive let alone mirror the data?

    Any thoughts?

    Thanks

    SJ

  • 5409045121009-7368

    SSCoach

    Points: 15740

    Will failover clustering be a overkill

  • joachim.verhagen

    Hall of Fame

    Points: 3558

    quote:


    This prevents a single point of failure on the transaction logs because it is written to two log files on different drive arrays. As such, in this scenario, you could continue writing transaction to the second set of log files and remain online and operational.


    I suppose that somebody who worked for Oracle knows it better than I do, but I have seen from incomplete restores that if one of the logfiles from the log group was missing that Oracle does not start, so I cannot imagine that it will continue if a drive goes down. The advantage is that you still have the data from the logfile and with some fiddling can start the database again.

    quote:


    Mirroring does not solve the problem. If you have a failure of the drive/drive array, how can it write to the drive let alone mirror the data?


    I believe that mirroring is a better solutions, as the server will stop anyway when you lose a drive that the database uses. After you break the mirror and disable the faulty disk you can continue. (It is better of course to have a reserve disk available, but here they alway find other uses for those "spare" disks :-))

    The only better way is to use failover clustering as the large number said.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply