I'm trying to make sure that we're following the "best practices" with SQL Server and I have some questions about log files.
One general DBA rule is that data files must be on a different device than log files. This way, you can lose either disk and still recover all committed transactions. If you lose the data disk, you restore from backup and then rollforward all of the transactions that are in the log file. If you lose the log disk, you roll back uncommitted transactions and then create a new log file.
This scenerio doesn't seem to be supported by SQL Server. It looks like SQL Sever can deal with a failed data disk but, it can't handled a failed log disk. Is that correct? The documentation says that the log file holds both before image data and after image data so, if the log disk drops dead, SQL server can't roll back uncommitted transactions so you're left with a corrupt (or and least suspect) database. You can restore from backup but, you can't roll forward since you've lost the log file.
SQL Server seems to be a really good database with the exception of this gigantic flaw. I must be missing something, what is it?
Thanks,
John Vottero
John,
I think your explanation of why you separate log and data is where the problem lies. The transaction log in SQL Server is the first place tranaction data is written, and it's only written to the data files when a checkpoint occurs.
The reason you want to keep the log and data separate is because log file access is mostly writes, and you want to put the log on a mirrored drive, optimized for sequential write access, and put the data files on a RAID drive, because you'll mostly be reading from the data files. This provides the best performance of your database.
|||Yes, there are lots of good reasons for putting logs and data on different disks but, whatever the configuration, I want to make sure that we don't lose a days worth of transactions if we lose a disk, any disk. Can that be done with SQL Server?
|||Standard practice is to put your log file on a RAID 10 device - hardware mirroring.
Your guidelines seem to be based on loosing only 1 disk, so having a hardware mirror fits the requirement. In the event a physical disk is lost the mirror picks up and moves on.
If you loose the data drive and part of the mirror you're still fine, just restore the last full backup and the tail of the log.
If you need more assurance than that you can ship the log to another server.
Should you need still more security than log shipping you can use database mirroring in sql 2005 sp1.
In the end your business reasons are going to dictate what kind of fault tolerance you will need, and what kind of down time you can tolerate. That will drive your decision toward "best practices".
|||John,
On our production servers I have a database maintenance plan which backs up the full database once a day in the early morning, and performs transaction log backups every hour. (One server has significant enough activity that I back up the transaction log every 15 minutes.)
These backups are done to disk files on a separate drive from either the data or the log files. Each night the backup files are then backed up to tape.
Since my data and log files are on our SAN, the backup files are sent to local drives on each server, so at no time will a single point of failure cause the loss of more than an hour's work, or in the case of the one server, more than 15 minutes work.
You might consider a similar backup strategy.
|||All of our logical disks will be RAID10 so it's unlikely that we will lose anything but, I want to make sure that all the bases are covered. It looks like we'll be doing log shipping or frequent log backups.
Thanks for all the advice!
No comments:
Post a Comment