Post reply

Upgrading database from 2000 to 2005

  • April 28, 2009 at 2:12 am

    #208555

    Are there any reasons as to why you can't upgrade a database straight from SQL2000 to SQL2005. Is there anything you need to be careful about, redesign etc.

    I ask as one of our suppliers has an application written in FoxPro 9 thats uses a SQL backend. When they upgraded to the last version they just converted a Fox database to a SQL database with minimal coding changes. This gave shocking performance, mulitple threads, and many errors which they slowly attempted to correct. I really don`t want to go back through the same problems if we can help it !!

  • April 28, 2009 at 2:41 am

    #984892

    Simon Smith (4/28/2009)

    Are there any reasons as to why you can't upgrade a database straight from SQL2000 to SQL2005.

    You can. I've done so on a number of occasions.

    There are some things that you need to do after the upgrade.

    DBCC UPDATEUSAGE because SQL 2000 was a little lax in keeping the space used metadata correct

    Update all statistics because SQL 2005 keeps more detailed stats than 2000 did and while the optimiser can use SQL 2000 stats, it doesn't do so very well.

    DBCC CheckDB with DataPurity because there were some forms of corruption that SQL 2000's checkDB ignored.

    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

  • April 28, 2009 at 2:47 am

    #984895

    Thats great stuff. I`ll ensure that the relevant tasks get done. I have no belief that the suppliers would even understand what you have recommended !

  • April 28, 2009 at 2:56 am

    #984903

    Bear in mind that there have been changes to what's considered valid SQL in SQL 2005. If the app uses any of that it will break on SQL 2005.

    Perhaps I should have said, there's no problems providing you've done sufficient testing and have fixed any deprecated and removed features.

    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

  • April 28, 2009 at 3:00 am

    #984906

    I`m advising our mangement that upgrading is not the way forward as I don`t believe the application will work correctly. For example the concept of locking seems to have bypassed them and locking whole tables whilst reporting is common.

    I could do without the hassle of setting up a test environment, testing, etc

  • April 28, 2009 at 3:12 am

    #984919

    Simon Smith (4/28/2009)

    For example the concept of locking seems to have bypassed them and locking whole tables whilst reporting is common.

    *twitch* Ouch

    I could do without the hassle of setting up a test environment, testing, etc

    If they do insist on upgrade, there's another thread discussing code changes - http://qa.sqlservercentral.com/Forums/Topic703774-361-1.aspx

    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

  • April 28, 2009 at 3:15 am

    #984924

    Great stuff. Time for some reading before I start upsetting people.

    Thanks again for your help.

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

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