Migrating to 2005, need advice

[tootsuite]

Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?

I seem to recall lots of permissions issues that arose with 2005.

HELP

Thanks

[Erland Sommarskog]

(to*******@gmai l.com) writes:

Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

It's certainly not a bad idea to install a second instance on the
same machine, and the migrate by BACKUP/RESTORE. This is great if
you run into performance issues and want ot compare query plans. The
drawback if you install a new instance is that a lot of clients will
be affected.

A second alternative is to install SQL 2005 on a new machine, and when
the install has completed, you change the names and IP-address of the
machines, so that the clients can't tell the difference. Of course,
this means that you need to shop for hardware.

This makes it sounds like an in-place upgrade should be avoided at
all cost, but it's not that bad. But I will have to admit that I
have never done one, nor do I plan to. If you have to go that path,
I recommend that you start with installing a second instance of SQL 2000,
and restore databases on this instance. Yes, I still think it is a
good idea to keep SQL 2000 for reference for a while.

In any case, you should first set up a test environment, so that you
can test doing in-place upgrades, and at least conduct some testing
of your applications.

Here is a short list of must-do:

1) Change the compatibility level of all databases to 90. If too many
things break, you may to move back to 90, but be optimistic.

2) Run sp_updatestats on all databases. Old statistics are voided by
the upgrade.

3) If you move databases from another server, you need to rematch
users with logins. (This applies even if you don't make upgrades.)
This includes setting the database owner, if the owner is not sa.

There a few things that can break. Here is a list of the most
probable cases:

* Old-style outer-join, *= and =*. Caught by the Upgrade Advisor,
and can be avoided with compat level 80.

* WITH is now required for hints with more than one work. Caught by the
Upgrade Advisor, and can be avoided with compat level 80.

* Views that uses SELECT TOP 100 PRECENT ORDER BY. In SQL 2000 a
SELECT without ORDER BY from this view seemed to get the order of
the ORDER BY in the view definition. This was mere chance, and it
does not happen that often on SQL 2005. This is *not* caught by
the Upgrade Advisor, as far as I know, and you cannot save the
day with compat level 80.

* Passwords are now always case-sensitive.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mork69]

On Feb 12, 5:52 am, tootsu...@gmail .com wrote:

Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?

I seem to recall lots of permissions issues that arose with 2005.

HELP

Thanks

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

For tools that make this process easy please visit www.dbghost.com

Regards,

Malcolm

[Erland Sommarskog]

Mork69 (ml****@bigfoot .com) writes:

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

I agree that running the scripts is a good idea, because you can
catch all compilation errors.

However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Mork69]

On Feb 13, 10:10 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

Mork69 (mle...@bigfoot .com) writes:

The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

I agree that running the scripts is a good idea, because you can
catch all compilation errors.

However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx

In fact, if both approaches are used then the migration should be
perfect. i.e. You script out and build in order to highlight and fix
all the problems. If you keep the scripts for the objects that were
fixed then you can do the detach/attach and then recreate them.