Migrating MOSS 2007 from SQL 2000 to SQL 2005 - Addendum

Question

This is a continuation of an earlier question I had about moving the databases for a MOSS 2007 installation from SQL 2000 to SQL 2005. Here's the URL for the original question: http://stackoverflow.com/questions/254517/migrating-moss-2007-from-sql-2000-to-sql-2005

In my test environment, I've successfully moved the databases to the SQL 2005 test machine and things appear to be working fine. But, on the "Servers in Farm" page of the Central Admin | Operations, it still shows the old (i.e. SQL 2000) server as the Configuration Database Server. Also, it shows the old config database as being the Configuration Database. I know that the SQL2000 server and old config database (that are showing on this page) are NOT being used, because we've deactived the SQL instance in SQL2000.

I've tried "removing" the server, and get a message about "Uninstalling SharePoint products and technologies" being the better route. So, I disconnected from the test databases, uninstalled SharePoint from the test WFE server, and reinstalled it. That didn't do anything. Before uninstalling/reinstalling I also tried simply rerunning the SharePoint Configuration wizard, and that didn't do anything either.

Does anyone know how to update the Config Server and Config Database on the "Servers in Farm" page after having moved the Config and Content DBs? Is there something I'm missing or overlooking?

Thanks.

Answer 1

stsadm setconfigdb

?

stsadm -help setconfigdb

shows:

stsadm.exe -o setconfigdb
           [-connect]
           -databaseserver 
           [-databaseuser ]
           [-databasepassword ]
           [-databasename ]
           [-hh]
           [-adcreation]
           [-addomain ]
           [-adou ]

Also see Technet which says PSConfig has superseded stsadm.

Answer 2

Hey, I completely understand your issue. YOu have moved your configuration database from the previous sql server to the new sql server. Let me make one thing very clear on this post that a configuration database cannot be moved to another sql server.

Hence, you need to run psconfig and disconnect from the current farm. Run psconfig again and create a new server farm. Once that is done, create new web application/applications and then attach the site content database/databases back to them. This is the only way to get your farm restored properly with the new sql server.

Answer 3

The best way we have found to do this is to use a SQL client side or DNS alias for the SQL server so that you can easily move the SP databases to a new server and just update the alias to point to the new one. In your case you could try the steps below, server names have been changed to protect the innocent. In this scenario you will need to have the same instance name (or use default) and possibly the same port.

• Turn off SharePoint services
• Rename the old SQL server from SQL2000 to SQL2000OLD
• Create a DNS CNAME record called SQL2000 that points to new server called SQL2005
• Detach/attach or backup/restore all databases from SQL2000 to SQL2005
• Ping SQL2000 on SharePoint server to make sure that it resolves to SQL2005 and if not then run "ipconfig /flushdns" and repeat test until it resolves to new server
• Bring SharePoint services back up, should connect to new server using DNS alias