SSAS Cluster: Issue with Mount Points while Performing Administrative Tasks
Issue:
Environment consists of 2 nodes, SQL and SSAS 2008 can failover between the 2 nodes, and accessible for queries.
All the drives and mount points come online on the nodes after failover.
SQL Server Fails over perfectly.
The Windows Cluster has been validated and no errors in the log.
When failover from Node 1 to Node 2 we get the following error when attempting to process anything on Node 2:
File System Error: The following error occurred when opening the file ‘\\?\Z:\ASDATADIR\txn.vmp
Assessment:
In this scenario directory which hosts AS Data is mount points to another volume, which means I have a Z Drive and I have created a Mount Point for Z Drive Calling it as ASDATADIR so here ASDATADIR is nothing but a Mount Point to Z Drive.
After research we found
Windows does not let you replace permissions on the root of a volume. To prevent problems during new installations and applications of updates to SQL Server, create a folder on the root of the mount point and install to that folder. At this point, permissions are set correctly. If you previously installed to a root directory, we highly recommended that you create a folder.
In Simple words:
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.
Excerpt from the Cindygross’s Post
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.
Resolution:
In our scenario we have 2 options to fix the issue:
Workaround 1: Using a sub directory under the mount points. For example, T:\mountX is a mount points of volume X. Then specify T:\mountX\data as the database directory will solve this issue.
Workaround 2: Add SQL server service account full control permissions to the volume, such as X, in disk management console.
We resolved issue by following workaround 1, in this case Processing / Restore or Creation of New Database was failing because we were not able to create New Files in the MountPoint Directory, when we created a new Directory inside Mount Point it worked Successfully