Connection limit to a sql server application

[Guest]

Hi All,

I have an application that uses SQL Server 2000 and I would like to put it
on a Windows 2000 Professional operating system. I read an article in
Microsoft's Knowledge Base, 154628 - SQL Logs 17832 with Multiple TCP\IP
Connection Requests, that an application like SQL Server can handle only 5
connection requests when it is on a Windows 2000 OS because that is the
highest backlog value.

Is this true or am I misunderstanding the article?

Windows 2000 Server has a backlog maximum of 200 but the vision software I
need to use on the application does not work with the server operating
system. I would prefer to use Windows 2000 Professional.

Thanks alot.
Gloria

 

[Ryan Hanisco]

Gloria,

If this is something that you think will be getting so many requests that it
can't handle the connections you would want to do something like putting the
SQL instance on a separate server from the application. This would get rid
of the problem where the application does not want to run on the server OS.
In this case of high load and availability, the software usually manages the
connections in the middle tier and the database connections are tightly
managed to avoid problems like this.

In a smaller case, you generally wouldn't run into this problem except under
extreme load. The article you quote is listing the number of simultaneous
connections the server is able to process and respond to (even NAK) before
it starts dropping. This is acting as a cache for incoming TCP connections.
With connection pooling and the speed of most machines processing SQL
applications, you are not likely to run into this limitation except under
extreme circumstances. You may have several dozen people using the database
and never run into a backlog of more than 5 TCP connection requests. If you
are anticipating a load like that, you had better be using server.

 

[Guest]

Hi Ryan,

Thanks for the quick response.
Just so I completely understand what you are saying.
The backlog number refers to simultaneaous connections only. If 20 people
are connected to the database thru TCP/IP, that will not cause a problem. If
6 simultaneaous requests are made to the database, then 1 will be dropped.
Because these queries happen in a few milliseconds, this small number of
people is usually not cause a problem.

Gloria

 

[Ryan Hanisco]

True. that 6th connection will just get dropped -- like a router with its
fifo queue full. It won't even get a wait or retry message as the others
will.

I will point out, though, that Pro is not made for "server duty" and may run
into other problems under heavy load. If you anticipate high utilization,
then you need to find a way to use the right tools for the job.