Sql-server – SQL Hard Drive Config

configurationhardwareraidsql server

I am setting up new server and am a little confused in my research of HD configuration. My main question is :

  1. Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?

  2. Do Trans Logs and ldf logs write concurrently or one after the other?

  3. My plan is to configure it as follows:

    C:\ Raid 1,

    Data (Raid 10),

    Logs (Trans & .ldf) Raid 10,

    TempDB (Raid 1),

    Local Backups (Raid 10).

Does that make sense or should I separate the Trans & .ldf files onto separate raid configs ?

Best Answer

Your questions are very broad as you are touching a lot of topics in sql server. I am just going to provide you relevant links so you can understand the subject in a better way and at your own pace.

Is it recommended to have the Trans Logs and DB logs (ldf) on separate spindles or can they be on the same?

Seperate spindles is the best recommendation. Read on When should you put data and logs on the same drive?

Do Trans Logs and ldf logs write concurrently or one after the other?

Transaction log is your ldf file. Writes to the transaction log are sequential in nature and benefit by being isolated on to separate physical devices.

Read up on : Diagnosing Transaction Log Performance Issues and Limits of the Log Manager and Optimizing Transaction Log Throughput

Regardless of your decision, though, use Perfmon after the system goes live and track the drive activity. If one of the two arrays is being overwhelmed with load while the other one sits idle, then it’s time to rethink the decision.

I would suggest :

  • RAID 1 for OS
  • RAID 10 for data, logs and tempdb

Note that there are many other factors that will directly or indirectly affect your choice as to whether your workload is OLTP or DSS ?

Related Question