What is a SQL alias and why use one?
sqlalias

What is a SQL alias and why use one?

Posted by on Monday, January 13th, 2014  

 

A SQL alias is an alternative name given to a SQL server that is more familiar to the user. It is like a nickname for a person, except it is for a server. Sometimes server names can be complex and hard to remember, this is where an alias would come in handy. Instead of having “Prod _SRV_SP_SQL_1” as the server name, you can use “SQL1” or any variation of that for simplification. Creating an alias for a SQL server is recommended when building a new production SharePoint server farm for many reasons.

While you are building a new farm, it is important to consider what actions need to take place if your database server fails. Having a SQL alias will allow you to switch database servers and continue running all of your applications as normal. Without a SQL alias you would have to go into each application that touches SQL and manually reconfigure the connection. With SharePoint if you have one database server and no alias you would have to rebuild your existing farm and point to your existing databases (Restored from backups). 

Having a SQL alias will also provide you with a performance boost. When SharePoint connects to SQL it goes through the available protocols to determine how to send and receive information. With an alias you can specify a protocol to speed up this process. When creating an alias you should select TCP/IP (Transmission Control Protocol) as the preferred protocol. It is reliable and provides error checking for information that is sent/received. Make sure you document your SQL alias for future reference.

Create a SQL Alias

Now that you know why to have a SQL alias, here is a step by step guide on creating one. You will want to repeat the following steps for each SharePoint server in your farm.

1. Navigate to C:\Windows\SysWOW64\cliconfg.exe

SQL Alias

 

2. Select the Alias tab at the top and select Add.

 

SQL Alias

 

3. Select TCP/IP

SQL Alias

Enter in the name of your new SQL Server Alias

Enter in the computer name of your SQL Server

Select OK

Note: If you have a more secure SQL environment and changed the default port along with adding a SQL instance this method will still work. You will need to uncheck dynamically determine port and enter in the new port number you configured for SQL. You will only need to enter the SQL Server computer name and not add in the instance name.

4. Navigate to c:\Windows\System32 and repeat steps 2-3. It is best to keep the connection to SQL consistent even if you end up not needing the 32-bit and 64-bit alias.

SQL Alias

 

5. Now that you have a SQL alias you can begin installing SharePoint and other applications you use that might involve SQL.

SQL Alias

However, If your SharePoint environment is already setup you are still able to create an alias. The process is more complicated, but it is possible. You will have to build a new SQL server. Do a backup/restore or a database attach of all your existing databases to the new server. Create SQL logins for your new server (the database permissions will persist). Create an alias on all of your SharePoint servers to point to the new SQL server and you will be up and running in no time. Look for my next blog post for screen shots on how to do this.  Thanks for reading!

 

For a more detailed description of the performance enhancement provided by a SQL Alias check out this blog post http://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx

 

Subscribe to RSS Feed

Sign Up for Newsletter

2

2

comments

    Feb 18
    2014

    Josh

    Shane,

    What advantages are there to using a SQL alias vs. just creating a DNS record for whatever alias?

    Reply
      Feb 18
      2014

      Shane Shipley

      DNS records will allow you to have one centrally managed location. Using DNS will not increase performance as mentioned in the blog and it will not allow you to target a specific instance of SQL. Another downfall when using DNS is that every computer will have access to this alias and you can not tell which servers are using it.

      Using an alias will allow you to track which servers are using the alias and allow for easier database changes. A downfall to this approach is that you will have to make sure you make the alias on each server talking to the SQL server and it can cause a small outage while transitioning to the new alias.

      Reply

Leave a Reply