SQL Alias for SharePoint


Having SharePoint connect to a SQL Alias instead of the NetBIOS name is always a good idea. The main benefit is, if you ever have to switch the SQL Server or connect to a SQL Cluster VIP address, you just change the Alias to point to the SQL Cluster name on the SharePoint Server and restart the SharePoint Timer Service and you are good to go. This can save huge amount of time and headache. Follow the steps below to create a SQL Alias on all your SharePoint Servers:

1. Perform this on the Application Server that is hosting Central Administration

a. Stop all SharePoint Services

b. Open CLICONFIG.exe from C:\Windows\System32\cliconfg.exe (64 bit version of cliconfig.exe)

c. Enable TCP/IP under general tab
clip_image002

d. Click on Alias Tab

e. Type Current SQL Server Name in the Alias Name field

f. Type Current SQL Server Name in the Server field (see screenshot below. In your case SQL Alias and SQL Server name is the same)
clip_image004

g. Validate SQL Alias

i. Create a new text file on SharePoint Server and name it “TestDBConnection.udl”

ii. Double click to open the file and enter your SQL Server Alias name

iii. Use Windows Integrated Security

iv. You should be able to see all your SharePoint databases when you click on “Select the database on the Server”

h. Start all services for SharePoint Server / Reboot SharePoint Server

i. Perform the steps above on all other SharePoint servers

 

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments (5)

  1. BlueSky2010 says:

    Good reference article – thanks!

  2. Powershell and SQL aliases says:

    When I execute something like a new-spcontentdatabase in powershell, can I use the alias as the argument to the -databaseserver parameter?

    Thank you for a great blog!

  3. Wayne says:

    I have been asked to move off of our Windows Server 2003 with sql 2005 server which  our old MOSS 2007 farm uses. It was created before I learned of the alias method so it is hardcoded to a server, oldSQLName.

    My plan is to move all dbs and logins to the new SQL server, newSQLName, and once I delete the A-Record for the old SQL server, simple create an alias with the same name as the old SQL server that points to the new SQL server on all servers in the farm?

    In short new alias would be as follows:

    oldSQLName => newSQLName;1433

    do you see any issues with this method?

  4. shouldn't be a problem as long as the Alias is set up correctly and can be resolved in DNS. SharePoint doesn't care or know about Aliases, it treats an alias as a SQL Instance

Skip to main content