CREATING A SHAREPOINT SQL SERVER ALIAS

What is an SQL Alias? 

This is a name used to reference your database, via which any application connecting to the database can connect. So if you have say, a SharePoint farm, that needs to access your SQL server, rather than point the SharePoint farm to the actual database name, you could create an alias, to which you will point the SharePoint server. It is this alias that would in turn, point to the actual SQL server.

Why the need for an SQL alias?

Should there be any need whatsoever for your SQL server to be changed, if you want to use a different server there would be confusion on the SharePoint server, because the farm would look for its existing databases and not find them. The initial server name used would have registered itself everywhere on the farm, and this cannot be changed.

I hear someone say, if the need for this arrives, I’d provision a new SQL server with the same server mane as the previous, and same instance name or cluster name as the previous. While this might prove a solution to this case, it should also be worthy of note that this could be time consuming, time which may not be a readily available might have to be expended. Also, the old server might still be used by other applications.

To curb all these, it is necessary to create an alias, when creating a SharePoint farm for the first time, use the alias name as the SQL server name. So if there is the need for the server hosting SharePoint to change, the appropriate databases are backed up from the old server, and restored to the new one, then the alias pointing to the old server now points to the new server.

To create an SQL alias, you have to run both the 32 and 64 bit SQL tool.      

1.       RUN THE 32 BIT SQL TOOL

          C:\windows\system32\cliconfg.exe

On the general Tab, ensure that TCP IP is enabled 

Next, go to the “alias tab” and select “add”.

Then enter the name you wish to use as alias, and your actual server instance name or cluster name. Also enter your port number.

(In the event that you have to change your SQL server, it is in this “Server name” text box you would have to enter the name of the new server, so even if SharePoint still has the alias name, it points to this new server).

To get your port number, run the SQL server configuration manager tool.

SQL Server Network Configuration > Protocols for [SQLSERVERNAME], select TCP IP, click on the

IP addresses tab. Then check the port number from the “TCP Port” section.

2.       RUN THE 64 BIT SQL TOOL

      C:\windows\SysWOW64\cliconfg.exe, and repeat all the steps done for 32 bit tool.

When you are done with that, run the SQL server configuration manager.

Go to SQL Native Client 11.0 Configuration> aliases. Right click alias, select new, and then enter the values.

When done with that, it should look somewhat like this.

Repeat same process for SQL Native Client 11.0 Configuration (32 bit)

Then you can use the alias in place of the SQL server\instance name whenever you need to refer to the server. In the creation of a SharePoint farm, this name could be used.

Now, here is a bit of an over sight that really gave me a night of headache when creating my SharePoint farm. For those using a cluster, this might not be much of a trouble. But here I was using a SQL server name\instance name.

I kept getting the error:

“Cannot connect to database master at sql server < MSSQLSERVERTEST_ALIAS> { ie  <ServerAliasName>. The database might not exist, or current user might not have permission to connect to it.

I thought there was something wrong with my PowerShell scripts. So I decided to create the farm using farm configuration wizard.

But lo, same problem persisted. After a whole night of time wasting, I found out that when registering my aliases on my WFE server, I used just instance name

Rather than server name\instance name.

This is what my connection to SQL looks like

So unless you are using an SQL cluster (where you would then have to enter your cluster name), in the place of server name, do ensure to enter your server name\instance name. Else you want to waste a whole lot of time trouble shooting.

Good luck.