May 18, 2007 at 12:57 am
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)
May 18, 2007 at 6:43 am
the 8.00.818 is
8.00.818 | Microsoft 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
May 19, 2007 at 11:04 pm
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,
May 20, 2007 at 11:47 am
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
May 22, 2007 at 1:35 am
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
May 22, 2007 at 4:50 am
- first of all run the SQL server 2005 upgrade advisor (the is an article on it at SQL Server 2005 Upgrade Advisor 
- 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
May 23, 2007 at 4:55 am
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
James Horsley
Workflow Consulting Limited
May 24, 2007 at 1:27 am
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