combining SQL 2000 and SQL 2005 databases on one server

Question

combining SQL 2000 and SQL 2005 databases on one server

dspain

Valued Member

Points: 51
More actions
November 28, 2007 at 2:06 am

#70542

We currently have multiple SQL Server 2000 and SQL Server 2005 databases on separate servers. We want to combine all of these onto one SQL server. Does anyone have any experience of this, pitfalls, hints, tips etc. We just want to gauge how big of a problem it is to achieve before we make the move. Is there any white papers on this?

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

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

Clive Strong SSChampion Points: 11222 More actions · Answer 1

You can house the 2000 databases in 2005, no problem. Just not the other way around.

Also, once you restore/attach the 2000 database in 2005, the internals of the database and altered for 2005. You can't go back. I didn't realise this when I attached a database to 2005 (even though the compatibility was 80) then wondered why it errored when I detached it and attached it back to 2000!

dspain Valued Member Points: 51 More actions · Answer 2

Thanks Clive, good advice. Do you know of any white papers on this issue?

Clive Strong SSChampion Points: 11222 More actions · Answer 3

I didn't actually look for any about the actual changes made to 2000 databases. I found out the hard way - luckily on a test box!

To be safe, I'd copy the database to the 2005 box (keep a backup of the 2000 db) and attach it. That way you have a backout if thnigs don't go to plan.

Also, when you move the 2000 database into 2005, you'll need to change the compatibility level to 90 to get all the extra features that 2005 brings.

dspain Valued Member Points: 51 More actions · Answer 4

Clive,

WE also have a MySQL database that we want to move onto the server. Can this be incorporated into an instance in 2005 or do we deal with this separatey?

Clive Strong SSChampion Points: 11222 More actions · Answer 5

MySQL is a seperate process all together. For this, you'll need to create use SSIS to move the data between MySQL and SQL Server.

Key DBA SSCertifiable Points: 6029 More actions · Answer 6

dspain,

You could install a SQL Server 2000 instance as well as a SQL Server 2005 instance on the same physical server.

The major drawback is that you have 2 SQL Server engines competing for resources on the same server, BUT you get all of your databases on one machine.

More details can be found at:

SQL Server 2005 Books Online (September 2007)

Working with Multiple Versions of SQL Server

http://technet.microsoft.com/en-us/library/ms143694.aspx

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP