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