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

clip_image001[1]

Enable TCP/IP

clip_image002[1]

Add a new alias as follows

clip_image003[1]

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

clip_image005[1]

 

For a server that has SQL Server Tools Installed

Open SQL Server Configuration Manager

clip_image001

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

clip_image002

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

clip_image003

Repeat the steps above for SQL Native Client 10.0 Configuration

** SharePoint uses the 64 Bit Native Client

clip_image004

Test your connection using enterprise manager

clip_image005

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

clip_image006

Or just connect to another server’s default instance

clip_image007


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