To improve ease of maintenance and allow flexibility to move SharePoint databases to the different SQL server instance, one of the best practices for SharePoint Farm administrators is to configure SQL Server Connection Aliases for each SharePoint server in your farm.
To correctly configure SQL Server Alias, you must configure SQL Alias on all the SharePoint Web and Application Servers, except DB servers. Additionally, you can consider creating multiple aliases to support SQL scaling for dedicated databases for search, content, service app DBs etc.
For last few years, one of the best practice is to use Windows Utility CliConfg.exe to configure SQL Alias and avoid CName/DNS Alias record for SQL Alias especially for Kerberos (Note => One of the new guidance released by Bill Baer suggest otherwise).
You can either follow these steps to configure SQL Server Alias using CliConfg.exe or use AutoSPInstaller to install and configure your SharePoint environment which would allow you to configure SQL Alias on each server in the farm.
- Start the SQL Server Native Client Network Utility located at C:\windows\system32\cliconfg.exe. Please note that even though this in system32 directory, this is 64-bit version of SQL Alias Utility. If you have 32-bit .NET applications or WCF Services applications running on your SharePoint Servers, Please plan to configure SQL Alias with C:\Windows\SysWOW64\cliconfg.exe as well)
- On the General tab, verify that TCP/IP is enabled.
- On the Alias tab, click Add.
- The Add Network Library Configuration dialog box appears.
- In the Server alias box, enter the name of the Server Alias (e.g. SharePoint).
- In the Network libraries area, click TCP/IP.
- In the Connection parameters area, in the Server name box, enter the new instance of SQL server name or SQL Cluster Name of your SQL environment to associate with the alias (e.g. My SQL Server instance name is SP2013VM)
- Assign port TCP port 1433 or any other SQL custom port number or Select “Dynamically determine port” check box
- Repeat steps on all the web and application servers in the SharePoint farm that connect to SQL Server.
- As a closing note, please keep in mind that there are two versions of Client Config Utility for SQL Serer.
- C:\windows\system32\cliconfg.exe – 64-bit version of software needed for SharePoint
- C:\windows\syswow64\cliconfg.exe – 32-bit version of software needed for any .NET Custom Solution deployed on server.
- Step by Step Creating SQL Server Alias for SharePoint Farm – http://www.sharepointdiary.com/2012/11/create-sql-server-alias-for-sharepoint.html
- Step by Step – Moving SharePoint to different SQL Server using SQL Server Aliases – http://www.toddklindt.com/blog/Lists/Posts/Post.aspx?ID=255
- SQL Server Alias or DNS Alias – http://sharepoint.stackexchange.com/questions/24331/db-planning-sql-alias-or-cname-record