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!