none
Data Source Format in SQL Server Connection String

    Question

  • Hi,

    I'm using VS2008 and SQL Server 2005 Express. The following code lists the servers available and their instances:

    DataTable table = SqlDataSourceEnumerator.Instance.GetDataSources();
    foreach (DataRow r in table.Rows)
        {
            Debug.Print("server = '{0}', instance = '{1}'", r["ServerName"], r["InstanceName"]);
        }
    

    As far as my understanding goes, in order to successfully connect to a server I need to use something like:

    Data Source = serverName\instanceName.

    What happens if for some servers the instance name is the null string? What does this mean?

    Kind regards,

    Carlos Mallen

    Thursday, February 23, 2012 4:06 PM

Answers

  • Hi Carlos Mallen,

    Welcome to MSDN Forum.

    If you only appoint a server name, it means it will use the default instance. More information about it, please refer to the link below.

    Instance Configuration

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 27, 2012 8:40 AM
  • Just a quick summary to get your head straight.

    SQL Server can have multiple "instances" of a database service on the same computer.   These are called named instances.   Each named instance will listen on a different port.   You can create up to 16 named instances on a single computer.

    When connecting you have 2 options.  #1 you can specify computer name and instance name in the Data Source.  #2 you can specify computer name and port number if the instance is listening on tcp protocol.

    For local connectivity, it is easier to just use the instance name, you can abbreviate local SQL by using (local) or ., for example:

    Data Source=.\MyInstance;

    or

    Data Source=(local)\MyInstance;

    The local connectivity uses a special protocol called "shared memory".  This allows client to connect without having to use a tcp socket.   This is also safer for a local only copy of SQL Server to not expose itself to remote tcp sockets, so often they have the tcp sockets turned off.

    For remote connections, you use the computer name  and instance name or computer name and port.  If you use the instance name, this requires you setup and enable the SQL Browser service.  The SQL Browser service is the service that resolves the port for the remote clients, so it converts MyInstance to the appropriate tcp port for the client.

    Hope this makes it clearer!


    Matt

    Monday, February 27, 2012 5:47 PM

All replies

  • Hi Carlos Mallen,

    Welcome to MSDN Forum.

    If you only appoint a server name, it means it will use the default instance. More information about it, please refer to the link below.

    Instance Configuration

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, February 27, 2012 8:40 AM
  • Just a quick summary to get your head straight.

    SQL Server can have multiple "instances" of a database service on the same computer.   These are called named instances.   Each named instance will listen on a different port.   You can create up to 16 named instances on a single computer.

    When connecting you have 2 options.  #1 you can specify computer name and instance name in the Data Source.  #2 you can specify computer name and port number if the instance is listening on tcp protocol.

    For local connectivity, it is easier to just use the instance name, you can abbreviate local SQL by using (local) or ., for example:

    Data Source=.\MyInstance;

    or

    Data Source=(local)\MyInstance;

    The local connectivity uses a special protocol called "shared memory".  This allows client to connect without having to use a tcp socket.   This is also safer for a local only copy of SQL Server to not expose itself to remote tcp sockets, so often they have the tcp sockets turned off.

    For remote connections, you use the computer name  and instance name or computer name and port.  If you use the instance name, this requires you setup and enable the SQL Browser service.  The SQL Browser service is the service that resolves the port for the remote clients, so it converts MyInstance to the appropriate tcp port for the client.

    Hope this makes it clearer!


    Matt

    Monday, February 27, 2012 5:47 PM