In the Part-6 of this Series, I’ve shown you how to Install a SQL Server Failover Cluster. In this Part Let’s add a Passive Node(Node2 in our case) to our Failover cluster. I assume you’ve followed all the parts in this series upto this point as shown without any issues.
Now, your very first step would be..You’ve to go to your passive node(node2) and insert your SQL Server installation media. At this Point your MSDTC, SQL Sever Service, Quorum Disk…literally all your resources are running on your node1. In other words, Node1 is hosting every application and service on your cluster.
Open you setup.exe and again annoyingly on my Node2 as well it asked me to install (KB942288) which i did just by clicking OK and it required a restart of your machine:( Well, once rebooted i opened my setup.exe and now I’m welcomed with typical SQL Server Installation Screen. This time You’ve to choose “Add Node to a SQL Server Failover Cluster” in the below screenshot.
It does a basic Rule check, once finished with no errors/warnings click OK. Choose your SQL Edition(in my case Eval Enterprise) and accept license terms and Next -> Install. It’ll do some Windows Installer actions/extracts all the required setup files. Once finished, you’ll be welcomed with the below screen where it does a thorough Rule check for your cluster settings.
Once everything is Green, you can proceed further by clicking Next and now at this point, SQL is smart enough to automatically identify your SQL Network name and SQL Instance name(Since we’ve only one Instance at this point, if you’ve multiple Instances you’ve to select from the drop down menu) and all you need is just click NEXT blindly as shown below:)
Now enter your Service account(s) password(s) and Click Next as shown below.
Next ->Next(choose Error Reporting to be sent to MSFT if you need). It will now do a validation on Add Node Rules as shown below.
Click Next and INSTALL. That’s all you need to do..So basically It’s nothing but a blind game where you have to click couple of NEXT, OKAY,INSTALL buttons to add a node to your existing SQL Cluster 😉
Hurrayyyyyyyyyyyyyy! We are done Guys…Now let’s failover our SQL Server back and forth and actually let’s see from SSMS.
I’ve opened my SSMS on NODE2 and connected to SQL Server and issued
select serverproperty(‘computernamephysicalnetbios’) to verify the current physical node under which SQL is running under as shown below.
Now, let’s failover SQL to node2 and let’s issue the same command once again(please note I’m not changing any connection strings….That’s the magic of Clustering).
Now, as you can see in the below Screenshot you can verify from T-SQL under which node SQL is currently running under(it says Node2 as expected).
Prestooooooooooo!! We did it…See how Simple it is to create a Cluster in your LAB Environment. Please note, I’ve violated all the enterprise rules in this world by disabling my Firewall and Not checking my Partition Alignments on my drives…but, as i mentioned this demo is just to create your Lab environment. If you’ve enough patience dealing with all those Firewall rules, Please explore those as well:)
BTW, in your Failover Cluster Manager there’s my favorite feature something called as dependency report…which gives awesome pictorial representation of all your services dependencies along with brief verbiage. You can pull that report as shown below.
As you can see SQL Agent is dependent on SQL Server, SQL is dependent on Physical Disks and Network name, Network name is dependent on Network IP =>Agent is actually dependent on all and SQL Server is dependent on above shown disk drives and Network name, but not on Agent and SQL Network name is dependent on IP Address….Everything is self explanatory in this Awesome report which MSFT is offering us starting Windows Server 2008. Kudos to Microsft:)
Let’s see how to apply Patches to your SQL Server cluster in next part of this Series!