This would be the last part of this Cluster Installation Series! In the Previous part, I’ve shown you how to Configure an Active-Passive SQL Server 20008 Failover Cluster. So at this moment our SQL Version will be at RTM, let’s apply Service pack 2 over our RTM.
Let’s begin! FYI I’ve downloaded SQL Server 2008 Service Pack 2 from microsoft and copied the exe files onto both the Nodes of our Cluster.
Procedure:
To be very simple and precise, We’ve to Install SP2 on our Passive Node(I’ve chosen Node2 as current Passive) first. Once completed, we’ve to manually failover SQL Services to Node2(Currently SQL is running on Node1) and apply patch on Node1.
This model has been introduced starting SQL 2008. Earlier SQL patching was cluster aware where it used to automatically patch all the nodes in your Cluster. But this leads to more down time depending on number of Nodes involved in your Cluster. Let us say…we’ve 8 Nodes, in this case SQL Server will not be available until all the Nodes are successfully being patched. Starting 2008 MSFT avoided this, basically making High Availability Solution more highly available 😉
Same logic applies to Active-Active Cluster as well. You’ve to Manually failover your SQL Services to other node(so at this point 2 instances of SQL will be running on single node) and you’ve to apply patch to idle node. Once patching is completed successfully on your idle node, you’ve to manually failover both the Instances to this node and patch the other node. Hope am not confusing!!
Let’s begin! I’ve chosen to patch my second node first. So I’ll make sure that SQL and all other dependencies are running on Node1. You can see below in the screenshot that Node1 is owning every SQL resource.
Very Important: You should remove Node 2 as a possible owner through out the patching process of Node2, making sure that SQL is not going(allowed) to failover to Node2 for any reason throughout this process.
Also,Just to double check open your SQL Server Configuration manager on your Node2 and you should be seeing SQL Server and SQL Server Agent as Offline as shown below. you might be seeing Browser and FTS services as running since they are not clustered (SQL Cluster Patch installation will take care of those services as needed).
Last thing to do is open your SSMS and note your Current build and Version just by issuing @@Version. For me the current Output is
“Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1) (VM) “
Close your SSMS(if any) on Node2 and double click on the SQL Server 2008 SP2 executable, you should be welcomed with the below screen
Next ->Accept the License Terms ->Next and you’ll get the below screen where you’ve to choose the features which you want to apply patch to.
Next and It’ll perform a basic Check for any Files in Use which might prevent successful installation of SP2 as shown below.
Click Next -> Update as shown below…That’s it:)
It will take few minutes to complete and you’ll get below screen.
Click Next->Close. Once Done, go to your Failover cluster Manager and add your Node2 back as a possible owner and Failover SQL Services to Node2(At this Point If you open your SQL Server Configuration manager on your Node1, SQL Server Service and SQL Agent Service should be Offline) and repeat the same procedure what we did for Node2, on Node1 to patch Node1 as well.
Note: Don’t forget to remove Node1 as possible owner now through out the process, making sure that SQL is not going(allowed) to failover to Node1 for any reason through out this process.
Once you are done with patching both the nodes successfully, add node1 back as possible Owner and now open your SSMS and issue @@Version and let’s compare with what we had prior to Installation.
As you see in the above screenshot we’ve upgraded our SQL from “Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34″ to “Microsoft SQL Server 2008 (SP2) – 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22″.
Perfect…That’s all for the day!
Basically we’ve covered everything right from scratch(Installing Virtual machines, Installing Windows Server 2008 Enterprise OS, Configure your DNS and Domain controller, Adding new Physical Computers to Domain, Creating your own ISCSI SAN, configuring NIC’s…Clustering Windows and creating SQL Server Cluster and finally how to Patch your SQL Servers which are configured as clusters).
Hope this series will be useful to someone who is new to SQL Failover Cluster setups and also hoping you enjoyed this entire series of SQL 2008 Cluster Installation as i did:)
MSSQLTREK 
Do you know if it is possible to automate this process? Rather than going through this process manually each time I’d like to be able to run a script that does the same thing… any ideas?
I Never did automate this Process, considering the frequency we apply patches in our Environment…!I would point you to check Failover Cluster Cmdlets in PowerShell and INI Installation of SQL Server. I think PowerShell can do this magic for you.
Hi
I have 4 + 1 cluster… 4 – Active – 1 Passive…
As per your suggestion move all the instance to a single node. and upgrade rest of the nodes. My question is can I install the SP2 simultaneously on all passive nodes.
thanks in advance.
Thanks
Murali
Hi Murali,
Nope! Each node has to be Patched separately/individually(Also, Patching is Per Instance Level).
HI sreekanth,
Thanks for the concise and clear post.
I am a newbie to SQL server clustering. I think you can help me here.
One question,
I have a Active -Passive 2008 failover cluster and I don’t see SQL server services on SQL Server configuration manager on passive node. I don’t see the services at all. As you have mentioned, I should see them as offline.
Please clarify why would this happen.
OS:Windows 2008
SQL Server version: SQL server 2008
Thanks in advance
Hi Sri,
Are you sure that you’ve added your SQL Sever Instance to the Other Node of your Cluster? What do you see when you open Failover Cluster Manager?(Can you look at Possible Owners and what happens when you try to failover to the 2nd node?)
I think you missed adding the 2nd node to your SQL Sever Cluster Installation.
Hi Sreekanth,
Thanks for the response.
When I open Failover cluster manager, I see this option of move this service or application to another node.
I haven’t tried failing over the service to another node. But I heard from the team members here that it doesn’t stay on that node 2 when someone tries to failover, it will restart the service and come back to node1 again.
So this might be because, windows 2008 cluster is setup in place and whoever had installed SQL server might have installed only on node1 and missed the other part of adding a node to the cluster(i.e running SQL server setup on node2 and performing the add node action).
If that is the case, why does failover cluster manager shows the option of “Move this service or application to another node”. And a recent incident that I saw was, SQL server service was on node2 and offline. This happened because of a failover triggered by a resource failure and SQL cant come online on node2. We have to move the services to node1 and this brought the services online.
Please let me know your thoughts.
I assume you’ve 2 Instances(Let’s say SQLPRD1 and SQLPRD2) and you’ve 2 node(Node1 and Node2) Windows Cluster. Let’s talk about just SQLPRD1 Instance for the sake of simplicity. If the Installation was properly done without any warnings/Errors on Node1 and also If the Addition of the Node2 to your SQL server Cluster was properly done without any warnings/errors, you will see your SQL Instance in “running” state on the current active Node(Node1-assuming you didn’t perform any failover yet) and in “Stopped” state on the current passive node(Node2) if you open your SQLServer Config manager on respective nodes.
As I said, Looks to me like Installation was not done properly in your case(interruptions in between setup might lead to this situation).
1.Look at the Installation Logs(Summary)/Application Logs/Cluster Logs for further investigation.
2.Look for SQL Server Binaries(For a default Installation, you should see “sqlservr.exe” on both the Nodes dedicated for your Instance under your C:\programfiles\microsoftsqlserver\MSSQL10_50.InstnaceName\MSSSQL\BIN Folder.(Assuming yours is 2008R2 SQL)
3. The Best of all, check with the person whoever Installed this. He/She might know what did they do more than anyone:D
4. Hope this is not an Instance which was sent Live into Production without testing/validating.
Also, I see the SQL server service has 2 nodes as preferred owners.
Hi Sreekanth,
My cluster setup –
Node1- Active – Default SQL server instance
Node2- Passive – No SQL server instance installed. I see only client tools installed.
I don’t see SQL server binaries on node2.
I followed your advice and looked for setup logs and there are so many logs for remove of node on node2 and also a final install of sql server client tools on node2. The person in charge of this left this place.
SO now, on node2, there are only client tools.
My questions,
1. Why does failover cluster show me the option of “Move this service to another node” and shows Node2 there. I want to try this option but unfortunately this is a production server.
2. If I am not wrong, I saw sql services failing over to node2 because of a resource failure and it didn’t come up online on node2. We have to bring the resources and services to node1 and that brought everything online again.
As we dont have SQL server database engine installed, can node2 take the ownership of resources or let me put it this way, Can a manual or triggered failover possible at all?.
Please share your knowledge.
Thanks in advance.
Okay…At first place, if that is the only Instance on this Cluster, This makes Single Instance Cluster(A-P, not A-A as you mentioned initially).
Ans1: I think It’s because, initially this node has been added successfully and recently someone tried to Uninstall from your Passive Node(Where it might have failed) or a clean uninstall was not performed. Or a clean addition of Node was not performed in the first place. (I would go with the former considering the Uninstall logs you are seeing on this node). That might be the reason why Failover Cluster is still showing that node.
Ans2: Nope, you can’t. Well, let me explain how SQL Server cluster works in the first place.
At a very High level(in a Nutshell):
Step1: You Install Clustered SQL on Node1. This Installs SQL Server Binaries on this Node for this Instnace.
Step2: You add Node2 for this SQL Server Cluster. This Installs a completely separate SQL Server Binaries dedicated to this Node/Instance which can handle your SQL Server Instance on this node in case of Failover. That’s the whole point of building such an expensive solution of cluster right…? See, for some reason SQLServer Binaries are corrupted or missing/deleted on NodeA, then the SQL Server Binaries on Node2 will handle your DB Engine by FailingOver Services. (Without Binaries, what;s the point??) – Hope you are getting what am trying to explain…!
In your case, you are saying there are no SQL Binaries at all on your 2nd Node. So how can SQL Server DB Engine be brought Online on Node2?? It can’t….! That’s the reason if if you are failing it to Node2, it’s failing back immediately, because it can’t be brought Online on Node2.
Am really Surprised how can one do this on production!!!! Check with your Manager if he/she’s aware of this change on your Prod Cluster, why this happened at first place. If this is an accident, removed by mistake without knowing under the hoods of cluster, He/she’s is in trouble.
I would have tried adding Node2 Back to this SQL Cluster if this is a Non-Prod Envi. But, Considering production, for changing anything I would keep my management in loop, being transparent.
All the Best!
Thanks for the clarification Sreeknath. You are very helpful with your explanation.
I will work on adding the node2 to SQL server cluster and I will let you know the results here.
I will also check the DMV, sys.dm_os_cluster_nodes to find whether SQL server is considering the node2 for failover option.
Thanks again.
Thanks for the post.
It was very helpful, when I learned how to apply the new SP3 on my SQL SERVER 2008 Cluster env.
Hi Sreekanth,
Question regarding upgrading on cluster environment.
I have a Active-Passive failover cluster 2008 with SQL Server 2008 Sp1 installed. For upgrading to Sp3, I am trying to see if the following scenario makes sense.
Passive Node(Node2)- Upgrade to Sp3 and failover SQL server to run on the upgraded node. Now Node2 is active.
Node1- This is passive now and leave it on SP1 for a week. If any application failures are noticed during this week, fail over the SQL server from node2 to node1 and run it on node1 with SP1.
Question – After upgrading to SP3, can SQL server failover to node with SP1 and run on SP1. I haven’t done this so far but want to try this. Unfortunately, no test environment to test this.
I read on a post that as long as it is same version(SQL server 2008 in this case), service packs should not affect the restore of database on any service pack level. Will this work for clustering as well.
Note: Preparing for a rollback plan if application fails on Sp3. I know the Sp3 can be uninstalled alone on Node2, but interested in knowing if the above scenario works.
Thanks in advance.
My First Question…Why would you want to leave SQL Server on the other node unpatched for a week…….Really?? I wouldn’t recommend that personally.
Yes, SQL will run on the other node if you failover without any issues after patching(Remember, that is how we are reducing the down time starting SQL Server 2008….). But you might see a lot of entries something like “upgrading msdb stored Procedures, resourceDB blah blah and what not” in your error Logs if it Fails Back for some inevitable reasons….without the SQL being patched on the other node.
Answer for Q2 – DB restores act same irrespective of Standalone or Cluster. If your Restore works on a Standalone SQL Instance, they will work exactly the same way on your clustered Instance as well.
And Please don’t say…this is Prod!!
Thanks for the response Sreekanth.
I am want to leave the node as unpatched to make sure if any application fails on SP3, I can failover the sql services to node which is having SP1. This way downtime is minimal.
This is just a thought as our environment doesn’t have test boxes and no way to make sure all applications works fine on SP3.
And, I know we can uninstall SP3 just by itself without having to uninstall SQL Server in Failover cluster 2008.
BUt I agree with you, ‘Not recommended to do on Production’.