Post reply

Migration from SQL 2000 to SQL 2005

  • August 22, 2007 at 12:13 pm

    #121253

    Hello,

    I am helping with the migration of a database from SQL 2000 to SQL 2005. Are there any links or other references that people could point me to about how to go about this migration the best way? I'm looking for help on issues such as how to migrate the security, how best to move a somewhat large database (~20 GB), and so on.

    Also, I am wondering if there is a quick way to see what other databases - at least on the current SQL 2000 server - have views or procedures that depend on the current database.

    Another complication is that the SQL 2005 server is on an AD domain but the SQL 2000 one is on NT. If there are view and procedures on the SQL 2000 database, what is the best way to proceed? Is it possible to just set up the SQL 2005 server as a linked server and then update the queries to use that linked server?

    Sorry for what are probably many newbie questions. I just want to make sure I learn as much as I can about how to handle this kind of migration as well as possible.

    Thanks!

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • August 22, 2007 at 11:30 pm

    #728785

    Start off with the SQL Upgrade Advisor. You can install it when you install SQL Server 2005.

    In addition get onto the SQL Server home page on the Microsoft website and download the white paper for migrating to SQL Server 2005.

    Kindest Regards,

  • August 23, 2007 at 6:51 am

    #728882

    If you want the whole book on the migration, here's the link for SQL Server 2005 Upgrade Handbook:

    http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx

  • August 23, 2007 at 8:07 am

    #728940

    Webrunner,

    I have had good success with just backing up a SQL2000 and then restoring it to SQL2005.  On all the DBs that I've migrated this process has worked for all objects except the security.  To fix the security I just had to run two queries.  One is a select to get the SID of the logon in SQL2005 then another Update query to fix the new SID.

    --Use this to fix the logins for a data base

    --They must be run individually.

    --Cut and paste the SID from the first into the second one.

     SELECT sid FROM sys.sysusers WHERE name = <login>

     CREATE LOGIN <login> WITH <A href="mailtoASSWORD='@free1111'">PASSWORD='newpassword', SID= <sid>

    Hope this helps.

  • August 23, 2007 at 8:49 am

    #728980

    Thanks to everyone for their help.

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

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