Configuring SQL Server alias with SharePoint Server 2013

  • 아티클

This post will show how to configure SQL Server alias in a SharePoint Server 2013 environment and the benefits of this configuration.

SQL Server alias is very usefull in the following scenarios:

  • Upgrading your SQL Server from a older version
  • Migrating your Databases from a instance to other
  • Aplying a database switch on a Disaster Recovery

This setting is a best practice when you are creating a new Farm, because it is possible to easily switch your database instance without major changes. This happens because SharePoint will connect to the alias that will be configured and not to a specific SQl Server instance. If a SQL Server instance goes down, SharePoint can be pointed to another instance, just changing the alias’ information.

IMPORTANT: This configuration must be done on all server where SharePoint Server is installed (aka Application servers and Web Front-End servers). Don’t do this on the SQL Server.

The following steps show how to create a SQL Server alias on SharePoint 2013 Servers  using the SQL Server Client Network Utility:

1 - Run SQL Server Client Network Utility at:

C:\windows\system32\cliconfg.exe

image

2 - Select the Alias tab, and click Add.

image

3 - From the Network Libraries section, select TCP/IP.

4 - Type an alias in the Server Alias text box and the SQL Server instance in the Server Name text box.

5 - Check Dynamically Determine Port and click OK.

NOTE: If you use another port to connect on SQL Server, uncheck this option and set the correct port manually.

image

image

6 - Run the 32-bit version at:

C:\windows\syswow64\cliconfg.exe

7 - Repeat all these steps in 32-bit version of SQL Server Client Network Utility.

Now you can create your Farm, setting, on the Database Server field in Configuration Wizard, the name of the alias that you have created. If you need to change the SQL Server Instance for you SharePoint databases, you just need running the SQL Server Client Network Utility again and change the Server Name text box.

Remember to do this configuration in all SharePoint Servers that you have in your Farm.

[UPDATE 04/16/14]

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.

Comments

  • Anonymous
    January 01, 2003
    Thanks Carlos,
    This config may help many Farm administrators when a change in SQL Server instance is needed.
    :)
  • Anonymous
    January 01, 2003
    Nice post Gabriel!
  • Anonymous
    May 22, 2014
    Muito útil. Obrigado.
  • Anonymous
    August 12, 2014
    Nice Post..
    You can also get the detail information for same configuration on
    http://codecreature.wordpress.com/2014/08/11/create-and-configure-sql-server-instance-and-alias-for-sharepoint-installation/
  • Anonymous
    February 24, 2015
    Nice post, but how about creating CName Alias in Active Directory to make it easy.
    you don't need to do this on all SharePoint server every time you change sql server.
  • Anonymous
    July 01, 2015
    New-ItemProperty HKLM:SOFTWAREMicrosoftMSSQLServerClientConnectTo
    i can not access this path it give me error when i run this script in windows powershell
  • Anonymous
    October 27, 2015
    If you know the port - even if it is the default 1433 - then you should specify it and untick 'Dnamically determine port'. And then you don't need the instance name either, since only one instance will be listening on the specified port.

    If you don't know the TCP port, then specify the server and instance name, and tick 'Dynamically determine port'. This requires that the SQL Browser service is running and accessible (UDP port 1434).
  • Anonymous
    October 27, 2015
    You have confused the 32- and 64-bit locations, which is easy to do given their names.

    64-bit is "native" and is at C:WindowsSystem32 and HKLM:SOFTWARE.

    32-bit is the Windows-On-Windows "emulated" functionality, and is at C:WindowsSysWOW64 and HKLM:SOFTWAREWow6432Node.
  • Anonymous
    January 28, 2016
    You have typo on this config tool executable name. It is not cliconfig.exe, it cliconfg.exe
  • Anonymous
    December 20, 2016
    This is extremely helpful - it is the only solution for adding new server to a SP farm if SQL is using non-standard SQL port.
  • Anonymous
    February 22, 2017
    My current environment is not configured for SQL server alias. Can i do this on my production environment? If i do this, will anything hamper on my current running production environment?
  • Anonymous
    March 10, 2017
    I have a question related to this. In my Central admin default db server is mentioned as server.domain.com , recently we moved our web servers to new domain. in the CLICONFG should i be giving alias as server.domain.com --> newserver or i will need to give full name everywhere?