Microsoft would have tested upgrades to 2005/8 from 2000 on the supported platform at the time . At the time when they did this it was SQL 2000 SP4.
sql 2000 SP4 was release in june 2005.
It is a simple upgrade and will take much less than an hour to complete.
I would not upgrade SQL 2000 SP1/2/3 directly to sql 20005/8. i would be concerned that if you ran it in SQL 2000 compatibility mode, you would experience database issues as that compatibility mode is equal to SQL 2000 SP4.
mainstream support for any version of sql 2000 was retired in august 2008. there is still extended support available until 2013 but this will be for large companies only.
There were some significant issues with upgrade from SQL 2000 SP3 to SP4 as I recollect it (disastrous query plans where implicit casting was used in JOINs if my memory serves me).
That suggests that a full regression test would be advisable even between Service Packs ...
... and, backward-compatibility mode or not!!, I would not move to a new version of SQL Server without a full regression test ...
... and extrapolating that given that the regression test would be required anyway I would change to latest version [compatibility] so as to have the benefit of new features etc. as well.
But ... for side-by-side that might require regression test for all existing database in order to move them to SP4, plus regression test again when they were moved to SQL 2008.
So we put SQL 2008 on a brand new machine and migrated the DBs to the new server. (Also has the possible benefit that no databases on our SQL 2008 box are running in compatibility mode [I don't know if compatibility mode robs any CPU cycles? e.g. if running a DB in SQL2005 or SQL2000 compatibility on SQL 2008 box?]
This is a very relevant discussion to me but I cannot work out what you concluded.
Does Kristen’s post suggest that we DO NOT need to upgrade to SP4 on a 2000 installation before upgrading to 20008/2008R2, provided we do a database migration to a new installation on a different machine?
We are in that position now. We are installing SQL2008R2 on a new machine and want to migrate our SQL2000 database (that are still SP3a). We would prefer not to have to do an upgrade to SP4 on our old machine before doing the migration.