SQL Server Clustering (FCI) setup using Storage Replica

I have been playing with SQL Server Clustering (FCI) setup with a Widnows Server 2016 Preview 4 and Windows Storage Replica.

This involves local storage and Cluster Shared Volume with Replication Enabled.

http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.15

This is incomplete – if Node 1 goes offline the SQL Server role fails!

 

Advertisement

Tools used when building Windows cluster with iscsi

Tools used when building Windows cluster with iscsi:

  • Control Panel -> Network and Internet -> View network status and tasks including Change adapter settings
  • Control Panel -> System -> Device Manager including View -> show hidden devices
  • Disk Management
  • Server Manager -> Manage -> Add roles and features
  • Server Manager -> Tools -> Failover Cluster Manager
  • Server Manager -> Tools -> iSCSI Initiator
  • Server Manager->Tools->Routing and Remote Access
  • Server Manager -> File and Storage Services on left panel -> iSCSI
  • Server Manager->Tools-> Group Policy Management
  • Server Manager->Tools-> Active Directory Users and Computers
  • diskpart
  • sysprep

Tip and undo hints when things go wrong!:

  • Sysprep everything, see below, technically I read only DC needs sysprep but I was safe.
  • diskpart – to remove GPT protected or recovery partitions – list disk – select disk N – list partitions – select partition N- delete partition override
  • Server Manager->Tools-> Active Directory Users and Computers -> expand domain -> expand computers -> delete computer to allow retry – not only done on my play setup!
  • Backup all VMs BEFORE installing Failover Clustering features

Sysprep myth

After a lot of reading I found Mark Russinovich’s answer.

https://blogs.technet.microsoft.com/markrussinovich/2009/11/03/the-machine-sid-duplication-myth-and-why-sysprep-matters/

So sysprep is only needed when 1 machine will reference another machine’s SID :

  • The first domain controller in a domainhas a machine SID and this becomes the “Domain SID”.
  • All DCs have the their machine SID matching the “Domain SID”.Machines
  • All machines which are not a Domain Controller MUST not have a machine SID matching the “Domain SID”.

I sysprep’ed everything in my cluster builds so I have not tested this!


SQL Server and Windows Cluster Shared Volumes

Introduction

Windows Cluster Shared Volumes are a disk volume which acts as a clustered filesystem i.e. read-write on all nodes.

In fact, in Disk Management on the cold side of the cluster,the Volume is tagged with ‘(CSVFS)’

SMB 3.0 is used behind the scenes

How do I setup a Cluster Shared Volume?

See http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.14

What if a cluster node goes down?

No storage failover is needed when a node goes down

  • sql server has no cluster dependency on the disks anymore!

There is still a co-ordinator node for writes.

What if all storage paths to a node go down?

If this is the co-ordinator node, another co-ordinator node is chosen and i/o rediection occurs. See http://www.smooth1.co.uk/mssql/mssql_csv_iorediection.html

How do Cluster Shared Volumes work internally?

http://blogs.msdn.com/b/clustering/archive/2013/12/02/10473247.aspx

Cluster Shared Volume Diagnostics

http://blogs.msdn.com/b/clustering/archive/2014/03/13/10507826.aspx

Cluster Shared Volume Performance Counters

http://blogs.msdn.com/b/clustering/archive/2014/06/05/10531462.aspx

Cluster Shared Volume Failure Handling

http://blogs.msdn.com/b/clustering/archive/2014/10/27/10567706.aspx


SQL Server Clustering (FCI) setup using Cluster Shared Volumes with multipathed iSCSI disks

Just finished an article on SQL Server Clustering (FCI) setup using

  • Windows Server 2016 Preview 4 cluster nodes
  • SQL Server 2016 CTP 3.2
  • Windows Server 2016 Preview 4 iSCSI storage using iSCSI targets as disks
  • Cluster Shared Volumes on top of the iSCSI disks
  • Multi-subnet setup
  • Domain Controller Setup
  • SSMS December 2015 release to test connections

http://smooth1.co.uk/installs/dbinstalls.html#3.1.14


SQL Server Clustering (FCI) setup using multipathed iSCSI disks

Just finished an article on SQL Server Clustering (FCI) setup using

  • Windows Server 2016 Preview 4 cluster nodes
  • SQL Server 2016 CTP 3.2
  • Windows Server 2016 Preview 4 iSCSI storage using iSCSI targets as disks
  • Multi-subnet setup
  • Domain Controller Setup
  • SSMS December 2015 release to test connections

http://smooth1.co.uk/installs/dbinstalls.html#3.1.13

NOTE: Only DB Engine and Analysis Services are supported as clustered.