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 ?
example :
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 ?
Best Answer
Using loop back linked servers isn't the greatest idea. This can lead to some interesting performance problems once you start doing joins between local tables and "remote" tables as you are going through a linked server so suddenly instead of doing a normal join you may end up transferring the entire table across the linked server (which means possibly reading it off of the disk), stuffing it into tempdb and joining to a hash table which doesn't have any indexes on it.
Don't setup linked servers unless you actually need them. You can get some very powerful servers these days for not much money (I'm currently working on a server which has 32 cores and 256 Gigs of RAM) so scaling a server up isn't all that hard. If the application actually needs to be scaled out across multiple servers you'll need someone who is used to doing very complex tuning to help out to make sure that you aren't shooting yourself in the foot in the process (which isn't hard to do).
Once you do get to needing linked servers, there's going to be so much stuff that needs to be done to move the database and test everything going through and fixing the code isn't going to seem all that bad.