SQL Server communication protocol issue

Question

Hello everyone,

I am using VSTS 2008 + C# + ADO.Net + SQL Server 2008. My questions about what kinds of communication protocols SQL Server 2008 will be using, more details of my questions,

1. If the connection string looks like this, whether Named Pipe or TCP/IP is used? Will different communication protocol being used dependent on whether client and SQL Server on the same machine?

   Data Source=labtest1;Initial Catalog=CustomerDB;Trusted_Connection=true;Asynchronous

2. In SQL Server Configuration Manager, there are items called "SQL Server Network Configuration" and "SQL Native Client 10.0 Configuration". I find both of them has configuration options (for communication protocols) of Named Pipe or TCP/IP, what are the differences between "SQL Server Network Configuration" and "SQL Native Client 10.0 Configuration"?

thanks in advance, George

Answer 1

According to SQL Server 2008 Books Online, this is what happens:

Connecting Locally on the Server

When you connect to the Database Engine from a local client (the client application and SQL Server are on the same computer), the connection uses shared memory protocol by default. SQL Server Native Client accepts any of the following formats to connect locally using the shared memory protocol:

• ""
• "\" for a named instance "(local)"
• "(local)\" for a named instance
• "Localhost"
• "localhost\" for a named instance
• A single period "."
• ".\" for a named instance

To connect locally using another protocol (for troubleshooting), do one of the following with the protocol enabled:

• Connect to a client alias that specifies a protocol. For more information, see "Aliases" in SQL Server Configuration Manager help.
• Prefix the computer name with the protocol (for example, "np:" or "tcp:").
• Connect to the IP address which results in a TCP/IP connection.
• Connect to the fully qualified domain name (FQDN) which results in a TCP/IP connection (for example, "..com"

Connecting over the Network

By default, the default protocol for SQL Server clients is TCP/IP. If the connection cannot be made using TCP/IP, the other enabled protocols are attempted. A shared memory connection cannot be made over a network. Use SQL Server Configuration Manager to enable or disable client protocols, and to change the order in which connection attempts are made.

In the Sql Server Configuration Manager, you can define an order for the client protocols - I would assume that's the order the client tries to connect to SQL Server with. Whichever protocol is the first being supported by the server will be used.

AS for the configuration utility:

• SQL Server Network Configuration is all about configuration of network protocols on the server side of things
• SQL Native Client Configuration is the client-side configuration

If you have a dev box with both on it, you'll see both. A regular PC connecting to a SQL Server typically doesn't have SQL Server itself installed on it locally, so the server-side settings are meaningless there, obviously.

Marc