April 13, 2011 at 4:10 pm
We are having about 20 servers. I can see for most of our sql 2005 is in Service pack 2 or 3.We don't have any problem now for those servers.
My question is do we need to keep them to new version of service pack?
The problem is for some of the servers that host multiple applications, we don't know if they support service pack 3 or 4 or not? But we know they defintely support sp2.
I wonder if I do a new sp, it will break the applications.
It becomes even harder if a server hosts multiple applications, some are developed in house, some are vendors, we don't have an easy way to find out what service pack it supports.
I would prefer to stay in what we have now, because we don
t we have any issues.
Any suggestions/
April 13, 2011 at 5:51 pm
Service Packs definately fix many issues, but also potentially alter the behavior of SQL Server too. They can include adjustment to how the Query Optimizer decides how to get to the data, and on and on.
Therefore, applying a service pack or hotfix should always be done carefully and with testing. Always. If you just apply it and hope for the best, then it MAY break things right away that you can detect during the maintenance window, or it may break tomorrow morning when a heavier load is applied to it. So the procedure is to:
1. (Best) Have test/development hardware that looks like production. Setup a copy of the vendor apps to point here. Record a production load of activity (or use a benchmark tool from a popular vendor like Quest or Idera). Take a baseline on the test hardware, then apply your proposed changes or service packs. Then benchmark it to see how the changes impact the system functionality as well as performance. You'll then see if it'll be able to handle the heavy load in the morning when all of the users arrive for work.
2. (Not so best) If you do not have at least 1 test system and you are running 20 production systems, then there is a bigger problem. But the procedure would probably involve doing a full backup of the entire box (if a VM, shutdown the VM and duplicate the vm file) and do the baseline/benchmark on the same box at night. If your benchmark shows a problem, then it is easy to revert to the pre-fixed condition. If you are not using a VM, then it is harder to do a full system snapshot, but there are fairly cheap tools to do that (and you need a ton of disk space depending on how large the databases are).
3. (Terrible) If you just run SQL backups then apply a service pack/hotfix without a baseline/benchmark, then you are gambling. Tomorrow morning may not be fun for you. Or it may be OK by chance. If you detect any issues and need to 'undo' the service pack, don't expect that you can just uninstall it. Service packs are not that clean to remove/revert. They may change .dll/exe files in the mssql directory or in windows\system32, and they may change registry settings, and they usually run 'change scripts' against your system databases so Master or MSDB end up being different, and on and on. But worse, if an existing file needs replacing, it is harder to back out the change and restore the 'old' version of the file. Backing out a service pack is a nightmare, if it is even possible at all. It is usually easier to blow away the installation, and install from the media all over again, then apply the service packs/hotfixes to get it back to the exact same point version that it was before (SP1 or 2 like it is today).
Many times, applying a service pack will yield no problems in more environments. This leads many people do perform on-the-fly fixes directly on production without testing. However, sometimes it fails and it is a REAL pain to recover from. So it is advisable to use a test system in all cases, even for a tiny 'harmless' hotfix.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
April 13, 2011 at 6:21 pm
Excellent advice from Jim above.
Service Packs fix things, but if you are not having issues with any of the things addressed in a service pack, then you don't need to apply it. However if you have supportability issues, likely right now Microsoft would require SP4 on a server in order to work on certain issues.
I would check with vendors, and see what their stance is on service packs. If you have in-house developed applications, see if you can apply SP4 to a test server or VM and run through regression testing to see if everything still works correctly.
April 14, 2011 at 7:09 am
[Jim].[dba].[Murphy] (4/13/2011)
Service Packs definately fix many issues, but also potentially alter the behavior of SQL Server too. They can include adjustment to how the Query Optimizer decides how to get to the data, and on and on.Therefore, applying a service pack or hotfix should always be done carefully and with testing. Always. If you just apply it and hope for the best, then it MAY break things right away that you can detect during the maintenance window, or it may break tomorrow morning when a heavier load is applied to it. So the procedure is to:
1. (Best) Have test/development hardware that looks like production. Setup a copy of the vendor apps to point here. Record a production load of activity (or use a benchmark tool from a popular vendor like Quest or Idera). Take a baseline on the test hardware, then apply your proposed changes or service packs. Then benchmark it to see how the changes impact the system functionality as well as performance. You'll then see if it'll be able to handle the heavy load in the morning when all of the users arrive for work.
2. (Not so best) If you do not have at least 1 test system and you are running 20 production systems, then there is a bigger problem. But the procedure would probably involve doing a full backup of the entire box (if a VM, shutdown the VM and duplicate the vm file) and do the baseline/benchmark on the same box at night. If your benchmark shows a problem, then it is easy to revert to the pre-fixed condition. If you are not using a VM, then it is harder to do a full system snapshot, but there are fairly cheap tools to do that (and you need a ton of disk space depending on how large the databases are).
3. (Terrible) If you just run SQL backups then apply a service pack/hotfix without a baseline/benchmark, then you are gambling. Tomorrow morning may not be fun for you. Or it may be OK by chance. If you detect any issues and need to 'undo' the service pack, don't expect that you can just uninstall it. Service packs are not that clean to remove/revert. They may change .dll/exe files in the mssql directory or in windows\system32, and they may change registry settings, and they usually run 'change scripts' against your system databases so Master or MSDB end up being different, and on and on. But worse, if an existing file needs replacing, it is harder to back out the change and restore the 'old' version of the file. Backing out a service pack is a nightmare, if it is even possible at all. It is usually easier to blow away the installation, and install from the media all over again, then apply the service packs/hotfixes to get it back to the exact same point version that it was before (SP1 or 2 like it is today).
Many times, applying a service pack will yield no problems in more environments. This leads many people do perform on-the-fly fixes directly on production without testing. However, sometimes it fails and it is a REAL pain to recover from. So it is advisable to use a test system in all cases, even for a tiny 'harmless' hotfix.
Jim
Great advice..
Regards
Sushant Kumar
MCTS,MCP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply