I’ve seen this come up a lot so I figured I’d write a short post about it. A lot of people have been asking how to move their databases to a new server and to do this the way most people are prescribing you’ve got a lot of reading and a lot of steps to perform. The following two post provide just a couple of examples of the numerous steps and issues to consider: http://techacid.spaces.live.com/blog/cns!6D62FC28E76BE4B!230.entry and http://blogs.technet.com/corybu/archive/2007/06/01/detaching-databases-in-moss-2007-environments.aspx.
But that’s all just way too much work – who wants to deal with all those steps and what if something goes wrong and, oh, I have to rebuild my configuration database because you can’t move it using the typical approaches. Shouldn’t there be an easier way? Well, there is – use SQL Aliasing. SQL Aliasing basically just involves installing the SQL Server Configuration Manager on each server and configuring an Alias to point to your SQL Server and the good news is that Microsoft now recognizes this as a supported configuration for SharePoint.
So say you have an existing farm with all the databases on a server called MOSSSQL1 and you want to move all the databases to MOSSSQL2. To do this you can either install the SQL Server Configuration Manager on each server or if you have MDAC installed (which you should) then simply open up c:\windows\system32\cliconfg.exe (recommended over installing the SQL Configuration Manager). Go to the Alias tab and click “Add”. In the dialog that appears you can then configure your alias (Alias Name=MOSSSQL1, Server=MOSSSQL2 if using Named Pipes or the IP address if using TCP/IP). Then click “OK” to save the alias (be sure to do this during off hours as once you click OK SharePoint will now start looking to your new server for the databases).
Once you have the alias configured you can now simply detach all the databases from your old server and then copy and re-attach the databases to the new server. If you’re using Kerberos make sure you remember to create your SPNs for the new server. Keep in mind though that using this approach will mean that all of your databases will need to be moved (even non-SharePoint databases if you are connecting to those databases from any of your SharePoint servers). You can get around this if you use aliases prior to building your farm (keep reading). One thing to watch out for is your permissions – make sure you take note of what permissions each database has as some may change with the re-attach.
That’s it – your done! Wasn’t that easier than all the other crap you’d have to deal with otherwise? But wait, there’s more! If you are in a position where you haven’t yet built your farm then why not preemptively configure aliases? So perhaps you have just one SQL Server today but you anticipate the need to eventually distribute the databases across servers in the future to address future performance needs or maybe you just want to isolate the SharePoint databases from other databases in a shared server situation? Simply create the aliases that you anticipate needing and point them all to the same server for now – then when you need to move a database group you can just change the alias and move the databases in that group. If you want ultimate flexibility (perhaps you just don’t have a clue as to how you will need to distribute your databases in the future) then create an alias for each database (not sure I’d take it this far myself but it would give you the most flexibility in allowing you to very easily move any one or more databases to any other server with ease).
I’ve been using this approach for years with both 2003 and 2007 and it’s always worked great for me and hopefully others will benefit as well.