Drive configuration for transaction log

bbrown

I'm looking for people's opinions/feedback on their preferred drive configurations for transaction logs. In these scenarios only 1 databases transaction log is being supported on a given drive set.

1. 2 large physical drives configured as a single RAID 1 array.

2. 4 (or more) smaller physical drives configured as a single RAID 10 array.

3. 4 (or more) smaller physical drives configured as multiple RAID 1 arrays with a transaction log file on each array.

DenSter

More smaller disks is always faster than fewer larger disks, given high volumes of throughput. It all depends on the specifics of your system, but the general rules of thumb are:

• only one transaction log file
• 2 disks in RAID1 is usually enough

What you need to do is consider a number of things:
1 - The size of the database and whether your customer likes to use NAV backups to create databases. In that case, you will need to have about 2-3 times the database size in disc capacity, because the TL grows to that size when restoring a NAV backup. You don't want a restore to fail because there's no room on the disks for the TL.

2 - Measure I/O perfmon counters to make sure how well the disks are performing in their current state. Read Waldo's blog about performance counters that count to learn which ones to look for. If the counters are within 'normal' parameters, even during peak hours, then you don't need to expand. Many times it's not even so much that you need to expand, but that the disk RAID settings need to be optimized. Get an expert involved, show them the perfmon counters and explain where you want those values.

3 - If you do need to expand, keep it all in one file, on RAID10

bbrown

DenSter wrote:

• only one transaction log file

Can you expand on this statement? What are the advantages of a single file? If my system requirements are for a 50 GB transaction log what are the advantages of having it on a single RAID 1 array with 2 72 GB drives or a single RAID 10 with 4 36 GB drives versus having 2 RAID 1 arrays with 2 36 GB drives each and placing a file on each.

DenSter

The advantage is that you let SQL Server just write to the TL without having to worry which file it goes to, and the RAID controller will take care of where the bits are physically written to.

The size of your TL is less important than the disk subsytem's throughput capacity. If all I/O counters are well within reason on one 2 disk mirrored array, you don't need to expand. If you do need to expand I'd suggest one TL file on a RAID10 array. If there's budget for 4 disks maybe you will want to set it up for growth, but most NAV databases don't need more than 2.

Like I said you need to consider a number of variables, such as throughput capacity and disk space. Then let SQL Server just write to one file and let the RAID controller take care of where to write the bits to and how to do it.

bbrown

Thanks for the replies.

I'm not asking this question in regards to any particular system. The three scenarios I present are all valid configurations for supporting transaction logs. With Navision, I tend to use a single RAID 1 array size to support the expected size. Usually the used log never gets more then a few GB with the exception of running things from the NAV client like a restore. The killer is that initial restore to migrate the database. It would be great if there was the option to make that a non-logged (or bulk logged) transaction.

I am looking for people's feedback on why they prefer one configuration over the others.

In the area of 1 vs. multiple log files, this is transparent to the developer and the user. The multiple files are managed internally by SQL.

In the area of multiple drives vs. fewer drives the advantage of many drives is apparent in random i/o demand (think data) but not so much when dealing with sequential i/o like logging.

There is also the issue of the RAID controller and the use of write-cache. This can add sizable performance. Of course you must be willing to accept the potential data loss.

DenSter

They are all valid configurations, and on any system that is running well within its capacity I doubt that there is any real advantage of one setting over the other.

I just like to keep it simple, that's all