Upgrade sql server 2000 to 2005 on VMWare

  • Hi,

    I preparing for SQL server 2000 to 2005 upgrade. Currently MSSQL2K is running on Win2K. We are planing to upgrade both os and SQL server. OS will be upgraded to Windows 2003. Also we are moving to VMWare. With all these in mind I plan to do in-place upgrade after I setup the new server on VMware. I set up the new Win2003 server and Installed MSSQL2000. I am planing to setup the same version/service packs as my original server. In my current production server the SELECT @@VERSION reports the following

    Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

    In my new vmware server after I apply SP4 it displays different build no and service pack no as follows.

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    I don't understand how to get the same version as in production (8.00.818) in my new server. Is it dependent on OS? As my production server is Win2k and new VM server is Win2K3.

    Would be there any issue, if I port the databases from production to VM server as they on different OS and different build and version no.?

    Appreciate your help.

    With rgds,

    (UserID=310675)

  • the 8.00.818 is

    8.00.818Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031

    you can upgrade from sp3 and sp4.

    There shouldn't be an issue with the databases.

    If you have the time try building a performance baseline so you can compare pre and post upgrade.

     

    If you build a new (vmware) server maybe you'd be better off with only installing the sql2005. You might as well copy the users, databases, jobs, ... directly on sql2005.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi,

    Thank you very much for your reply.  Actually I experienced problem in recovery/restore the database on different MSSQL Build version.  Thats the reason I am looking for a help. If not I would choose the method to copy and upgrade. 

    In which, how should I copy the users to new database?

    Do you have any doc to prepare the performance baseline before upgrade to SQL2005?

    Rgds,

     

  • 1) what kind of problems did you experience ? (did you sql-backup the old db and restore it at the new server ?

    2) Users are restored if you restored masterdb. If you didn't restore master db, and the old server is stil available, you can generate a sqlscript to copy the users to the new server.

    execute this at the old server and run the results at the new server:

    select 'exec sp_addlogin ['

    + name

    + '],'

    , password

    , ', @encryptopt=skip_encryption'

    from master..sysxlogins

     

    After this you'll have to run this for every user in every db, unless you've also copied the sid for the user (check sp_addlogin in BOL)

    sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Hi ALZ,

    Thank You Very much for your help.

    I was able to restore the production database into the new server after applying one of the HotFix which as you mentioned on MS03-031.

    Now I am planing for upgrade to SQL2005.  I am planing to do in-place upgrade.  Would you advice me if I need to take care of anything during the upgrade.  Do you have any docs/URL for SQL2005 DB hardening?

    Once again thanks for your prompt response.

    Rgds

     

  • - first of all run the SQL server 2005 upgrade advisor (the is an article on it at SQL Server 2005 Upgrade Advisor&nbsp

    - for dataprotection you may want to read http://www.microsoft.com/technet/itshowcase/content/sqldatsec.mspx

    - try to apply a "minimal rights" philosophy.

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I think your confusion is on the version info - this is giving you the info of the SQL Server version/sp and the OS version/sp

    The bit in "Microsoft SQL Server 2000 - 8.00.xxxx" identifies the SQL Server version

    The bit "on Windows NT 5.0 (Build 2195: Service Pack 4)" indicates you are on Win 2000 sp4 - i.e. sp4 of the OS NOT SQL Server

    SQL server version numbers to SP's are documented here http://support.microsoft.com/default.aspx/kb/321185

    So from that you can see for your new install 8.00.2039 is SQL 2000 sp4

    Strangely the value for your original server is not listed - so it may be SP3a + hotfix

  • Hi ALZ and James,

    Thanks for your quick response.  I got the info already.  As you both said its a hotfix after sp3a.  After applying the fix I am able to upgrade the database without any issues.

    Thanks for your support.

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

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