(to*******@gmail.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****@sommarskog.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 
