Difference between these connection strings?

Question

Can anybody tell me the effective difference between the following connection strings:

<add key="ConnectionString" value="server=tcp:192.168.0.12\Sqlserver2005;database=;user id=sa;password=;">



<add key="ConnectionString" value="server=192.168.0.12\Sqlserver2005;database=;user id=sa;password=;Network Library=DBMSSOCN;">

I believe both are effectively the same. Specifying "Network Library=DBMSSOCN" explicitly connects using TCPIP and prefixing server value with TCP does the same thing.

Please tell me if there is any difference or any performance implication of specifying these settings in web.config.

Answer 1

The difference between the two options is:

Specifying the protocol

By using either,

Multiprotocol = rpc

Shared Memory = lpc

NWlink IPX / SPX = spx

Banyan VINES = vines

Apple Talk = adsp

TCP = tcp

This is the recommended way. I also feel its safer as DLL names are likelier to change than protocol names.

It also allows you to change the port number, rather than configuring it across the board by using the SQL Server Client Network Utility.

Specyfying the Library without the dll extension

Specifying the library will use the same name as the actual network DLL library file without the .dll extension.

Example:

TCP/IP: C:\WINDOWS\system32\DBMSSOCN.dll

Named Pipes: C:\WINDOWS\system32\DBNMPNTW.dll

Multiprotocol (RPC): C:\WINDOWS\system32\DBMSRPCN.dll

NWLink IPX/SPX: C:\WINDOWS\system32\DBMSSPXN.dll

AppleTalk: C:\WINDOWS\system32\DBMSADSN.dll

Banyan VINES: C:\WINDOWS\system32\DBMSVINN.dll

This will work best when you want to write your own network library. Please see SQL Server Client Network Utility.

The performance trade-off is minute and not noticeable. It is best to stick to standards and have it configured in the web.config as other developers will know where to look for the connections settings. Similar to having a data-source connection in java.