Highly Available Failover Cluster For SQL Server 2012 On Microsoft Windows Server 2012 R2

In this lab we will install HIGHLY AVAILABLE SQL Server Failover Cluster on a iSCSI SAN service that we will configure on Windows Server 2012 R2 operating system.

We will use 3 machines for this lab as described below.

• DC01 (192.168.1.20) acting as a Domain Controller for the domain DALARIS.LOCAL, running Microsoft Windows Server 2012 R2 Standard. This server also acts as an iSCSI Target Server that we will be configuring later.
• SQL01 (192.168.1.21) This is the Node1 of the SQL Server cluster.
• SQL02 (192.168.1.22) This is the Node2 of the SQL Server cluster.

Step by step implementation

• Server Settings / Preparation
• Prepare iSCSI Storage
• Connect SQL01 and SQL02 to iSCSI Storage
• Crate the Failover Cluster
• Install SQL on SQL01
• Connect SQL02 to the Failover Cluster
• Test High Availability For SQL Server.

0/ Server Settings / Preparation

TCP/IP Settings for DC01

Server Information

I have changed the host name of the server to DC01. Type hostname to verify that is the case. Additionally this server has been promoted to a Domain Controller with the five FSMO roles on it. Type the following command to verify that this is the case:

netdom query fsmo

TCP/IP Settings for SQL01

The hostname and domain information for SQL01.

TCP/IP settings on SQL02

Hostname and Domain information for SQL02

1/ Prepare iSCSI Storage on DC01

While we could use another server for iSCSI Target services, I used the Domain Controller for this purpose to save on resources. In production environment, this should be on a separate server.

On DC01, install iSCSI Target Server (Under File and Storage Services / File and iSCSI Services).

Choose Role-based or feature-based installation.

Choose the server to be DC01.DALARIS.LOCAL.

Choose role iSCSI Target Server.

Do not select any features.

Click Install.

Once installation of iSCSI Target Server is completed, click Close.

Now create three iSCSI Virtual Disks (C:\iSCSIVirtualDisks\Diskn.vhd) and have the 2 machines 192.168.1.21 and 192.168.1.22 connecting to each virtual disk. Use target1 as target name for all three disks. Disk1 is 5GB, Disk2 is 10GB and Disk3 is 10GB. to do all of that, follow the steps below.

In Server Manager, click File and Storage Services.

Click iSCSI and then click on “To create an iSCSI virtual disk, start the new iSCSI Virtual Disk Wizard”

Leave the Volume as C:\, the iSCSI virtual disks will be saved at C:\iSCSIVirtualDisk\*. Click Next.

Type the disk name as Disk1. Click Next.

Choose Fixed size 5GB and allow it to clear the virtual disk on allocation. Click Next.

Choose New iSCSI target and click Next. This will create a new target.

Enter the name of the target as Target1 and click Next.

We need to add the initiators that are allowed to access the target. Click Add…

Enter the IP address of the first node and click OK.

Click Add… again to add the second node.

Enter the IP address of the second node and click OK.

Click Next.

For the purpose of this lab, I leave CHAP and reverse CHAP disabled.

Click Create. This will create the first virtual disk and store it on the C: drive of the domain controller under C:\iSCSIVirtualDisks\

Click Close when done.

Note that now we have one virtual disk created. This is just a VHDX file called Disk1.VHDX.

We will need to create two more disks. Right-click on an empty area under the first disk created, choose New iSCSI Virtual Disk…

Leave the default volume and location. Click Next.

Name the disk Disk2 and click Next.

Enter 10G and click Next.

Choose existing target and click Next.

Click Create.

Click Close.

Note that now we have two disks created.

Repeat the same steps above to create the third disk called disk3. After creating disk3, we will have totally 3 disks.

Under C:\iSCSIVirtualDisks, there are three VHDX files created with the respective sizes as well.

2/ Connect to iSCSI Storage

On the two machines SQL01 and SQL02, use iSCSI Initiator to connect to the target. I am going to demonstrate this process on SQL01. You will need to repeat the same process on SQL02.

Click Start, type iscsi to search and then click on iSCSI Initiator.

Click Yes to start the Microsoft iSCSI service.

In the Target box, type 192.168.1.20 (this is the IP address of the iSCSI Target server, which is DC01). Click Quick Connect. It should be connected.

When it is connected, note the IQN and click Done.

Note that the status is now Connected. Click OK to dismiss the dialog box.

On SQL01, use Disk Management (diskmgmt.msc) to bring all three disks online.

Right-click on each of the Offline disk and click Online.

Note that the disks are now online but are not initialized.

Right-click on Disk1 and Initialize it (using MBR). Then create a simple volume. Use the name of ClusterDisk1 as a volume name.

Choose all three disks to initialize.

As you can see, now all disks are Online, Initialized but not partitioned yet.

Right-click on each disk and create a volume for it. Name the volumes to be ClusterDisk1, ClusterDisk2, and ClusterDisk3 respectively.

PART 3: Create Failover Cluster

On both SQL01 and SQL02, install the Failover Clustering Feature. In Server Manager, click Add roles and features.

Choose Role-based or Feature-based installation and click Next.

Select the server and click Next.

Do not select any roles and click Next.

Choose Failover Clustering feature to be installed.

Click Add Features.

Click Install.

Click Close when done.

Now, please perform the same steps above to install the Failover Clusteringfeature on SQL02. When done, we will start configuring the failover cluster using the Failover Cluster Manager. Let’s start configuring it on SQL01.

On SQL01, open Failover Clustering Manager.

Click Create a Cluster.

Now enter the servers SQL01 and SQL02 into the selected servers, click Add each time. Click Next.

Choose “No. I do not require support from Microsoft.” Click Next.

Choose a Cluster name. In this case, I used SQLCluster.

Enter IP address for the cluster. I will use a spare IP address of 192.168.1.30 (This is the cluster IP we will use). Note that on the DNS server, there should be an A record pointing sqlcluster.dalaris.local to 192.168.1.30.

Checkmark on “Add all eligible storage to the cluster” and click Next.

Click Finish to create the cluster.

In the Failover Cluster Manager, choose SQLCluster.dalaris.local / Storage / Disks. We see that all three disks are Offline.

Just wait for a few minutes for them to come Online.

Note that all three disks must be online.

Now we need to validate the cluster. Right-click on the Cluster name and choose Validate Cluster.

Click Next.

Click Next, choose run all tests.

Click Next.

Click Next.

Click View Report and Make sure there is no FAILURE. Close the report.

Click Finish.

Now highlight Cluster Disk 3 and make it a Shared Cluster Volume. ClusterDisk3 will be used as Disk Witness Quorum.

Now Cluster Disk 3 is shown as Cluster Shared volume.

PART 4: Install SQL on SQL01

On SQL01, insert the SQL Server 2012 DVD and run setup.exe

Choose New SQL Server failover Cluster Installation.

Show details, OK.

Enter the Product Key. Click Next.

Accept the license terms, click Next.

Checkmark on Include SQL Server updates if you want. Click Next.

Click Show details then click Next again. We will take care of these warnings later.

Choose SQL Server Feature Installation, click Next.

Choose SQL Server Replication, Full-text and semantic Extraction, Data Quality Service, Mangement Tools Basic and Complete then click Next

Click Show Details then click Next.

SQL Server Network Name: make up a name for the SQL Server Failover Cluster, such as SQLCLU. Click Next.

Click Next at the Summary screen.

Click Next.

Choose Cluster Disk 2 to store cluster info.

In IP addres, enter an unused IP for cluster (192.168.1.31) then click Next.

In Active Directory, I have a user called sqlsvc. This user is used for SQL Server Services. Enter the user name (Dalaris\sqlsvc) and password for the services SQL Server Agent and SQL Server Database Engine.

Choose Mixed Mode; also click Add Current User to make the user Administrator to be the SQL Server Administrator. Click Next.

On Error Reporting, click Next.

Click Show Details, click Next.

Click Install.

Wait for installation to finish, Click Close.

Launch Failover Cluster Manager and click Roles. You will see SQL Serverrunning.

Choose Storage, Disks. You will see Disk2 is used for SQL Server.

PART 5: Link SQL02 to the Failover Cluster

On SQL02, insert the Microsoft SQL Server 2012 DVD and run setup.exe.

Click Installation: Choose Add node to a SQL Server failover cluster.

Click OK.

Enter product key and click Next.

Accept the license terms and click Next.

You should include the SQL Server product updates. For the purpose of this lab, I will skip this. Click Next.

Make sure that all tests pass. We will fix the warning later. Click Next.

Choose the SQL Server Instance name as MSSQLServer, name of this node is SQL02. Note that nodes show SQL01 already listed there. Click Next.

Observe the IP Address of cluster: 192.168.1.31. This IP address was made up by us previously. Click Next.

Enter the password for Database Engine and SQL Server Agent (for the account sqlsvc). Click Next.

Click Next again at the Error Reporting screen.

Click Show Details to make sure all tests pass and Click Next.

Click Install.

Click Close when the installation completes.

Disable the Windows Firewall on both servers.

PART 6: Verify High Availability

On either SQL01 or SQL02, open Failover Clustering Manager. Choose Roles, observe that SQL Server MSSQLServer is running. Its owner is currently SQL01.

Choose Nodes, SQL01 and observe in the Roles tab, we see that this node is running as SQL Server (MSSQLServer).

Choose Nodes, SQL02 and observe that this node is blank.

Let’s pretend that we want to manually move MSSQLServer to SQL02.

Click Roles, right-click on MSSQLServer and click Move. Choose Select
Node.

Choose the node SQL02. Click OK.

Wait for the movement… The status will show as pending.

After the move is successful, the owner will be SQL02.

Under Nodes, SQL01, it is shown as blank.

Under Nodes, SQL02, observe that SQL Server (MSSQLServer) is running.

Let’s perform an automatic failover. For a moment, just pretend that SQL02 has NIC card failure. Let’s disable NIC on SQL02 to simulate that.

Go to SQL01, choose Nodes, we see that SQL02 is down.

Choose Roles, wait shortly and check that service is automatically moved to SQL01.

Check that SQL Server (MSSQLServer) is running normally on SQL01.

Congratulations. You have finished the lab of installing SQL server 2012 Failover Cluster.