How To Configure a SQL Server Alias for Sharepoint in SQL Server 2008

A SQL Server alias is a tidy way to protect yourself from configuration changes in a SharePoint farm. Anybody who works with SharePoint on a regular basis knows that it is very difficult to move SharePoint to a different SQL Server. When disaster strikes wouldn’t it be nice if you could simply change SQL Server that SharePoint references? To do so, install the SQL client access tools on each server and create a SQL Alias in SQL Server Configuration Manager, from there your SharePoint Server can be re-homed with little effort.

Note, there are two methods to create a SQL Server alias, one for machines that already have SQL Server client tools installed, and another for machines that have no SQL components at all.  If your machine does not have any SL software installed and you’d like to creat an alias, follow the steps diagrammed below after running this in the server’s run dialog: cliconfg.exe.

For a server with NO SQL Server Tools Installed

From the command line run cliconfg.exe



Enable TCP/IP



Add a new alias as follows



Once your alias is entered, launch SharePoint Products and technologies wizard and build your SharePoint farm. 
Verify on your SQL server that TCP/IP communication is allowed in the sql server configuration manager

 

For a server that has SQL Server Tools Installed

Open SQL Server Configuration Manager



Enable TIP/IP if you haven’t done so already



Under the 32 bit Client 10.0 Configuration (32 Bit)
** yes, one would think you need to do it under the 64 bit but it looks like enterprise manager thunks down to 32 bit



Repeat the steps above for SQL Native Client 10.0 Configuration
** SharePoint uses the 64 Bit Native Client



Test your connection using enterprise manager


I
n the event of a SQL failure, or if you simply want to migrate to a different SQL Server, all you have to do is update the SQL Server Alias in each client machine to reference the new SQL Server. The example below shows how to connect using a named instance on a new server



Or just connect to another server’s default instance