Post reply

Migrating to 2005

  • August 18, 2008 at 3:42 am

    #127106

    Hi

    I am wondering what the best way would be to migrate to 2005! Particularly i am interested what happens with the different master db! Is it ok to install first all the DB's from the 2005 server incl. master msdb etc an then detach attach the 2000 db's?

    I have a 2000 SP3 with 5 db's and a 2005 Sp2 with 3 db's!

    Thanks

    Juri

  • August 18, 2008 at 4:03 am

    #859334

    Don't know if you done your migration already, but I found this article to be very helpful:

    http://www.dell.com/downloads/global/solutions/public/white_papers/sql2005_upgrade_wp.pdf

    hope it helps.

  • August 18, 2008 at 4:27 am

    #859341

    gtjuri (8/18/2008)

    Is it ok to install first all the DB's from the 2005 server incl. master msdb etc an then detach attach the 2000 db's?

    Keep in mind that the new server knows nothing about your 2000 users/logins. So any SQL logins will need to be either exported via sp_help_revlogin or created anew and any AD logins need to be created.

    Just creating new SQL logins may be an issue if SQL logins (from 2000) own schemas or are dbo's etc (this is because their SID's will be different).

    The databases should work on the whole, though there are several Trigger, DTS and View gotchas.

    Make sure to download and run the Upgrade Advisor (and keep lots of backups)!!!

    Good luck. 🙂

    Adam Zacks-------------------------------------------Be Nice, Or Leave

  • August 18, 2008 at 4:51 am

    #859349

    Schadenfreude-Mei (8/18/2008)

    Just creating new SQL logins may be an issue if SQL logins (from 2000) own schemas or are dbo's etc (this is because their SID's will be different).

    The logins can be scripted with the SIDs, which eliminates that problem. It's probably the best way to migrate the logins, otherwise all the users are orphaned.

    The 2005 syntax is

    CREATE LOGIN <Login name> WITH PASSWORD = <Password> , SID = <SID>

    or

    CREATE LOGIN <Login name> SID = <SID> FROM WINDOWS

    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

  • August 18, 2008 at 6:05 am

    #859380

    it is preferred to do the following post upgrade tasks to avoid performance problems after upgrading:

    - rebuild all indexes , that also lead to rebuild all statistics

    - recompile all stored procedure / functions/ triggers by using sp_recompile

  • August 18, 2008 at 8:10 am

    #859468

    Thanks to all of you:)

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

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