How to Create an SQL Server Alias for SharePoint?
In My SQL Server best practices for SharePoint, I wrote a point on SQL Server Alias. Allow me to elaborate on that by going a little deep on configuring SQL alias for SharePoint.
Why do 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 aliases are a best practice! It isn’t easy to move SharePoint to a different SQL Server if you don’t use SQL Aliases. When disaster strikes, just change the 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 a 64-bit OS), it’s 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 Set up an SQL Alias?
Launch SQL Server Client Network Utility as stated above.
Select the “Alias” tab and click on the “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.
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)
- Create an empty text file, say: Test.txt, then rename it from Test.txt to Test.udl
- 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.
- Also, in the database drop-down, you should see the list of databases from your SQL Server!
OK, the 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 the SharePoint Products and Technologies wizard and configure your SharePoint farm with the new SQL Alias.
Don’t want to use SQL Server Alias? Run the following command on all servers to change the name of the specified server in the configuration database if the SQL Server is renamed.
stsadm.exe -o renameserver -oldservername <oldname> -newservername <newname>
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.
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!