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
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
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.