Using SQL Aliases with SharePoint by Thomas Vochten
Thomas Vochten, a former colleague of mine, wrote about an interesting way to create and configure your SharePoint farm to take into account any possible changes in your backend SQL infrastructure by using SQL aliases on your MOSS servers and therefore adding the possibility to change your backend SQL server transparently to your SharePoint configuration.
On his blog, he wrote:
"I recently found out about SQL Aliases and how to use them in combination with SharePoint installations. It enables you to define local alias name to connect to with a SQL Client, so you can change the actual connection later on. This may come in handy when switching over to your mirror database server, when moving servers, when virtualizing you database server etc. "
Furthermore he describes the actual steps to configure this. I am not adding those steps to this blog post, to have you go look at the original post instead 
I also checked with my favorite Premier support Engineer form Microsoft to check if this is supported and he confirms it is. He aslo confirmed seeing this more and more for use with Excel Services and failover scenarios as suggested by Thomas.
**** Update ****
Also worth mentionning is Thomas's comment on my question:
I wrote: "Just to be clear: this needs to be configured on the MOSS servers locally, right, and not on the SQL box? And if so, then it would need to be configured on all MOSS servers part of the farm, too, right? "
Thomas replied: "Indeed, it needs to be configured on every MOSS server. You could also do this after MOSS is already installed: configure the alias exactly as your existing connection, and change it when needed."
Thanks Thomas for sharing.