September 2016 – Database Consultant

Practical High Availability (HA) my lists:-

– HA is measured in uptime (EG: five nines), zero data loss, automatic failover
– DR is measured in recovery time, minimal data loss, point-in-time recovery

Cluster – (AKA Windows Cluster, Failover Cluster) is an HA solution
Node – a server, part of a cluster, can support production by its self

Cluster prerequisites
– same architecture (32 bit / 64 bit)
– SQL Server
– same OS version (eg: Windows Server 2012/2016)
– (memory, cpu, patch-level, hardware, configuration)
– at least two nodes
– shared storage (eg’s: san, SCSI)
– common network (with no single point of failure

Cluster setup click-by-click …
– node-1, 2
– server manager
– add feature
– failover clustering
– iSCSI virtual disks
– 1 data, 2 log files, 3 quorum
– rescan storage
– new volume wizard 1, 2, 3
– size, drive letter, label (ie: Data, log, quorum)
– File and Storage Services
– iSCSI initiator
– target vsan ip
– quick connect
– auto configure (1, 2, 3)
– failover cluster manager
– validate configuration
– node1, node2, run all tests
– warning: only one network found (single point of failure) ignore if vm
– create cluster
– just add node-1 (simpler to identify/fix issues, eg: security)
– cluster name
– warning: network single-point-of-failure (ignore if vm)
– actions: add node (node-2) …

Install SQL Server (2014)
– node-1 (individual features)
– advanced, advanced cluster preparation *
– node-2 (individual features
– advanced, advanced cluster preparation **
– node-1 or 2 (common features)
– advanced cluster completion ***

* eg: – Database Engine Services (tic), Management tools – complete (tic)
– instance root directory etc – d-drive
– named instance
** repeat

*** – sql server network name (for dns / ad)
– cluster disk 1,3 (tic) not quorum
– cluster network configuration (type in) ip
– collation, mixed mode authentication, add self, add svc-account
– data directory (eg: g-drive), log directory (eg h-drive)

Test cluster #1
– failover cluster manager
– running / owner node (~1)
– move / pending
– running / owner node (~2)

Test cluster #2
– SSMS (3 queries)
– select @@servername
– select serverproperty(‘ComputerNamePhysicalNetBIOS’)
– select * from sys.dm_os_cluster_nodes — node-1 is up
– hard crash node-1
– repeat 3 queries (node-2 is up)