The new face on SQL Server 2014, the “Clustered Shared Volumes” technology, or simply CSV, is not that new in the Microsoft world. CSV was used to make the Virtual Machine management easier on Windows environments, facilitating the access to VHD files through a shared and accessible to all nodes volume. Supported from Windows 2008 R2, this option is now available for the SQL Server 2014!
Basically, the CSV simplifies the storage management, making a volume accessible for all the nodes of a cluster, simultaneously. Only one node is the CSV’s owner, called “Coordinator Node” (CN). The CN takes advantage of the SMB (Server Message Block) to manage the I/O operations between the cluster nodes and the volume. All the metadata write requests are managed by the CN, however, data I/O are directly passed to the shared storage by each node.
This strategy increases the high availability and reliability of a cluster, as a new path to the data is opened. This path is utilized in case of a failure, which is detected by the I/O failure and recovery system. The cluster will choose the less costly path to perform the I/O operations, using the network path with better response time – This behavior can be manually defined.
The CSV is a NTFS reparse point, such as a mountpoint, and it accessible through the path %SystemDrive%\ClusterStorage, so it is not assigned to a drive letter.
In the following image we can see how a CSV shows up on Windows Explorer.
The access point to the volume will be the same in all cluster nodes, and are always active and accessible to read and write in normal conditions. As the volume is mounted and accessible for all cluster nodes, the failover action of a clustered SQL Server instance will be faster. This is because it’s no longer needed to take the disks offline (at the active node) and bring the disks online (at the future active node.) This way, only the other SQL Server Role resources are going to be online in the active node, and offline in all other nodes.
How do I create a CSV?
There’s no mystery here. Creating a CSV is as simple as the click of a button. Having the shared storage added to the cluster, just right-click to the available storage which you want to convert into a CSV and select the option “Add to Cluster Shared Volumes” and you’re done! It’s that simple, as shown in the following image:
Afterwards, we can notice that the “Assigned To” column will show the text “Clustered Shared Volume”, instead of some specific “Role”. The disk is now available for all the nodes.
On SQL Server, the desired CSV should be specified either during the installation (to make the CSV a default location for the files) or on using the CREATE DATABASE command, pointing to the CSV path.
We can also use the SSMS UI to change the default paths or create a new database, but we need to manually insert the CSV path, as the SSMS UI is not able to identify the CSV as a valid disk — maybe this will change.
You can verifiy this situation in the image bellow:
Another advantage of a CSV is that we are able to create new a CSV and add it to SQL Server without the need to restart the service! Isn’t that nice?
That’s it for today! As this is a new SQL Server feature, we can still have some improvements, so if you know something more to add, please comment! :) I hope you liked the post, if you have any questions, I’m available to help.
Nice post Murilo, thanks. I have a guest SQL 2014 cluster running on Win 2012R2 Hyper cluster. In the parent host I have 3 CSV, each of them is represented by a LUN on the SAN. In CSV1 of the parent cluster, I created one shared VHDX and it’s configured as CSV11 for the guest cluster. I have another VHDX on CSV2 on the parent cluster, and it’s configured as CSV22 in the guest cluster. I’m planning to put the database, temp on CSV11, and log files on CSV22 at the guest. Do you think this is a good design? In my old cluster, we have separate lun for data, temp, MSDTc,log for each SQL instance. I don’t like to create many CSV on the parent cluster and use each of them to carry CSV of the guest cluster, i.e. one csv for log, data, msdtc, temp,… Your help will be highly apreciated. Thanks,