Sql-server – MDF, LDF, TempDb Location on same Hybrid SAN

disk-structuressansql serversql-server-2012ssd

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!

Best Answer

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.

Related Solutions

Sql-server – SQL Disk Setup Advice – TempDB, Log DB, Data file placement question

Unfortunately this is a big ol' "it depends". Complicated further by your having so many applications, probably with wildly different IO profiles, sharing the same instance/server and arrays.

Usually you would want to isolate data and log files as the IO profiles are polar opposite. Typically weighted toward random read for data files and sequential write for logs. The log element is somewhat different in your case as you have multiple log files.

I'd start by getting an understanding of the IO the databases are consuming relative to each other.

    SELECT
        DB_NAME(fs.database_id) AS [Database Name]
      , mf.physical_name
      , io_stall_read_ms
      , num_of_reads
      , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
      , io_stall_write_ms
      , num_of_writes
      , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
      , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
      , num_of_reads + num_of_writes AS [total_io]
      , CAST((io_stall_read_ms + io_stall_write_ms) 
        / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
    FROM
        sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf
    ON  fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]
    ORDER BY
            DatabaseName ASC, mf.physical_name
--  num_of_reads DESC
--  num_of_writes DESC  
    OPTION (RECOMPILE) ;

If you can format the output of this into a readable form and add to your question, someone can make a more educated guess at the best use of your 18 disks.

If I was doing this blind, I'd probably start off allocating:

6 RAID10 for the busiest database's data files
6 RAID10 for the other database's data files
4 RAID10 shared Logs
2 RAID1 Tempdb (your single disk configuration is a risky single point of failure)

Assuming that:

the SAN hosts other arrays and therefore hot spares are available from a pool
snapshot isolation isn't in use or isn't placing heavy demands on tempdb

Edit: Following update to question regarding queue lengths:

Queue length counters are oft misunderstood when evaluating SQL Server performance. One of the best analogies I've come across is from a Simon Sabin blog post, Disk Queue Length - a bit like buying Guiness.

In your case, they are so low that you might get by with half the spindles. Would still be interesting to see the other stats from the above query though.

Sql-server – Attaching MDF without LDF

Your MDF is actually corrupt, based on that error message. This means you can't attach it.

You could create a database with the same name, substitute MDF (and rename LDF), and hope it comes online or at least suspect to start recovering it.

Otherwise, you'll need to find a 3rd party tool that understands MDF files to get data back.

Or just restore from your latest backup...?

Best Answer

Related Solutions

Sql-server – SQL Disk Setup Advice – TempDB, Log DB, Data file placement question

Sql-server – Attaching MDF without LDF

Related Question