January 21, 2009 at 4:12 pm
I have SQL2000 with sp4 installed. It contains several databases. Could someone please help me understand how I can upgrade my SQL2K instance to SQL2K5?
Also, what (if anything) do I do with my existing SQL2000 databases? Are they upgraded to SQL2K5 when the Instance is upgraded? In short, do I have to do anything specific with my SQL2K databases?
thanks
January 21, 2009 at 4:18 pm
Your SQL 2000 databases are upgraded automatically if you upgrade the SQL 2000 instance. If you opt to install a SQL 2005 instance from scratch, you can either attach the SQL 2000 database to the new instance or restore a backup of the SQL 2000 database to the new 2005 instance and the old database is upgraded automatically.
January 21, 2009 at 4:23 pm
Thanks Ruprecht for that quick response about the database question.
What's the best path for upgrading my SQL2000 Instance? When I run SQL2K5 setup on the SQL2K server will it prompt me to upgrade my existing Instance? Or will it simply install a SQL2K5 instance and leave SQL2K alone?
January 21, 2009 at 4:35 pm
It will prompt you, just select the SQL 2000 instance during the setup wizard. Before you upgrade, back up everything and make sure your upgrade path is supported. For example, you can upgrade from SQL 2000 Standard to SQL 2005 Standard or Enterprise. But you cannot upgrade from SQL 2000 Enterprise to SQL 2005 Standard.
January 21, 2009 at 4:38 pm
Thanks again, I shall do that.
I'm checking out the SQL Upgrade Advisor now to see what I can learn from that.
January 22, 2009 at 6:00 am
You can do an upgrade one of three ways, an inplace upgrade, as you've been discussing, a back up & restore or detach and attach. Any of the three will migrate the database from 2000 to 2005. But once completed all three leave the database in 80 compatibility mode. You have to switch it to 90 manually in order for it to be a full fledged 2005 database.
Definately get the Upgrade Advisor and run it against your databases because some 2000 T-SQL code will not work in 2005 or will work differently. Best to get that fixed before you upgrade.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2009 at 7:05 am
You didn't specify what editions you're upgrading from/to, but just in case (or for others in a similar boat)...
The database property "auto_close" is defaulted to true for the free versions of SQL (MSDE and Express). If someone in the past changed the MSDE setting to false, upgrading to Express will reset it back to true. If that's the case, you may see a big drop in performance speed.
January 22, 2009 at 7:09 am
Thanks for that info Grant and Dcorbitt.
I'm upgrading from SQL 2000 STANDARD edition to SQL 2005 STANDARD edition and am currently checking out the SQL Upgrade Advisor, trying to understand what it's trying to tell me.
January 22, 2009 at 7:21 am
I'd reccomend running thru the upgrade process a couple of times on a dev/test server before doing your production server.
We just did an upgrade of about 8 dev boxes and 50 production boxes last year.
We found that about half of the time, the upgrade process didn't go very smoothly (never did figure out if it was the upgrade process that was buggy, or if the 2000 installs were a little corrupted) on the dev servers. So we actually ended up backing up all of the user databases, scriping all the users/jobs/linked servers, etc. (we found it to be faster than dealing with restoring system databases), uninstalling the SQL 2000 instance and installing a 2005 instance from scratch.
The Redneck DBA
January 22, 2009 at 7:25 am
You need to watch out for any *= or =* joins in the where clause as this is no longer supported!
January 22, 2009 at 7:34 am
Andrew Reilly (1/22/2009)
You need to watch out for any *= or =* joins in the where clause as this is no longer supported!
The Upgrade Advisor will catch that, as well as several other things such as the changes in the syntax on JOIN hints.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply