Install & Configure SharePoint 2013 with SQL Client Alias
Though this topic is very simple and highly recommended approach in the enterprise deployments, I have seen many deployments which are not following this approach. E.g.: Using SQL client alias for SharePoint installations will be really useful if you want move all databases to another SQL Server by just making alias change point to the new SQL server. In this post I’m going to cover how we can install & configure SharePoint 2013 Preview with SQL Client Alias. Though this post is talking about SharePoint 2013 preview, the underlying concept will be same for SharePoint 2010 & SharePoint 2013 RTM.
In my test lab, I have total four virtual machines configured. This post is based on a SharePoint small server farm for setup and proof of concept. I will refer to this same environment in my future blog posts, there will be more servers adding to the existing server farm.
litdc : is the domain controller and AD server (domain : litware.local), this machine is installed with Windows 2008 R2 + SP1
litsql1 : is the SQL server machine which is installed with SQL Server 2012 RTM with Windows 2012 Server Release Candidate.
litsp1 : is the one of the SharePoint Servers, I’m configuring this server with WFE role. This server is installed with SharePoint Server 2013 Preview with Windows 2012 Server Release Candidate.
litsp3 : is the one of the SharePoint Servers, I’m configuring this server with Application role (central administration and other services). This server is installed with SharePoint Server 2013 Preview with Windows 2012 Server Release Candidate. Name of the server is litsp3 as I’m planning to add one more SharePoint Server litsp2 to with WFE layer later.
In this post I’m only concentrating on the part of configuring SharePoint Servers with SQL Client Alias.
# Step 1
To harden the security for SQL server it is highly recommend to install SQL Server with named instance with custom port and block all default SQL specific ports. So, that is our first step to consider while setting up the SQL Server.
While installing the SQL Server in litsql1 server I have installed it with named instance “litsql1\sql1”.
After the installation of SQL Server with named instance, we have to assign a custom static port number for the SQL Service. You can do this in “SQL Server Configuration Manager”. We have to configure it by taking the TCP/IP properties of “Protocols for SQL1 (SQL1 instance name)”. By default, whenever we install SQL Server with named instance it will assign a “TCP Dynamic Port”, we have to clear it out in the same location (just before TCP Port).
After doing the above step, we have to restart the “SQL Server Service” to use the newly assigned port.
To confirm the usage of the new port, you can either look at the SQL Server Logs in the SQL Server Managements studio or can look at the windows application event logs directly.
# Step 2
Once the above port validation is over the next important step is open the custom port for In bound connections if you have firewall enabled. Just need to create a new Inbound Rule for allowing connections for the custom port, in my case it is “65000”
# Step 3
Alright, now we can go to SharePoint side. In my case there are two SharePoint Servers to be configured, one WFE – litsp1 and one Application Server – litsp3. Since I’m going to host central administration in litsp3, I’m going to configure this server first. Main configuration needs only before running the PSConfig. I have installed SharePoint 2013 Preview in both of these servers initially.
Before running the PSConfig , we have to configure the SQL Client Alias. There will be two versions of cliconfig in 64 bit operating system.
C:\Windows\System32\cliconfg.exe - 64 bit version of cliconfig.exe
C:\Windows\SysWOW64\cliconfig.exe – 32 bit version of cliconfig.exe
SharePoint 2010 & 2013 are 64 based so we have to configure the 64 bit version of cliconfig.
C:\Windows\System32\cliconfg.exe
In my test case I have created alias “spsql” with network library type “TCP/IP” (don’t use named pipes). In the Server Name textbox we have to give the SQL Server name (litsql1) and then uncheck the “Dynamically determine port” option and give the custom port number, in my case “65000”. After saving the changes you can validate the registry settings to make sure that it is applied correctly.
# Step 4
At this point we are good to run SharePoint PSConfig and provision a new server farm. While creating a new farm, provide the SQL Client Alias “spsql” instead of the the SQL Server Instance name “litsql1\sql1”.
After finishing the PSCofig tasks, central administration site will be provisioned the SQL Server name will be used as “spsql”. To connect other servers to the SharePoint farm we have to repeat step #3 in all servers. In my test lab I have one more server to be added to the same farm.
Here is the download link for free eBook of “SharePoint 2013 deployment guide”
Comments
Anonymous
July 07, 2013
Very needfull post that i found on this topic.. Thanks :)Anonymous
July 19, 2013
Thanks a lot, just what I was looking for!Anonymous
August 26, 2013
very useful post for me too :) million thanks :)Anonymous
November 13, 2013
Thanks a lot your guide is the best for this topic, so noboby has a total aproach, because other guides only explain in a stand a lone server. You are great whit this help.Anonymous
February 12, 2014
In our case, the Database Access Account should be in Domain Admin group. It would not connect to database until we added DAA to the goup.Anonymous
April 23, 2014
The comment has been removedAnonymous
April 24, 2014
max - Please publish your solution for the SP2013 client - we have the same issue.Anonymous
May 11, 2014
The comment has been removedAnonymous
August 11, 2014
Nice Post.. You can also get the details information for same configuration on codecreature.wordpress.com/.../create-and-configure-sql-server-instance-and-alias-for-sharepoint-installationAnonymous
December 11, 2014
Thanks for putting this together!Anonymous
April 27, 2015
Very good quality article. Appreciate your effort. I have added inputs how to move/migrate sharepoint db during which sql client alias is very important and best practice adicodes.com/move-sharepoint-database-to-another-database-server Hope this helps someone who is looking forAnonymous
May 11, 2015
Very good. I wish I had seen this before I started my install. I have not completed the configuration yet, is it too late to create the aliases?Anonymous
July 05, 2015
This blog is very informative and clear explanation staygreenacademy.com/sharepoint-trainingAnonymous
January 13, 2016
So the 32bit version of CLI config is in "SysWOW64" and the 64bit version can be found inside "System32". Is this an error in your post or is this Microsoft's style for making jokes?