How to Create SQL Server Alias for SharePoint?

In My SQL Server best practices for SharePoint, I wrote a point on SQL Server Alias. Let me elaborate that by going a little deep on configuring SQL alias for SharePoint.

Why We need SQL Alias for SharePoint?
When installing SharePoint, you’ll be prompted to enter the SQL Server instance and that’s stored in the farm’s configuration. Creating SQL Alias for SharePoint enables you to change the Database Server of your SharePoint Farm! This will be a great help during disaster recovery, virtualization, or migrations.

SharePoint SQL alias is a best practice! Its difficult to move SharePoint to a different SQL Server, If you don’t use SQL Alias. When disaster strikes, just change SQL Server alias that SharePoint references.

Where to Set up SQL Alias? 
SQL Alias to be set on All SharePoint Servers except DB Server (SQL Server!)
CliConfig.exe located at C:\windows\system32\cliconfg.exe in Windows 2003. For Windows 2008 and 2012, (or in 64 bit OS), its located at C:\windows\syswow64\cliconfg.exe

You don’t need to install anything on your machine, as this SQL Alias Configuration utility comes with Windows. You can create it with SQL Server Config Manager also When you have SQL Server installed on the SharePoint server.

How to Setup SQL Alias?

Launch SQL Server Client Network Utility as stated above.

SQL Server Alias for SharePoint

Select the “Alias” tab and click on “Add” button.

Select “TCP/IP” and provide the name for the alias. Then fill in the hostname of your database server and select the “Dynamically determine port” check box.
SharePoint SQL alias best practice
Now you have defined your SQL alias name. Click “OK” to complete.

How to Verify the SQL Alias Created?

We’ve created the SQL Alias. Let’s make sure, Our SQL Alias works. Here is how: Log in to any of your servers where you created SQL Alias (E.g. SharePoint Web Front end Server)

  1. Create an empty text file, say: Test.txt, then rename it from Test.txt to Test.udl
  2. Double-click the test.udl file. From the Data Link Properties screen,  Enter your SQL alias name, choose your authentication method, and click on “Test connection” button. You should get “Successful” message.
  3. Also, in the database drop down, you should see the list of databases from your SQL Server! 
How to test SQL Server Alias

OK, Alias is configured on all SharePoint WFE/APP Servers. Now what?
It’s a best practice to set up SQL alias during the initial farm setup. If not re-run the SharePoint Configuration wizard on an established farm. If your SharePoint is already up and running, launch SharePoint Products and technologies wizard and configure your SharePoint farm with the new SQL Alias.

Don’t want to use SQL Server Alias? Run stsadm.exe -o renameserver -oldservername <oldname> -newservername <newname> on all servers to change the name of the specified server in the configuration database if the SQL Server is renamed.

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

2 thoughts on “How to Create SQL Server Alias for SharePoint?

  • April 19, 2021 at 2:38 PM

    Hi there.
    Is it possible to have different sql alias for site dbs and another one for config dbs as a result of having 2 availability groups on the sql cluster?

    Thank you.

    Reply
  • November 30, 2015 at 10:55 PM

    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!

    Reply

Leave a Reply