Specifications for SQL Server failover cluster instance #FCI #SQL #Server #winServ #CSV

 

Lately I see a lot of questions about SQL Server failover cluster instance FCI and I have only a two node cluster can I use the multiple instances ?

Well sure you can you don’t need a 64 node cluster to run 50 instances.image

With just two nodes running 4 or more instances!

But there is a Limit on what you can use it sounds odd but think about the drive letters.. yeh somebody thought he I need only 26  letters. and yes do we still need the drive letter this is so 2000 eh no we don’t need it. with SQL 2014 you can use CSV

Cluster Shared Volumes (CSVs) is a feature of Windows Server Failover Cluster. A CSV is a shared disk that is made accessible to all nodes in a Windows Server Failover Cluster. CSVs build a common global namespace across the cluster (accessible under the %SystemDrive%ClusterStorage root directory). This allows creating as many shared volumes as desired in the shared storage, removing the 24 drive letter limitation. For SQL Server failover cluster instance FCI this means that you can create as many SQL Server failover cluster instance FCI  as desired, without having to manage mount points.

Now with all the new stuff in the line Windows Server 2012 R2 and SQL server 2014 this is a great combo

Placing the SQL Database files on shares or CSV volumes removes the drive letters and increases the flexibility and scalability which can result in more uptime.

So the NEW SQL admin need to know about Windows clustering also and what is new!

image  image

So the limit is here In the SQL 2012 version these are gone in SQL server 2014 IF you use CSV !

50 instances on a stand-alone server.

25 instances on a failover cluster when using a shared cluster disk as the stored option for you cluster installation SQL Server supports 50 instances on a failover cluster if you choose SMB file shares as the storage option for your cluster installation For more information, see Storage Types for Data Files.

Instances are not the same as availability Groups !

Support for Cluster Shared Volumes

Cluster Shared Volumes (CSVs) is a feature of Windows Server Failover Cluster. A CSV is a shared disk that is made accessible to all nodes in a Windows Server Failover Cluster. CSVs build a common global namespace across the cluster (accessible under the %SystemDrive%ClusterStorage root directory). This allows creating as many shared volumes as desired in the shared storage, removing the 24 drive letter limitation. For FCI this means that you can create as many FCIs as desired, without having to manage mount points.

CSVs also increase the resiliency of the cluster by having I/O fault detection and recovery over alternate communication paths between the nodes in the cluster. This is built on top of the Server Message Block (SMB) protocol. For FCI this means that, if the active server is not able to read/write directly to the shared storage, the CSV will automatically re-route the request through another node that has access. In addition, if the shared storage has any transient issues, CSV will transparently cache and re-try the operation.

Finally, CSVs increase the reliability of failovers, as disks don’t need to be unmounted and mounted as with traditional cluster disks. On failover, the new primary SQL Server instance can simply access the CSV.

CSVs are supported on Windows Server 2012 and Windows Server 2012 R2.

Posted September 6, 2013 by Robert Smit in SQL Server 2014

Tagged with

  • Tag