How to change SQL server in SharePoint Farm

If you need to go through some steps of disaster recovery plan with your SharePoint 2010 Farm after falling down your MS SQL server (of course if it’s not on the same machine with your SharePoint), there are some steps to do it. I’ll describe steps how to install another instance of MS SQL Server 2008 R2 with another name and actions after restoring databases from backup. The steps are:

1. Install new SQL server instance with any name
2. Restore all databases from backup
3. Add your SharePoint admin to Windows administrators and administrators of SQL server
4. Add alias equivalent to old MS SQL server name so SharePoint servers could connect to it. Below screenshots how to do it.

The steps 1-3 are simple and obvious, but it's not enough for normal functionality of SharePoint. Untill the 4th step your SharePoint servers won't connect to SQL Server. So you need to add alias to MS SQL Server R2 instance.

How to add alias to MS SQL Server 2008 R2

Open in Start menu Microsoft SQL Server 2008 R2 / Configuration Tools / SQL Server Configuration Manager (Figure 1)

Figure 1. SQL Server Start Menu

Expand SQL Native Client 10.0 Configuration, go to Aliases (Figure 2), right click and choose from context menu item "New Alias" (Figure 3).

Figure 2. SQL Server Configuration Manager

Figure 3. Add new Alias

In popup windows enter in "Alias name" the name of old SQL Server, "Port No" – port number, "Server" – new name of SQL server.

Figure 4. Alias parameters

I advise you to add alias not only for x64 version but for 32bit SQL Native Client 10.0 Configuration too, especially if you have custom applications with your SharePoint server or SQL server.