Configure SQL server fail-over cluster and Always On in multi-subnet environment-Part 4

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Lets Begin: We are going to start a series of blogs on configuring SQL server fail-over cluster and AG in multi-subnet environment.

This is the 4th part of the series where we will take you through setting up SQL FCI Installation and configuration.

Also note, we want SQL fail-over to be happened on California i.e. in same DC or same subnet only. We do not want here that SQL cluster fail-over to NewYork DC that means SQL sever failover cluster setup would be between Nodes of Primary DC(California in same subnet only).

NewYork DC (Different subnet) will be dedicated for AG only,like AG should be owned on California or NewYork but SQL cluster should always been on California Primary DC.

Click Here to get part-3 of this series.

Windows server 2012 R2 and SQL sever 2014 are taken as an example in this series.

IMP Note:- Multi-subnet windows cluster has been supported from Windows server 2008 Onward but for SQL, multi-subnet fail-over cluster started from version:- SQL server 2012 onward.
We have considered the setup for multi-subnet as per below example.
California–> In same subnet–>Same(1st) Datacenter–>SQL fail-over cluster will be setup between them in same subnet.
New York–> In Different subnet–>Different DC(2nd)–> AG will be setup between 1st and 2nd DC i.e. multi-subnet network.

In this part,we will talk about steps involved setting up SQL fail-over cluster.

Pre-Installation Steps for SQL Server fail-over cluster

Steps for Creation of Services in Failover Cluster Manager

Start–>Control Panel –>Administrative Tools–>Failover Cluster Manager–>Services & Applications–>Right Click and select more Actions–>Create Empty Service Application
Once the Empty Service Application is created we can rename the same as SQLSERVER
Right Click on SQL Server and select Add Storage.
Add the Disk Drives Required for SQL Installation, Data, Log, Backup & Temp DB allocation.
Restart the Server to check the failover for the drives.
Now we can start with the SQL Cluster Installation on Current Owner of the SQL Server Application.

SQL Installation on First Node i.e. Primary DC-California

Run the setup.exe from the SQL Server Install-able and select Installation–>New SQL Server Failover Cluster Installation.

SQL Server Setup Support Rules Check

Select Installation option from SQL Server setup menu. Click on “New SQL Server Failover Cluster Installation”. It will check the pre-requisite before SQL Cluster installation like Operating System and edition, registry, required services etc.

The first Step in Installation will be rules check for setup Support Rule.

After passing the entire test click on OK.The detailed report of above System Configuration Checker will be displayed.

SQL SERVER Product Key

Now enter the necessary product key provided by Microsoft as per the license and click next to continue

Check the “I accept the license term” and Click on Next

After clicking next, we have to select required feature for SQL Server 2014 Failover cluster. Also we need to provide the SQL Server installation shared files path.

Now we have to provide Network name(this is going to be your SQL fail-over cluster client access name) for default instance and path for Instance Root Directory (SQL Installation files). Click on next to continue.

It also checks the disk space requirement for installation. Click next after checking the necessary disk space is available.

SQL Resource in Cluster Manager

We have to specify the Resource Group for SQL Server default instance which we created earlier prior to SQL Cluster installation.

After specifying the resource name, we have to provide the shared storage details to default instance. I.e. Disk Drives used for data, log, Tempdb, installation etc…

After providing the storage details, we have to provide the virtual IP and subnet mask for SQL Server Network resources. Make sure that DHCP option is not selected before proceeding further,

Uncheck DHCP Option if we are adding IP manually.

Refer Part-1 for IP details, we will need to provide Virtual IP for California (Primary DC-same subnet) here and same IP need to provide on second node while adding SQL failover cluster from Node of Primary DC california.

Note:- We are not going to provide any virtual IP from NewYork(different subnet) coz we do not want to add NewYork node into SQL fail-over cluster. NewYork Node will be for AG-DR.

After that, we have to provide SQL Service Account details for SQL Server Services.
Then, we have to choose authentication mode (Window or Mixed Mode) details. We can add the currently logged-in user for SQL Server Administration
In Database Engine Configuration we have to provide the path for System databases user and log file, user databases user and log file and TEMPDB data file and log files.
Skip the process for Analysis Services Configuration and Reporting Services since we are not installing those components. Click next after the Cluster Installation Rules is complete
Now, click on Install to begin the SQL Cluster installation

The installation on first node is completed…

Now we have to add second node from Primary DC to failover cluster

For this we have to select “Add node to SQL server Failover Cluster” option from Installation menu.

Note: The installation should be run from the Second Node of the Cluster

Once we click on Add node the installation process will begin for SQL Server Installation.

SQL Server will prompt for Product key, click next to continue.
Sql Server will prompt the configuration for SQL Server on First Node, verify and click next to continue.

Click Next and Enter same Virtual IP address used for Node 1 i.e. Primary DC virtual IP for SQL fail-over cluster.

Click Next and Install it.

Once we start the installation for SQL Server on cluster on Second Node the installation may take around 30 mins to complete depend upon system resources.

Note: Please do not stop the installation or restart the server in middle of the SQL Server Cluster installation.