Solved

How do you install hot-fixes, patches to sql server 2005 cluster

Posted on 2008-10-17
19
1,374 Views
Last Modified: 2011-10-19
I am fairly new to sql server 2005 cluster and have a question regarding installing patches/hot-fixes. We have a active/active/passive cluster.  This is what I believe needs to take place, please correct me if I am mistaken:
-install patch/hot-fix on node1.  since cluster-aware, it will automatically install on the other 2 nodes
- the nodes will need to be bounced.  Therefore, bounce node1, which cause it to failover to the passive node.  When done, bouce the passive node, causing node1 to failback, repeat for node3.
What additional steps, if any, need to be performed for the non-cluster-aware components.
What steps need to be performed if you do not have any window for 'down-time'.
Lastly, what needs to be done for installs of items which are not cluster-aware, such as drivers... do they simply get installed on the local drive or will it cause a failover situation?
0
Comment
Question by:sqlnewbie08
  • 10
  • 9
19 Comments
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
We're running 2-node active/active with multiple instances per cluster (about 9 months) so take it as this has been my experience:

If it is a new instance install, then it needs to be installed from the inactive node.

If it is an sp that affects the DB/Core of SQL Server/SQL Server Agent it will need to be applied to the active node and then at some point flop it to an inactive node.

If it is another SQL service such as (SSIS, Browser, etc.) it will have to be applied to each node individually. But that sort of depends on where your SQL App is installed.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Still need more clarification/specifics on applying patches/hotfixes and non-cluster-aware products, such as 'drivers'.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> non-cluster-aware products

If you are talking about applications like Office, you will want to install them to the C: drive. If you are talking other non MS applications that are dependent on SQL, then you install them to drive that your database/virtual server drive resides on the active node. There are two options then depending on your application and its tolerance for installation and how spread through the registry the app is.

You create a Service or Generic Application under the cluster manager and under the Advanced will be an option for copying the registry entry. If it works then all you have to do is keep that registry key updated.

If not then you'll have to install on Node A to the SQL drive. Then flop to the inactive node and you re-install to there.

>> such as 'drivers'.

Which drivers? Typically they will have to be added on a per node basis.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Regarding the 'driver'.  We have a request to install an informix driver so we can retreive data from a vender from our sql server cluster.  My thoughts were that since this is NOT cluster-aware, the driver would need to be installed on each NODE individually.  However, would this cause the node to failover?

Regarding the patches/hot-fixes.  I am referring to Micorosoft patches/hot-fixes for the sql server.
So, you would install the patch on the 'active' node first, then it would need to be installed on the 'passive' node.  Not sure how this works.. can you provide specific steps?
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> We have a request to install an informix driver so we can retreive data from a

You're pulling data from the Infomix to the SQL DB?

As long as the informix doesn't require a reboot, you should be able to install it to the C: of each node and then configure it as normal on each.

>> I am referring to Micorosoft patches

Patches from WSUS/Windows Update generally will appear in the system tray.

The MS Patches for Windows and Office may or may not require a reboot, as on a normal machine. They will have to be installed on each node individually. If a reboot is needed then a message will pop-up. I would suggest that you release the update to the inactive node first.

The big thing is to remember never reboot both the primary and its failover node at the same time.

If it is a SQL Patch, then you only need to release it on the active node. When the next failover occurs it will carry the changes in the registry settings, the drive and master/msdb across. (That's assuming you are using the default resource types in the Cluster Admin.)
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Regarding your question: You're pulling data from the Infomix to the SQL DB?
Yes.  They will be logged into the SQL server DB and using the informix driver, connect to the vendor to retrieve data.  I have no idea if the 'driver' will require a reboot.  Most likely not.  So installing drivers... that are not cluster-aware on the C driver of each node, will NOT in itself cause a failover, correct?

Regarding the sql patch and your statement...
When the next failover occurs ...  what do you mean by that?  

Thank you so much for providing your expertise.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> C driver of each node, will NOT in itself cause a failover, correct?

Provided a reboot is not required that is correct.

>> When the next failover occurs ...  what do you mean by that?  

If the patch updates the SQL executables or DLLs then when you move the instance from the currently active to the inactive node, the updates will follow and be the updated version. You can either do it manually (Move Group) or it will occur automatically in the next failover (i.e. reboot Node A for other patches). An example is when you install SQL SP2, if you have the SQL Server (and agent up) a box pops-up saying these services need to be stopped or a reboot will be required. You can either stop the services and continue with the install, or leave them running. If you leave them running then the server will have a reboot flag on it. When you finally do the reboot and it flips to the inactive node, when the service comes up on the previously inactive node, it will be the patched version.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Thank you for your response.  That is what I thought would occur with the patch.  What about the other 'active' nodes.  Once you then put Node1 back to 'active' and the original 'passive' node back to 'passive'.  What must occur for the 3rd... nodes?  Do you re-boot the 3rd node, causing it to failover to the passive node, then re-boot the original 'passive' node, causing it to fail-back, which will then cause the patch to be applied on additional nodes?

Thanks so much again!!
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
You will have to patch each SQL instance(s) per node separately. If you have NodeA with 2 active instances and NodeB with 2 active instances with NodeC being the failover for Node A & B, then you would have to SQL patch NodeA, fail it over (Move Group) to NodeC, then move it back. Then you would have to do the same for NodeB.

Each instance of SQL has its own copy of sqlservr.exe (and other apps) in its individual x:\Program Files\Microsoft SQL Server\MSSQL.#\MSSQL\Binn folder.

Each instance has its own drive so if you 10 instances you will have 10 sqlservr.exe that need upgrades.

There are some applications that occur once per server such as the SQL Browser and SSIS. They only need to be upgraded on a per node basis.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:sqlnewbie08
Comment Utility
You run the patch on NodeA, which will therefore apply the patch to each instance that resides on NodeA, right?   Or do you need to re-run the patch on a NodeA for each instance that resides on that node.  Ie:  if you have 3 instances on NodeA, then you would re-run the patch/hot-fix 3 times on NodeA?

I see documentation that shows a screen which reflects 'All Instances', so at this time I'm assuming that when running the patch/hot-fix on (ex) NodeA, it will apply the patch to each instance on that node.

Thank you.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> I see documentation that shows a screen which reflects 'All Instances', so

You have the option of doing all 3 or you can do 1 at a time. Most just select the All Instances.  It depends on what your needs and risk tolerance are. Blow up all 3 nodes at once, you only have a 15 minute down time window every Friday and testing took 20 minutes for one instance, You aren't able to test before install to production.....

But if you do only 1 or all 3 on a node, it will carry over when it goes to the other node.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Yes, but do you need to re-run the patch/hot-fix on NodeC, which has 3 different instances residing on it?  I would think you would need to re-run the patch/hot-fix on NodeC in order for it's executables to be updated.  Is that correct?
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> I would think you would need to re-run the patch/hot-fix

Each SQL Instance in a cluster has it resource group including the IP addy, the instance name, the drive(s) its master DB and the Microsoft SQL Server\MSSQL.#\MSSQL\Binn folder resides on.

If you update that stuff while its active on NodeA then when it moves to NodeC that stuff moves along with it, including the registry settings.

The only thing which would have to be done on node C is patches to the standalone components such as the SQL Server Management Studio. SSMS installs to the C: drive of the server, not to an instance.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Each SQL Instance in a cluster has it resource group including the IP addy, the instance name, the drive(s) its master DB and the Microsoft SQL Server\MSSQL.#\MSSQL\Binn folder resides on.

I understand that regarding the Node that would move.  However, we have differenct instances on NodeC then those which are on NodeA.
ie: NodeA is 'active' with a default instance and a named instance,  Node B is 'passive' and NodeC has 2 named instances on it.  

I'm more gearing this question towards whether you need to re-run the installation of the patch/hot-fix on NodeC as well.

Therefore, I'm confused (as you may see), regarding different scenarios that I've been reading.
Some state that patches/hot-fix are cluster-aware; only need to install on one active node and it will be updated on all the others automatcially.  Other documents state that you need to install on each node indivdually.

which is it?



0
 

Author Comment

by:sqlnewbie08
Comment Utility
Maybe to be more clear.  All the instances will NOT be on NodeA when the patch/hotfix is run on NodeA.  Only 2 instances will be on NodeA, when being patched.  The other 2 instances will be on NodeC.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
Then you will have to run the SP against the 2 instances on NodeA and either stop the SQL services to allow them to upgrade and restart the SQL services after, or after the patch runs, flop them to NodeC and they will be upgraded.

Then the opposite will occur for NodeC.

The other option is flop NodeC to NodeA, run the SP and upgrade all 4 at the same time.

Either way the SQL services will need to be stopped and restarted during the patching.


Setup-Regular-1.jpg
Setup-Advanced-1.jpg
Setup-Advanced-2.jpg
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I submitted before I commented on the above images.

This is from a workstation so some options are grayed out.

Group 1:
The SQL Server DB Services and Analysis Services are on a per instance basis.

Group 2:
The reporting services, I think, are sort of a half and half -- The base reporting services server is per node, but the IIS and SQL DBs it reports from are per instance.

The Notification, Integration and Workstation components are on each individual node. (The exception being the Sample AdventureWorks databases.)

What that means is that anything in group 1 is going to have its own x:\Microsoft SQL Server\MSSQL.#\ folder that it operates out of. They will be upgraded by patching the active instance on whatever node they are on and it will follow from Node to Node.

The group 2 objects reside on the individual nodes in the C:\Program Files\Microsoft SQL Server\80 & C:\Program Files\Microsoft SQL Server\90 folders and have to be touched on each one.

So if you go to NodeA and it upgrades the Client Components and Instance1 and Instance2, then you flop them so all four instances are running on NodeC then when the SP is run the Client Components and Instance3 and Instance4 will need upgrading, but it would ignore Inst1 & 2 as having been patched.
0
 

Author Comment

by:sqlnewbie08
Comment Utility
Basically, any patch/hot-fix will need to be applied to all  ACTIVE nodes, which will therefore update all instances residing on that node.  

Thank you very much for your time.
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
That's what it boils down to.

<Off-topic>
I just realized that you are new to EE. Welcome to site. If you have this figured out already please ignore this.

When you have your question answered it requested that you close a question by accepting the solution(s) that helped you the most. You should click on the accept as solution or Accept Multiple Solutions links and then put points to them. If there had been more than one expert in this question you could award points among us. Some links to help you get it done right are below.

Why do Experts want points?
http://www.experts-exchange.com/help.jsp#hi101

What's the right grade to give?
http://www.experts-exchange.com/help.jsp#hi403

How do I close a question?
http://www.experts-exchange.com/help.jsp#hi407
</off-topic>
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now