In continuation of our series on how to install a SQL Server 2012 clustered instance, let’s discuss how to add a node into an existing SQL Server clustered instance. The following steps are performed either to add one more node to some already installed clustered instance, or to continue the installation of a brand new clustered instance — It all boils down to the same thing. To perform this phase, you will need to have at least one node installed. In this case, we installed a new SQL Server failover instance in the Part 2 of this series.
So connect to the next node, in this case W2012SRV04, and perform the following steps:
1. Make sure that you have the same SQL Server 2012 media used to install in the other node available and execute the “Setup” binary.
2. The “SQL Server Installation Center” will be opened.
3. Still on “SQL Server Installation Center”, click “Installation” and select “Add node to a SQL Server failover cluster”.
4. A check will run in order to verify the setup support rules. Click “OK”.
5. Now the setup will check and install the latest updates. Keep the “Include SQL Server product updates” checked and click “Next”.
6. Another check will run in order to identify problems within the installation process. Click “Next”.
7. Insert the product key and click “Next”.
8. Accept the terms and click “Next”.
9. On this step you need to pick the instance where this installation will be related. Notice that you have a list of installed instances, as well as the nodes that the instances are already installed.
In our case, the Instance Name “DB” is installed in only one node, and we need to choose this instance in the list box in the top to proceed to the node addition.
10. Now. confirm the IP settings as you did in the first node installation. Click “Next”.
11. Fill the passwords for the Engine and Agent service account, and click “Next”.
12. Like in the other (first) node, you have the option to send error reports to Microsoft. Click on “Next”.
13. Now the setup will verify if the installation process can be blocked. In the end, click “Next”.
14. Review the options and click on “Install”.
15 Now you can watch the installation progress. Click “Next” when it is done.
16. Now the node addition is complete! Just verify if all of the features have succeeded. Click “Close” and you are done!
At the end of this installation, you will have one more node available to run out our instance. This means that we can now perform a failover from W2012SRV03 to W2012SRV04, so we have now a high availability (HA) solution. In case of a hardware failure on the active node, we will have a failover action.
For some reason, such as a test or for maintenance purposes, you can do a failover manually. To perform this, open the Failover Cluster Manager tool (the same used on step 2), right-click over the role name (in our case “SQL Server (DB)” and then select the “Move” option. Two options will be shown — the first “Best Possible Node” and the second one “Select Node…” Both are valid, but the second gives you the chance to choose the node to move the Role, which is useful when you have more than two nodes.
That’s it for today. I will be back soon with the last part of this series, talking about MS DTC on clustered instances.
If you have’t read the first blog post in this series, here is Step-by-Step Installation of a SQL Server 2012 Clustered Instance — Part 1.
Then find the second blog post in this series, Step-by-Step Installation of a SQL Server 2012 Clustered Instance — Part 2.
Finally, find the fourth blog post in this series, Step-by-Step Installation of a SQL Server 2012 Clustered Instance — Part 4.
Pythian is a global leader in data consulting and managed services. We specialize in optimizing and managing mission-critical data systems, combining the world’s leading data experts with advanced, secure service delivery. Learn more about Pythian’s Microsoft SQL Server expertise or check out some more SQL Server-related blog posts
Hi, I am wondering how come, the second node didn’t ask you to choose the instance. Something missing?
Nice catch! I already added the missing screenshot.
Thank you for reading!
Hi, nice article,
i have 2 node active/active cluster and how add 3rd passive node?
please explain the step by step?
Jessy, we are just adding the instance to another node. So, it’s the same instance, with same shared disks, IPs, etc…
Can you please send active active cluset setup doc
You are using the same IP address for both instances? Or did you just reuse the screen shot? I thought every instance had to have its own IP address.
I´m newest in this world of MS SQL Server Failover Cluster, but for training I want to build a MS SQL Server Failover Cluster with two nodes! It´s possible to do that with VMWare machines or Hyper-V? Can anyone tell me if I can make that in my own laptop with some virtual machines?!
did you try clustering using VMware instances?
I have tried using 3 VMs. 2 as nodes and 1 as DC. But i was not able to validate the cluster while forming it due to cipconfig errors.
Please let me know if you came across any good doc or material for forming a cluster with 2 nodes.
you can use VM ESXi, but 3 VM.. 1 for storage pool, 2 SQL nodes.
just setup iSCSI on ur storage VM with 2 IP, one production, one storage vlan
anyone ever try multi-site failover cluster?? it would be perfect for DR co-location.
something like 2/2 or possible 3/2 setup
We have 2 node cluster setup in our environment by windows admin with msdtc configured using ip address. Is it possible to configure active/passive sql server cluster instance with the same ipaddress of msdtc? If yes, what will be the consequences of using same msdtc ip address to sql server active/passive cluster instance. If there is a consequences then I can request new ip address for sql cluster instance.
Windows 2012 Enterprise and SQL 2012 standard edition’s were involved in this scenario
Thank you :)
plz can u share active-active cluster setup document..
I’ve set up a Two node Fail Over Cluster. However, I find that in node B, i can see sql services of both nodes A and B, while in node A, i can only see sql services of node A. SQL services of node B is missing.
This is causing issue that sql server on node B isn’t available when node B fails over to node A.
How to resolve this issue? Do I need to re-install node B again? Any help will be appreciated.
Hi! For the second node, do I need to install SQL Server then perform the steps you have provided?
Thank you very much for this great step-by-step guide. I managed to setup a test env (1 VM running as iSCSI target – 2 VMs as clustered SQL nodes) and using the Cluster Manager I can failover.
D u r i n g the failover process, if I am running a query, I am getting the following error: “A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)”.
When the failover process c o m p l e t e s (~5 secs), then I can work without any issue and all the queries run.
i would like to ask you:
– How much time the failover procedure last in a production env? What will notice a user?
– Is it normal to get the message that I mention above? Is it acceptable in a production env?
– Ways to decrease the failover time and speedup the failover procedure?
Thank you in advance.