Microsoft SQL server 2008 and linked servers

Question

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 ?

Answer 1

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.

Answer 2

I think the easiest solution is to have as many rows in your hosts file as you need, all of them pointing to the same IP address of your current development server and start the whole stuff by making the linked servers.

So let's suppose that you need two additional (future linked) servers. Then your hosts file (c:\windows\system32\drivers\etc) would look something like this:

123.123.123.101 mylinkedserver1.mydomain.com
123.123.123.102 mylinkedserver2.mydomain.com

Doing this, you can create and use the linked servers right at the beginning.

Answer 3

One way could be to link both servers on both directions and adding a "loopback" link to the current server. You would then write the stored procedures in a way that you access your tables using the linked server names (eg linkedservera.Databasea.dbo.tableA).

 A --> B
 B --> A
 A --> A
 B --> B

Your procs can now be written in terms of the linked server names and be deployed identically, to both.

Answer 4

I Thing you must use Synonyms in each database for create alias of each table in other database. you must only use created Synonyms in stored procedures and query. and each time you want to change database, you only must change Sysnonyms table address.

For create new synonyms see below :

In your query an stored procedure you must use dbo.tbx2 instead of linkserverName.DatabaseA.dbo.tbx.