Early this year we were faced with a certain challenge when we had to build what would become two massive SQL Server Failover Cluster Instances for a wide variety of applications. It was necessary to ensure that for each application, the data and log files were dedicated and separate. This implies a minimum of two drives for each application's database. For almost twenty applications, we were bound to end up with a minimum of thirty drives (the smaller databases would share drives). Our architecture comprised of the following:
- Number of Nodes: 2
- Number of Clustered Instances: 2 (each would primarily run on the respective nodes as primary so as to optimize the resources)
- Number of Disks on Clustered Instance 1: 18 (instance 1 was built to host more application databases than instance 2)
- Number of Disks on Clustered Instance 2: 9
At this point I am sure the challenge has become quite clear. The total number of disk resources was going to be twenty-seven. For each disk we needed one letter of the English Alphabet. C, E and X were already taken as the system drive, CDROM and MSDTC respectively. We eliminated the need for quorum disks by using File Share Quorum else we would also have needed a drive letter for the quorum disk. In effect we had about twenty-four letters of the alphabet left and we needed twenty-seven at the minimum.
Thanks be to God for Social Media. In response to a cheeky post on LinkedIn, an old colleague, Tekena Orugbani, who happens to know a lot about Windows suggested we use Volume Mount Points. We read around the concept a bit and got round to doing so. The simple approach is shown in the following screen shots:
Creating a New Simple Volume
Given a raw disk, we created a volume, shown here, and in the next few steps.
Fig. 1 Raw Disk Presented to Server
Fig. 2 Simple Volume Wizard - Step 1
Fig. 3 Simple Volume Wizard - Step 2
Fig. 4 Simple Volume Wizard - Step 3
At this point we need to select the folder in which we intend to mount the disk using the Browse button and create a new folder with the desired name.
Fig. 5 Simple Volume Wizard - Step 4
Fig. 6 Simple Volume Wizard - Step 5
Fig. 7 Simple Volume Wizard - Step 6
Fig. 8 Simple Volume Wizard - Step 7
In Step 7 (Fig. 8), we assign a volume label that is same as the name of the folder on which we mounted the disk. This makes for easy identification. We shall use this same name when adding the disk to the cluster.
Also note we are using the NTFS files system as an Allocation Unit Size of 64K to perform a format. The choice of NTFS is because we have experienced weird issues while trying to use the new ReFS for SQL Server in the recent past. A 64K Allocation Unit Size is based on Disk Partition Alignment Best Practices for SQL Server, which has been around since SQL Server 2008.
Fig. 9 Simple Volume Wizard - Step 6
This just about brings us to the end of this section but for the following error:
Fig. 10 Error
The error is obviously ambiguous but a little digging showed us we needed to put the disk which owns the drive letter we are mounting our new disk to in Maintenance Mode in the Windows Cluster. Maintenance Mode on Windows Clusters allows another process to gain exclusive access on a Cluster Shared Disk resource. The situation had an additional symptom: When one browsed the drive (P:\) using Windows Explorer one found two weird things about the volume we just mounted:
- It does not have the small caret indicating it is a mounted Volume
- Windows cannot read its size.
Once we turned on Maintenance mode and re-mounted the disk, we were successful. (See. Fig 12 and 13). It is very important to note that turning on Maintenance Mode disrupts service and should be done either during development or during a Change/Maintenance Window.
Fig. 11 Mounted Volume is Flawed
Fig. 12 Turn on Maintenance Mode in Drive P
Fig. 13 Mounted Volume is Fixed
Adding the Volume to the Cluster
Once we had prepared the volume, we had to add it to the cluster and subsequently to the SQL Server role we intended the disk for. It behaved exactly like a disk within the cluster and was accessed via a path synonymous with the folder on which we mounted the volume. When adding a disk to the cluster it is important to identify the disk by its disk number (in this case 38). Take a look at Fig 1 again to confirm this.
Take the following steps to make the new disk useful to the SQL Server instance:
While "Disks" is selected on the left pane of the Failover Cluster Manager, click "Add Disks" on the right pane.
Fig. 14 Adding a Disk to the Cluster - Step 1
Choose the disk, as shown below.
Fig. 15 Adding a Disk to the Cluster - Step 2
We see all our disks here:
Fig. 16 Disk Added to the Cluster
We can add a label in the disk properties.
Fig. 17 Labeling the New Cluster Disk Appropriately
Fig. 18 Adding the Disk to A Role - Step 1
Fig. 19 Adding the Disk to A Role - Step 2
Conclusion
In effect we have greatly simplified out disk organization and been able to provision disks for a large number of applications which will share the same SQL Server Clusters. The limitation of drive letters on Windows can be surmounted for SQL Server using Volume Mount Points.
References
https://technet.microsoft.com/en-us/library/cc938934.aspx
https://blogs.msdn.microsoft.com/mariussutara/2008/09/05/cluster-and-maintenance-mode/
http://support.microsoft.com/kb/903650
http://technet.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx?f=255&MSPPError=-2147217396
