Migration of 2000 to 2005 on the same server

  • HI

    I have 2 instances of 2000 running on server DB03 as DB03\DEV and DB03\TEST. I have installe d 2005 and created new instances called DB03\DEV_TEMP and DB03\TEMP_TEST.

    My question is what is the best way to migrate so that we have DEV and and TEMP on the 2005 box? Re-Naming instances is not an option that is supported by microsoft.

    1)Do I restore all the databases onto 2005,uninstall 2000 instances,create DEV and TEST on 2005 and then attach and re-attach from DEV_TEMP and TEMP_TEST?

    OR

    2)DO an in place upgrade?

    It doesnt seem like there is much benefit in migrating on the same server because the rollback is going to be another install of sql 2000 -- may as well do in place upgrade

    Is there a way to fool a server/domain through DNS that a new instance is an old instance.

    I'm just not sure what the best option will be

    thanks

    Chris

  • I would recommed doing an In place upgrade for the instances . but make sure that you have backups of the existing databases before you start anything (and  a lots of time).

    If your application allows you to configure seperate SQL server then you can install a seperate instance and move the DBs Else there is no way you can (and should try to ) change the name of an existing instance.

      One workaround that you can use is create the 2005 instances on seperate machine. Move the database to new instances, then remove/rename the old machine. Name the new machine to the old machine name !!!!!!! Seems complicated.

    All your choice

     

  • I would avoid doing an in-place upgrade if at all possible.  You end up with a mix of SQL 2000 and SQL 2005 folder names and file locations.  SAC normally refuses to run, and I fear you may end up with long-term support issues.

    The recommendation in my shop is to detach all user databases, uninstall SQL 2000, install SQL 2005, attach user databases using SQL 2005 folder name standards.  (Simplified process... we do other things like scripting SQL 2000 jobs, accounts, etc and reloading them into SQL 2005...)

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • Totally agree with my friend Ed. An in-place upgrade is a risky business. I have been through this "upgrades" many times my advice is that you should do it as clean install as you possibly can. The reason is that sooner or later something didn't upgraded very well or was left out. Now you don't want that to happend after all "looks" like done, right?

    Cheers,


    * Noel

  • Hi Guys

    It looks like detach/fresh install/re-attach wins the vote and makes more sense.

    Thanks

    Chris

  • To have a completely clean and unbroken database you should script out your SQL 2000 database and cleanly rebuild it on SQL 2005. This has the advantage of explicitly telling you if any code has been broken by the change to SQL 2005 as 2005 is more rigorous than 2000 in a number of areas.

    Malcolm
    DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
    www.dbghost.com

  • You could migrate your butt to a job where they let you buy a new server once in a while

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

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