You would think that SharePoint would have a simple Database Server type setting that you could change at anytime, but it doesn't work like that.  As unfortunate as this is, there is a fairly simple way of moving all of your databases to a different server or instance using a SQL Client Alias.

The SQL Client Alias basically just tells the SQL Connection String to go somewhere else.  For instance, if I have a connection string pointing to "SQLServer01SP" and I setup a SQL Client Alias to point to "SQLServer02SP", then all SQL traffic from that server will be re-directed if it specifies "SQLServer01SP" in the connection string.

This allows us to keep SharePoint thinking that it's going to one SQL Server, when in fact it is going to another SQL Server entirely.  However, there is a catch for SharePoint 2007.  You will need to backup and restore any SSPs (2007 only) that you have configured.

Here are the steps I took...

1.	Backup all DBs on the old SQL server
2.	Backup all SSPs in Central Admin
3.	Delete all SSPs in Central Admin
4.	Stop all SharePoint services in the SharePoint Farm and stop IIS.
5.	Restore all DBs to the new SQL server
6.	Setup a SQL Client Alias on all WFEs in the SharePoint farm to point the ServerInstance of the old SQL Server to the new SQL server.
7.	Start IIS, then start all SharePoint services in the SharePoint Farm.
8.	Restore the SSPs using the New Configuration option

<a href="http://technet.microsoft.com/en-us/library/cc512725.aspx">Move all databases (SharePoint Server 2010)</a>

<a href="http://technet.microsoft.com/en-us/library/cc512725(office.12).aspx">Move all databases (Office SharePoint Server 2007)</a>

<a href="http://www.mssqltips.com/tip.asp?tip=1620">How to setup and use a SQL Server alias</a>

Delete an SSP using STSADM
stsadm -o deletessp -title  -deletedatabases -force

Delete the SSP content DB
stsadm -o -deletecontentdb -url  -databasename

Where URL is the URL of the Web application from which the content database will be detached and database name is the name of the content database to be detached.