Recently faced an issue in connectivity where the SQL box was taking 5 seconds to do basic things like opening up a connection, etc – one fix proposed was to change our connection from TCP/IP to Named Pipes. Was this a correct choice?
This blog article echoed the statement from Books Online: “Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.” The author noted issues where the application would periodically disconnect from the database; and unlike TCP/IP, the app wouldn’t fail gracefully or attempt to reconnect. Named Pipes doesn’t support Kerberos, only NTLM.
This article also favored TCP/IP over Named Pipes for these reasons:
- For small to moderate amounts of database traffic that aren’t heavily saturated, it doesn’t matter
- Where connectivity is strained or there’s additional routing overhead (i.e. VPN) then TCP/IP has the advantage – esp where applications are chatty.
- Named Pipes connectivity always uses more packets to get the same amount of work done with remote clients.
- Named Pipes has a significant advantage when running an IIS app on the same box as your SQL backend (?!?!)