Preflight Checklist – Installing SQL 2016 Failover Cluster

How to Connect

  1. VPN: [SSL1.upcBroadband.com] (login using UIM account)
  2. [NL CSA P JUMP 001] (login using CSA account)
  3. 172.23.103.219 / 38 (login using CSA ADMIN account)

Nodes

  • Node1 Full Name (rdc ip): LG-W-P-DBS00007.CSA.INTERNAL (172.23.103.219)
  • Node2 Full Name (rdc ip): LG-W-P-DBS00008.CSA.INTERNAL (172.23.103.38)

IP’s

  • Cluster Management (node1 / node2): 172.23.103.219 / 172.23.103.38
  • SQL Server Instance: ??
  • DTC: ??

Drive Allocation

  • D. Media
  • E. Sys & TempDB
  • F. User ID
  • G. Logs
  • H. Backups etc

Collation

  • [SQL Latin1 General CP1 AS]

Windows Checks

  • Power saving NOT enabled
  • Instant File Initialization Enabled (secpol.msc, Local ~, User ~, Perform ~
  • Service Account

 

Configuring SQL 2014 Clustering

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)

Log Space

I noticed a jump in logfile size the other day and was wondering how to predict a autogrowth event.

I know old data is truncated after a log-backup but that’s internal and not normally visable.

I came up with this to run across production …

--LogSpace.sql
-- To help find near-full logfiles that may autogrow soon.

-- create table to hold raw data
CREATE TABLE #temp (DBName varchar(100), SizeMB int, UsedPct float, [STATUS] bit)

-- populate table
INSERT #temp EXEC('DBCC SQLPERF(logspace)')

-- output
SELECT DBName, SizeMB, UsedPct FROM #temp --WHERE UsedPct > 90 -- 90% full

-- clean-up
DROP TABLE #temp