Ask the Expert

Setting up databases, indexes and log files

Bits & Bytes: When setting up your storage solution, you may have asked yourself a question or two. "Do I need to mirror my log files if I use RAID technology or, do I still need to archive my logfiles if I use RAID?" Once again, expert Chris Poelker gives us the low down on the proper way to set up a storage solution.

Requires Free Membership to View

The reader asks: 1. Do I need to mirror my log files if I use RAID technology?
2. Do I need to worry about what and where datafiles go if I use RAID technology?
3. Do I need to worry about keeping data and indexes separately if I have RAID?
4. Do I still need to archive my logfiles if I use RAID?

Chris Poelker's response

The short answer to your question is YES.

Set up your storage as follows:

* Database files can be located on dedicated RAID5 storage. This is the best compromise between protection and speed.

* Indexes can be co-mingled with the database files but may be placed on in-memory resident or solid-state disk for speed.

* Log files should be placed on RAID1 or RAID10 storage. There should be no other processes located on these disks.

Depending on the resiliency of the storage solution, snapshots may be used to archive everything. Use a script to put the database in hot backup mode, implement the snapshots and then undo hot backup mode. If the storage array supports instant snapshots, this can take just a few seconds.

Archiving using snapshots can be done in two ways. Metadata-based snapshots or hardware-based clones. The ability to clone instantly is done differently by the various storage vendors. You may need to have a pre-configured third mirror. Others allow you to clone a RAID5 volume instantly. Ask your storage vendor what they allow.

The metadata solutions can be host based or storage based. Many of the solutions available for metadata snapshots use a "copy on write" technology that moves the original data block to the snapshot before a new write is allowed to the primary volume. This can lead to performance overhead. The good news is that as more data is copied out, the solution gets faster.

If you can afford it, it's best to put everything on striped mirrors (RAID0+1). In my opinion, it's best to mirror first, then stripe. This way you can survive multiple disk failures. If you stripe first and then mirror the stripes and if you lose a disk in one of the stripes, all data must be accessed from the other stripe and you will be unprotected if you lose another disk.

Chris

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .bphAaR2qhqA^0@/searchstorage>discussion forums.


This was first published in June 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: