Using aliases for SharePoint databases

Aliases are good. In fact, aliases are excellent. The purpose of this post is to explain why and how to use aliases when working with SharePoint.

This is the second post in the series about building an highly available BI setup. A series that will culminate with Mark Broadbent (B|T) and my preconference at SQL Saturday Cambridge on September 12^th 2015 (either that or it will fail eminently J).

Why

So having SharePoint connect to an alias instead of the NetBios name of the server (or the named instance) is always a good idea because it allows you to move SharePoint databases without SharePoint even being aware of it (except for the occurring downtime while migrating).

In a setup where high availability is required it also allows us to manipulate the SQL Cluster Virtual IP address we just need to change the alias to point to the SQL Cluster Name on the SharePoint server name and restart the SharePoint timer service for the changes to take effect and you should be good to go.

This can save you for a lot of troubles and a tremendous amount of wasted time.

How

There are several ways to change an alias and there are aliases that works for 32-bit and some for 64-bit. It can be a bit tricky and more information about this (not SharePoint specific) can be found there: http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/08/sql-client-config-32-and-64-bit.aspx

For a SharePoint specific way to do it, please follow the steps below

1. Log on the server running Central Administration
2. Stop all SharePoint Services:
   1. SharePoint 2013 Administration
   2. SharePoint 2013 Timer
   3. SharePoint 2013 Tracing
   4. SharePoint 2013 User Code Host
   5. SharePoint 2013 VSS Writer
   6. World Wide Web Publishing Service
   7. SharePoint Server Search
3. Stop IIS on the server running the Central Administration website
4. Start SQL Server Client Network utility (type cliconfg.exe in run) – the 64-bit version of the utility is in the C:\Windows\System32 folder (no, it’s not a typo)
5. Make sure that TCP/IP is enabled

   

6. Click on the Alias tab and add a new TCP/IP alias
7. In the server alias field, write the name of the alias
8. In the server name field add the NetBIOS name of the server that alias is pointing to
9. If running on another port than 1433, write in the port number as illustrated below.

   

    

   And you’re done!

    

   For testing it Create an ODBC connection (with the 64-bit version of the ODBC tool) using the alias to test it.

    

   

    

   Happy aliasing!!!