SQL Server Cluster Shared Volumes (CSV) Frequently Asked Questions and Answers (FAQ) in Interviews

You can find below, some of the Frequently Asked Interview Question and Answers on Cluster Shared Volumes (CSV) in relation with SQL Server.

What is Cluster Shared Volumes (CSV)?
Cluster Shared Volumes (CSV) provides multiple cluster instances in a failover cluster environment to have simultaneous read-write access to the same LUN (disk) that is provisioned as an NTFS volume.

Example: Two SQL Server instances installed on a failover cluster can use same disk D:\ to store its database files. You can failover one SQL instance from one node to another node and does not require the disk D:\ to be failed over to new node, rather the SQL instance will access the disk D:\ from other node, resulting in two SQL instances, each one running on different nodes can use disk D:\ for thier own database files. Refer Cluster Shared Volumes (CSV) for more information.

When was Cluster Shared Volumes (CSV) introduced?
Cluster Shared Volumes (CSV) was introduced with Windows Server 2008 R2 version, but has been completely re-architected in Windows Server 2012 version.

From Which version of SQL Server was Support to Cluster Shared Volumes (CSV) introduced?
Starting with SQL Server 2014, support for deployment of a SQL Server Failover Cluster Instance (FCI) with Cluster Shared Volumes (CSV) was introduced.

What are the advantages of Deploying SQL Server 2014 with Cluster Shared Volumes (CSV)?
Below are some of the advantages of deploying SQL Server with Cluster Shared Volumes (CSV)

Scalability – Allows multiple SQL Server instances to use or share the same LUN/Disk, which otherwise wound need separate disks for each SQL Server instance with traditional disks.

Availability – When connectivity between a cluster node and LUN/DISK fails, the connectivity will be established where Cluster Shared Volumes (CSV) routes the traffic to over the network to the LUN/DISK, thus allowing the SQL Server instance to allow connections without any failures.

Manageability – Cluster Shared Volumes (CSV) simplifies the management of multiple SQL Server instances on cluster nodes.

Performance – Cluster Shared Volumes (CSV) provide a read-only cache for unbuffered I/O to SQL databases.

Security – Cluster Shared Volumes (CSV) allows integration with BitLocker which allows to secure the deployments outside of the data-centers, such as at branch offices. Volume level encryption allows in meeting the security compliance requirements.

How does SQL Server installation procedure differs with using Cluster Shared Volumes (CSV)?
First, we need to setup Cluster Shared Volumes (CSV) instead of traditional cluster disks, this is to be done by System Administrator. Most of the SQL Server cluster instance installation steps are same even while we use Cluster Shared Volumes (CSV), only place where we change settings during the installation process is in Database Engine Configuration – Data Directories tab where we provide the Cluster Shared Volumes paths like C:\ClusterStortage\SQLServerInstance\ for data root directory and for user database data and log files.

How do I see the physical database files which are on Cluster Shared Volumes (CSV)?
We can browse to the Cluster Shared Volumes (CSV) just like we browse any other folder through windows file explorer. We need to check the path C:\ClusterStorage directory and under that the folder which we specified during the installation for the respective database files, which will have our database files.

Does Disk resource move to another node during the SQL Server instance failover process?
No, disk resource is nor part of the SQL Server group where we have our SQL instances, so disk resource will not failover when we failover a SQL Server instance.

What all cluster resources move during failover of SQL Server instance?
Below are the cluster resources which move to new node when we perform a failover of SQL Server instance from one node to another node.

SQL Server Network Name
SQL Server IP Addresses
SQL Server
SQL Server Agent
Any other resources in the group file fileshare or third party backup resources, etc.

What is the state of Cluster Shared Volumes (CSV) during the failover of SQL Server instance?
Cluster Shared Volumes (CSV) remains ONLINE and is not affected during the time when SQL Server instance is failed over from one node to another node. There is no requirement to failover the disk resources to the node where SQL Instances are running.

Receiving error during the installation of SQL Server while using the Cluster Shared Volumes (CSV)?
There are some people who received various errors while they try to specify the data directories path to Cluster Shared Volumes (CSV) and may receive errors like ” The volume that contains the SQL server Data directory does not belong to Cluster Group”.

These errors are mostly related and occurs when you try to use the Cluster Shared Volumes (CSV) with SQL Server 2012 or lower versions. Support to Cluster Shared Volumes (CSV) for SQL Server was only introduced with SQL Server 2014.

Is Cluster Shared Volumes (CSV) similar to Oracle RAC and does SQL Server allow all cluster nodes to perform Read/Write, thus allows load balancing?
Cluster Shared Volumes (CSV) provides a clustered file system which is basically a storage infrastructure for SQL Server, it is possible to allow RAC like support. Even without a Cluster Shared Volumes (CSV), you can still achieve a load balance where read workloads can be spread on primary and multiple secondary replicas.

Unable to view Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) while trying to perform Backup or Restore?
This ideally should work, where one should be able to view the Cluster Shared Volumes (CSV) from SQL Server Management Studio (SSMS) backup or restore wizards. This has been notified as a bug to the Microsoft Product team and most likely a fix would be released, so make sure that SQL Server 2014 instance is patched with latest updates and test the backup and restores.

 

Unable to perform Backup or Restore to Cluster Shared Volume(CSV) Using SSMS GUI in SQL Server 2014

I had been playing with new features of SQL Server 2014 and I encountered an issue with SQL Server 2014 Cluster Shared Volume(CSV) related to Backup/Restore. Refer, if you are new to Cluster Shared Volume(CSV) whose supported has been introduced starting SQL Server 2014. This is a Windows feature which has been available since Windows Server 2012, but SQL Server only started supporting from SQL Server 2014.

I have setup two node Windows Server 2012 cluster and installed one SQL Server 2014 clustered instance. I have added a disk as Cluster Shared Volume(CSV).

When I try to backup a database, I encountered an issue where I tried to perform backup of a database and normally we see file structure where we can browse to choose the location where to backup the databases, but I see a blank file structure, so nothing there to select any path. If I try to specify the backup path manually to CSV disk, I get an error as below. as mentioned in the error, I have checked permissions to the CSV path, but no issues with the permission. I am able to create databases on CSV and access them, but just could not use the CSV path to perform backups/restores.

C:\ClusterStorage\MSSQL\MSSQL12.InstanceName\Backups

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

After some research, I found out couple of workarounds to perform backups or restores.

Alternative 1 – Use T-SQL for performing backup from SSMS Query Analyzer

Alternative 2 – Follow below mentioned steps

– Add a regular cluster disk(Non Clustered Shared Volume(CSV)) to SQL Server Group in the cluster from Cluster Administrator and then add the new disk resource as dependency to SQL Server Group.

– From SSMS, right click on the SQL Instance properties, go to database settings tab, copy the existing path and save it for future reference and then change the path to the new regular disk which we just added, this is just like how we used to be in SQL Server 2012 or previous versions.

– Now try to perform the backup and you will be able to open the locate backup file and locate database files dialog with out any errors. We can also specify the backup path manually to CSV, but the path still will not be visible from the GUI to browse.

– Later, the new regular storage which we added before could be removed and then change back the default backup location to CSV path which we saved before changing to to new disk in Instance properties -> Database Settings tab.

– You should not encounter any more issues and now you can also see CSV path from GUI to choose path to backup/restore.

There is a Connect bug opened for this issue where we cannot see CSV path using SSMS GUI while trying to perform backup/resstore. You may check on the connect page to see any updates on fixing this issue in coming Service Pack/Cumulative Updates.

Hope this was helpful.

Thanks,
SQLServerF1 Team

 

SQL Server 2014 Support for Cluster Shared Volumes (CSV) in a Failover Cluster

Starting SQL Server 2014, Failover Cluster Instances supports Cluster Shared Volumes (CSV) in both Windows Server 2008 R2 and Windows Server 2012.

What is Cluster Shared Volumes (CSV)? How Cluster Shared Volumes (CSV) works?
As per Technet below is the definition of CSV.

To understand how Cluster Shared Volumes (CSV) works in a failover cluster, it is important to understand how a cluster works without CSV. Without CSV, a failover cluster allows a given disk (LUN) to be accessed by only one node at a time. Given this constraint, each Hyper-V virtual machine in the failover cluster requires its own set of LUNs in order to be migrated or fail over independently of other virtual machines. In this type of deployment, the number of LUNs must increase with the addition of each virtual machine, which makes management of LUNs and clustered virtual machines more complex.
In contrast, on a failover cluster that uses CSV, multiple virtual machines that are distributed across multiple cluster nodes can all access their Virtual Hard Disk (VHD) files at the same time, even if the VHD files are on a single disk (LUN) in the storage. The clustered virtual machines can all fail over independently of one another.

The above mentioned description is interms of Windows Operating System, which can be better understood by a System Administrator, so let’s try to put it in terms of SQL Server, as CSV is supported by SQL Server 2014 or higher.

Without CSV, a traditional failover cluster which we already know about, allows a given disk (LUN) to be accessed by only one node at a time, meaning SQL Group will own the disk resource and that disk resource cannot be used by any other groups.

Now, with CSV support in SQL Server 2014, we can have multiple groups which are owning different SQL Server Instances, can all use single LUN. Now, we can failover one instance from one node to another node, without affecting or having to failover the disk resources along.

Let’s take an example for better understanding.

– Let’s say we have two nodes Node1 and Node2.

– We then ask System Admins to create a windows cluster/a> and then ask them to add Disk Resources as Cluster Shared Volumes(CSV).

– When SysAdmins add the CSV, we will see the LUN in a path like C:\ClusterStorage\SQLDBFilesDisk\ , where we can rename to VolumeX to more meaningful name.

– We can access this path from both the nodes.

– We then Install one SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in below image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance1\

Cluster Storage Volume CSV

Cluster Storage Volume CSV


– We then Install another SQL Server 2014 cluster instance, which can failover between two nodes Node1 and Node2. During the SQL Server installation, in that page where we specify the path for “Data Directories” as shown in above image, we need to change the path like C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\Instance2\

– Once the installation completes, we can browse to the database files by going to C:\ClusterStorage\SQLDBFilesDisk\MicrosoftSQLServer\ from any of the nodes. Here we are using the same LUN for both the SQL Server Instances, which was not possible before SQL Server 2014.

– If we open SQL Cluster Manager, and go to SQL Server Group, we will only see SQL Server, SQL Agent, SQL Virtual Network Name and IP Address resoures, but the Disk resources will not be there anymore as there are now independent of any one group.

Hope this clarifies what is Cluster Shared Volumes (CSV) and How it can be used with SQL Server 2014.

References:
Cluster Storage Volume
Deploying SQL Server 2014 with Cluster Shared Volumes

Happy Reading,
SQLServerF1 Team