bodnarg
2 Iron

Large Windows SQL Server LUN layout

I know this is a complex question, but it seems to be difficult to find details on LUN size recommendations for large SQL environments.

Without getting into too much detail asssume a large database with a few VERY large tables in the multi-TB range.  Assume the same physical drives on the array side to handle this workload.

Is there any advantage to having a layout with multiple smaller LUNs and filegroups across those volumes vs. just having a single large volume with those same filegroups contained within that volume?

FG1 is on LUN1, FG2 is on LUN2, etc.  LUN1 => 8 drives, LUN2 => 8 drives, etc.

vs.

FG1, FG2, etc. are on LUN1 which is spread across all of the drives

Assuming that FG1/FG2 all contain really just a few large tables in both layouts.

The disadvantage with the multiple LUNs is that only scales so far and for clusters requires some pain to add them into the environment.

I have not read this specifically but for Windows is there any OS advantage to having multipe LUNs/mount points assuming the same drives behind the volumes?

Any input is appreciated.

Tags (3)
0 Kudos
2 Replies
Highlighted
HankD1
1 Nickel

Re: Large Windows SQL Server LUN layout

It depends on you IO workload. I have a high IO 10TB SAP SQL DB with 24 datafiles/LUNs that has a maximum IO/s around 16K. We built it this way from a SAP recomendation, they recomend one datafile per LUN per CPU/core.  If it's a low IO DB a couple large LUNs should work.

Jianyun
1 Nickel

Re: Large Windows SQL Server LUN layout

first of all, symmetrix has maximum meta LUNlimitation, 16TB for Vmax, 8TB for dmx3&4, and 4TB for dmx1&2. So if your SQL database is larger than symm max LUN size, you have to use multiple LUNs.

second, if there is MSCS environment, it doesn't support volume set, then you can't use multiple LUNs for a single file system.

If you are not hit by above limitations, then your question turns into "host side string or Symmetrix side string" choice.

The answer is: it depends.

We mostly don't like to configure LUNs in its maximum size. there are always many reasons to make such limits (maximum size). Performance is most likely the reason.

So finally you may have to configure several not huge meta luns, and make volumes sets over them. If these is MSCS environment, you have to build a LUN as large as you required but it couldn't exceed Symmetrix limitation.