Part 2- 4 NODE MULTI-SITE SQLSERVER 2012 CLUSTER

In the Part1 of this series I went over setting up Multi-Site cluster, today I will go over setting up a 4 Node SQL 2012 cluster which now supports Cross Subnet Multi-Site clustering.

We already have a 5GB disk which I will use it for SQL Database, added a 1 GB iSCSI disk for MSDTC .

Lab:

Site: INDIA Subnet : 10.92.76.0/24

Domain Controller/iSCSI Target Server :  FOODC1 - Windows 2008 R2 SP1

FOOPRIMARY – Windows 2012 RC

FOOSECONDARY – Windows 2012 RC

Site: Hartford,USA Subnet: 172.168.0.0/16

Domain Controller/iSCSI Target Server: DRDC1 - Windows 2008 R2 SP1

DRPRIMARY - Windows 2012 RC

DRSECONDARY Windows 2012 RC

Site: Singapore Subnet: 100.0.0.0/8

Domain Controller: SGPDC1 - Windows 2008 R2 SP1

CLUSTER NAME: FOOMULTICLS IP ADDRESS: 10.92.76.36/172.168.0.36

INSTALLING MSDTC

MSDTC is still a required component before installing SQL cluster. If you don't install MSDTC you would see a warning message during setup.

Note : You can log into any Node and do the install, but best practice is to do it on the Node which owns the disk. So I will do doing MSDTC install and SQL 2012 install on FOOPRIMARY.

Provide Name and 2 Static IP ADDRESS.

Click Finish

Note: wait till FOOMSDTC computer object and DNS record gets replicated to all the sites.

Also keep any eye on DNS update when you failover, as you would see only 1 IP address and we need to fix the all IP registration and DNSTTL,More please check my Part 1 on this.

Testing the Failover:

So we have successfully tested the failover.

Lets fix the All IP registration and DNS TTL issue by running the below Powershell cmdlet.

Get-clusterresource FOOMSDTC | set-clusterparameter RegisterallprovidersIP 1

Get-clusterresource FOOMSDTC | set-clusterparameter HostrecordTTL 300

This finishes installing MSDTC, so lets move on to installing SQL 2012 on the first Node.

INSTALLING SQL 2012 ON THE FIRST NODE:

You can begin your install from any Node, but best practice is to start the install on the Node which owns the Disks required for SQL .I have it on FOOPRIMARY.

FOOPRIMARY:

Note: Also best practice is to copy the SQL 2012 DVD on the local hard drive and run it, I have seen some issues where install would either take long or fail when run from DVD.

Run the SETUP.EXE

click on installation

click New SQL failover cluster installation

click ok

As I don't have access to internet on this Node I just click skip scan and click next..

MSCS has warning, see the Part1 validating cluster which explains why.

.NET Application security warning because the install need windows update option enabled, which is not on this Node.

Both warnings can be skipped safely, click next....

I just picked only Database Engine and Management studio... click next..

Please change the resource group name, this how it will show up in Failover cluster Manager.

click next...

Pick all the disk you need for SQL and click next...

Provide a Static IP and click next..

best practice is to create a SQL group and add user to it and provide the SQL group here, I am just using the domain admin user here .Click collation...

change if you need to and click next...

pick a authentication mode and users and click on Data Directories...

Provide SAN disk Path. Click FILESTREAM if you need TO ENABLE IT..,

click next...

review and go back change anything if you need too and click Install...

click close and open Failover Manager.

So we have successfully installed the SQL on the 1st Node.

ADDING FOOSECONDARY NODE which is same Subnet:

Note: Before you proceed further make your SQL CLUSTER NAME AND DNS RECORD replicated to all the sites.

FOOSECONDARY:

Run Setup and click Installation ..

click Add node to a SQL Server failover cluster....

the rest of the steps are pretty much the same as above, I will include steps from Cluster Node Configuration.

so it automatically identified the Instance, just click next...

As this server is in the same Subnet it shows the IP Address for SQL cluster which we assigned when we installed FOOPRIMARY NODE.

Just click next..

Provide password for Service account and click next...

Click Close and test failing over from FOOPRIMARY to FOOSECONDARY

So we have successfully installed and added the FOOSECONDARY .

ADDING DRPRIMARY NODE FROM OTHER SUBNET:

Run Setup and click Installation ..

click Add node to a SQL Server failover cluster....

the rest of the steps are pretty much the same as above, I will include steps from Cluster Node Configuration.

Click IP4 and provide a static address and click next...

read and click Yes.

Before we test by failing over to DRPRIMARY node , lets KEEP A EYE ON THE DNS record for SQL CLUSTER NAME

Let move the SQL resource to DRPRIMARY.

So we have successfully failed over the SQL Sever to DRPRIMARY on the Other Subnet.

LETS CHECK THE DNS RECORD NOW ON BOTH SITES:

Note: So during failover,a new DNS record with new IP Address 172.168.0.38 has been added/.

So what basically cluster did was registered all the IP Address of SQL CLUSTER NAME without we need to run the POWERSHELL cmdlet RegisterAllIPAddress

Note: We still need to run cmdlet for DNS TTL if you need to change the default 20 mints.

ADDING DRSECONDARY NODE FROM OTHER SUBNET:

Run Setup and click Installation ..

click Add node to a SQL Server failover cluster....

the rest of the steps are pretty much the same as above, I will include steps from Cluster Node Configuration.

So we have successfully installed and tested SQL Server 4 Node cluster.

In part3 I will go various Disaster recovery scenarios.

.