First published on TECHNET on Jan 25, 2018
Hello All,
Recently I was working with a customer as they migrated between SQL servers, and was implementing a SQL alias and after sending them instructions to implement via Registry key they saw performance issues when they hit approx. 5,000 concurrent user connections. The problem was that they missed implementing one of the registry keys causing delays in SQL communication, so I thought I would the recommend methods of implementing SQL alias for SharePoint.
If implementing SQL Alias for SharePoint 2010/SharePoint 2013 there are two different ways to implement the change:
Using Cliconfg (On each SharePoint server perform the following)
a. Stop all SharePoint Services
b. Open CLICONFIG.exe from C:\Windows\System32\cliconfg.exe (64 bit version)
c. Enable TCP/IP under general tab
d. Click on Alias Tab
e. Type SharePoint Database Server Name in the Alias Name field
f. Type Current SQL Server Name in the Server field
g. Validate SQL Alias
i. Create a new text file on SharePoint Server and name it “Test.udl”
ii. Double click to open the file and enter your SQL Server Alias name
iii. Use Windows Integrated Security
iv. You should be able to see all your SharePoint databases when you click on “Select the database on the Server”
h. Start all services for SharePoint Server / Reboot SharePoint Server
i. Perform the steps above on all other SharePoint servers
j. Repeat above steps with C:\Windows\syswow64\cliconfg.exe (32 bit version)
Using RegistryKeys
a. Open PowerShell as Admin
b. Run the following commands (Remember to update alias and SQL instance with your information)
New-ItemProperty HKLM:SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo –name <alias> -propertytype String -value "DBMSSOCN,<sql instance>,1433"
New-ItemProperty HKLM:SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo –name <alias> -propertytype String -value "DBMSSOCN,<sql instance>,1433"
If implementing SQL Alias for SharePoint 2016 the recommended way to implement the change is to perform the following:
a. Create a DNS A Record pointing to the IP address of the target server
b. Configure SQL Server to listen on port 1433 on that IP address
c. Add BackConnectionHostNames entries to enable NTLM Authentication, following steps in this article.
Setting up SQL alias should be done when you create the farm, but if you missed that then I encourage you to do it ASAP so that you can be prepared for any changes that come down the road.
Pax
