Post reply

DB server Setup and Backup/Recovery strategy

  • July 5, 2006 at 2:47 pm

    #118538

    Guys,

    We are planning DB environment for 24/7 database. Basically the DB would be subjected to whole lot of DML operations and batch jobs (planning to have a batch job which inserts 12 million rows at each instance during the day).

    Can you suggest what would be the best database mirroring strategy interms of RAID and partitioning

    Also what would be the best backup and recovery strategy - currently we are planning to a complete backup once a week and transaction log backup every hour. Is this a good strategy

    Any comments/suggestions would be helpful

    Thanks

  • July 5, 2006 at 3:48 pm

    #647635

    Basic high level answers:

    RAID:

    • RAID 5 or RAID 10 for DATA.  The number of spindles will depend on the amount of disk IO.  You may want to split your data files onto multiple arrays to improve performance, again this depends on the amount of IO and $$
    • RAID 1 or RAID 10 for LOG.  Spindles will depend on disk IO

    BACKUP:

    • I'd suggest full backups on a daily basis
    • Transaction log backup will really depend on your requirements.  Is loosing 1 hour of data acceptable or do you need to tighten up this window?

    I hope that helps.

     

  • July 7, 2006 at 7:45 am

    #647976

    Full backups nightly sound good but you may wish to look into doing some Differential Backups (maybe every other night) to save some time and space.

    Steve

  • July 7, 2006 at 2:31 pm

    #648074

    Other than the hardware setup you really need to fit your backup schedule to what the business tells you it can withstand as far as data loss and/or down time.

    depending on the size of the database weekly fulls are usually fine. If the database is quite large 800GB+ kind of situation you may have to move to a diffrent strategy that includes fulls and file level backups.

    Doing weekly fulls and hourly tlogs would be a nightmare to restore, plus it exposes you to more risk. If you need to restore monday through thurdsay and have a bad tlog on wed backup you are done.

    I would recommend a minimum of daily diffs and hourly tlogs if that suits the business needs.

    If you could share more details it would be easier to map out a backup AND recovery plan.

    Cheers,

    Wes

  • July 7, 2006 at 3:40 pm

    #648090

    Let me add my recomendation for daily full backups.  Then do tlog backups based on what you are willing to lose, and the amount of activity (I have actually had databases with so many updates that quick tlog backups every 5 minutes worked best).  You may want to consider doing diff backup after your 12 million row batch insert.  In this way, a full recovery would be last nights full backup, last diff backup, and any tlog backups since that last diff.

    Ideally, you want to write your backups to same server then move them off to either tape or another server.  But you will not want to write the backups to same disks as data or logs.  So unless you are going to have different array and controller for backup disks, it would be better to backup to another server.

    I strongly recommend avoiding Raid5 for a database with anything but mostly readonly databases.  Raid5 has twice as many IO operations for a single write as any other Raid configuration, creating a performance problem quickly.  If you are doing 12 million inserts one or more times a day, you definitely do not want Raid5 for your data files.  Go with Raid10 (if 4 or more physical disks) or Raid1 (for 2 disk) for data files.

    Hope this helps



    Mark

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

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