Difference between connection, session and instance

  • michael.leach2015

    SSCommitted

    Points: 1546

    I am having trouble finding some clear definitions of these things.  Are the following statements true or false?

    1. Each instance of  SQL Server is an installation of SQL Server.
    2. A server can have more than one instance (installation) of SQL Server on it.
    3. You can open more than one query tab in SSMS.  Each tab represents a connection.
    4. A connection and a session are the same thing.
  • Sue_H

    SSC Guru

    Points: 90544

    A connection and a session aren't the same thing. Often you have one session on each connection but there are exceptions. For example, you can have multiple sessions on a connection with something like MARS (multiple active result sets). You can also have sessions with no connection with system sessions, service broker activated procedures. You can have connections without sessions with service broker connections or mirroring connections.

    Sue

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21507

    my advice - simplify the connection/session thing into SPIDs - much more useful.

    quite often a connection will remain idle and the SPID does nothing (this gets more complicated when connection pooling is involved)

    as for servers and instances

    a server can have many instances each instance has its own confirguration

    Each instance has it's own databases and acts as a server in it's own right, but having to share resources with the other instances on the physical server

    a good example - my laptop is a "server" - I have a sql2017 instance installed and a sql2019 instance installed (for testing purposes) - but there is nothing to stop me putting 10 more SQL2019 instances on my laptop, they just have to have different names

    server1\2017

    server1\2019

    Server1\my2019test

    MVDBA

  • michael.leach2015

    SSCommitted

    Points: 1546

    MVDBA,

    This part of your post really cleared things up for me about what exactly an instance is.  Sounds like each time you install SSMS, that installation is called an instance.  This is what I thought it would be.  Thank you.

    MVDBA (Mike Vessey) wrote:

    a good example - my laptop is a "server" - I have a sql2017 instance installed and a sql2019 instance installed (for testing purposes) - but there is nothing to stop me putting 10 more SQL2019 instances on my laptop, they just have to have different names

    server1\2017

    server1\2019

    Server1\my2019test

    I'm still unclear about what a session is and also how exactly a connection is defined.  If you can give an example of when a session is created and when it is terminated, I think that would really help, as well as an example of how a single user would create multiple sessions (if that is possible).

    As for a connection, when you log on to SSMS with Windows Authentication or SQL Authentication, isn't that when a connection is established?  I would think so.

    However, in a video I was watching, the guy opened a new query tab and called that a new connection which also makes sense.  So is a connection established when you logon to SSMS or is a connection established each time you open a new query tab?

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21507

    michael.leach2015 wrote:

    MVDBA,

    This part of your post really cleared things up for me about what exactly an instance is.  Sounds like each time you install SSMS, that installation is called an instance.  This is what I thought it would be.  Thank you.

    MVDBA (Mike Vessey) wrote:

    a good example - my laptop is a "server" - I have a sql2017 instance installed and a sql2019 instance installed (for testing purposes) - but there is nothing to stop me putting 10 more SQL2019 instances on my laptop, they just have to have different names

    server1\2017

    server1\2019

    Server1\my2019test

    I'm still unclear about what a session is and also how exactly a connection is defined.  If you can give an example of when a session is created and when it is terminated, I think that would really help, as well as an example of how a single user would create multiple sessions (if that is possible).

    As for a connection, when you log on to SSMS with Windows Authentication or SQL Authentication, isn't that when a connection is established?  I would think so.

    However, in a video I was watching, the guy opened a new query tab and called that a new connection which also makes sense.  So is a connection established when you logon to SSMS or is a connection established each time you open a new query tab?

    be careful with your terminology. SSMS is SQL server management studio - the tool to connect to SQL as a user - it is not an instance. an instance is a service that gives you the data.

    A connection is just a link between 2 devices you can leave the connection open forever (or until reboot) - the connection does nothing at all - it is just  a tunnel between client and server. a session is an execution on that connection

    bit of pseudo code here

    declare ado.net connection

    declare ado.command

    execute command

    close connection

    this is a simple connection and single command with 1 session

    if you had a single connection but that used connection pooling or some form of pararellism then you could run multiple queries on the same connection - multiple SPID

    here's a really good way so show it - run sp_who2 on your local server. don't even run a query, just click on an object - management studio will have 1 connection but will create multiple sessions

    sorry pal, best way I can explain it.

    MVDBA

  • michael.leach2015

    SSCommitted

    Points: 1546

    Thank you.  Since my original post, I have determined that each query tab is a session because each tab has it's own unique SPID.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21507

    michael.leach2015 wrote:

    Thank you.  Since my original post, I have determined that each query tab is a session because each tab has it's own unique SPID.

    we didn't know that you didn't know this - the SPID number is top left in your tab and also bottom right in management studio - it's in brackets

    MVDBA

  • michael.leach2015

    SSCommitted

    Points: 1546

    MVDBA (Mike Vessey) wrote:

    michael.leach2015 wrote:

    Thank you.  Since my original post, I have determined that each query tab is a session because each tab has it's own unique SPID.

    we didn't know that you didn't know this - the SPID number is top left in your tab and also bottom right in management studio - it's in brackets

    I didn't notice that before.  Good to know.  Thank you for pointing that out.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply