Scenario: In SQL Server on-prem and plan to go the cloud in a year or two.
I am writing a stored procedure in database A and I need to access a table from database B.
Is it a best practice to create a synonym that points to the table in database B or just fully qualify the table in the package?
If the cloud solution you are using is SQL Azure, you're somewhat limited in what you can do, because cross-database queries run from database A would have to treat database B as an external datasource. Each table that you reference in database B would require that an external table be created in database A. I don't think making a synonym for each of those tables is going to help you get around that.
There's a couple types of objects that I find make things more difficult to debug and / or manage in SQL Server, mostly because they end up being second thought to most developers. One of those things is Triggers and the other is Synonyms.
The reason I believe this for Synonyms is because:
Synonyms are not commonly used as is, and many developers don't even know of their existence.
Synonyms generally look (and function) like a regular entity object when part of a query, which can be confusing to other developers when they encounter one. When they try to trace back to where that Synonym object lives, it may take them a while to realize it's a Synonym and not an entity object like a Table or View for example. I'd say this is especially the case for developers who aren't leveraging schemas and keep everything in dbo.
Conversely, Synonyms are good in theory, especially for the use case of abstracting away the object reference that they are defined on. This way if such object is referenced in a lot of places, and one day needs to change, then you only need to update the Synonym definition. That's a change to only one object as opposed to having to fix the reference in all of the dependencies. So it can be argued Synonyms can improve database manageability too.
Additionally, having a naming convention with your Synonyms may help developers realize that it's a Synonym when they first encounter it, for example prefixing all synonyms with a lowercase s.
As for whether you should use one or not for your use cases depends on if you think the above good attributes of a Synonym outweigh the potential bad ones. There's not really a right or wrong answer here, it's up to you to decide. Personally I like to be explicit and fully qualify my references, as opposed to using Synonyms.
