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
2. Select the Alias tab at the top and select Add.
3. Select TCP/IP
Enter in the name of your new SQL Server Alias
Enter in the computer name of your SQL Server
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.
5. Now that you have a SQL alias you can begin installing SharePoint and other applications you use that might involve SQL.
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