We are upgrading from SQL Server 2000 (32-bit) to SQL Server 2005 (64-bit).
We do not have new hardware, so my understanding is that we have to do a
migration by installing SQL 2005 side-by-side with SQL 2000, then
detaching/reattaching the databases, etc. We will be doing it all on the same
box.

The issue: to do a side-by-side means we will have to provide a named
instance for SQL 2005 (we can't use the default instance, because that would
be an upgrade from the 2000 default instance already on the machine, and
32-bit to 64-bit is not a valid upgrade path). But we don't want to have to
change all our applications and connection strings to use a new named
instance for 2005.

Can anyone recommend how we should accomplish our upgrade most easily? Can
we change the default instance of SQL 2000 to a named instance, and then
install SQL 2005 as a default instance? Or, if we provide a named instance
for 2005, can we take 2000 offline after the migration and then rename the
2005 instance back to the default in some way? We will not be continuing to
run SQL 2000 on this server after the migration has been successfully
concluded.

Or, if there is only one instance, named or not, on a server, will it be
treated as the default (i.e., connection strings will not have to specify
it)? Or is the default hard-coded to be only equal to MSSQLSERVER?

Any practical help (not just "it might work", please would be *greatly*
appreciated!

Thanks,
Jason Fisher

You can't rename an instance after setup. So that's out.
A suggestion:
It sounds like you want to stop SQL Server 2000. Save off the database
files.
Uninstall the default instance of SQL Server 2000.
Install the default instance of SQL Server 2005.
Put a copy of the SQL Server 2000 database files back and attach them to SQL
Server 2005.
That solves your default instance problem. You have stayed as the default
instance.
I haven't reviewed the upgrade during attach for SQL Server 2005 lately.
Books Online has 2000 sp2 to 2005 as a migration path, but that might
warrant a little study.
You will have trouble with the contents of the system databases: logins and
such in the master, jobs in msdb, etc. But you have the same problems if you
do side-by-side.
--
Rick Byham, MSFT
(Implies no warranty or rights)


"Jason Fisher" <JasonFisher (AT) discussions (DOT) microsoft.com> wrote