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.
Site:
INDIA Subnet : 10.92.76.0/24
We already have a
5GB disk which I will use it for SQL Database, added a 1 GB iSCSI
disk for MSDTC .
Lab:
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.
.
