We are developing a database that consolidates few SQL Server DBs for a reporting requirement. Each DB has a number of views. No direct table or SP objects require to be accessible. All DBs and the new DB sitting in the same server. So we can develop cross-database queries quite easily with the Fully qualified object names.
i.e SELECT * FROM [SERVER].[DATABASE].[Schema].[View]
The problem is the depending database names differ between dev/ staging/ production environments of depending databases.
We have investigated the Synonym approach for the individual views and working fine. The advice we trying to seek from someone expert here is what is the best practice we should adhere to creating a single point to maintain this database alias instead of creating synonyms for each view so that we could do this once per environment and can maintain new queries same across different environments.
