To minimize downtime, here's how you can proceed
2) Backup all of the SP2010 databases on the SQL Server 2005 instance and restore them on the new SQL Server 2008 R2 instance (you can also use the detach/attach option mentioned in the TechNet article, although, I try to stay
away from detach/attach to avoid database consistency issues)
3) You can either create a DNS alias that points to the new SQL Server 2008 R2 instance or use the stsadm utility or SharePoint Central Administrator to rename the SQL Server instance. For disaster recovery purposes, I configure
all my server connections using DNS aliases. With DNS aliases, you don't have to worry about reassociate the databases once they are in the new SQL Server as the connection string is transparent
I have a single SharePoint 2010 WFE with a single SQL 2005 64-bit backend. I need to decomission the SQL 2005 server. What is the best procedure to move all the SharePoint associated databases to SQL 2008 R2?
I have already read this MS doc on moving all databases:
So the actual move procedure is standard SQL fodder.
What I'm not clear about is what to do about the "other" SharePoint databases and how to reassociate them once the DBs are on the new SQL server. For example, the Usage and Health DB is going to "Wss_Logging" and it specifies my old SQL server.
It is greyed so I cannot chagne it in Central Admin. How do I let SharePoint know that these databases have been moved too?