Thursday, March 3, 2011

How To Determine What Applications Are Accessing SQL Server

When upgrading or decommissioning an existing server, one of the things you will need to know is what applications are accessing data on the server? The first place to start is SQL Server Profiler (Trace). By choosing the correct events to capture, this tool will allow you to track all the applications and users that login to the server and access SQL for as long as the trace is running.

Since you want your trace to be as light-weight as possible (server side tracing is also an option here), you will only select one event, Audit Login. By default the TextData, ApplicationName, NTUserName, LoginName, ClientProcessID, SPID, and StartTime are selected. Another piece of handy information is knowing where the request originated from, i.e. HostName. To see the HostName column, check Show All Columns.

Once you’ve done this click Run. Now, every time an application connects to SQL Server, you will see the name of the application, the user that is connecting, and what computer originated the request. Using this information as a starting point, you should be able to begin preparations for testing applications in the event of an upgrade or moving connections in the case of decommissioning.

No comments:

Post a Comment