Migrating MOSS 2007 from SQL 2000 to SQL 2005

Question

We have an installation of MOSS 2007 that is using MS SQL 2000 as the backend database. The web front end for MOSS is on one server and the database on a separate server. We would like to move the database to a MS SQL 2005 server. Is there a recommended approach for doing this? Does anyone know of any resources that offer tips, tricks, tutorials, best practices for doing this?

Answer 1

I suppose since you're just migrating the database(s), staying with the same version of Sharepoint, general recommendations for migrating a database from SQL Server 2000 to 2005 should apply.

Answer 2

• Shutdown SharePoint services
• Backup SQL 2000 databases (config, content)
• Retore backups to SQL 2005
• Run "stsadm -o setconfigdb -databaseserver "
• Remove content database connections in "Central Admin > Application > Content database"
• Add new connection to content database in "Central Admin > Application > Content database"

Answer 3

I do not know if it's tacky to answer your own question, but none of the existing answers quite worked for me.

I ended up recreating the farm. Specifically, I...

1. Detached from the existing content databases by running SharePoint Products & Technologies Configuration Wizard.
2. Copied the relevant content databases from the SQL 2000 server to the SQL 2005 server. That is, saved back-ups from SQL2000 and then restored them to SQL2005.
3. Re-ran the SharePoint Products & Technologies Configuration Wizard to create a new farm. This creates a brand new configuration database.
4. When setting up the Admin SSP, I made sure that I specified the existing content database that had been moved to SQL2005.
5. Reconfigured SSPs.

This approach, while rather drastic, had the added benefit of allowing me to rename the database, SSPs, settings in IIS (i.e. web sites and application pools). Our installation was an in place upgrade from SPS2003, so the database names and SSPs were a little goofy. Recreating the farm allowed me to reconfigure things to be more similar to the default MOSS 2007 installation.

Answer 4

I appreciate this is a very old post but in case anyone else is planning to migrate to a new SQL server in MOSS, I have posted some guidance based on my experience over at http://mossblogger.blogspot.com/2009/10/migrating-to-new-sql-server-in-moss.html. I hope that helps.

Cheers, Benjamin Athawes.