January 28, 2020 at 12:39 am
I am having trouble finding some clear definitions of these things. Are the following statements true or false?
January 28, 2020 at 2:18 am
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
January 28, 2020 at 12:13 pm
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
February 1, 2020 at 12:07 am
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.
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?
February 3, 2020 at 9:25 am
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
February 7, 2020 at 3:33 am
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.
February 7, 2020 at 8:53 am
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
February 7, 2020 at 8:04 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply