Establishing a SQL alias for SharePoint access is a best practice, though not required. Learn the necessary steps for creating an alias rather than hard coding the server name into the SharePoint instance.
- [Instructor] Another useful but optional way to help SQL and SharePoint to communicate is using a SQL alias. The alias can be configured to refer to the server and/or instance that will hold SharePoint's databases as well as specify the custom port if necessary. Windows server 2016 and 2012 R2 have a client configuration utility to set up an alias for the SQL Server. This is useful in case SQL Server names and IP addresses change during the lifetime of SharePoint.
When you configure SharePoint during installation, the location of the SharePoint configuration and administrative settings and content databases is hardcoded into the implementation. If that needs to change, there's no easy way to do so. To cope with that, we can create a SQL alias on each SharePoint server in the farm that points to the SQL Server that we specify. So that alias can be hardcoded into SharePoint but we can change what that alias points to on each SharePoint server if we have to without having to reinstall SharePoint.
There are two versions of the client configuration utility, one for 32 bit and one for 64 bit. Both are named Cliconfg, C-L-I-C-O-N-F-G and it's spelled that way to conform to the old eight-character convention. For applications that are 32 bit, there's a Cliconfg executable in the Syswow64 folder under Windows. For applications that are 64-bit, there's a Cliconfg executable in the system32 folder. SharePoint is a 64-bit product, so technically you only have to configure the 64-bit version.
However, the two versions don't share lists so I suggest configuring both just in case you ever have a 32-bit application that uses SharePoint. And to be clear, the alias must be configured on each SharePoint server and they must be identical because they have to match what was hardcoded during the installation of the first SharePoint server that created the farm.
To configure the SQL alias on the SharePoint server go to the Windows folder which I'm going to do by going to this PC, opening up the local disk, going to Windows, then go to system32 because I'm going to set up the Cliconfg in system32 and I'm going to assume that you can do the same thing in the SYSWOW64 folder. So you don't have to watch me do it twice. So here I am going to the Cliconfg file so I'm just going to actually navigate to see and then CL, it's a little trick, bang.
You go right to Cliconfg. I'm going to open it up and in the SQL Server client network utility, I'm going to the alias tab and click add. I'm going to make sure that I've selected TCP/IP as my protocol. The server alias, I'm going to name it SPDB for SharePoint database. For the server name, my server's name is SQL1. Make sure that you know the name of your SQL Server so that you can put that in there.
If you were to have an instance you'd also put in the instance name. I am using the default instance. So all I need to do is refer to the server name. I am not going to use a dynamically determined port. My port needs to be specifically 1433. If you're using a different port you would put that port number in here and then click okay. And here we are, the server alias name, the protocol we're using and the connection parameters, the server name and the port.
Click okay and we're done. Simply do the same thing with Cliconfg in the Syswow64 folder in order to also accommodate 32 bit applications. Now this may seem a little bit underwhelming. It's anticlimactic. How do you know that actually worked? Well, here's a way to test it. I'm going to create a UDL file. It's really easy to do. All you need to do is create a text document and I'm going to call this Testalias.
I'm going to open and save it with a different extension, so I'm going to choose file, save as. Save as type, I'm going to do all files, so it doesn't automatically add TXT to the end and I'm going to type .UDL and save. And now you can see on the desktop, I dragged the file down, can you see that? Testalias does not look like a text document anymore. It's actually a datalink file. If you open it up, that's not a text document.
All you need to do to test your alias is type in your alias name which in my case was SPDB and choose your authentication. I'm logged in as my SharePoint setup account. I'm going to use Windows NT integrated security and that's because, as you can see right here, I can prove it, on the SharePoint server and we know that the SharePoint setup account has rights to the SQL instance that we're using because we set that up.
So now all you have to do is say test connection, and test connection succeeded. Why? My firewall is set up correctly. I'm authenticating correctly and I configured my alias correctly. If this doesn't work for you, check the firewall. Check your account and check to make sure that you typed in the right port and the right server name. Because particular with a firewall, people often forget to do that setting and that will cause problems.
So this is a great way to test the connection between your SQL Server and the SharePoint server.
CA Callahan takes you through each stage of the process, covering everything from software installation prerequisites, to common topologies and MinRoles, to how to slipstream updates into an installation. Callahan introduces best practices, as well as steps involved in configuring outgoing email, web applications, site collections, managed accounts, and farm accounts. Plus, discover how to use some common tools that experienced administrators use on a day-to-day basis to make the most of their installation efforts.
- SharePoint installation considerations
- Configuring SQL for SharePoint
- Software installation prerequisites
- Using the Installation Wizard
- Installing SharePoint 2016 on additional servers
- Configuring outgoing email
- Configuring a web application and site collection