Configuring Application Data Access and Network Protocols for SQL Server
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
- 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:
- 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; …”
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.