SQL traffic and usage

I am putting a plan together to upgrade a SQL 2008 R2 to 2016.
New to the work-place therefore not sure who / what is hitting and using this server remotely and/or by connecting.
What's the best way to find out what and who is hitting this box so I can be proactively be able to upgrade the box?
thx

JE
John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?
 
ValentinoVBI ConsultantCommented:
Terminal Services Manager and sp_who2 only return the active connections at that moment. Given the situation I believe John will want to monitor the incoming connections over a period of time. Profiler is one option.  sp_who2 can indeed be an option too but it would need to be put in a loop and the output would need to be written to a table. The loop could contain a statement to pause it for a bit, like WAITFOR DELAY '00:00:05' (waits 5 seconds). Preferably this would be put in a SQL Agent job so it doesn't get interrupted. Start the job for a while, stop it when you're happy with the collected data.

My 2 cents :)

VV
0
 
ITSysTechSenior Systems AdministratorCommented:
You would go to start then search and type in Terminal Services Manager then select your server on the left then on the right you will see the users tab and sessions tab to view whom is hitting it.

1.png
0
 
RobertSystem AdminCommented:
If you mean the SQL traffic use SQL profiler to capture the connections.
You would want to limit it so that you don't create a huge capture file.
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
I am glad that we are thinking the same.  The profiler is the best way to start the investigation.  Thx
JohnE
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need SQL Profiler if you just want to see if somebody is connected. You can just open a new query window in SSMS and run the following command:
exec sp_who2

Open in new window

Or open SSMS and right-click on SQL Server instance name and then choose the "Activity Monitor" option. Wait few seconds and it will show a graphic with the current connections.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
My understanding of author's question is he wants to know when is possible to perform the upgrade, meaning when nobody is connected.
If not, then yes, SQL Profiler should be used. Otherwise to get an actual report of who's connected, just check the current activity in SQL Server. Terminal Services connections doesn't say who's connected to the database.
0
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
good morning all,
excellent answers and suggestions.

To be able to in-place upgrade from 1 version to another would requires me to understand the in-bound and out-bound processes and therefore their compatibility levels during the pre-upgrade.

The outbound is easy, as many suggested.  The in-bound is a bit tricky because there are several servers that have either SSIS running and hitting this box, which is easy to find out (well takes time a bit) and there are hundred of OS level scheduled tasks that are written in VB.NET and scheduled.

Perhaps the profiler is indeed one of the good solutions.
I do appreciate your insight and excellent suggestions.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
John, if you want, you can use DEA to help you with the migration assessment. I've wrote an article that may help you working with DEA.
1
 
John EsraeloDatabase / SQL DeveloperAuthor Commented:
thank you all.
I was able to find out that there were a few SSIS packages hitting the box and the profiler revealed a few pieces of info
thx
 again
JohnE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.