Problem solve Get help with specific problems with your technologies, process and projects.

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.
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.


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.

Dig Deeper on Database storage

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.