How to configure SQL server Fail over clustering Instance (FCI).

This blog is part of a SQL HA-DR solution series , In my previous blogs I mentioned how Log Shipping, Mirroring & AlwaysOn Availability Group can be configured, now here you will get step by step procedure for SQL Fail Over Cluster Instance (FCI) high Availability solution. SQL FCI is sometime also known as AlwaysOn FCI and it’s bit different then AlwaysOn AG (Availability group). Always ON FCI need shared storage that is accessible from all the participant node and it provide instance level high availability.  If your primary (or active) server is down then secondary (passive) take responsibilities for all SQL operation.

The details about SQL FCI can be found here.

We are covering end to end configuration and divided entire information in 3 different section. If you have already setup one section already then move to next section.

  1. Setting up SCSI (shared disk) for temp, data & logs files.
  2. Using Iscsi initiator access shared virtual disk.
  3. Preparing windows Fail-over cluster.
  4. Install SQL 2016 on Fail-over cluster.

Following steps are tested on Windows 2012 R2 with SQL 2016. To reduce the time, we have included powershell script to achieve end goal.

Section 1 Setting up SCSI (shared disk) for quorum, data & logs files.

In most of the environment, SCSI Lun are supplied by the storage team and shared storage directly accessible using cluster node. If you are testing failover clustering in LAB/TEST environment then use following power shell commands on a separate windows server that is acting as storage server (Iscsi target).

#Step1 Install "iSCSI Target Storage provider" windows feature.  
Add-WindowsFeature FS-iSCSITarget-Server

#Step2 Create 3 virtual disk (VHDX) file.
New-IscsiVirtualDisk C:\HD\Temp.vhdx –size 5GB 
New-IscsiVirtualDisk C:\HD\SQLDATA.vhdx –size 10GB
New-IscsiVirtualDisk C:\HD\SQLLOG.vhdx –size 10GB

#Step3 Create iSCSI Target, cluster node will be initiator, my case the FQDN of node1 & node2is used.
New-IscsiServerTarget SQLNodes -InitiatorIds @("dnsname:node1.domain.local","dnsname:node2.domain.local")
 
#Step4 Assigns shared virtual disk to an iSCSI target.
Add-IscsiVirtualDiskTargetMapping -TargetName sqlnodes c:\hd\sqllog.vhdx
Add-IscsiVirtualDiskTargetMapping -TargetName sqlnodes c:\hd\sqldata.vhdx
Add-IscsiVirtualDiskTargetMapping -TargetName sqlnodes c:\hd\temp.vhdx

Section 2 Using Iscsi intiator access shared virtual disk.

Microsoft iSCSI Initiator Service (MSiSCSI) is responsible for accessing iSCSI devices. Use following power shell script to access the shared disk created in section 1 from cluster node (here Node1).

#Step 1 Setup startup type of "Microsoft iSCSI Initiator Service" to automatic then start service.  
Set-Service -Name msiscsi -StartupType Automatic
Start-Service msiscsi

#Step 2 List available firewall rules related to the MSiSCSI Service, it should be false
Get-NetFirewallServiceFilter -Service msiscsi | Get-NetFirewallRule |Select DisplayGroup,DisplayName,Enabled
 
#Step 3 Configures an iSCSI target portal, my shared SCSI disk are created on Storage Server(my case it's DC)
New-IscsiTargetPortal -TargetPortalAddress DC

#Step 4 Verify an iSCSI target object for each iSCSI target that is registered with the iSCSI initiator then contact to it. 
Get-IscsiTarget|Connect-IscsiTarget

#Step 5 Now disk will be connected & you can get details of these shared disk. The operation status will be offline. 
Get-iSCSISession |Get-Disk 

#Step 6 Initialize all shared disk, disk number would be available in last PS command. 
Initialize-Disk 1,2,3

#Step 6 Create partition & use maximum available space (you can use less size if wish to be). The popup may come if disk is not formatted, It's recommend to format the disk first time before it can be used. 
New-Partition DiskNumber 1 -UseMaximumSize -DriveLetter D
New-Partition DiskNumber 2 -UseMaximumSize -DriveLetter F
New-Partition DiskNumber 3 -UseMaximumSize -DriveLetter G 

Repeat steps from 1 to 4 to each node (node 2 in my configuration) participating in SQL FCI.

Section 3 Preparing windows failover cluster.

Use following power shell script to install windows failover cluster then create SQL cluster then add shared disk into the fail over cluster.

#Step 1 Open Power shell on one of domain joined machine (with Domain Admin credential) and Install ‘Fail-over clustering’ windows feature on all node in single run.
#You can run PS ('Install-WindowsFeature') individually if needs to be
$servers = "Node1","Node2"
foreach ($server in $servers)
{
Install-WindowsFeature -Computername $server –Name File-Services, Failover-Clustering IncludeManagementTools
}

#Step2 Restart all nodes once above feature is installed. you can use following PS (error may come even though successful execution)
$servers = "Node1","Node2"
foreach ($server in $servers)
{
Restart-computer $servers -Force
}

#Step3 Create Windows cluster, add all shared shared disk then verify cluster configuration. 
New-Cluster -Name SQL2016FCI -Node Node1, Node2 -StaticAddress 192.168.2.151 
Get-ClusterAvailableDisk | Add-ClusterDisk
Test-Cluster

Section 4 Install SQL 2016 on Failover cluster.

Please note, we will not show all installation screen as it’s same as normal standalone setup however we will show most crucial & unique for clustered SQL setup.

On node1 (that will be active by default), run SQL setup and select option “New SQL server Fail over cluster installation”.

Install1.jpg

Give appropriate name for virtual SQL server, my case the name is ‘LABSQL2016’ and using default instance.

Install3.jpg

Specify SQL server resource group name by default it will ‘SQL server (mssqlserver)’Install4.jpg

Select all shared disk & write appropriate message for these disk.

Install5

Give IP address and network name of SQL virtual server.

Install6.jpg

It’s recommended to use domain user a/c for SQL server service when it’s hosted on FCI.

Install7.jpg

Select SQL authentication, add required user. I have added Administrator & Service a/c.Install8.jpg

Select Data, log, temp directory. I have used 3 different virtual disk for 3 different types of SQL files.

install9.jpg

A successful installation will finish here.

install11.jpg

On Node 2, run setup and select option Install13.jpg

Confirm SQL server instance & cluster network name.install14

Follow remaining steps those are same as discussed in above steps. Once setup complete, open ‘Fail over cluster management’ then verify cluster settings.

install16.jpg

 

 

Advertisements