468,242 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

number of Connection for Linked Server

Hi,

Following is our different database infrastructure.

Server1 on NetwrokDomain1
SQL Server 2000
Server2 on NetwrokDomain2
SQL Server 2000

The database on Server2 is being used by third party clients and
database on Server1 is used by us.

We want to get information from databases on Server2 on Server1. To
accomplish this, we had created a Linked Server Connection on Server1
to Server2 and created certain views on Server1 which then used within
SPs on Server1. The requests are coming from Web application by many
user to Server1.

Does anyone got any idea on how many number of connection will be
created between Server1 and Server2 per request? Will there be
different connection created for each request or it will utilise the
same connection (connection created while creating the linked server)?

It would be very helpful if anyone can point me to a correct
direction.

Any help is appreciated.

Thanks

-pb-

Oct 14 '08 #1
1 3969
-pb- (pr*********@gmail.com) writes:
We want to get information from databases on Server2 on Server1. To
accomplish this, we had created a Linked Server Connection on Server1
to Server2 and created certain views on Server1 which then used within
SPs on Server1. The requests are coming from Web application by many
user to Server1.

Does anyone got any idea on how many number of connection will be
created between Server1 and Server2 per request? Will there be
different connection created for each request or it will utilise the
same connection (connection created while creating the linked server)?
I would expect the answer is "as many as needed". That is, if there are
five parallel requests to the linked server, there will be five connections
to the linked server. But if the request come at different times, there
would only be one connection, even if the requests were on different
connections on Server1. However, there is one qualification: the requests
must all use the same login on the remote server.

This is based on the knowledge that SQL Server uses OLE DB for linked
server, and OLE DB provides connection pooling. But else it is just
speculation. I would have to test to find out - but then I figured you
could easily run that test yourself.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Oct 14 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Fred Zuckerman | last post: by
reply views Thread by Igor | last post: by
4 posts views Thread by Fred Zuckerman | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.