Tuesday 21 August 2012

Part 4- 2 NODE MULTI-SITE SQL SERVER 2012 CLUSTER


In Part 1 of the series I went over setting up Starwind ISCSI SAN and in Part2, configuring ISCSI SAN on the Cluster Nodes and Installing and testing Multi-site Failover cluster, Part 3 I covered configuring File Server Service on Multi-site cluster. In this last part of this series we will go over setting up SQL 2012 which is the first version which supports Multi-site cross subnet clustering, so let’s get started…

Lab:

Steps:
1.       Configuring MSDTC:

Log into Failover Cluster Manager on any Node, right click on the Cluster Name and select Configure a Service or Application


High Availability Wizard begins, click next

Select “Distributed Transaction Coordinator (DTC) “and click next


Provide Name and 2 IP Address from each subnet.

Click next…
Note: Before you can click next, make sure you are either domain administrator or pre populate the Name computer object.
Please refer to this technet article which explains in depth.

Select a disk and click next…

Click next

Click Finish
This finishes Installing MSDTC; we need to wait for MSDTC (MULTICLUSTERDTC) Computer object to be populated in both sites.
If you had gone through my Part2, Part3 series of this posting, you would know that we have a DNS replication and Host record TTL issues. I would not go over the same here, so will run the 2 Powershell commands failover over the nodes for the changes to take effect.
Get-clusterresource “MULTICLUSTERDTC” | set-clusterparameter RegisterAllProvidersIP 1
Get-clusterresource “MULTICLUSTERDTC” | set-clusterparameter HostRecordTTL 300

  
Testing the Failover:

After Failover




2.       Installing SQL Server 2012 on the First Node:

Log-in to any Node (preferably the node which has disk for SQL) and start the SQL Server 2012 setup


Click Installation and click on “New SQL Server failover cluster installation”




Make sure the status is Passed for all operations, and click OK

You may see this error, this is because setup is checking for windows update service and I have not enabled on this Node, this can be safely ignore and click next….



We have 2 warnings,
Ø  Microsoft Cluster Service verification: In part2of series, when validation Cluster we skipped validating Storage, so that is reason for warning.
Ø  Microsoft .Net Application Security: .Net is trying to see if this Node as access to Internet, which it doesn’t so a warning.
Both can be safely ignored and click next…

If you have a product key, enter the product key and click next…

Click I accept check box and click next…

Select SQL Server Feature Installation and click next..

Select what you need and click next…
Note: Reporting service is not cluster aware.

Click next….

Provide a SQL Server Network Name and depending on your environment you can either click Default instance or Named instance.
Note: If you need to install a Active-Active SQL cluster its mandatory to click Names instance.

Note: I picked Named Instance....

Click next…

Click next…

Change the SQL Server cluster resource group name if you need to and click next…

Pick a Cluster Disk and click next….

Depending on subnet the Node you are installing SQL that Network IP4 option will be enabled.
In my lab I am installing on VM2008C Node which is on 10.92.76 subnet, so I can only enable the IPv4 option for this subnet.

Click IPv4 and provide an IP Address and click next…

Provide a Account name to start SQL Server Agent and SQL Server Database Engine and click on the collation tab…
Note: its best practice that you provide a separate Domain user account as service account to startup the SQL Server agent and SQL Server Database Engine.

If you need to change the collation, change it and click next….

Pick Authentication mode, add any user accounts to be part of SQL server administrators group and click on the Tab Data Directories


Specify directory path on the cluster disk and click on the TAB FILESTREAM…
Note: Best practices to have different cluster disk for User database,TempDB and Backup.

Enable Filestream if you need to and click next….


Click the check box if you want to send Error reports and click next…


Click next…


Verify all the options and if ready click on Install….



Click Close…. And now let’s open Failover Manager



This finishes installing SQL on the first Node (VM2008c), the next step would be to Add a Node (VM2008d) from the other subnet to this cluster…




