SQL Best Practices for SharePoint Farm – Configure SQL Alias on all SharePoint Web and Application Servers

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.

SLI-1

  • 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

SLI-2

  • Repeat steps on all the web and application servers in the SharePoint farm that connect to SQL Server.

SLI-3

  • 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.

Additional References

That’s it.

Advertisements
This entry was posted in SP2010 Admin, SP2013 Admin, SQL Server. Bookmark the permalink.

7 Responses to SQL Best Practices for SharePoint Farm – Configure SQL Alias on all SharePoint Web and Application Servers

  1. Naresh Veeragoni says:

    We are trying to add an alias where the existing db server is Cluster and the new server is not going to cluster. would there going to be any issues when we add Alias names where we mention ‘Server alias’ name as clustername and ‘Server name’ as new DB server name?

    • Naresh Veeragoni says:

      I have an update and a question-

      we have added the SQL alias to the web front ends and that did the magic.

      Question:
      I just happened to check back on the alias which does not exist any more in the configuration, it could have been wiped out after the OS patches? but SP never complained and still working without any issues.. the odd thing is, when I look at the central admin, the DB name that is refereed is no longer exists but SP still works.. I am just curios and wondering if the SP web front end servers once gets connected through alias, they don’t care about checking the alias configuration again to continue to work?

      Thanks!

      • Naresh Veeragoni says:

        Please ignore my question. I found that the alias configurations exists under system32. I was looking under 64bit version folder in the previous case 🙂

  2. Pingback: [IT Pro] Configurar SQL Alias en SharePoint 2013 | JoSharePoint

  3. Pingback: Configure SQL Alias on all SharePoint Web and Application Servers | SharePoint Interests

  4. Someone at work had to explain why SQL Server aliases are a best practice for SharePoint and now that I have been exposed to the concept it actually does make sense. I am going to reference this blog post in the near future from my blog at http://whoisidaho.com

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s