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

Supported Objects

Assembly (CLR) stored procedure
Assembly (CLR) table-valued function
Assembly (CLR) scalar function
Assembly (CLR) aggregate functions
Replication-filter-procedure
Extended stored procedure
SQL table-valued function
SQL scalar function
SQL stored procedure
SQL inline-tabled-valued function
View
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

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

SYNONYM

View

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.

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *

*


x

Related Posts

Performance View on INNER JOIN and OUTER JOIN
Hope you guys are getting ready for Diwali Festival :-). Before you guys start with the festival event I thought of writing an article on perform...
Pagination in Result Set - OFFSET \ FETCH - SQL Server 2012
Today I’m going to discuss another new feature in SQL Server 2012. Pagination in result set is a new feature in SQL Server 2012. ORDER BY clause ...
Conversion Functions – SQL Server Denali
Microsoft released 3 new conversion function in SQL Server Denali. In which two functions will tell you whether the action is possible the third ...
powered by RelatedPosts