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?

John EsraeloDatabase / SQL DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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.
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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.
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.
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.