May 11, 2006 at 10:33 am
From what I have read, the new server has to have the same OS and SQL version as the old server. Is there any way around this?
Here's the scenario: Old Server - Windows 2000 Terminal; SQL 2000
New Server was ordered with: Windows Server 2003 Standard Ed; SQL 2005
Will this work?
May 12, 2006 at 6:04 am
I am not sure how you were planning to move the databases from Old Server to New Server (there are different options for that) - but I would think if you simply took a backup of the database from your Old Server/SQL 2000 and restored it on your New Server/SQL 2005 that things would be fine.
The restore would create the database and automatically convert it to SQL 2005 (which I have had no problems with to this point).
A few things that might be helpful if you do this and when you move to SQL 2005:
1. After you restore the backup to SQL 2005, if you make a SQL 2005 backup you cannot restore that to the SQL 2000 server.
2. After you restore the backup to SQL 2005, even though the database is converted to SQL 2005, the actual database compatibility level will still be set to "80" which is SQL 2000. So if you want to take advantage of SQL 2005 fully you'll want to go to properties of database and change that compatibility level to "90".
3. If you have the same SQL Logins created on the new SQL 2005 server (assuming you're using mixed mode) that you had on the SQL 200 server. And if you had some of those as Users in the database in question, then after you restore the backup to SQL 2005 (which still should be ok), then when you try to connect to the database with the Users you could receive errors about those Users already existing. These would likely be from SID (security ID) problems between the two servers. I have resolved these SID issues by running the following against the database in question: sp_change_users_login 'AutoFix', 'User'. Or you can use sp_change_users_login 'Update_One', 'User', 'Login'. These will map the existing database User name (originally in SQL 2000) with the matching SQL login (now in SQL 2005) - so replace 'User' (and 'Login') as appropriate.
To answer your question more generally though- I do not think it is uncommon at all when upgrading to upgrade the hardware, OS, and even SQL versions and this should be fine as long as you take care of the little details and issues that can arise..
Good luck.
May 17, 2006 at 9:46 am
You might want to run SQL Server 2005 Upgrade Advisor against your 2000 database to find out what areas need fixing before it gets converted to 2005. I just downloaded it from Microsoft and it looks like a handy tool.
Changing OS should be fine. We just swapped out our 2000 server for a new one running server 2003 with no problems. Still on SQL 2000 though.
Your title says "Moving database from current server to a new one" ... Does that mean you are moving just one database to a new server, or migrating everything ?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply