Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Upgrade SQL Server 2000 to SQL Server 2008


Upgrade SQL Server 2000 to SQL Server 2008

Author
Message
jralston88
jralston88
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 689
I'm looking to upgrade 4-5 SQL Server 2000 database instances to 2008. Most are SP3 but one is RTM. Upgrade destination is SQL Server 2008 Enterprise edition (side by side upgrade). Three questions:

1. Is it necessary to upgrade SQL Server 2000 to SP4?
2. The supposed OS requirement before upgrading is Windows Server 2003, SP2. The upgrade candidates have Windows 2003 SP1 and Windows 2000 SP4. Is this a concern?
3. I want to copy the .bak file to the test SQL 2008 instance, then restore. Any issues or concerns here?

Any comments would be appreciated - thank you.
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 9585
1. I've never done an upgrade from SQL 2000 to 2008, so I'm going to leave that one alone.

2. I looked up the requirements for SQL 2008 and you're right - it does say Windows 2003 SP2 or above. I would definitely listen to this. Microsoft probably knows things about their operating system that we don't, so I would be safe rather than being sorry later.

3. As for backing up a database, moving it to another server and restoring it there - that's exactly what I would do. In fact, that's what I do when I have to copy databases from production to test. I think you'll find that this is a preferred method of copying databases from one server to another.

For the exact requirements: http://msdn.microsoft.com/en-us/library/ms143506%28v=SQL.100%29.aspx


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6974 Visits: 8839
let me answer Q1 for you : Yes

ref: Version and Edition Upgrades http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

Q3 We prefer upgrading using the migration path (backup/restore).
With inplace upgrades, the instance is configured starting from the old version configuration; That may leave e.g. security holes opened which would have been closed with a regular install. In case of DRP you would also need to start recovery from the old version, upgrading once more to be sure all settings are in place. People have been bitten by this quiet often and only regret it at the moment they really could mis it.

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
dwain.c
dwain.c
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4255 Visits: 6431
jralston88 (7/8/2013)

2. The supposed OS requirement before upgrading is Windows Server 2003, SP2. The upgrade candidates have Windows 2003 SP1 and Windows 2000 SP4. Is this a concern?


Why aren't you considering Win 2008 or 2012?

Win 2003 is almost out of extended support at this time. We're in the process of trying to get systems off of it not migrating to it.

Edit: I would also like to suggest that when you upgrade the db you go full on and go with compatibility level 100 instead of keeping it at 80 because you think it will simplify the upgrade. I think you'll find very few incompatibilities at 100.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Gazareth
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2870 Visits: 5351
If you haven't already run it, upgrade advisor will be useful:

http://msdn.microsoft.com/en-us/library/ms144256(v=sql.100).aspx
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 9585
Dwain.c raises a very good point. Granted, I never implement something in production until it's been out for a while, but Win Server 2003 is pretty close to end-of-life, which means no more support. I'd definitely consider a newer OS.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
+1 more for the Windows upgrade. Go to W2K8 R2 or, preferably, W2K12.

Also, +1 for Johan's note. Backup restore for upgrades.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jralston88
jralston88
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 689
I may have confused some of you:
I want to upgrade SQL Server 2000 instances. Their OS is Windows Server 2003 SP1. The instances have no SQL Server service pack or SP3.

Microsoft does not support anything less than SQL Server SP4 when upgrading to SQL Server 2008.
It was also my understanding that the OS on the SQL Server 2000 instances must be Windows Server 2003 SP2 before attempting to upgrade to SQL Server 2008. Is this correct?

Once this is complete, I want to copy their .bak files to the new SQL Server 2008 instance. That instance has Windows Server 2008 SP2.

Before copying the files over I will use the upgrade advisor. That's it. Sorry for the confusion...
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36146 Visits: 18751
When you describe your situation, you're not always being clear and using pronouns which are somewhat ambiguous. We also have no idea if you are talking about in place or moving to new machines. Be careful of that.

You do need SP4 on SQL Server 2000 for an upgrade.

According to this, http://msdn.microsoft.com/en-us/library/ms143506%28v=sql.100%29.aspx, you need SP2 for Windows 2003.

However, if you have a new SQL Server 2008 instance, and are moving the files to that instance, why upgrade? You can restore a SQL Server 2000 (SP4) backup on a SQL Server 2008 instance.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
SimonLiew
SimonLiew
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3500 Visits: 1771
Re your question 1.

You mention you wanted to do a side-by-side upgrade. So you don't need SQL 2000 to be on SP4 as a pre-requisite. You can perform a direct restore of a database backup on SQL 2000 RTM up to SQL 2008 R2 SP2. Upgrade Advisor SQL 2008 R2 will also work againsts a SQL 2000 RTM.


ref: Version and Edition Upgrades http://msdn.microsoft.com/en-us/library/ms143393%28v=sql.105%29.aspx

I reckon this link refers to an in-place upgrade. That is if you want to run SQL 2008 Setup against an existing SQL 2000 to upgrade the same SQL instance to SQL 2008, then it'll not work unless you're on SQL 2000 SP4 to begin with.

What's confusing is you mention you wanted to do a side-by-side upgrade (backup\restore SQL 2000 databases to another instance of SQL 2008), then you indicate you wanted to do an in-place upgrade (upgrade SQL Server 2000 instances). There are major differences between a db upgrade vs SQL instance upgrade

Side-by-side upgrade: i.e. you backup SQL 2000 db and restore it on SQL 2008 R2 somewhere else
In-place upgrade: i.e. you run SQL 2008 setup against an existing SQL 2000 instance. That SQL 2000 instance will be upgraded to SQL 2008 along with all the databases hosted. Read the mininum hardware and software requirement as per the link posted by Steve. SQL 2008 setup will not run successfully if you don't satify the minimum requirement whether its an in-place or a new install

One of the key step when the databases are on SQL 2008 is to run DBCC CHECKDB WITH DATA_PURITY. Database corruption on invalid values are not checked in SQL 2000 CHECKDB, and there's *no* CHECKDB option to check them on SQL 2000 (exception with a trace flag).

Hope this clarifies both scenario whether you wanted to perform a side-by-side or an in-place upgrade.

Simon Liew
Microsoft Certified Master: SQL Server 2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search