13

The previous Senior DBA set up mount points for all of our drives across every SQL Server throughout the company. The new Senior DBA is horrified by mount points wants to change our standard (mainly, I think, because he has no experience with them).

Based on the results of numerous internet searches, I can't find any (post-SQL Server 2000) reason to not use mount points.

Is anyone aware of Windows OS limitations regarding this topic?

  • I've been hearing the claim "the OS doesn't recognize mount points" a lot lately. (Untrue, based on my research into the versions of Windows Server we use).

Is there any evidence- or experience-based reason NOT to use mount points with SQL Server?

  • Assume that running out of drive letters is not an issue for us.

It's my understanding that mount points are incredibly useful for segregating workloads.

Can anyone confirm or refute my understanding that mount points actually segregate/isolate workloads of the different types of data and log files (system database files, user database files, tempDB) more efficiently than one drive each for data files, log files, and tempdb?

Improve this question
1
  • Physical storage is largely abstracted when one uses a SAN. Regardless of whether one uses a drive letter or mount point, you need to work with storage admins to provide a LUN with the needed characteristics. Neither SQL Server nor the OS will have knowledge of the underlying configuration, although you can install vendor tools to provide visibility.
    – Dan Guzman
    Commented Aug 4, 2017 at 11:50

4 Answers 4

Reset to default
13

It depends on what's at the other end of the mount point. If the mounts are all LUNS spread across the same physical drives, then no gains. If the LUNS are all over a shared, slow, iSCSI channel, maybe no gains. If the LUNS are all under 1 controller...you see how many variables are at play. There's no one answer.

To determine the configuration of the mount points, see Locating Mount Points Using PowerShell by Boe Prox.

SQL Server has no problem with mount points. These are defined at the OS level and SQL Server "doesn't know1" they're not the same volume as the drive they appear to be mounted in.

Some Monitoring tools might give you bad information based on that last sentence, however.

For example if you have three mount points like

  1. C:\SQLData\SQL_Data where all your .MDB files are stored
  2. C:\SQLData\SQL_Logs where all your .LDF files are stored
  3. C:\SQLData\SQL_Backups where all of your .BAK and .TRN backup files are stored

Then SQL Server will work without any issues. But if you run some sort of tool that warns you when disks are low in space, it may check the C: and not the mounted volumes below it, so you may not know when those mount points are low on disk space. Also, various "best practices" queries will throw false warnings telling you that you shouldn't have your data, logs, and backups all on the same disk because SQL Server thinks they are on the same disk. Those are false flags, and can be ignored.

But you'd basically want to set up some additional steps in your server monitoring to make sure the C: drive has enough space and that each mount point does, too.

The times I've used mount points in SQL Server, that's been the only issue I've run into: SQL Server system health reports that give false data about free space available, and false errors saying you shouldn't have all of your data on the same drive. Since you know those are false errors, they are easy enough to ignore.

1 SQL Server has data that makes it aware of the mount point, but from a practical use viewpoint, there's no difference in behavior. It "just works," trusting the OS to handle the specifics. Just as it trusts the OS to handle the specifics for iSCSI LUNs that are connected as local drives.

Improve this answer
2
  • 2
    Not sure if there's still an issue around SQL install and ACL around mount points on the root of a drive, but it's probably worth putting them inside a mountpoints folder just in case. Ex: C:\SQLMountPoints\SQL_Data, C:\SQLMountPoints\SQL_Log
    – Nic
    Commented Feb 14, 2017 at 18:14
  • True. The one time I did it this way, I had a "SQLDATA" folder, then "data", "Log", and "backup" mount points under that. Or something to that effect.
    – CaM
    Commented Feb 14, 2017 at 18:22
8
+50

In addition to CM_Dayton's answer and Sean Gallardy's answer, one issue not yet identified with Mount Points is related to security. To quote Guidelines for Setting SQL Permissions on Mount Point Folders:

Although the mount-point root folders may look like regular folders and are accessed in the same way folders are accessed, they are not regular folders. As a result, when you set permissions on a mount-point root folder, permissions are not inherited from the “parent volume” the same way as regular folders. In fact, they are not inherited at all. This is because although it appears the mounted volume is a child of the “parent volume”, it is not. You are simply accessing the mounted volume via a path from the “parent volume”.

In a nutshell, you have to assign permissions to Mount Folders differently if you want to utilize the mount-point root folder. Instead of assigning permissions like you would do with a normal folder, you have to assign permission on the volume. Again, from that same article (highlighting is Microsoft's):

Gotchas

Unfortunately, it is still possible to set/view permissions on the mount-point root folder via Windows Explorer, which can lead to unexpected results because the permissions of the mount-point root folder may seem valid and you can see “proper” inherited permissions, however these are not the permissions applied to the mounted volume.

Guidelines

  1. It is recommended that you do not place any files directly in the mount-point root folder. This will make permissions management much simpler, because the tendency is to always check the folder permissions, which in this case is misleading. Instead, create a subfolder under the mount-point root folder, and set the proper permissions to that subfolder. Since the subfolder is a regular folder, the folder permissions you observe and set are indeed the permissions being applied. So using the previous example, you would want to create a new folder: D:\FolderForVol3**SubfolderXYZ**. Now, set your folder permissions against that new SubfolderXYZ folder as you normally would.
  2. If you absolutely must place items directly in the mount-point root folder (Not the recommend approach), then you will need to set volume permissions, not folder permissions. Recall, that this is because the mount-point root folder permissions are not the permissions which will actually get set on the mounted volume (because the mount-point root folder is not a real folder). You can set volume permissions as follows:
  3. If you are adding a new folder for SQL to use, be aware of the required permissions for SQL access:

If you don't want to nest everything in a subfolder under the Mount Point as MS recommends, the way I find it easiest to assign permissions is via the cacls.exe utility. Detailed instructions for it can be found in You cannot apply permissions to the root directory of an NTFS file system volume in Windows Server 2003.

I don't think it's a fully resolved issue quite yet. This recent question SQL Server FCI installation with Mount Point permissions issues shows it can still happen on Windows 2012 with SQL Server 2016.

Depending on what security you want assigned, the command may vary, but testing is key to success so get acquainted with the command before running it against a live mount point as you can quickly bork security if you forget something as simple as the \E flag.

Improve this answer
2
  • The previous senior DBA was not aware of this security concern (ack!) and I wasn't either until this post. I'm going to put together a CMS query to find all db files affected. Cacls seems like a good route (though I am going to look for something PoSh-based). @JohnEisbrener - have you had problems setting the ACLs on db files when locked in exclusive use? If so, what's the next step?
    – SQL_Deadwood
    Commented Aug 9, 2017 at 17:33
  • 1
    @SQL_Underworld, I would recommend doing anything with cacls during a maintenance window, during which you can take the database instance offline. While probably not necessary, it will reduce the amount of variables that could happen.
    – John Eisbrener
    Commented Aug 13, 2017 at 15:47
8

Based on the results of numerous internet searches I can't find any (post-SQL Server 2000) reason to not use mount points.

The main reason is someone had a bad experience with them (or, conversely, no experience with them) and has completely ridden them off... forever. This is otherwise known as personal preference.

Now, there are some reasons that you couldn't use them. The number one reason I can think of is that a 3rd party driver or application/tool (think filter driver, disk replication, etc.) does not support it. A quick example of this is a block level disk replication tool that did not support anything other than NTFS, with only specific cluster sizes and couldn't go above 2 TB for any specific volume.

Is anyone aware of Windows OS limitations regarding this topic?

No. you can make many, many mount points. In fact, you'll generally have an issue with your device interfaces before you hit any appreciable limit inside of Windows Server (Assuming you're not using a version of Windows Server that is over 17 years old...).

•I've been hearing the claim "the OS doesn't recognize mount points" a lot lately. (Untrue, based on my research into the versions of Windows Server we use).

If the OS didn't recognize mount points, then how would it even let you use a mount point? That just makes no sense.

If the OS doesn't recognize mount points, why would it track them and query their metadata? Also, please note that a mount point is a construct of the filesystem which an OS may or may not support. Not all filesystems you come upon may support mount points, however the most common filesystem in Windows Server is NTFS which in fact does support mount points and it has for a while.

Just to bring this untrue item home even further; Windows Clustering has something called Cluster Shared Volumes (CSVs) which actually use mount points for the volumes... that's a native item using technology. I have to say, whomever told you this needs to be educated in the issue.

Is there any evidence- or experience-based reason NOT to use mount points with SQL Server?

Yes, there is always that one server running Windows NT 4... don't use it there. You may also want to make sure you're running a supported version of Windows Server and staying current with updates.

However, as I described above, there may be 3rd party items that are not supported or do not work properly with them. I'd say drop that provider and find a new one.

It's my understanding that mount points are incredibly useful for segregating workloads.

Mount points are just incredibly useful. There are many ways to use them, the most common is to get around the drive letter limitations (as in, there are only so many) of Windows. The next most common use is to have smaller manageable sized drives (think LUNs, virtual disk[VMDK, VHDX]) to help get away from insanely large and rarely manageable monolith volumes (it's really becomes a problem to manage drives in the 10TB range as a single LUN, virtual disk, etc.) especially on older versions of NTFS where the implementation was less than the possible usage... for example, in older versions of Windows the maximum NTFS size was 2TB.

Workload segregation is another great use. You can definitely see, there are many uses and it depends on your individual use case. There are also improper ways to use it... such as making a blanket statement that everything needs to be a mount point. That's just crazy administrative overhead at that point.

Improve this answer
3

Mountpoints are the way to go for servers that have shared applications or for slicing up data across more than just the typical D-Z volumes.

For example, you can install all of one applications data, log and temp files on the e: drive for example. E:/MP_1 can have data files for Business A, E:/MP_2 can have the log files E:/mp_3 can have temp files for Business A and so on. Then you have Business B on the mount points on the F: Drive. Each mount point has its own space.

The OS has absolutely no problems with MPs. Clusters and Always On have no problems with MPs. I worked for a very well known bank that had the majority of their SQL Servers installed on MPs. Once the DBA uses them and understands the concepts it will propel them to easier solutions in shops that need it.

It was mentioned not to install anything on the MP root. That is right. Nothing on MP root just like you wouldn't install anything to the root of D as an example.

Auditing and monitoring solutions like Foglight, Guardiam, EMS, and PBM also have no issues with mount points.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.