Author |
Topic |
Emily_2
Starting Member
9 Posts |
Posted - 2007-02-20 : 15:08:18
|
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 Win2000SQL 2005 SP1, on Win2003Sql 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 - 2007-02-20 : 15:11:54
|
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). |
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-02-22 : 13:39:50
|
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)? |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-22 : 13:52:19
|
"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 |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2007-02-22 : 13:52:44
|
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. |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-22 : 18:33:02
|
quote: Originally posted by KristenWell, 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 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-23 : 08:06:41
|
Sorry jezemine, I did specifically meandbo.[sysobjects]so if you've got that [or its cousins!] in your code its going to have to be re-factored.Kristen |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-23 : 12:53:48
|
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 |
|
|
Emily_2
Starting Member
9 Posts |
Posted - 2007-03-02 : 12:30:17
|
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 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-03-05 : 12:52:07
|
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. |
|
|
|