March 17, 2011 at 6:49 am
I have SQL Server 2005 Enterprise edition. I need to install SQL Server SP4 to be installed which was released in Dec 2010. Now we have a Active\Passive node and i need some instructions on how to install SP4 on a Active\Passive node.
This is my first installation on a Cluster environment so please try to explain me in detail the steps required to update to SP4.
Thank you.
March 17, 2011 at 7:53 am
any idea friends???
March 17, 2011 at 9:57 am
1. ensure you have 3gb disk space free on the system drive of all nodes
2. ensure you are logged on with local admin privileges
*edit* and the account you are using to run the patch (normally your own) has logged onto all nodes previously to create a user profile.
3. take system database backups. I stop the SQL cluster resource copy the system database to a subfolder and restart the cluster resource in case everything goes bang. As copying the system DB from a subfolder is a lot less work than trying to restore system databases from backups using DAC.
4. rename sa account back to sa if it has been renamed KB955706
5. if the distribution db logs have been moved, put them back in the default location KB970892
6. you might need to install Visual Studio 2005 SP1 on all nodes first depending on what components you have installed. Some people say this is a prerequisite but I am not entirely sure.
7. Apply service pack on active node
*edit* there must be no one logged onto the passive node when the active node is being patched or the installer hangs.
8. Apply service pack on passive node
9. Restart each node of the cluster and test SQL works fine on all nodes using client application.
10. run backups of system databases and also run a tlog backup on each node to test SSIS.
11. check patch didn’t cause “Named Instances on Windows Cluster changed from Mixed Mode to Windows only after reboot randomly” http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=480487
Further notes at http://social.technet.microsoft.com/wiki/contents/articles/microsoft-sql-server-2005-sp4-release-notes.aspx and http://download.microsoft.com/download/B/7/3/B73AFEF4-1A6C-42E6-B5A8-CBF990687A9E/ReadmeSQL2005SP4.htm
If there are problems consult “Failover Cluster Troubleshooting” http://msdn.microsoft.com/en-us/library/ms189117(v=SQL.90).aspx
Not that you would do it during a service pack but make sure the service account does not get changed on a cluster when the SQL resource is down or offline on any nodes. I have had this cause issues like SQL Agent only running on one node. http://msdn.microsoft.com/en-us/library/ms178061(SQL.90).aspx
March 17, 2011 at 11:31 am
thank you. atleast this might help me at some point.
March 17, 2011 at 12:04 pm
Hi,
log shipping restore job got failed due to below error. i find that error from alert job. please assistance how to troubleshoot that.
The log shipping secondary database XXXXXXXDATA.XX_DB has restore threshold of 45 minutes and is out of sync. No restore was performed for 193 minutes. Restored latency is 6 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14421). The step failed.
and also i checked the log_shipping_monitor_secondary table on secondary server i got the error like restore failed due database in use .
March 17, 2011 at 3:30 pm
please don't hijack threads. start a new thread with your question if you want help.
nice answer on the SP application by the way. I would add ensure any server level triggers are disabled and take normal system db backups as well as a belt and braces approach. I highly recommend taking file copies of the system dbs before and after though, including mssqlsystemresource.
---------------------------------------------------------------------
May 4, 2011 at 6:37 am
Thanks. Those are good instructions but I have never had to install a SQL service pack manually on both cluster nodes (active then passive). I always run the SP on the active node and the SP takes care of the install on the passive node at the same time.
If it's a multi-instance cluster, you must move all SQL instances to one node and run the SP there.
October 18, 2011 at 8:44 am
Hello James,
I am going to apply the SP4 on our MSSQL 2005 clustered environment this weekend. I want to ask why do we need to apply the patch on the passive node ? I would have thought applying to the Active node would write to both underlying nodes but is it does as a precautionary step or is there something reason ? (I just want to understand what SQL is doing and not to question your step process) !
October 18, 2011 at 8:59 am
James_B (3/17/2011)
2. ensure you are logged on with local admin privileges
more importantly, the account you run the service pack under must have admin access to the sql server instance. If you removed builtin administrators from SQL Server then manually ensure the windows account you are installing as has been provisioned appropriately
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2011 at 8:11 am
Yeah, most of points above are correct. Some clarifications:-
SQL Server 2005 uses task scheduler service to install the patch on the remote node as well. SO you don't have to install the patch separately on the passive node.
Ensure that you verify the task scheduler service running on both the nodes.
Many times the remote task (SQL setup) would get created but it wont run on the passive node due to some issue. Check the task scheduler logs on the passive nodes to investigate further.
You can also test the task scheduler running fine by creating a task on the passive node and running it remotely.
October 19, 2011 at 8:23 am
Thank you for your help, I shall check the task schedulers on both sides, this explains the logic of how the SP gets applied to active/passive.
We have 2 instances of MSSQL (UAT and Prod) running on our cluster which is made up of 2 nodes. Is there a way of applying SP4 to only the UAT instance ? or to just the passive node so we can test before applying to the other node.
October 19, 2011 at 9:55 am
Imaan (10/19/2011)
Thank you for your help, I shall check the task schedulers on both sides, this explains the logic of how the SP gets applied to active/passive.We have 2 instances of MSSQL (UAT and Prod) running on our cluster which is made up of 2 nodes. Is there a way of applying SP4 to only the UAT instance ? or to just the passive node so we can test before applying to the other node.
Don't use your production server for testing!
Apply the patch to a test system and perform testing there. Building a virtualised SQL server cluster for testing is easy using my guide at the following link[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 19, 2011 at 10:55 am
Yes, you can just install the patch only on the UAT instance of SQL server.
Also in SQL 2005, you have to perform the patch installation from the active node. In SQL 2008, you can patch from any node (rolling upgrade)
February 15, 2012 at 9:15 am
I believe the Service pack will not update the client tools on the passive boxes.
So that is one reason to run the SP on the passive boxes, to update other components.
Thanks
Mark
February 15, 2012 at 9:40 am
deva_etc (10/19/2011)
Also in SQL 2005, you have to perform the patch installation from the active node. In SQL 2008, you can patch from any node (rolling upgrade)
Starting 2005 SP2 there is an option for /PASSIVE on the service pack installer which allows you to use the rolling upgrade functionality 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply