Decommissioning: how to list clients/applications depending on my SQL Server?

I have a SQL box that I want to decommission. Before doing so, I want to understand all the applications that may be relying on the machine, rather than just turning it off and hearing people scream.

What is the best way to do this?

I know I can run a SQL trace for a while to get login info etc - is there any other way?

I'd like to know as much about the caller including, IP address or machine name that the request is coming from, the app name, credentials. Mostly I want to somehow know if many apps might be running on a single box calling with similar credentials. I'd also like to be able to know that there are 2+ apps (for example) rather than them just look like one.

If SQL Server itself doesn't give me this, are there other tools at the server level that I can leverage?

Solution 1:

You might want to take a look at WireShark. You can install it on the SQL box, and monitor all the incoming requests to the SQL server. I'd narrow the filter in wireshark down to just things making requests on your SQL ports. I'm not sure how long you want it to run for - but once you are done you can then take that trace and run queries against it via filters to get things back like distinct IP's etc...

Solution 2:

I think that your idea of running a trace for a while is the best idea at this point. There is no other way of finding this info out definitively - you can't know what clients "might" connect, you can only see what clients "do" connect.

Some things may help you guess though:

1. Make a list of all the databases. Try to figure out that applications are likely to be using them.
2. Make a list of all of the SQL logins. That may help you with the above point. If everyone is connecting as SA, well, that's one of the reasons that's not a good idea.
3. Review any of your documentation - good documentation in the future will prevent this from happening again, that is hard to stress enough.

Also, when you decomm it : just unplug the LAN cable for a week before you shut it down. Then, it is literally a 1-second fix to bring it back up if someone DOES need something on it, you don't risk anything like an old disk refusing to spin up again. And of course, back up the whole bloody thing before you unplug it.