Transaction Log writes...

Question

Transaction Log writes...

swjohnson

Hall of Fame

Points: 3254
More actions
September 26, 2003 at 8:56 am

#65728

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
Accepting all invites

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

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

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

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

Accepting all invites

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 4

5409045121009-7368

SSCoach

Points: 15740

September 30, 2003 at 4:52 pm

#476457

Will failover clustering be a overkill

joachim.verhagen Hall of Fame Points: 3560 More actions · Answer 5

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.