Synonyms – SQL Server

I was working in a project where we are using database mirroring as high availability solution. In the same instance we have multiple databases in which only critical databases are configured for database mirroring. As you know database mirroring scope is limited to database only which means dependent database are not failed over (which is now possible in SQL Server 2012 as Availability groups). In my case the dependent database is not even configured for mirroring. They have some procedures \ views which will pull the data from dependent database. Assume if the mirrored database is failed over to next node they won’t be able to pull the data from dependent database which means they are screwed and application will be down till we go back to principal node.

To overcome this we have used the feature SYNONYMS, in simple we can say it’s a alias for database objects. So in our situation we have created a SYNONYM which points to the non critical database and this SYNONYM is used in queries, procedures etc. When the critical database gets failed over, we have created a linked server from mirror server to principal server to access the non critical database, we will update this schema in the SYNONYM in the event of failover so that the queries can work fine. Yes you are right, this will work if the principal server is available. Consider entire principal server is down in that case you can’t bring back the non critical database.

So to brief SYNONYMS are just pointers to database objects. Below are the list of Objects supported in Synonyms

Let’s create a synonym and do a test

CREATE SYNONYM ArticleInfo
FOR publisher.dbo.article
GO

Now you are good to go use this synonym. Just perform a select statement that’s it.

SELECT * FROM ArticleInfo

In the event of failover you just need to drop and recreate the synonym (there is no alter command for synonyms)

IF OBJECT_ID('ArticleInfo') IS NOT NULL
DROP SYNONYM ArticleInfo
GO
CREATE SYNONYM ArticleInfo
FOR node1.publisher.dbo.article
GO

If you notice in the above query, I have used four part identifier name. In this case I have created a linked server with name Node1 and using this I’m fetching data from the non critical database

You will be still able to query the base table. From performance point I couldn’t see any difference in execution plan, so you will get same performance using Synonyms

How can I find the list of synonyms available in the database?

SQL Server has a catalog view sys.synonyms for this purpose. You can query this view to get information about the list of synonyms created and the base object it’s referring to.

USE DBName
GO
SELECT name
,create_date
,modify_date
,base_object_name
,OBJECTPROPERTYEX(object_id,'BaseType') [BaseType]
FROM sys.synonyms

There were some questions in internet posted to get the difference between view and synonym as view does the same job. They both are different and the reason is below

So to conclude Synonyms are great to use as a alias for database objects, it will just point to the name mentioned while creating it.