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.
Very helpful post. I am trying to install MSSQL 2012 Enterprise with SP2 over windows 2012 R2 but i am not able to proceed further than point 19. I have went through your ppt as well. Could you please share if you have written for MSSQL 2012 Ent. x64 bit Clustering over Win2k12 R2
Please see my response to Tariq's post above. You are running into the same issue.
Is this setup similar to Oracle RAC? Is SQL Server on both/all nodes available to take client connections - meaning load balancing?
CSV is providing you a clustered file system i.e. storage infrastructure for SQL. Of course it could facilitate an Oracle RAC like setup. Independent of CSV, you can load balance read (not write) workloads across all AG replicas (primary and secondaries).
Can I use this same CSV on my 2 node cluster and Put shares on it, that work to serve Pics and Documents from the SQL database to users?
Hi Subhasish, are you aware of the issue where via SSMS you cannot see the CSV when you try to restore/backup as per here?
Does your CSV install method prevent this, or is it a bug as stated?
@Dan, This is technically possible and should not impact your SQL workload perf. That said you'd want to monitor the relevant perf counters to ensure that this indeed has is no perf impact.
@Ben, I was not aware of this issue. Thanks for bringing it to my attention. I'm following up with the SQL product team to see if they have any updates on a fix. If I get more information I'll post an update here...
Thanks for a great article. Would I be still able to use local disks for my temdb on CSV volume With SQL 2014?
Yes, you still can. In this configuration all you're doing is replacing your traditional clustered disks with CSVs.
Thanks for the great article!
I am running an SQL 2012 2-node cluster on Server 2012 and would like to use a CSV configuration. Would it work if I did an in-place upgrade of each node and then added the CSV to the cluster? Or would I need to start again from scratch as per this post?
Thanks for your time.
Thanks for your note. In place upgrades to SQL2014 would definitely allow you to migrate your USER DBs to CSVs. However, I am trying to find out if we've done the in-house validation of migration of System DBs. I'll post a follow-up comment once/if I've confirmed that.
For now it would be safest to backup your DBs and "start from scratch". I do understand that it would be ideal to avoid the downtime.
Thanks for getting back to me. That would be great if you could find out, it's kind of an all or nothing move for us so we need to be sure it'll go smoothly. If it comes to having to start again I don't think we could afford the downtime.
Many many thanks,