3

SQL Server 2014, Standard Edition (running under vmware, fwiw). Adding a Dell MD3420 DAS box.

In the old school, you would set up discrete spindle sets for MDF, LDF, TEMPDB, BACKUP.

E.g.

  • MDF: Qty 6: 300GB-15K, RAID 10 // 900GB storage
  • LDF: Qty 4: 150GB-15K, RAID 10 // smaller data footprint, spindles to increase IOPS
  • TEMPDB: Qty 4: 150GB-15K, RAID 10 // smaller data footprint, spindles to increase IOPS
  • BACKUP: Qty 2: 2TB, RAID 1

Use Case: This system has 5 databases, and is OLTP.

Nowadays, I am being told: Just set up Qty 10: 600GB-15K drives, and put MDF, LDF, TEMPDB and BACKUP all on there. The overall higher performance of the larger spindle set more than makes up for the conention writing to LDF and BACKUP concurrently during backups.

  • a) Is this new way the right way?
  • b) LDF is supposed to be discrete relative to MDF... I feel funny!
  • c) I really feel backups should be their own spindle set (for a bunch of reasons)

The winner is ? SQL Tradition? Or the Dell Storage Architect?

Improve this question
3
  • One thing to take into account is that log files write sequentially and mixing them with the data files you are going to force all of the disk io to be random. I've worked with Dell on my server and they told me similar things, but they also didn't disclose that some of the performance they referred to required buying the licensing for the optional features on the disk shelves. I'm also running all SSDs so the traditional thinking for layout of the files on the disk systems doesn't fully apply but with HDD I still feel they do.
    – Aaron
    Commented Feb 19, 2016 at 3:26
  • Maybe helpful RAID 10
    – Aaron
    Commented Feb 19, 2016 at 3:33
  • Log disk access won't be sequential on the disk given that there will be more than 1 db. Each db's log file will be sequentially written but collectively the disk will behave like random access when there is more than 1 db.
    – variable
    Commented Jul 17, 2022 at 9:09

2 Answers 2

Reset to default
2

This is a database recovery question first and foremost, then performance.

DB Recovery:

By keeping the LDF and the MDF file in the same physical array, then you lose any chance of performing a 'tail of log' backup. This means your data is gone from either:

  • The last transaction log taken and copied off the failed array.

  • A mirrored pair/availability group/replicated database elsewhere

So first question is 'how much data can you lose'. If you cannot lose more than the frequency in which you back your logs then putting it on that array isn't going to meet your data loss objective.

Performance:

The rest really depends on your usage patterns. It seems like you do not have a active machine we can get a baseline from, if I'm wrong let me know. Assuming we don't though, we should understand that DiskIO is critical, especially when there might not be enough RAM or in a high transaction environment. Do you think it will be very highly transactional? If so, give lots of disks to the transaction log drive. That version of the Dell SAN let's you short stroke your disks which should give you a good performance boost as the transaction log drives typically go multi disk not for space, but for write performance.

Putting TempDB on it's own disks was useful but if you don't have any real clear idea as to why you would do it, then really you should test this or just put it in the same array and test that. TempDB can have odd patterns so you might get various performance results and growth. Many people though do just put it on the same disks as the data. It's up to you in this case.

It sometimes might be worth segregating disks by the app patterns and the DBs that support them. I'm not sure if you have that option. If you don't, your layout looks fine but without testing it's impossible to tell. Personally I would test as much as I could first, look at how much growth we are expecting, and then see if it makes more sense to add more IOPS to the main data array, or to keep them separate.

Are you able to do any kind of benchmarking at all?

Improve this answer
1

The final answer:

As Ali and other posters here note, the real question is: What is the detailed use case?

Saying "OLTP" doesn't answer the question. The real question is much more detailed. E.g. "Is TEMPDB consistently hammered (such that you want to isolate that IO from MDF IO)"

In our case, the benefit of a larger span (more spindles) is significantly greater than the TEMPDB load, so that is step 1:

  • Put MDF and TEMPDB on same span.

The next point is backup. As discussed, backup MUST be on diff span.

  • Done: Backup is not on the main RAID10 disk span (in our case, it is on entirely diff disk system/cabinet over iSCSI).

Now the hard one: LDF. By the book, LDF should be on diff disk system than MDF. This is old, established SQL Server protocol.

But that means degrading the overall disk system performance.

This is a subtle topic. It starts to push you to ask "What is the failure scenario? What is the recovery scenario?"

Some answers:

  • 1) We are implementing a direct attached scsi (DAS) via SAS interface.
  • 2) The most likely critical failure would be the entire subsystem (due to backplane or HBA failure)
  • 3) So having LDF on discrete disks within this cabinet is unlikely to matter

Further:

  • If we log ship these dbs every few mins to another SQL box, losing LDF is not critical

  • If we just make log backups every few mins (in addition to full backups daily), LDF loss is not critical

  • Conclusion: LDF and MDF can sit on the same span.

The winner: Follow the Dell Storage Architect: The performance gain of a larger spindle set, in this situation, outweighs other concerns.

Improve this answer
1
  • Putting mdf and tempdb in same span(set of disks) is better than having tempdb in its separate span?
    – variable
    Commented Jul 17, 2022 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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