Understand Sql Server connectionstring for asp.net

Question

Hi,

I am trying to understand the differences between the following 2 connectionstrings. one uses servername\instancename and the other one uses the server ip address.

Can I specify port number for "serverName\instanceName". I know you can specify port number for ip address, something like '10.0.0.1,xxx'.

thanks,

Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;

Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

Answer 1

Mostly, its unimportant if you don't have duplicate instances.

For example, SQL supports multiple instances on the same box as a way of isolating running instances. Most of the time, you will never do this, and thus can get away with using the IP address in the connection string. If for some reason, you need to run multiple instances of SQL server on the same machine, you need to specify which instance you actually want to connect to.

This covers it in more detail.

Answer 2

Quite briefly:

• if you have just server=(machinename) or server=(ipaddress) then you're connecting to the default instance of SQL Server on that machine (no name for the instance)

• if you have server=(machinename)\InstanceName, then you're connecting to a named instance of SQL Server on that machine

Each physical server machine can have one default instance of SQL Server, and any number of named instances (names must be different, obviously).

To connect to a named instance and specify a non-standard port, use this syntax here:

server=(machinename)\InstanceName,xxxx   

or

server=(ip-address)\InstanceName,xxxx   

where the xxxx stands for the port you want to use.