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.
Enjoy!
