March 7, 2007 at 11:33 am
Hello -
I recently discovered our 2000 production server never had any service packs installed! (Don't look at me; I'm only a programmer, not the one that set this up . . .)
There is only one database being accessed by an application on this server. That database is scheduled for upgrade to Sql Server 2005 within the next few months. Currently, everything is going along nicely -- hate to mess with it if I don't have to -- would rather wait and just upgrade to 2005.
I have read that SP4 takes care of problems associated with the upcoming time change. What can I expect to happen if I don't install SP4?
Any advice/suggestions?
March 7, 2007 at 11:44 am
time changes are at the operating system level..there is a registry patch for the operating system to change the daylight savings time on the new date available from microsoft...
sql server gets the time from the operating system, and SP4 came out long before the determination was made to change the Daylight savings time on a different date than historically done...
unpatched servers are vulnerable to a number of attacks. you may have heard of the SQl Slammer worm?..regardless of the planned upgrade , patch the server now.
Lowell
March 7, 2007 at 1:33 pm
Hi Lowell -
How long does it take to patch 2000 with SP4? I need to figure out how long the server needs to be down. I just installed the service pack for Sql Server 2005 on my developer machine. It took forever!
Any frequently encountered problems that you know of with SP4 and Sql 2000?
March 7, 2007 at 1:47 pm
it's been a couple of months since i last installed an SP4 on a SQL server, but it went less than 15 minutes as i remember; if your server is running AWE or has huge amounts of ram > 4gig , as i remember there could potentially have some issues, but a normal non-clustered server hosting 1 main database, i wouldn't worry.
we have 30 or so servers in our shop, but they are all stand alones or dev machines, unclustered, and only two have 16 gig of ram or more; all upgraded without problems. I THINK the ram problem was after SP4 the server was not using all the ram available, and performance sucked because of it...never happened to me, but i read about it.
Lowell
March 7, 2007 at 2:13 pm
When I installed SP2a on 2005, it advised that various services should be shut down before installation to prevent having to reboot. What difference does it make if you don't shut down the services and just reboot?
Likewise, in the literature about SP4 for 2000, they recommending shutting down various services. Is that really necessary and if not, in what circumstances would it be beneficial?
March 7, 2007 at 3:16 pm
Some people don't want to reboot their servers....they may be production servers with other applications running. You have two options...
1. stop the services listed, apply the SP, restart the services
or
2. apply the SP, reboot the server
Either way works.
-SQLBill
March 7, 2007 at 3:42 pm
If you have > 2 GB of memory and you are using AWE, you will also want to apply this patch after the SP4 upgrade:
March 8, 2007 at 5:04 am
actually I'd advise applying the 2187 post sp4 rollup too, I'm told there were some problems with sp4 that are fixed in the rollup ( which also includes the awe patch )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 8, 2007 at 5:59 am
The time it takes to apply the SP will vary. On server speed, I/O etc AND if you have no SP, versus SP1, SP2, SP3. The reason is that the SP's are cumalative. So, since you have no SP installed it will have to indirectly patch stuff for SP1-Sp4 so it will run a little longer vs. going from Sp3 to SP4.
As you step through the steps to apply SP4 if you do not stop some services it will warn you that a reboot will be required if you do not shutdown the services.
I typically stop all unnneeded services when I apply a service pack as I remember when applying SP3 a few times that the service pack hung on a service being up. In the README of the service pack they recommend you stop all unnneeded services AND SQL Server (unless it is a cluster).
March 8, 2007 at 6:47 am
I would spend all my time testing the upgrade to 2005, rather than going from RTM to SP4 on the current box, assuming that the "next few months" timeframe is accurate.
It is not really safe to apply SQL SPs on a production system without thoroughly testing the results, so you will be doubling the amount of regression testing required in the "next few months".
March 8, 2007 at 8:46 am
If you are planning an in-place upgrade, you will need to be at SP3a or better. So you will eventually have to apply the SP.
March 8, 2007 at 11:26 am
Thanks, everyone, for your feedback.
Scenario: This is a normal, non-clustered server hosting one main database.
Jeff - I like your answer because it saves time. You also said "It is not really safe to apply SQL SPs on a production system without thoroughly testing the results." What do you mean by "thoroughly test the results"? I'm assuming it would be enough to just go through the functionality of the program to make sure all the buttons are working correctly . . . but I'm just a programmer.
Tom - What do you mean by an "in-place" upgrade?
March 8, 2007 at 11:31 am
An in-place upgrade simply is like if you had a PC with Win2000 and upgrade it to XP. So, if you have SQL 2000 running and all of a sudden one day wanted it to be SQL 2005 you would shutdown SQL Server, back everything up and used the SQL2005 CD to upgrade your existing install to 2005.
The other way to upgrade is to either have another server, install SQL2005 on it, bckup and move the dbs to it and shutdown the old server.
If you do an in-place upgrade you HAVE to be at SP3 though and at minimum be at Windows 2000 SP4.
March 8, 2007 at 1:29 pm
"I'm assuming it would be enough to just go through the functionality of the program "
That's pretty much it. Actually you would want to test for anything that you couldn't live without if it broke.
There is a low probability that something would get broken by the upgrade. BUT! I've seen strange things happen.
One of those things might be a change to how the compiler optimizes a particular query. You might find that after an upgrade one query out of 1000 has changed enough to cause you pain. It might still work, but it could choose a table scan and hash instead of a seek and lookup approach to that query and crush your application in the process.
March 8, 2007 at 6:02 pm
Thanks, All!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply