The best new method for moving SharePoint 2007 databases to a new database server!!!

So my team was up working for 18 hours on moving a new enterprise client SharePoint databases to a brand new database server. We followed this article, which was supposedly the best practice for moving the databases to a new database server. http://technet.microsoft.com/en-us/library/cc512725.aspx

After following this procedure we had several farm errors and had to roll back. We then took a moment to think about how SQL interacts with SharePoint and vice versa.

1. SharePoint servers all reference the SQL database server individually when serving content to end users and performing operations such as an index, excel web services render, InfoPath form render (app server roles). The servers know to look at the database server through this registry key. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB.

2. The SharePoint config databases also store the SharePoint server names in them as parentid = 'InstanceID_GUID'.

This is why many people are forced to either use the -renameserver command in stsadm or build a new farm and migrate all content databases over to that new farm when choosing to change the database backend.

Alas the excellent solution, to this highly annoying issue that even Microsoft will be publishing in a few months based on our call tonight.

So what is this solution you ask? Think of how nice it would be if there were a single place to go to change the database server name and it worked every time. There is, it’s called SQL Alias’s.

If you have and old database server and you want to change the server that the farm points to for all databases including the config database. All you have to do is install the SQL client tools on each server. Setup alias's with clicfg.exe and create an aliases that says the old server name = the new server name.

If you are using named instances this works as well. Just create an additional alias with the /instance name included. You have to create two aliases when you have instance names involve because some SharePoint functions operate directly with the SQL server (Central Admin Functions), and some need the instance name specified.

This farm supports 20,000 users and all testing so far indicated zero reduction in performance.

Here is the tutorial as promised.

Perform following steps before creating alias and moving the SharePoint databases to new SQL server instance

• Quiesce the SharePoint farm


• Backup all the database on old SQL server (OldServerA\i4)
  Note: The SQL is on Microsoft Cluster Service (MSCS) cluster group, they are not virtual. But they do have instances and i4 stands for instance


• Restore all the databases on new SQL server (NewServerB\i1)


• Stop all the web sites in IIS Manager (Start à intemgr à Stop each site that related to SharePoint)


• Stop the services on all the Web Front Ends (WFEs) and Application Servers (Start>Run > services.msc)


• Microsoft Single Sign-On service


• Office Document Conversions Launcher service


• Office Document Conversions Load Balancer service


• Office SharePoint Server Search service


• Windows SharePoint Services Administration service


• Windows SharePoint Services Search service


• Windows SharePoint Services Timer service


• Windows SharePoint Services Tracing service


• Windows SharePoint Services VSS Writer service

Next create the alias on all the SharePoint WFEs and Application servers

Start > Run > cliconfig
Note: you need to have admin rights to run SQL configuration manager command line tool

Click on Alias Tab>Click Add

Uncheck the Dynamically determine port
Click OK

Click Add


Uncheck the Dynamically determine port
Click OK

Final results will look similar to this.

Click Apply and OK

Restart the services (Start>Run>services.msc)
Start the web sites (Start>intemgr>Start each site that related to SharePoint)
Make sure all Application Pools are up and running.
Open Central Admin
Open Home Site