Failover Clustering and Network Load Balancing Team Blog
An exciting new feature in SQL Server 2014 is the support for the deployment of a Failover Cluster Instance (FCI) with Cluster Shared Volumes (CSV). In this blog, I am going to discuss the value of deploying SQL Server with CSV as well as how you can deploy SQL with CSV. I will also be discussing this topic at TechEd North America 2014 at the following session:
Update post TechEd: You can now find this session online to watch on-demand here.
Value of Deploying SQL 2014 with CSV
A SQL 2014 deployment with Cluster Shared Volumes provides several advantages over a deployment on “traditional” cluster storage.
Consolidation of multiple SQL instances: With traditional cluster storage, each SQL instance requires a separate LUN to be carved out. This because the LUN would need to failover with the SQL instance. CSV allows nodes in the cluster to have shared access to storage. This facilitates the consolidation of SQL instances by storing multiple SQL instances on a single CSV.
Better capacity planning, storage utilization: Consolidating multiple SQL instances on a single LUN makes the storage utilization more efficient.
Addresses drive letter limitation: Traditionally, the number of SQL instances that can be deployed on a cluster is limited to the number of drive letters (24 excluding the system drive and a drive for a peripheral device). There is no limit to the number of mount points for a CSV. Therefore, scalability of your SQL deployment is enhanced.
Resilience from storage failures: When storage connectivity on a node is disrupted, CSV routes traffic over the network using SMB 3.0 allowing the SQL instance to remain operational. In a traditional deployment, the SQL instance would need to be failed over to a node with connectivity to the storage, resulting in downtime.
Fast failover: Given that nodes in a cluster have shared access to storage, a SQL Server failover no longer required the dismounting and remounting of volumes. Additionally, the SQL Server DB is moved without drive ownership changes.
Zero downtime Chkdsk: CSV integrates with the improvements in Chkdsk in Windows Server 2012 to provide a disk repair without any SQL Server downtime.
With CSV, the management of your SQL Server Instance is simplified. You are able to manage the underlying storage from any node as there is an abstraction to which node owns the disk.
Performance and Security
CSV Block Cache: CSV provides a distributed read-only cache for unbuffered I/O to SQL databases.
BitLocker Encrypted CSV: With the CSV integration with BitLocker you have an option to secure your deployments outside your datacenters such as at branch offices. Volume level encryption allows you to meet compliance requirements.
How to deploy a SQL Server 2014 FCI on CSV
You can deploy a SQL Server 2014 FCI on CSV with the following steps:
Note: The Steps to deploy a SQL Server FCI with CSV is identical with that with traditional storage except for Steps 3, 4 and 19 below. The remaining steps have been provided as a reference. For detailed instructions on the installation steps for a "traditional" FCI deployment refer to: http://technet.microsoft.com/en-us/library/hh231721.aspx
1) Create the cluster which will host the FCI deployment.
2) Run validation on your cluster and ensure that there are no errors.
3) Provision storage for your cluster. Add the storage to the cluster. You may rename the cluster disks corresponding to the storage for your convenience. Add the cluster disks to CSV.
4) Rename your CSV mount points to enhance your manageability
5) Install .NET Framework 3.5
Using Windows PowerShell®
Using Server Manager
6) Begin SQL installation on the first cluster node. Choose the Installation tab and choose the New SQLServer failover cluster installation option.
7) Enter the Product Key
8) Accept the License Terms
9) Choose to use Microsoft Update to check for updates.
10) Failover Cluster rules will be installed. It is essential that this step completes without errors.
11) Choose the SQLServer Feature Installation option.
12) Select the Database Engine Services and Management Tools – Basic features.
13) Provide a Network Name for your SQL instance.
14) Specify a name for the SQL Server cluster resource group of proceed with the default.
15) Proceed with the default Cluster Disk selected. We will adjust this selection in step 19.
16) Choose both the IPv4 and IPv6 networks if available.
17) Configure your SQL Server Agent and Database Engine accounts
18) Specify your SQL Server administrators and choose your authentication mode.
19) Select the Data Directories tab. This allows you to customize the Cluster Shared Volumes paths where you want to store the files corresponding to your SQL Database.
20) Proceed with the final SQL Server installation.
On completion of installation you will now see the FCI data files stored in the CSV volumes specified.
Failover Cluster Manager (type cluadmin.msc on an elevated command prompt to launch) will reflect the SQL server instance deployed.
21) Now add the other cluster nodes to the FCI. In the SQL Server Installation Center, choose the Add node to a SQL Server failover cluster option.
22) Analogous to the installation on node 1. Proceed with the addition of the cluster node to the FCI.
Once your installation is done you can test a failover of your SQL instance through the Failover Cluster Manager. Right Click on the SQL Server role and choose to Move to the Best Possible Node.
Note the difference with CSV. Your CSV will remain online for the duration of the SQL Server failover. There is no need to failover the storage to the node the SQL Server instance is moved to.
Thanks and hope to see you at TechEd!
Subhasish Bhattacharya Program Manager Clustering & High Availability Microsoft
Thanks for the wonderful post.
Well I am trying to achieve CSV with SQL 2012 SP1 on a Win2012 R2 Server but at point#19 SQL is failing to add the CSV.
Here is a small brief of what I did:
1. Windows 2012R2 Hyper-V server with two SQL virtual machines
2. Hyper-V server also has iscsi target software running with two Disks for the SQL cluster servers (Quorum and SQLData)
3. SQL virtual machine installed with Win2012R2 and SQL setup is SQL2012SP1
4. iscsi initiator is running on both SQL servers and both the drives are visible on SQL nodes
5. Drive letter given to Quorum and formatted, no drive letter given to second disk for (SQL) and formatted
6. Followed the procedure for Fail Over cluster and everything is up and running
7. Added the second disk to CSV from Fail Over Cluster console
8. Move group between the nodes working fine, checked the path on both the servers "C:\ClusterStorage\Volume1\"
9. Now the real problem starts while running the SQL setup:
10. Selected the SQL CSV disk as mentioned in point#19
11. Now on the point#19 - (Data Directories), I can see –
I try to click NEXT, it throws error
(The path \\?\Volu... is malformed or not absolute)
12. Then I try to change the location to –
(The volume that contains the SQL server Data directory does not belong to Cluster Group)
Kindly let me know if I have missed anything.
As indicated in this blog, SQL Server for aggregated CSV storage configurations is only supported for SQL Server 2014 and beyond. That explains why you are seeing these errors with SQL Server 2012 SP1.
You can find the full support matrix on slide 29 of this presentation: view.officeapps.live.com/.../view.aspx
The configuration you are attempting is detailed on slide 27. You can however do a disaggregated configuration with a Scaleout File Server and SQL Server 2012 R2. This configuration is detailed on slide 28.
Many Thanks, the scenario is clear now.