.Net and SQL Server Diagnostics

Question

Our company has many .Net applications on many servers which utilize many different SQL Servers. We do not have a clear idea of what is using what. It would be great to get a full picture of what's going on down to the smallest details. Especially important is to know what App is connecting to what Database.

Idea's so far have been to aggregate many different log types such as: SQL Profile, IIS Logs, and Perfmon.

What will give me the details I need? Am I overlooking the logs I currently have at hand? Please help. Thanks in advance.

Starting to look at SysInternal's from Microsoft: http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx

Answer 1

Can you possibly consider existing third-party apps?

Check out Quest's Spotlight on SQL Server product - it would do all you're looking for, I believe.

Marc

(Disclaimer: I used to work for Quest almost 10 years ago, but not on this tool and not in this business area. I have no connection to Quest at this time, other than being a "Quest alumni")

Answer 2

One easy thing you can do is make sure your connection strings take advantage of the "Application Name" element.

Server=OurSqlServer;Database=AppDB;Integrated Security=SSPI;Application Name=Our Application Name

When you look at things like profiling this will be much easier to differentiate.

Another good practice would be to ensure each app is connecting with it's own set of credentials.