I'm designing a set of databases which might need to use each other's stored procedures or tables. The reason that I'm creating separate databases is that in the future I want to be able to move any of these databases to a dedicated server for itself if required and just create a linked server to the other server(s). however doing so I also need to change all the codes which are accessing the other databases.
does anyone know of any way to avoid this later modification in case of using a linked server ?
or in other words :
Is that possible to have stored procedures which would work regardless of the fact that it needs to access to a linked server or same local server ?
DatabaseA and DatabaseB are both located on SQLInstannceA Sp on DatabaseB :
SELECT * FROM DataBaseA.dbo.tbx
and now if I move DatabaseB to SQLInstanceB, and create a linked server to SQLInstanceA, I have to change the SP code to the following :
SELECT * from linkedserverName.DatabaseA.dbo.tbx
is there anyway to avoid this change in case of moving databases ?