Using Synonyms for Flexibility in a SQL Server Environment
Synonyms are a useful tool to make database aplications more flexible, portable and easier to code. A synonym can be viewed as a pointer to a fully qualified SQL Server object that is used in place of its fully qualified name. This allows application code to be more concise and easier to maintain.
Synonyms are a layer of abstraction that allow access to the underlying object without the need to know the name or location of the object. This shields the application from base object movement and name changes. They also provide a way to give objects shorter, more friendly names as opposed to SQL Server’s fully qualified object naming convention. This is specially appealing if you have end-users reporting against your database tables.
A synonym can reference objects across schemas, databases and servers. For example, a synonyn named “syn_Employee” can be coded to reference a local table named “dbo.Employee”. If, for example, the synonym was changed to point to a different table named “dbo.Emp_2” on a another server or changed to reference a view “vEmployee”, calling applications would not require any code changes. As long as the column names remain the same and the required permissions are in place, this synonym change would be transparent.
Synonyms can reference tables, views, stored procedures and some user defined functions. It is interesting to note that similar to a view calling another view, a synonym can reference a view and a view can reference a synonym. Care should be taken in the number of possible levels this can spawn.
Synonyms cannot be called across a linked server. Instead, a synonym should be created on the local server referencing the linked server’s database/table and the app code whould then reference the local synonym.
Synonyms can be created with the “New Synonym” dialog in SQL Server 2008 SSMS. Expand Databases, select and expand desired database, right click Synonyms and select New Synonym.
Or, they can be compiled using a Create Synonym statement:
CREATE SYNONYM MySchema.MySynonym FOR MyDatabase.MySchema.MyTable
Consider this sample use for synonyms.
You have database with a table (dbo.Sales) containing sales transactions. In order to improve performance, older transactions are periodically archived to another table (dbo.Sales_Archive). Creating a synonym referencing the table desired would allow applications to access either table without changing the called object’s name.
CREATE SYNONYM syn_Sales FOR MyDatabase.dbo.Sales
With the synonym coded as such, the sql code would access the dbo.Sales table using the name “syn_Sales”. (Select Col1, Col2, Col2 from syn_Sales where …).
Point the synonym to the table containing the archive data by executing the following:DROP SYNONYM dbo.syn_Sales CREATE SYNONYM dbo.syn_Sales FOR MyDatabase.dbo.Sales_Archive
The same sample code (Select Col1, Col2, Col2 from syn_Sales where …) would work without change.
Note that there is no “Alter” for a synonym. The synonym must be dropped and recreated for any change.
If the archive tables were in a different database, the synonym syntax would look like this:
CREATE SYNONYM dbo.syn_Sales FOR MyArchiveDatabase.dbo.Sales_Archive
If the archive tables were in a database on another server, all four qualifying levels of the name must be specified. Note that the linked server for the RemoteServer must be defined.
CREATE SYNONYM dbo.syn_Sales FOR RemoteServer.MyDatabase.dbo.Sales_Archive
Synonyms are loosley bound to their base object. This means that validation and permission checks are not done until run time. This also means that a synonym can be compiled with a reference to a non-existent object. It will not fail until the synonym is called. With this comes the advantage that a base object can be altered or dropped without affecting the synonym thus allowing DBAs the freedom to make changes to the underlying object without worrying about application code.
CREATE SYNONYM and ALTER SCHEMA permission are to required to create synonyms in a database / schema. Besides access permissions for a base object, select, update, etc. permissions are also needed for the synonyms themselves.
Generally, synonyms will be used in multiples. All the objects in a database or schema used in an application could be referenced by synonyms. So, to set an environment, multiple DROP / CREATE synonym statements would be issued:DROP SYNONYM dbo.syn_Sales CREATE SYNONYM dbo.syn_Sales FOR MyDatabase.dbo.Sales DROP SYNONYM dbo.syn_Customer CREATE SYNONYM dbo.syn_Customer FOR MyDatabase.dbo.Customer DROP SYNONYM dbo.syn_Product CREATE SYNONYM dbo.syn_Product FOR MyDatabase.dbo. Product
These statements can be issued either externally to the requesting application, or possibly internally by a switch in the application itself (note the permissions needed to drop and create a synonym). If one wanted to be really ambitious, a separate stored procedure or app could be developed to set all the synonyms needed for an application based on the environment desired, with parameters such as source and target databases and schemas and a list of objects, etc.
Consider using a naming convention for your synonyms. A prefix or suffix attached to your synonym name will make it easier to recognize an object as a synonym.
It is a good idea to always code all levels of the synonym name (Server.Database.Schema.Table). This standardizes your synonyms and makes it easier to classify and manage.
Although synonyms will add some additional complexity to database maintenance, a site with multiple environments can realize benefits when implementing a well thought out synonym strategy.