I have 2 SQL Servers, with several different databases on each. I want to present them as a single SQL Server showing all of the databases on either Sql Server.

We have yearly archive databases, so 1 database for each year. This year we decided to deploy a new SQL Server that will host newer years, so older yearly databases are on the old server, and future will be on new one.

I know I can link the servers, but I am not sure if I can do it in the transparent way so that no application has to change the query - so far I always had to use linked server explicitly in each query that uses it.

TLDR: I want to access databases on another SQL Server via main SQL Server so that app doesn't know or care about SQL Server details and don't need to have if (Year > 2021) {then use this server } else {use that server} type of logic and can behave like all databases live on the same server.

CodePudding user response：

You can follow the below steps:

1. Create a linked server pointing to older server
2. Create an Empty Database in the new server, corresponding to older database in the older server.
3. Create synonym for each object in the older database in the Empty database

Now, When you refer to synonyms in the empty database, it will appear as if the older database resides in the new server.