Configure a SQL Server Alias for Sharepoint (SQL Server 2008)

Anybody who works with SharePoint on a regular basis knows that it is very difficult to move SharePoint to a different SQL Server. I always use a SQL alias when building a new SharePoint farm.  In addition, it makes it extremely easy to clone VM's for DEV, TEST, and PROD.  And if you have a disaster, wouldn’t it be nice if you could simply change which 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 SLQ software installed and you’d like to create 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


In 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


Categories: SharePoint 2010
Permalink | Comments (0) | Post RSSRSS comment feed