Adding Storage to a SQL Server Always-On Cluster.
This is specific to adding storage, specifically one new drive (or mount point), for one database, to a 3-node Cluster, hosting 2 SQL Server Fail-over instances, using Always-on to a 3rd instance on the 3rd node. ( Image 1)
AccountMaster needs additional storage.
Currently two databases share the same drive (mount point) and we need additional space as well as isolate the i/o from the two databases.
The databases have an always-on node of EFReporting1.
The goal is to add storage to AccountMaster and EFReporting1, and move one of the databases on to the new storage.
In order to accomplish this storage must be zoned from SAN to cluster used by AccountMaster, physical machines LNPEFCSQL03 and LNPEFCSQL05. Storage must also be soned to the Always-on node, which uses the storage as a stand-alone instance. So a shared cluster LUN must be zoned to two servers, and a LUN of the same size zoned to LNPEFCSQL05. This is accomplished by the SAN Administrator.
Once completed, verify that the new storage is “visible”, and that it is not the same LUN. I do this by checking the LUN Number via power path, an EMC Utility started from the system tray. It is also possible to verify the LUN number via other EMC utilities.
Screen shots below, note, they must be different LUN numbers, you cannot have a shared LUN also used by the stand-alone always on node.
Screen Shot of PowerPath from server LNPEFCSQL03: (Image 2)
Screen Shot of PowerPath from server LNPEFCSQL05 (Image 3)
Notice the Disk Numbers are the same, but have different LUN numbers, so they are not the same devices.
Now you can proceed to importing the disk in disk manager.
On the SQL Server Fail Over Cluster (LNPEFCSQL03), bring the disk online (right click), than initialize the disk (right click again, dialog appears. (Image 4 and 5)
Connect to cluster manager and find “Storage”, right click and select add storage. You should see the new disk. In this picture below there are a bunch of other disks, make sure they are unchecked. (Image 6)
Once the disk is imported into cluster manager, right click on the disk and choose properties. Use this dialog to name the disk to your standards. (Image 7)
Once the disk is named appropriately, move it to the correct resource group so it can be used. (Image 8)
• There have been issues with these 3 node clusters,when only two nodes of the cluster are to receive the new storage. The disk will not come online sometimes, claiming an issue with the node that can’t “see” the storage. I’ve resolved those instances by rebooting that node. It is as if the cluster qurum is actually on that node and it wants to see the storage, by rebooting that node the quorum “moves” to one of the nodes that can “see” the storage. Than all is well.
Once in the correct group, right click on the disk so it can be assigned a drive letter or mountpoint depending on which you are using. If using a mountpoint make sure the root is set as a dependency in the properties. (Image 9)
Now switch to the Always-on node and confifure that storage. Ideally storage for the always-on node matches the storage for the primary node (either mount-point or drive), G = G etc. In this instance it is not possible to make the match because we have two sql instances sharing the same always on instance, and we would run out of drive letters to make them the same. (Keeping the drive letters the same is not a requirement, it just makes it easier to use the wizard to setup and restore the different databases because the drive letters are the same.) In this case they are not the same.
Bring the new drive on the Always-on node (05) online: (Image 10 and 11)
Once online, initialize it:
Once initialized, format it to your standards and assign it a drive letter or in this case a mount point, ensure the directory you are using for the mount point exists prior to doing this: (Image 12)
Now assign it a mount point: (Image 13)
Now that both servers have the correct storage, the database work can begin.
The goal of adding storage was to move an existing MDF for database that was sharing storage with another databases MDF file. In order to do this an outage will occur, it could be possible to do this in such a way that minimizes or does not cause an outage, but this is the simplest quickest way.
Both existing databases are participating in always-on availability groups. User Access Controls (UAC) are enabled on most servers, so ensure to always start Visual Studio with the “Run as Administrator”, it has made a difference before when “attaching / detaching and restoring databases for me”. (Image 14)
The steps are:
1. Stop (disable) any transaction log jobs on both servers.
2. Remove the database from the Availability Group. (Image 15)
3. Now you can detach the database and move the mdf file to the new drive.
4. Now attach the mdf file back
5. Ensure to go to the “Always-On” Node and detach the database
a. I have had issues with this
b. I’ve had to issue a “restore database with recovery”
c. Then detach
6. Move the mdf file on the “Always-On” node to the new drive
7. Now on the primary node you can add the database back to the availability group
a. I’ve had issues where this did not work, and I had to take a “fresh” backup to restore to the always-on node and even an instance where I then had to take a tlog backup, so the general issues of setting up always on begin to apply here.