0

I have seen this question asked before regarding SSDs or SANs, but never a Hybrid SAN. Also, most of the questions are several years old, so I figured I'd get a fall 2016 take on the answer.

We currently have a physical server with ~150 SQL 2012 databases all located on a RAID10 volume. The TempDb is located on the OS volume and is RAID1. We do mostly reading, with occasional, massive batch inserts.

We are migrating to a virtual server housed on a Hybrid SAN (24 drives, 1/3 SSD 2/3 10K HDD).

The general consensus is to keep MDF, LDF, and TempDb on different volumes, but I am getting conflicting opinions from my SQL guru vs. my SAN master.

One of the benefits to the Hybrid SAN is that I can create volumes of all SSD, all HDD, or a combination of both (magically migrated based on usage) with a different RAID method for each volume.

Given all that, what would be the suggested best practice for MDF, LDF, and TempDb placement? All on same volume of SSD? Split off into different volumes, some SSD some HDD? What RAID method? How about formatted block size and partition alignment (if that applies to SAN/SSD)?

Thanks in advance!

| improve this question | |
  • The "magical migration" is called autotiering. – vonPryz Nov 3 '16 at 14:54
  • I meant magically as in it is handled automatically by the SAN and I have no control over it (unless I specifically tell it to stay in a tier). – Jim Billig Nov 3 '16 at 18:30
1

The basic principle do not change regardless of what SAN, DAS or USB sticks you use to hold the data and transaction log files. TempDB is just a "special" database where its data and transaction log files have different workload behavior considerations since it can serve 1 - '000s of databases.

You design your database storage to handle the IOPS, throughput (MB/s), latency and reliability/availability. Modern SAN solutions largely removed the need to have custom designed physical drives and file placements. There's no magic and it's not a free ride. The SAN has to be setup to deliver on the numbers and ensuring it has the right level of protection against failure, including perf impact during partial failures. It's really boils down to letting the vendors/admins configure their physical layout optimized for their SAN and delivering on the key requirements listed above.

If you can correctly measure what you need and the SAN admins signup to deliver on the SLA, I won't try to get into the physical layout. For starters, it's technically interesting but you're signing up for long term maintenance. Also, with most SANs, you really shouldn't or even cannot pick some arbitrary physical disk configuration. Critical point here is having the right numbers for the 4 areas above and the SAN admins signing off and being accountable to deliver.

| improve this answer | |
  • +1 for SLA on the IOPS / latency deliverables. Everything else doesn't matter, because it's out of your hands. – Randolph West Nov 4 '16 at 2:55
  • This SAN lets you specify one or more Tiers and RAID configurations per volume. I am not speaking to a "physical disk configuration". The term Dell uses is a "virtualized storage architecture". You can create one volume with SSD/RAID5, then another with SSD/RAID10, and yet another with HDD/RAID6. Heck, you can make another with SSD/RAID10 autotiering down to HDD/RAID6. I am trying to determine whether the MDFs would do best on spinning disks at RAID5, or SSD at RAID10, or some other combination. Same for logs and TempDb. – Jim Billig Nov 4 '16 at 13:37
  • I get it. My point is, MDFs, NDFs and LDFs will do best on whatever delivers on the requirements above. That's how I design and size storage. DBAs got into the nitty gritty details because for decades, most storage admins/vendors didn't understand database workloads. Storage vendors/admins are MUCH better today at delivering on IOPS, throughput, latency and HADR SLA for databases. GB/TB are secondary considerations. If I need and get 3000IOPS for 64K blocks at <2ms latency, I don't care what the build out is underneath so long as my HADR SLA is also met. – SQLmojoe Nov 8 '16 at 21:36
  • If you have storage admins that don't get this, that's bad luck on your part. In that case, get the numbers you need for the requirements mentioned. From there, you can easily figure out which configuration will meet your requirements either from vendor specs or just running simple tests to measure. I like SQL IO but there are other tools you can use to test. There are lots of articles on the subject. They all mostly say the same things. (e.g. brentozar.com/sql/sql-server-san-best-practices). – SQLmojoe Nov 8 '16 at 21:40

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.