共用方式為

Considerations with using Mount Points in SQL Server 2008/R2 Failover Clusters

  • 發行項

Recently I was testing with using Mount Point in SQL Server 2008 failover cluster installation and came across some interesting issues, which I wanted to share for the benefits of my customers.

Installation Failure:

Scenario: The first time I tried the installation with mount points, the installation failed with the following error – “Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes”. Consider the following scenario -

  • During the installation, on the Data Directories page, you select separate mount points for system, data, log, Tempdb and Backup directories, but do not make any changes to the default folder structures
  • The SQL Server service account does not have sufficient permissions on the mounted drive.
  • The installation fails with the following error message – “Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes”

Cause: I discovered that this is a known issue in SQL Server 2008 / R2. If you proceed with the default folder hierarchy after selecting your Mount Points, the default location points to the root of the mounted volumes. If your SQL Server service does not have sufficient permissions to write to the root of the mounted volumes, installation will fail.

Workarounds: You can use one of the following two workarounds to fix this problem -

  • Workaround # 1: Change the default folder path to use a sub directory under the root drive. For example, if T:\mountX is a mount points of volume X. Then specify T:\mountX\data as the directory to store database files.
  • Workaround # 2: Grant SQL server service account full control permissions to the root volume, such as mountX by using the disk management console

References:

Mount Point Dependency Issues:

Scenario: After you have successfully installed a SQL Server 2008/R2 failover cluster with mount points, data corruption could occur because of incorrect resource dependencies in the SQL server instance group

Cause: This is a known issue in SQL Server 2008/R2. SQL Server setup will not create the required dependencies automatically and you will need to add the required dependencies manually.

Solution: Add the following dependencies manually -

  • Make sure that each of the mounted volume is dependent on the root (or host) drive.
  • Make sure that SQL Server is dependent on not just the root (or host) drive, but also on each of the mounted volumes.

References:

Best Practices when using Mount Points in SQL Server -

Follow the following best practices when using mount points with SQL Server standalone and failover cluster instances -

  • Use the root (host) volume exclusively for mount points. The root volume is the volume that is hosting the mount points. This greatly reduces the time that it takes to restore access to the mounted volumes if you have to run a chkdsk. This also reduces the time that it takes to restore from backup on the host volume.
  • If you use the root (host) volume exclusively for mount points, the size of the host volume only has to be several MB. This reduces the probability that the root volume will be used for anything other than the mount points.
  • During failover cluster installation, use subdirectories under the root of mounted volumes to store database and backup files. For example, say you have a mounted volume F:\SQL1. This is the root of the mount point and you shouldn't use this location directly to store your database files. You should instead create a subdirectory/subfolder such as d:\SQL1\USERDATA and use this location to store your database files.
  • Add missing dependencies after installing SQL Server 2008 / R2 failover cluster with mount points. Make sure that each of the mounted volume is dependent on the root (or host) drive. Additionally, make sure that SQL Server is dependent on not just the root (or host) drive, but also on each of the mounted volumes.
  • If configuring MSDTC on failover cluster, do not use mount points as storage for the MSDTC service. MSDTC currently does not supported mount points.

References:

Hope you find this useful!

Comments

  • Anonymous
    January 19, 2012
    In dependencies if you make the mount points dependent of the root, do you still need the sql server to be dependent on the mount root?

  • Anonymous
    January 26, 2012
    Yes Gary, its a best practice to to have both SQL Server and mounted drived dependant on the root (or host) drive

  • Anonymous
    August 09, 2012
    Very informative! Thanks for this blog post, Pankaj!

  • Anonymous
    February 02, 2016
    Does Filestream supports MountPoints in clustered SQL Server 2008 R2?