Replace SQL 2000 database server hosting your SPS 2003 (and MCMS 2002) databases with SQL 2005

Hello again,

It is been a while since I posted something, but hey, there’s more to life than working [:D]

I am currently working on a SharePoint migration project that requires migrating SharePoint Portal Server 2003 to MOSS 2007. Within this project there is also a need to replace the SQL 2000 backend with SQL 2005. Apparently the In-place upgrade has already been tried before and keeps failing, so I was asked if detaching the databases from SQL 2000, restaging the servers and install with SQL 2005 and attaching the databases would do the trick.

Well it did do the trick! On top of that the SPS 2003 servers were also running Content Management Server 2002 using the same SQL server.

This is what I did (in a lab environment):

1 Sql Server database server with SQL 2000 SP4, used by 2 application servers running both Content Management Server (CMS) 2002 SP2 and SharePoint Portal Server (SPS) 2003 SP2 in a farm scenario.

Once the complete environment was up and running, I stopped both the application servers and detached all CMS and SPS databases from SQL 2000. Next I uninstalled SQL 2000 completely from the database server and installed a clean SQL 2005 database engine with the additional SP2. Once completed I simply attached the CMS and SPS database files to the SQL 2005 configuration to finish off by starting the application servers again. Both CMS and SPS became operational again.

In my scenario everything went really smoothly because all my applications were using the same service accounts. Not being a SQL Guru, I can imagine that an issue can arise with the SQL logins that were defined in SQL2000 and given permissions on the CMS and/or SPS databases. Therefore I believe that these logins and their respective permissions need to be recorded from the original database(s) before uninstalling SQL 2000. Naturally these SQL logins need to be recreated on the SQL 2005 server and assigned their respective permissions on the reattached databases before restarting the application servers.

So replacing the database engine from SQL 2000 with a SQL 2005 database engine does not seem to have any effect on the CMS and SPS server(s) and can be considered an alternative for the in-place upgrade (that in my case seems to fail). Of course it is needless to say that if considering to use this scenario you need to have a good backup and rollback plan available. If you have the ability to install a completely new Database server with SQL 2005 next to the existing SQL 2000 server, you can try to consider to shut down the SQL 2000 server (and physically remove from the network) and installing the new server with the same name as the SQL 2000 server, attach the databases and start the application servers as described above. This way the fallback procedure becomes easier because you can bring the original SQL 2000 server back online if necessary after bringing the new SQL 2005 server down.

Hope this information helps you out.

Leave a Reply

Your email address will not be published. Required fields are marked *