SQL 2005 migration recommendations and thoughts

[Luvsql]

We have a server running sql 2000 Enterprise with 5 different application databases. We need to migrate to SQL 2005 Standard. I would like to do the following, but was wondering if there is a better recommendation. I have already tested this on a test server.

1. Backup server and all databases (including mdfs with services stopped).
2. Install SQL 2005 as named instance on server. This will keep sql 2000 active so apps still working.
3. Detach databases one at a time and attach to sql 2005 one at a time, then modify application's to point to new instance. This will prevent me from having to move them all at once.
4. Once all databases have been moved successfully, remove sql 2000. I assume this will have NO affect on the sql 2005 installation?

 

[mrdenny]

You can do it this way, but to do this you'll need to change the connection strings of everything that connects to the databases so that they connect to the named instance instead of the default instance.

It'll be much easier to schedule an outage of all the applications on the server and simply take the system offline for a few hours and remove the default instance, then bring the SQL 2005 default instance online.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[Luvsql]

If both are installed on the same server and the 2005 is a different version (sql 2000 is enterprise, 2005 is standard) do I not have to migrate to sql 2005 named instance? The upgrade will not work with sql 2005 to downgrade the version.

I have no issue modifying the connection strings as we're in a citrix environment and it can be done very easily.

 

[mrdenny]

Correct, you can't upgrade from SQL 2000 Enterprise to SQL 2005 Standard.

You've got a couple of options on how to handle this upgrade.

1. Script out all the logins to the system, then uninstall SQL 2000 Enterprise, and then install SQL 2005 Standard. Create the logins from the script, then attach all the databases.

2. Uninstall SQL 2000 Enterprise, install SQL 2000 Standard, attach the databases, then upgrade to SQL 2005 standard.

3. Use the technique you outline above.

Personally I'd go for #1 if I was doing the upgrade.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog