SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Odd SQL Server Migration
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Emily_2
Starting Member

9 Posts

Posted - 02/20/2007 :  15:08:18  Show Profile  Reply with Quote
Hello All,

Soon I shall have to migrate 2 medium sized databases from SQL server 7 to 2005.
Any words of wisdom, “how to”, “do and don’t” would be greatly appreciated.

Here are the details:
SQL 7.0 SP4, on Win2000
SQL 2005 SP1, on Win2003

Sql 2005 needs to be installed on a new server (Win 2003), which will also be used as a web server (IIS6). I was thinking to go with a clean install for sql 2005 first, and after that to migrate the databases.

Thank you in advance,
Emily

mcrowley
Aged Yak Warrior

771 Posts

Posted - 02/20/2007 :  15:11:54  Show Profile  Reply with Quote
Well, first off, what is "medium sized"? I define that as about 20GB, but some folks start the bidding at 50GB for medium databases.

For most databases, you can just back them up, copy the backup to the new machine, and restore the databases on the new machine. After that, just run sp_dbcomptlevel (sp?), add the logins, and you are done. You should try to re-use the SIDs from the old machine, so you avoid dropping and re-adding all the users in the database(s).
Go to Top of Page

Emily_2
Starting Member

9 Posts

Posted - 02/22/2007 :  13:39:50  Show Profile  Reply with Quote
Mcrowley, thank you for the reply.
About the dbs size: under a G each, I should have called then small. Does the backup restore work well between SQL 7 and 2005? Curently i use sql 2000, i have no experience with sql 2005. I understand sql 2005 is quite different from sql 7; is there any major difference between 'master' in sql 7 versus 2005 (i was thinking about eventual logins issues)?
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/22/2007 :  13:52:19  Show Profile  Reply with Quote
"which will also be used as a web server"

Much MUCH better to have separate machines ...

(That's under your category of "Any words of wisdom" )

I reckon you will need to do a full QA test. SQL7 to SQL2000 have very few surprises; there are quite a few I have read of going from SQL2000 to SQL2005.

"SQL 2005 SP1, on Win2003"

I think SP2 is out now - so if you are just about to do your testing etc. then prob. best to go straight to SP2.

"is there any major difference between 'master' in sql 7 versus 2005"

Well, I believe that dbo.[sysobjects] is no more ... so if you have anything that relies on that it will need re-factoring.

Kristen
Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 02/22/2007 :  13:52:44  Show Profile  Reply with Quote
Master has changed a good deal between SQL 2000 and SQL 2005. If you use mainly Windows Authentication for your logins, you should have an easy time of it. If you use SQL Authentication, then you will just need to make up a script of your logins to run on the SQL 2005 machine. In the SQL 2005 Books Online, look up the Create Login command. You will want to get the SID (Security IDentifier) from the SQL 7.0 box of each SQL Authenticated login (Windows Authenticated logins get their SIDs from the Domain Controller), and use that in your create login commands. That way, you will not have to drop and re-add every single user on each database.

While I have not tried it myself, I believe you should be able to restore a SQL 7.0 database to a SQL 2005 machine. If you have a test machine, you should be able to prove this out yourself.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/22/2007 :  18:33:02  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by Kristen
Well, I believe that dbo.[sysobjects] is no more ... so if you have anything that relies on that it will need re-factoring.



it exists, but is deprecated. all the sys* tables are deprecated, replaced with sys.*

that is, dbo.sysobjects -> sys.objects in 2005.


www.elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 02/23/2007 :  08:06:41  Show Profile  Reply with Quote
Sorry jezemine, I did specifically mean

dbo.[sysobjects]

so if you've got that [or its cousins!] in your code its going to have to be re-factored.

Kristen
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/23/2007 :  12:53:48  Show Profile  Visit jezemine's Homepage  Reply with Quote
well, for katmai maybe. what I was saying is that dbo.sysobjects exists in 2005, but it's deprecated. all that means is it's not recommended for new development as it will go away in a future release.


www.elsasoft.org
Go to Top of Page

Emily_2
Starting Member

9 Posts

Posted - 03/02/2007 :  12:30:17  Show Profile  Reply with Quote
The migration from SQL Server 7 to 2005 went well. I was expecting some problems, as it is a big gap between the versions, but all was fine.
Thank you all,
Emily
Go to Top of Page

pareshmotiwala
Constraint Violating Yak Guru

USA
323 Posts

Posted - 03/05/2007 :  12:52:07  Show Profile  Visit pareshmotiwala's Homepage  Click to see pareshmotiwala's MSN Messenger address  Send pareshmotiwala a Yahoo! Message  Reply with Quote
also, we found that some COM object act differently with sql2005 as against sql7.
So just be very very careful. also remember to set the compatibility of your databases to 9.0 as against 7.0. the other catch is, when you restore the database, it will take it out of "FULL" mode and hence you will have to restart the Transaction logging.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000