While SQL Server 2005 can handle thousands of connections, it is not a good idea to use two-tier architecture where the client directly connects to the database server. Dead connections will cause timeout and deadlock problems. You should use 3-tier architecture by placing an application processor (IIS, Websphere, ColdFusion, ...) between the client and the database server. For example, an IIS Webserver needs only a few connections to serve hundreds of web clients. If there are 10 webservers, the connections you need is 30 or less. sp_monitor can be used to see statistics on the number of connections. In the following example, 44 seconds past between runs of sp_monitor and 22 connections logged during this period. 350K connection since the server was last started.

EXEC sp_monitor

/*

last_run    current_run seconds

2010-03-14 05:59:25.530 2010-03-14 06:00:09.300 44

cpu_busy    io_busy     idle

759(0)-0%   230(0)-0%   2182777(84)-190%

packets_received  packets_sent      packet_errors

581622(56)  592672(56)  0(0)

total_read  total_write total_errors      connections

10155(0)    161565(14)  0(0)  350508(22)

*/