Configure SQL Aliases for SharePoint Server

SQL Aliases are optional (but recommended), and are a great way to configure your SharePoint environment.
They allow for flexibility in case the SQL instance is changed, or the server names/IPs change then it’s possible to only “re-point” to the new servers. This way, there’s no need to reinstall SharePoint for the new server/instance.

SQL Aliases are configured on each SharePoint Server. This is very important to remember!

Let’s dive in and configure our aliases.

Run cliconfg utility on SharePoint Server

On Windows Server 2012 R2 & Windows Server 2016, we have an utility called cliconfg.
There are 2 versions: 32-bit and 64-bit.

In this blog post, we will be installing the 64-bit version (32-bit would be for apps compatibility, and located at C:\Windows\SysWow64)

• Log into the SharePoint Server
• Browse to C:\Windows\System32
• Double-click on cliconfg

cliconfg utility location

When the SQL Server Client Network Utility opens, click on the “Alias” tab, then “Add“.
Then configure the following:

1.  Network libraries => TCP/IP
2. Server Alias => The name for your alias 
3. Server name => The SQL Server’s name (for default instance) or ServerName\Instance (if named instance) or FQDN from DNS Server
4. Port number => SQL Server port number for the instance or check Dynamicaly determine port

Then click on “OK”

Alias configuration

Recap

And we have our SQL Alias setup 

Now when installing SharePoint, enter the alias when asked for the database server.

Considering a large SharePoint environment, following the steps above is a very arduous task. To automate this configuration and easily deploy on multiple servers, you can use the following PowerShell commands:

For 64-bit version:

New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo –name <your_alias> -propertytype String -value"DBMSSOCN,<your_sql_instance>,1433"

For 32-bit version:

New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo –name <your_alias> -propertytype String-value "DBMSSOCN,<your_sql_instance>,1433"

Where:

<your_alias> = The name of your alias (In my case “sharepoint”)

<your_sql_instance> = The name of your SQL Server Instance (In my case “SQL2012\SHAREPOINT”)

NOTE: Thanks for my friend Christian Keller (SharePoint Premier Field Engineer from Microsoft Germany) who suggested me to update my post including large environments.