다음을 통해 공유

Using Mount Points with SQL Server

  • 아티클

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:

  • A valid mount point that can be used by SQL Server databases is one that is mounted to a host volume (a shared drive in a cluster) that is associated with a drive letter. Multiple mount points can be hosted by a single drive meaning multiple mount points share a drive letter.
  • In a cluster, SQL Server must depend on each mount point it uses to avoid database corruption.
  • Do not put a SQL Server 2000 instance in a cluster where mount points exist, whether or not SQL Server 2000 is expected to use them (which it cannot). Do not add mount points to a cluster where SQL 2000 is already installed or future SQL Server 2000 patches will break.
  • Do not install SQL Server to the root directory of a mount point, always specify a subdirectory for all files. This has to do with how permissions are granted. If you must put files in the root of the mount point you must manually manage the ACLs/permissions.
  • Do not put DTC on a mount point.

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:

  • Attach a USB drive formatted to NTFS
    • In my example it showed up as G:
    • It has a volume name of CGROSSFLASH
    • There is a file called BeforeMount.txt on the drive
  • Open “Disk Management” (Diskmgmt.msc)
  • Right click on CGROSSFLASH and choose “Change Drive Letter and Paths ”
  • Click “Remove” for G: then “Yes”
  • Right click on CGROSSFLASH and choose “Change Drive Letter and Paths ”
  • Choose “Add” then “Mount in the following empty NTFS folder”
  • Click the “New Folder” button and give the directory a name, I’ll use USBMountPoint
  • Click “OK”
  • C:\USBMountPoint is the root of my mount point. C: is the root of the host. You can see test.txt in C:\USBMountPoint.
  • Try creating various files and directories in C:\USBMountPoint.
  • Now remove the mount point then re-add it but create new folders first. You will end up mounting C:\USBMountPoint\Level2\Level3. The files you created before now show up in the Level3 directory, not in the USBMountPoint directory.

 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.

 References:

Comments

  • Anonymous
    February 06, 2013
    Good collection of suggestions on mount points for SQL Server. Thanks for blogging on this!
  • Anonymous
    February 10, 2013
    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
  • Anonymous
    December 27, 2015
    Thanks for leaving this up, here is a link to another informative post that might be useful to you: www.sqlserverlogexplorer.com/using-mount-points-in-cluster