Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
1  
If you are sure that in future your database will be moved, why don't start with linked server from scratch? I mean, you could create a linked server that points to a database in your own instance. –  Steve Mar 13 '12 at 9:18
    
I'm not sure that it will move in the future ! that depends on the usage of each database and also each client requirements. –  Asha Mar 13 '12 at 9:21
2  
Will be good if some SQLServer Guru could explain to us what is the cost in performance to use a linked server to our own instance. –  Steve Mar 13 '12 at 9:24
1  
Linked servers are fine for simple queries, but dont' expect great performance when dealing with cross server joins and whatnot. I recommend using linked servers mainly for administrative tasks / batch imports, etc. –  datagod Mar 14 '12 at 14:00
1  
Linked servers are nice for the sorts of activities that @datagod said. I would suggest rethinking your design considerations. Specifically, what would be the precipitating cause of moving the DB to its own server? –  swasheck Apr 13 '12 at 16:07
show 1 more comment

migrated from stackoverflow.com Mar 14 '12 at 15:52

This question came from our site for professional and enthusiast programmers.

4 Answers

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.

share|improve this answer
    
Using linked servers like this can cause all sorts of performance problems very quickly when joining between objects across the linked server, even if it's a loop back. –  mrdenny Aug 10 '12 at 8:12
add comment

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.

share|improve this answer
1  
Using linked servers like this can cause all sorts of performance problems very quickly when joining between objects across the linked server, even if it's a loop back. –  mrdenny Aug 10 '12 at 8:12
add comment

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 :

Create new Synonyms

Create new Synonyms

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

share|improve this answer
    
Using linked servers like this can cause all sorts of performance problems very quickly when joining between objects across the linked server, even if it's a loop back. –  mrdenny Aug 10 '12 at 8:14
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.