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
|Assembly (CLR) stored procedure
|Assembly (CLR) table-valued function
|Assembly (CLR) scalar function
|Assembly (CLR) aggregate functions
|Extended stored procedure
|SQL table-valued function
|SQL scalar function
|SQL stored procedure
|SQL inline-tabled-valued function
|Table (User-defined)(Includes local and global temporary tables)
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
|Supports multiple database objects as shown in above table
|Supports only view & tables
|A synonym cannot be used as base object for another synonym
|A view can be used as base for another view
|Dropping the base object can’t be prevented
|Dropping the base object can be prevented using SCHEMABINDING option
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.