Configuring Application Data Access and Network Protocols for SQL Server

  • Article

The notes taken while reading MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 (Self-Paced Training Kits).

When application access the SQL Server instance make sure those three configurations are in sync:

  • Network protocols available for the SQL Server instance.
  • Network protocols available for the client, the consuming application.
  • Connection string setting for network protocol.

Configure Network Protocol Available for SQL Server Instance

  • Start SQL Server Configuration Manager
  • Expand SQL Server Network Configuration node on the left

SQL Server Configuration Manager

  • Available protocols:
    • Shared memory – when application resides on the same machine where SQL Server instance lives – suitable for development.
    • Named pipes – designed for LAN, application access the SQL Server instance over the wire, fastest network protocol [Shared Memory is the fastest in general but not suitable for distributed deployment]
    • TCP/IP – when dealing with slow network or wide area. Seems like it’s a bad design when putting your SQL Server away from the app and letting it access over slow network.
    • VIA – applies to special case of Virtual Interface Adapter hardware – rare.

Configure Network Protocol Available for Client Application

  • Consuming application [e.g. ASP.NET] should be configured using SQL Native Client Configuration node:

SQL Server Configuration Manager

  • Order means which protocol being attempted first. Disable those that do not apply. For example, if the app access the SQL Server instance over the wire, disable Shared Memory protocol to short circuit protocol selection.

Configure Network Protocol Using Connection String

  • Use connection string to specify specific protocol following this pattern -  tcp:<serverName>\<instanceName>,protocolNumer
    • Ex. “server=tcp:.\sqlexpress, 1431; …”

Summary

Best case scenario:

  • Configure SQL Server’s Network protocol to use only one, Named Pipes, for example, as it appears to be the fastest.
  • Configure native client to use the same protocol that’s available for the SQL Server instance. This will reduce the number of attempted protocols, saves time.
  • Do not specify any protocols in the connection string to avoid the administration/configuration errors during deployment.