April 7, 2009 at 8:58 pm
Hi,
I have question - when we install service packs, actually what makes us to install services pack? - in other words dont we only have to install service packs when it is really needed?...for example now we have sp3 for sql server 2005. do we really have to rush to install sp3 or wait until the time/scenerio when it is really need to be installed in server and until then apply hotfixes or security patches?
Please advise
Thanks
Joseph
April 7, 2009 at 10:29 pm
How I normally do it is, I verify no security issues exist that will affect my environment. From there I only patch SQL server twice per year, unless of a Security alert by Microsoft. Reason I do it every 6 months is it gives other people to test out the patch and Microsoft to see if there are any issues with their latest build. I have seen re-releases of SP or very fast CU so I never install it as soon as it comes out.
So simple answer is I think it depends on your environment; I would check what is fixed and see if any of those issues affect you then. If so test, and implement; if not wait and see what issues other people run with. Keep an eye on patches and hot-fixes after SP3 and see if SP3 created some new issues that don't have right now ... Just read/research; until you are 100% sure you are safe to apply it.
Set a predefined patch cycle time, state how long the patch must be out for before you consider applying, do your testing/acceptance testing and then apply to production :).
That is how I approach it... I am looking forward to how other people approach it. Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 8, 2009 at 4:49 am
Usually, I apply service packs as they come in and that too only once confirmed that they are not causing major differences to the way my production servers are configured. But, hot-fixes and patches I apply them only when needed.
April 8, 2009 at 8:59 am
thanks guys..lets say i have a system having sql server 2005 sp2. last february there was latest security patch for sql server 2005. we have service pack 3 also there. i want to know which one should I do now? right or wrong, what i am thinking is installing sp will modify structure of system dbs...either we have security issues or not, we have to install security patches to defend the sql server from threats like sql injections etcs...once we installed the sp and in-case later it afffects some of the functionality of application, we have to rollback the service packs which will be tedious job right?...thats why I concern...but i think that was case in earlier version of sql server right?
please advise
April 8, 2009 at 9:17 am
Joseph (4/8/2009)
what i am thinking is installing sp will modify structure of system dbs...
No, I don't think it will
either we have security issues or not, we have to install security patches to defend the sql server from threats like sql injections etcs...
No, not really. Go through the support document of that hot-fix Ms will mention the security loop hole
once we installed the sp and in-case later it afffects some of the functionality of application, we have to rollback the service packs which will be tedious job right?...thats why I concern...but i think that was case in earlier version of sql server right?
Always, apply it to a test server first and then install it on a production server.
April 8, 2009 at 10:40 am
It can affect the system dbs because resource db is updated when patches are applied; because if resource db fells out of sync from the sql version we can have various degrees of issues.
SELECT SERVERPROPERTY('ResourceVersion') as RescourceDB,
SERVERPROPERTY('ResourceLastUpdateDateTime') as ResourceDBLastUpdate,
SERVERPROPERTY('ProductVersion') as Ver,
SERVERPROPERTY ('ProductLevel') as SP; That code gives you important information regarding SQL Server 2005+. I had it in one case where resource DB did not get updated so underline metadata code was never update for system dbs which cause great deal of grief.
But as Krishna said always apply to test and test it with applications have a standard test cases that should be tested when new version patch gets applied. If test fails don't apply to production.
I have had to only roll out of one SP install, in last 4 years; Since the server was not 24x7 server, I was able to uninstall and reinstall everything because original install was a mess. So I can't say I have much experience with removing SP/Hotfixes that have caused production issues.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 8, 2009 at 10:55 am
One additional driver for installing SPs and HFs.
When seeking support for SQL Server from Microsoft (or indeed for any piece of software from any vendor), usually the first thing you will be asked is what SP level you are running.
If your server is below the recommended level, then you're most likely going to be asked to bring it up to date before any more help is forthcoming.
April 8, 2009 at 10:58 am
Mohit,
But I am thinking it will only overwrite the existing product version details and gets updated, are you sure even the system objects gets over written and even if they are would there be any change? I haven't seen that till now, may be i am missing something
And also I have got a question for you, how do you roll back an installed Service pack? never done that...
April 8, 2009 at 11:11 am
You don't rollback an install of a service pack. That usually requires a reinstall of SQL Server. This is why you want to test the service pack on a test server before rolling it out to production servers.
April 8, 2009 at 11:17 am
thanks Lynn I was being silly enough:-D
April 8, 2009 at 11:26 am
Heh Thanks Lynn. I have never rolled backed as I said I have uninstall / reinstall. Making sure to have my System DB backups so I was able to roll foward the server the the old patch restore system DBS and was ready to go from there.
And for system dbs changing; that is a definate yes. Search for a script called sysdbupg.sql on your SQL installs; it is in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install folder for me.
This script is executed against master db and changes number of objects. There are various scripts in this folder I am not sure for all their use. When I had issue with resource db version out of sync with the SQL Server version level; this was the script Microsoft had me run to fix my issues.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 8, 2009 at 11:39 am
Great ! Thanks for confirming it, Mohit:-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply