September 24, 2003 at 11:31 am
Named Pipes or TCP/IP Sockets used for client to connect to SQL Server in local area network environment, ie client and server are in separate machines, which one do you perfer to use? Why?
September 24, 2003 at 12:36 pm
We use TCP/IP.
This is from Inside Microsoft SQL Server 2000 by Kalen Delaney.
quote:
Microsoft internal testing has found the TCP/IP Sockets Net-Library to be the fastest networking choice, although in a typical LAN you rarely see the network as a performanc bottleneck. In a low-speed WAN, however, this can be an important issue. Some network administrators have also been concerned about potentially reouting NetBIOS traffic across their LANs and WANs. However, if SQL Serve is not using the Named Pipes Net-Library and the Multiprotocol Net-Library is not using named pipes under the covers for IPC, SQL Server is not using NetBIOS at all and this is not a concern.
I have also seen comments elsewhere that TCP/IP is better for security.
-SQLBill
September 25, 2003 at 1:24 am
I have found that tcp-ip has less connection issues, espically now with ms03-031 restricting named-pipe access (Q823492)
Steven
September 25, 2003 at 1:33 am
We use TCP/IP. We've also found it to be more reliable than Named Pipes.
Thomas Rushton
blog: https://thelonedba.wordpress.com
September 25, 2003 at 2:22 am
Well, what I have abserved is that if you are trying to connect to a server which resides in another LAN, often noticed that connection via Named Pipes fails and TCP/IP goes thru. There are times when Named Pipe within the LAN fails where TCP/IP goes thru.
September 25, 2003 at 2:22 am
Well, what I have abserved is that if you are trying to connect to a server which resides in another LAN, often noticed that connection via Named Pipes fails and TCP/IP goes thru. There are times when Named Pipe within the LAN fails where TCP/IP goes thru.
September 25, 2003 at 2:26 am
Well, what I have abserved is that if you are trying to connect to a server which resides in another LAN, often noticed that connection via Named Pipes fails and TCP/IP goes thru. There are times when Named Pipe within the LAN fails where TCP/IP goes thru.
September 25, 2003 at 3:46 am
hi!
depending on how "clean" your SMB network is set up, we've found TCP/IP more efficient when it comes to network resources, means, whenever possible we use TCP/IP to avoid all the NetBIOS overhead. named pipes is our "fallback" if TCP/IP won't work (eg. weird windows network configuration issues, like no wins server and LM resolution on, means, something you'd never expect to find in the "wild" 😉
best regards,
chris
September 25, 2003 at 3:25 pm
I heard a story about a guy who set up his ODBC in his DMZ using TCP and then disabled NETBEUI to protect against Worms. (or was it NetBIOS?) Does that sound feasible or is this a tall tale.
September 26, 2003 at 6:26 am
TCP/IP is better than Named Pipes any day...owing to the performance and security.
Cheers!
Arvind
Arvind
September 26, 2003 at 9:24 am
We're using TCP/IP since a number of 3rd party applications that need to access a named SQL instance can only do so via the port number
September 29, 2003 at 5:05 am
probably yet another reason:
minimize the number of different protocol on your LAN
September 29, 2003 at 10:43 am
Interesting InfoWorld test article here:
http://archive.infoworld.com/articles/tc/xml/01/11/26/011126tcmdac.xml
Here's an excerpt or two:
We discovered this phenomenon quite by accident while testing client/server database workloads on a Windows XP PC. Although throughput to and from the server was excellent, the processing overhead involved in maintaining the SQL Server connection began to overwhelm the PC. Multitasking test scenarios that required only seconds to complete on Windows 2000 were taking several minutes on Windows XP.
The version of MDAC included in XP had changed the default from Named Pipes to TCP/IP.
We conducted a series of tests to gauge transactional database query performance.
Although overall server CPU utilization rose by a modest 8 percent using TCP/IP, context switches per second -- a measure of the operating system's ability to juggle multiple tasks -- dropped by more than 150 percent for a 10-user workload. In real-world terms, this meant a 10 percent to 15 percent loss in overall SQL Server transaction performance as we scaled the server from 10 users to 25 users and finally to 50 users.
TCP/IP (the new default) is faster than Named Pipes (the old default), but it also consumes more CPU cycles per transaction, and the processing overhead can lead to scalability problems down the road.
October 1, 2003 at 9:38 am
Just a note with regards to protocols because I've seen this confusion before. Named Pipes will travel across whatever protocols the OS is using. For instance, if the OS is configured to use TCP/IP, Named Pipes will travel across it. So from a network perspective it won't throw another protocol on (if you ask a network engineer). From our perspective, it is, because it's like HTTP... Technically HTTP goes across TCP, but we consider it a different protocol.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply