Create SQL Server Alias in SharePoint Farm
Recently we ran in to a problem, where our production application suddenly started throwing 502 errors due to performance degradation. After analyzing few things, we suspect that our database server has some problems. As this was a production server and we didn’t want to keep this problem for a long time, so we decided to create a new instance of production database server parallel and we were lucky that we used the SharePoint best practice of creating SQL Server alias. After copying the databases to new server, it took only 5 minutes to point all the WFEs and App servers to new database server.
Right time to set up a SQL Server Alias: After installing the SQL Server and before installing SharePoint on the farm.
Why we need a SQL Server Alias: This should be the number 1 best practice during a SharePoint farm setup. Consider the scenario, One fine day database server gets crashed or faulty and there is no way to recover it or its time-consuming to recover the server and you don’t want your business site go down for a while.
When we install SharePoint on a farm, during SharePoint_Config database creation, the wizard asks for SQL Server name/IP with user credentials to proceed. SharePoint then saves this information as hard-coded at multiple places for future operations (database/registry etc.). Now if you need to change the database server in any case then you can’t simply change the server as previous server name will already be hard-coded on the farm.
So for such scenario, you create a permanent SQL Server alias for your SharePoint farm and can change the database server name/IP under the hood without recreating the whole farm as SharePoint will be continued referring the alias name in place of server name/IP.
Steps to create a SQL Server Alias:
- Go to SQL Server box and fire SSMS editor; run query
select @@SERVERNAME
- Above step will give you the name of SQL Server instance. Copy it for later use.
- Now go to application/wfe server; Open SQL Server client network utility. (I would recommend that on every machine do the aliasing on both flavors i.e. 32 bit and 64 bit)
- For 32 bit;
C:\windows\system32\cliconfg.exe
- For 64 bit;
C:\windows\syswow64\cliconfg.exe
- For 32 bit;
- Do the steps as highlighted in below screenshot. Click on Alias tab, and click on Add… button.
- Give a user-friendly name in Server alias: box, select TCP/IP in Network libraries section.
- Paste the details from #2 in Server name text box and check Dynamically determine port and click Ok.
- Do step #3-#6 on all servers in farm and you are done with SQL aliasing.
Use the SQL Server Alias, wherever a database server instance name is required (config files, farm setup etc.). This is the best way to hide the SQL Server instance name as well.
Happy SharePointing 🙂