3.       Installing the SQL 2012 on additional Nodes :

Before you proceed further we got to make sure SQL Cluster Name (MULTICLUSTERSQL) computer object is replicated to all the sites and also associated Host record in DNS.




Note: If you failover the SQL server resources on the other node(VM2008d) which is not added into cluster this is the error you would see..



Let’s proceed with installing the SQL on the other node (VM2008d)


 Start the setup, got to Installation and click ”Add Node to a SQL Server failover cluster”



Click ok...


Enter product key and click on Next…


Click I accept and click next….



Setup is looking for windows update service and this is disabled on the server, so a warning…  click next…..


  
Click next….



Your already existing SQL Server instance name will be populated, click next….
 (Note:  If you have more than 1 SQL server instance then you will need to pick the correct instance from drop down box)



Provide an IP address and click next…



It’s a self explanatory warning, so click yes



Provide the password for the service account, and click next…..



Enable the check box if you need to and click next….



Click next….



Check all the settings and click install




Click ok…this may be because of .Net 4.0, which will be installed during Management studio installation.




Click close and restart the server as required…

Step 4:

Let’s check the Failover Manager on the Active Node (VM2008c)



Installing the additional Node as added the IP address as dependency on the resource and its offline now.
So how does the SQL cluster is online even though the IP address resource is offline, because of the new OR option in dependency.


 Testing Failover:



Right click on the SQL server resource and say Move resources to Passive Node, VM2008d…


So we successfully failed over SQL in cross subnet Multi site cluster.

 At this point you know that we have a DNS update record replication and Host record TTL problem as I went over this in Part 2,Part 3 of this series and also when we configured MSTDC. So I would not go over it again.

Registering All IP address:

Before Failover:




Note: Keep a eye on the MULTICLUSTERSQL Host record


After Failover:



So SQL cluster has registered all its IP address automatically whereas during testing of Failover cluster or File server or MSDTC we had to run a power shell command to register all IP address.


Let’s confirm the same using the Powershell command:





We still have a Host record TTL Problem:



Before Powershell:




After Powershell:
Get-clusterresource “SQL NETWORK NAME (MULTICLUSTERSQL)” | set-clusterparameter HostRecordTTL 300






Additional step:

Reverse lookup for the SQL Server cluster name (MULTICLUSTERSQL) will fail. So to fix it, right click on the SQL Server cluster name, go to properties and enable the check box “Publish PTR records”, apply it and failover the cluster nodes.




This end’s the 4 part series on Multi-site cross subnet installation and configuration...

Note: Microsoft Technical Evangelist Symon Perriman has an excellent video,I highly recommend seeing this video as there are other settings like cross subnet delay, etc… which needs to be looked into before putting the cluster to production.
http://technet.microsoft.com/en-us/video/disaster-recovery-cluster-deployment-demo-multi-site-failover-clustering

Recommended Articles:

Ø  http://blogs.msdn.com/b/sqlalwayson/: SQL Server Always On blogs
Ø  Cluster Resource Dependency Expressions blog: http://blogs.msdn.com/b/clustering/archive/2008/01/28/7293705.aspx
Ø  The Microsoft Support Policy for Windows Server 2008 or Windows Server 2008 R2 Failover Clusters: http://support.microsoft.com/kb/943984
Ø  What’s New in Failover Clusters for Windows Server 2008 R2: http://technet.microsoft.com/en-us/library/dd621586(WS.10).aspx
Ø  Failover Cluster Step-by-Step Guide: Configuring the Quorum in a Failover Cluster: http://technet.microsoft.com/en-us/library/cc770620(WS.10).aspx
Ø  Requirements and Recommendations for a Multi-site Failover Cluster: http://technet.microsoft.com/en-us/library/dd197575(WS.10).aspx
Ø  The Microsoft Support Policy for Windows Server 2008 or Windows Server 2008 R2 Failover Clusters: http://support.microsoft.com/kb/943984

1 comment: