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

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.

Still need more clarification/specifics on applying patches/hotfixes and non-cluster-aware products, such as 'drivers'.

>> 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.

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?

>> 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.)

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.

>> 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.

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!!

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.

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.

>> 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.

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?

>> 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.

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?

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.

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

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.

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.