​One of the most common mistakes I see in a SharePoint installation is the lack of SQL Alias’s. The person installing never knows they made a mistake. The mistake isn’t realized until O&M when the SharePoint Admin are asked to move databases or servers around. Remember, SharePoint stores a lot of information about it’s database. So, make your life easy, use an Alias. Then we can change where SharePoint is pointing at any time.

Steps:

Setup the Alias on SQL

  1. Go to SQL Server Configuration Manager –> SQL Server Network Configuration –> Protocols for SQL –> TCP/IP
    • Clear the TCP Dynamic Ports and TCP Ports for All the IPS (IP1, IP2, IP3, etc…). Even if it is set to 0, clear it.
      TCPIP
    • In the “IPAll” set a non-standard (i.e.: not 1433) port. Ex: 41000
    • Restart the SQL Server Service. Note: you can do this right in the SQL Server configuration manager. Just click on SQL Server Services on the left –> right click SQL Server –> click restart
      restartsql

Setup the SharePoint Servers

NOTE: This must be done on all Application and Web Front End Servers in your SharePoint farm

  1. Go to c:\windows\system32\cliconfg.exe
  2. Click the Alias tab
  3. Add an Alias
    • Network library: TCP/IP
    • Name: {Your SQL Alias Name Here}
    • Port: Use the same port you setup in the SQL steps on the previous section. Ex: 41000
    • Server: {your servers name here}
  4. Test Connection
    • Create a file called TestDBConnection.udl and open it
    • Enter the alias in the “select or enter a server name”
    • Switch the log on to “Use Windows NT integrated security” (note: this assumes you are logged in as someone with access to the database)
    • Confirm the databases show in the drop down under “Select the database on the server” and click “Test Connection”

Now you can use the Alias name you setup on your App and WFE servers in your SharePoint farm when you do your SharePoint installation. Thus, if your SQL server ever has to move or is renamed, you now have an easy way to reconnect by going back to your APP and WFE servers and changing the server property of the Alias.