Using Mount Points with SQL Server
Author: Cindy Gross, Dedicated Support Engineer
Tech Reviewer: Min He, SQL Cluster PM
Summary: Microsoft does NOT support installing or putting databases on the “root” of a mount point in SQL Server 2005, 2008, 2008 R2 unless you manually manage the ACLs/permissions yourself (modified 1/13/12).
The key points for mount points in combination with SQL Server 2005, 2008, 2008 R2 are:
For various reasons such as standardization, flexibility, space management, and just not enough letters in the alphabet many people use mount points on their servers. A mount point (aka mounted drive or volume junction) is a separate file system that is “mounted” onto a host drive so that it appears to be a subdirectory of the host drive. For example, say you have LUN volume A that is made visible to Windows as drive X:. You have a LUN volume B from another storage array and you want to present it to Windows. You might choose to mount it as X:\SQL1. To SQL it looks like a subdirectory, but it’s really a whole different file system. Because it is a different file system, permissions are not inherited from the host system. So when you grant permissions to X: and say to propagate them to child folders, they are NOT applied to the mount point!
For testing purposes, try mounting a USB drive to your client box as C:\USBMountPoint. You can move files to C:\USBMountPoint and they will appear on the USB drive. Disconnect the USB drive and attach it to another box and those files are available on the new system but NOT on the old C:\USBMountPoint location. That’s because the files aren’t really on C:, they’re on the USB drive that was mounted to C: for a while.
Example of creating a mount point on non-clustered disks:
SQL Server 2000 and later standalone boxes and SQL Server 2005 and later clustered instances are supported on mount points mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. However, you must put all databases, including the system databases, on a subdirectory of the mount point, not in the root of the mount point, unless you manually manage the ACLs/permissions. The root of a mount point is the entire directory as defined when you mount it. For example, say I mount d:\SQL1. That is the root of the mount point and I cannot install to it. If I mount d:\SQL1\ALL_DATA that is the root and I cannot install to d:\SQL\ALL_DATA. I could install SQL to a subdirectory/subfolder such as d:\SQL1\ALL_DATA\DATA. If you install SQL to the root of a mount point setup makes the assumption that permissions are propogated up from the host root (d: in this example). However, that is not how Windows works with mount points. When you create a subdirectory setup creates permissions explicitly and therefore setup works when you install to a subdirectory of the mount point. The same logic applies to adding new databases or moving existing databases, they should not be in the root of a mount point.
In addition to putting the databases in subdirectories, you also have to make SQL depend on each individual mount point. When you make SQL depend on a mount point you force that mount point to come online before SQL does and therefore prevent certain types of potential corruption.
It is becoming more and more common in Windows 2008 and later clusters to have DTC depend on one of the lesser used/less important SQL Server drives. However, if you are using mount points for your SQL files you cannot do this as currently DTC is not supported on mount points.
Mount points are a very useful tool. As long as you follow the rules for SQL Server you can increase your flexibility, spread your IO out over more IO paths, and/or add space to the system from multiple storage system with mount points.
Good collection of suggestions on mount points for SQL Server. Thanks for blogging on this!
Hello! I'm collecting info to update best practices. I'd assumed that SQL server would behave the same way to drive letters vs mounted volumes. Turns out that dbcc checkdb by default creates one list of pages to read per drive letter. This can reduce parallel io for mounted volumes sharing a drive letter. Trace flag 2549 can be used if needed, to create one list per database file. support.microsoft.com/.../2634571