Data & Logs on same disk?

  • dba92081

    SSC Eights!

    Points: 934

    Can the data & log files be on the same disk drive?

    I have always been told to keep them separate, but my boss wants to setup our new cluster server with RAID10. Issue: we don't have enough drives to keep the logs separate on a RAID1, so he wants to combine them. Is there any downside with doing this?

    Also: since we are clustering the server, won't we still have to backup the databases with maintenance plans? He keeps telling me its a mute point.

    Thank you for any advice.

  • Yelena Varshal

    SSC-Dedicated

    Points: 34207

    I sometimes keep data and log on the same drive if I have only one logical drive. This is OK. They say that log writing is mostly sequential writes and the head stais at the same place while a database access is random reads and writes and the head has to travel all over the disk. That is why they recommend to separate log on its separate drive for performance. As well as a Page File, Backup files etc. But a lot of other things may affect performance including poorly writtten queries. In your case you will use RAID10 with gives good performance, so to have log and database files on the same drive will be OK.

    BUT... If the log and database files are on the same drive it is very important to perform frequent log backups and move backup files out somewhere or to implement log shipping for data protection.

    Regards,Yelena Varsha

  • laker_42

    SSCarpal Tunnel

    Points: 4785

    Yes, it will be OK but don't expect to get good performance with it just because the drives are RAID 10.  If this server will get much traffic at all, you are going to feel the performance hit from having them on the same drive.  I would try to find a way to get 2 more drives for the log files.  It would be well worth it in the long haul.

    Just my 2 cents...

    John

  • Linda Johanning

    SSCertifiable

    Points: 6727

    Regarding the database backups... tell your supervisor that they are an absolute necessity.  What are your plans for a disaster situation?  Let's say you get hit by a natural disaster and your Data Center is wiped out and you have no database backups. All your data is lost.

    Also, what if something or someone runs amok on the database and data is deleted or corrupted--again you have no database backups to restore from.  You must insist on database backups even on a cluster.  Tell him/her that it is a DBA's job to protect the data 🙂

  • dba92081

    SSC Eights!

    Points: 934

    Thank you for all your responses!

    What kind of backup plan would you recommend for a clustered environment?  We currently have full backups happen nightly to tape (plus another local disk drive because I don't trust tapes ) with log backups happening every 30 minutes.  I was thinking of having an extra drive just for the log & data backups that our backup admin can get for the tape backups with a similar backup plan we currently have.

  • Yelena Varshal

    SSC-Dedicated

    Points: 34207

    Totally Agree with Linda. I had to restore databases because developers deleted tables, databases, imported bad data, generated SQL Script with default options (with Drop Table statement)and then accidentally ran it on the production database.

    You do have to run database backups and store them

    Regards,Yelena Varsha

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

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