Lesson Learned #174: Using Synonyms in Azure SQL Managed Instance for Linked Server tables

Posted by

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

Today, I worked on a very interesting case when our customer has a linked server to Azure SQL Database, but, they want to remove the prefix of the linked server that everytime that run a query against this Linked Server, for example, SELECT * FROM MyLinkedServer.DatabaseName.SchemaName.TableName. In this article, I would like to share with an example how to do it. 


The first thing that we need is to have a linked server connected to any other Managed Instance, OnPremises or Azure SQL Database. Let's give the name LinkedServerMI to this linked server. 


Once we have this, basically, we need to create a new synonym using the following command: CREATE SYNONYM [dbo].[MyNewTableForLinkedServer] FOR [LinkedServerMI].[DatabaseName].[SchemaName].[TableName]


Using this synonym right now, everytime that I execute any command, for example, SELECT * FROM MyNewTableforLinkedServer automatically this command will run a query against Linked Server, database, schema and tablename. 





This articles are republished, there may be more discussion at the original link. But if you found this helpful, you're more than welcome to let us know!

This site uses Akismet to reduce spam. Learn how your comment data is processed.