Benfits of upgrading 2000 too 2005 (mode 80)?

Question

Benfits of upgrading 2000 too 2005 (mode 80)?

Edward-445599

Hall of Fame

Points: 3147
More actions
September 10, 2008 at 2:42 am

#131180

What are the benefits of upgrading from 2000 to 20005 if the database is still in compatibility mode 80?
My main interest would be the online indexing will this work in mode 80? Also will I get any general performance increases?
Many thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Online indexing won't work, neither will any of the new 2005 features. You will get the benefit of the improved query optimiser and other internal changes.

Why can't you change to mode 90?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 2

Using exactly the same hardware, data, workload etc, I saw an overall improvement of about 20% after upgrading to SQL 2005 (leaving the databases in 80 compatibility).

And... SQL Server 2000 is only in extended (paid for) support now!!

Edward-445599 Hall of Fame Points: 3147 More actions · Answer 3

Its a huge huge old system written for sql7. Databases over 200Gb, managment says it does not have the resources to spare to upgrade fully. You would laugh if I told you the company I work for.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You'll need to test carefully even if you do leave the DB in 80 compat mode. There are enough changes (to the optimiser especially) to potentially break things even running the DB in 80 mode on SQL 2005

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NicHopper SSCrazy Eights Points: 9090 More actions · Answer 5

Edward (9/10/2008)
Its a huge huge old system written for sql7. Databases over 200Gb, managment says it does not have the resources to spare to upgrade fully. You would laugh if I told you the company I work for.

I want to know which company you work for now.

Check out my blog http://www.sqlservercentral.com/articles/Best+Practices/61537/

Glen SSCertifiable Points: 6737 More actions · Answer 6

Edward,

you will not get any new and shiny features of SQL 2005, but you will still get better memory management; different behavior of response on memory pressure etc. You also did not mention what version you are switching from/to. If you' re switching from 32 bit edition to 64 bit edition; you will get additional benefits of large memory addressing; cashing.