Migrate 2000 -> 2005

I've got the responsibility over a small SQL server with 8 Database and approx. 100GByte data online. Most database are used and need to be available 24*7*366. Also a number of local data transformation packages are defined and used on a regular basis. Furthermore a number of third party applications rely on this database.

My question if it is worth the effort to migrate this to 2005? I know there are all kinds of nice migration wizards but do they really work? What if one of the 3rd party apps don't like 2005?

Love to hear both good and bad experiences migrating -> 2005 .

1. SQL Server 2005 can be installed side by side with SQL Server 2000 successfully. So you can install SQL Server 2005 on the same machine where SQL Server 2000 is installed. But make sure you choose a different instance name for SQL Server 2005. Otherwise, setup will performs upgrading. After you install SQL Server 2005, you can transfer data from SQL Server 2000 to SQL Server 2005. At the same time, your SQL Server 2000 is not affected. After you think SQL Server 2005 runs well and stable, you can switch to SQL Server 2005. If required, you can uninstall SQL Server 2000.

2. You can upgrade SQL Server 2000 to SQL Server 2005 directly. During setup, make sure you choose the same instance name of SQL Server 2000 for SQL Server 2005. Follow the UI, which is very simple.

3. Or you uninstall SQL Server 2000 and saved database, followed by installing SQL Server 2005 with attaching the saved database.

Among, these three approaches, the first one is prefered if your machine resource is not an issue, which is pretty safe. At the same time, you will not affect all application depending on SQL Server 2000.

Before you do any major operations, please back up your database first.

|||

Thanks Chen, did not know 2000 and 2005 can run side by side but this will greatly increase the possibility of a migration. I don't like the idea of an upgrade only to find out that one of the zillion applications doesn't like 2005 and not being able to rollback :-) Still one question: can I attach the 2000 database devices (mdf, ndf and ldf) to a 2005 directly? Or should we go through the full backup/restore route?

What will happen to the data translation packages? 2005 doesn't have data translation packages so we will not be able to modify them.

Microsoft SQL Server for the Oracle-centric

I've recently taken over management responsibility for an application that uses Microsoft SQL Server 2000.

My experience is primarily in Oracle and I've already self educated myself about some of the differences (such as needing to initiate a BEGIN TRAN if I want a rollback segment.)

I couldn't readily find any resources that would coach me in the core differences between Oracle and SQL Server and I wondered if someone could point me in the right direction.

Thanks for your help.Basic Oracle to SQL dictionary

Tablespace = Filegroup

Archivelog = Full Recovery Mode

NoArchivelog = Simple recovery Mode

AutoArchive = well, there ain't no Santa Clause either...

Redo Log = Transaction log

Rollback Area = Transaction log

Block = Page

Extent = Extent (except you have no control on the size)

Sequence = Hmm. About the Easter Bunny...

sys = sa

system = sa

Control File = Remember when you put a tooth under your pillow, and got a quarter...?

System Catalog = Two dozen tables whose names all start with "sys"

SQLPlus = Query Analyzer

Enterprise Manager = Enterprise Manager...sorta

This enough to get you started, or was there a particular area you needed?|||Perhaps this guys book would be useful to you.

http://www.chriskempster.com/

Hans.|||I'm fairly comfortable with the tools and the layouts. So most of what you posted is already absorbed.

Let me give you an example:

Myself and my co-worker are both on this project straight from an Oracle shop. We've had no formal training as MS SQL DBA or programmers.

I knew about BEGIN TRAN just from experience. When I told her about it she was slightly shocked/surprised.

I'm looking for other pitfalls/suprises/traps that I might encounter as an Oracle-Centric.
Or do you think I should just bite the bullet and take some classes?|||Hopefully you know that readers block writers in SQL Server, whereas Readers are never blocked in Oracle?|||There's a bunch of stuff here...

obviously it's slanted toward M$

If you know Oracle, SQL Server will be a breeze....

Do you know if the system is using replication or clustering?

If not...It'll be a walk in the park....

And get some good books

http://www.sqlteam.com/store.asp

Oh, and the forums ain't to shabby either...|||Originally posted by nugenth
looking for other pitfalls/suprises/traps that I might encounter as an Oracle-Centric.


Well...You're gonna need to drop the cursor conciousnous...You know how Oracle uses Cursor pointers a lot?

Doesn't work that way here. You need to avoid cursors like the plague

Or do you think I should just bite the bullet and take some classes?

They gonna pay for it? Why not...Maybe South Beach...|||And there's tons of stuff to google..

http://www.databasejournal.com/features/mssql/article.php/2170201

And the search feature of the forums are useful

http://www.dbforums.com/showthread.php?threadid=694254&highlight=oracle+comparison

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26552&SearchTerms=Oracle|||such as needing to initiate a BEGIN TRAN if I want a rollback segment.

That is incorrect.
This is the default settings for transactions in SQL Server (Auto Commit).
Look up SET IMPLICIT_TRANSACTIONS { ON | OFF } in BOL...

System Catalog = Two dozen tables whose names all start with "sys"

This is partical correct, but not advised.. Use the INFORMATION_SCHEMA Views that are used in DB2 and SQL Server but Oracle...|||Originally posted by MCrowley
Basic Oracle to SQL dictionary

Tablespace = Filegroup

Archivelog = Full Recovery Mode

NoArchivelog = Simple recovery Mode

AutoArchive = well, there ain't no Santa Clause either...

Redo Log = Transaction log

Rollback Area = Transaction log

Block = Page

Extent = Extent (except you have no control on the size)

Sequence = Hmm. About the Easter Bunny...

sys = sa

system = sa

Control File = Remember when you put a tooth under your pillow, and got a quarter...?

System Catalog = Two dozen tables whose names all start with "sys"

SQLPlus = Query Analyzer

Enterprise Manager = Enterprise Manager...sorta

This enough to get you started, or was there a particular area you needed?

sequence - identity (thought it was just a different lingo...
control file - sp_configure + sp_dboption, at least
system catalog - contents of master and msdb + system objects in every user database|||oye...it's nor Cursor pointers...

it's reference cursors...

Oracle doesn't move data around like sql server

It's all logical...

Damn, I need another Oracle project...it's been awhile....|||Are DB2 and SQL server not enough ...|||No!

You have to learn how to wash windows as well!

AND know how to make a MEAN margaritta!|||where Rownum() < 100 = select top 100

Originally posted by nugenth
I've recently taken over management responsibility for an application that uses Microsoft SQL Server 2000.

My experience is primarily in Oracle and I've already self educated myself about some of the differences (such as needing to initiate a BEGIN TRAN if I want a rollback segment.)

I couldn't readily find any resources that would coach me in the core differences between Oracle and SQL Server and I wondered if someone could point me in the right direction.

Thanks for your help.