Linked Server Best Practice

I have a general question regarding the use of linked servers.
Is it better to have one linked server that five different people would use
to pull data from five different databases on the target server, or to have
five different linked servers, each pointing to one particular database?
Is the load on the server the same at runtime if all five people are running
queries?
Thomas LaRock
Database Administrator
ING Investment ManagementIf all 5 databases are on the same linked (target) server, I wouldn't
see any need to create more linked servers - just use the one - your
queries will point to the right database anyway
select column
from server.database.dbo.table
1 linked server is easier to manage. Not sure about the load on the
servers - which one do you mean? The originating server or the target
server? It seems the target server will be doing most of the
processing anyway, and the originating server just passes the data back.|||Thomas,
One linked server would be easier to administer and manage. However, more
than one LS may be required depending on the permissions required by the
individual users.
HTH
Jerry
"Thomas LaRock" <thomas.larock@.discussions.microsoft.com> wrote in message
news:441C4598-8973-495B-90B4-795DFA68C691@.microsoft.com...
>I have a general question regarding the use of linked servers.
> Is it better to have one linked server that five different people would
> use
> to pull data from five different databases on the target server, or to
> have
> five different linked servers, each pointing to one particular database?
> Is the load on the server the same at runtime if all five people are
> running
> queries?
> --
> Thomas LaRock
> Database Administrator
> ING Investment Management
>|||I will try to make it more clear.
I agree that one linked server is easier to manage. The desire here is to
create a naming convention for our linked servers. Currently, we use an
abbreviated servername for the name of the linked server, say 'SQLAA'. this
way when a person connected to SQLBB needs to link to SQLAA all they need to
do is use SQLAA in their code. Simple, right?
Well, maybe not. Here, we try to keep our dev, test, and prod environments
separated. As such, we try to link dev to dev, test to test, and prod to
prod. So, the shortened name allows for a same named link server in all thre
e
environments, which each link pointing to a different place. Confused yet? I
n
other words, your code can be the same in each environment, you only need to
use 'SQLAA' in order to get whatever you need from the appropriate server.
Now the problem we have is migrating to a different server. As such, we have
a desire to start naming some of our linked servers with regards to the
system they are trying to conenct. If we go that route, then we would have a
proliferation of linked servers, as each team would want a different named
linked server to make migration of their source code easier in the event of
a
server migration.
Well, the only problem I have is that I do not know what the difference is
on the source server if there are ten linked servers being used at the same
time, or one linked server being used simultaneously by ten people. The link
server(s) on the source server exist in memory there, not on the target. And
my concern is with the memory to leave area of sql server. I know this can b
e
upped with the -g option on startup, but I want to know if there is a
difference between the two models, without having to run many tests.
If the difference is significant, then it would be a factor in our naming
standards.
Does that make sense? I am hoping someone can give me a quick and dirty
answer because i do not have time to run tests before a decision will need t
o
be made.
Thanks in advance.
--
Thomas LaRock
Database Administrator
ING Investment Management
"Corey Bunch" wrote:

> If all 5 databases are on the same linked (target) server, I wouldn't
> see any need to create more linked servers - just use the one - your
> queries will point to the right database anyway
> select column
> from server.database.dbo.table
> 1 linked server is easier to manage. Not sure about the load on the
> servers - which one do you mean? The originating server or the target
> server? It seems the target server will be doing most of the
> processing anyway, and the originating server just passes the data back.
>