DNS Alias vs. SQL Server Alias

Setting alias for SQL Server provides the system extra capability in disaster recovery. In the case of an SQL server outage, the admin could bring online an identical instance (if any) and point the same alias to refer to the new instance. In this case, the clients may not even know the SQL server has been changed at the back end.

Therefore, it is recommended to have and alias in place. However, questions always come up as to the choice between a DNS alias and a SQL Serve alias. This article is for a comparison of the two option.

DNS Alias

Pros

  • It is more resilient (as mentioned at the beginning of the second video here). Once it is setup, all the clients could take advantage of it. In the case of SQL server change, the update is only needed at DNS. All the servers using the same DNS will be able to pick it up.  Of course, DNS cache is not considered here. For example, in a large SharePoint Farm, you don’t have to go into each server to update it.

Cons

  • This usually requires cooperation across teams, which may not be easy for many organizations. SQL Server, SharePoint and DNS are usually managed by different teams or personnel.
  • Single point of failure. If DNS is down, all the clients using the alias will be affected. What’s more, it is sometimes difficult to know who will be affected once the alias is modified because anyone could use it after it is setup.
  • You cannot specify ports. So the applications that connect to the SQL Server should either always remember the port or has to use the default ports.

SQL Server Alias

Pros

  • It is controlled at the application side, by the same team that manages the clients, such as the SharePoint team.
  • If the alias is set after the SharePoint farm is built, it is easier to configure a SQL alias. Just set it in the SQL Server Client Network Utility (Cliconfg.exe) and restart the server or restart the SharePoint services (Todd Klindt has a post that includes this topic). For DNS alias, you will have to reconfigure the farm.
  • You can specify a different port other than the default one. In this case, to applications that connect to SQL Server, they only need to know the alias name.

Cons

  • It has to be updated at every client machine. If you have 10 SharePoint Servers in the farm, you will have to set and update the alias on each one of them.  If you don’t have a way to automate the update, there will be certain time of outage of the system.
  • The same name maybe used by different systems. For example, the alias “SQLDB” can be used for SharePoint to refer to SQL Server A, while on TFS, “SQLDB” is referring to InstanceA on SQL Server 2.  Once there is an integration between the two platforms, there will be potential confusion and problems.

This is my opinion and you may have you own idea about this topic. If you could share your thoughts, I would really appreciate it. 🙂