2

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 ?

  • 1
    Your ldf files are your transaction logs. – Eric Higgins Dec 4 '14 at 18:14
4

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 ?

  • Also worth noting: the system databases should be on their own disk, or at least with the data, but not in their default location of C: – LowlyDBA Dec 4 '14 at 18:59
  • With regards to tempdb, storing it on a ramdisk will be generally faster when using temporary tables/CTEs. If you've got memory overhead, I would highly recommend. – Papa Dec 5 '14 at 4:47
  • Thanks for the very helpful information. I am sorry I was confusing .ldf files and my Transaction Log backups that are going to a different drive on my current server. I believe I have things sorted out. I do have one other question regarding the tempdb, would it possibly be faster to use 2 Raid 1 SSD's for the tembdb or 4 Raid 10 SAS 10K drives? Thanks again for the help. – PBK Dec 5 '14 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.