Discovering aliases and synonyms

Configuring a SQL alias

Configuring a SQL alias

My Oracle to SQL Server 2008 migration is going well and is close to wrapping up. In an effort to make the transition as seamless as possible for the users, I wondered what kind of options were available to me to  reuse the known name of the Oracle server, once it’s been taken offline. There has already been a DNS alias set up to obscure the actual name of the SQL Server, but the instance name still has to be added. Our SQL Servers are all named instances; can I refer to a named instance as DBPROD instead of PRDDB\PRD1?

A search for ‘sql 2008 alias’ returns over a million results – no real surprise. The most popular results are for configuring an alias on the workstation or web server using the SQL Server Configuration Manager. This looks promising, but it  requires a setup on individual machines. That seems like a lot more work than I want to do on a global scale, even in a small environment like ours. But definitely a possibility for some circumstances.

Then I started reading about synonyms. Synonyms were introduced with SQL Server 2005. They can’t be used to create an alias for a server, but they are for database-level objects. Synonyms have some important advantages:

  • Reference objects by a simple name rather than a fully qualified name. This is handy when referencing objects in other databases or on other servers that require multi-part names.
  • Avoid hard-coding names in case objects move. Instead of having to update every reference to an object, the synonym can be updated or replaced.
  • Facilitate backward compatibility when objects are renamed.

Creating a synonym is painless:

CREATE SYNONYM synonym_name FOR object; CREATE SYNONYM MyEmpTable FOR DBPROD.AdventureWorks.HumanResources.Employee; 

To use the synonym:

SELECT JobTitle FROM MyEmpTable; 

While neither of these options provided me with a perfect solution to my original enquiry, it was definitely useful to investigate both. I already know of one place where I intend to use a synonym. I found it interesting that there is a request on Microsoft Connect to expand the use of synonyms to both databases and linked servers. I look forward to seeing if that feature makes it into the next version of SQL Server!