.Net and SQL Server Diagnostics
We have many .Net applications on many servers that use many different SQL servers. We don't have a clear idea of what is being used. It would be great to get a complete picture of what's going on down to the smallest detail. It is especially important to know which application is connecting to which database.
The idea so far has been to combine many different types of logs such as: SQL Profile, IIS logs and Perfmon.
Which will give me the details I need? Can I view the magazines I currently have on hand? Please help. Thanks in advance.
Let's start with Microsoft's SysInternal: http://technet.microsoft.com/en-us/sysinternals/bb842062.aspx
source to share
One simple thing you can do is make sure your connection strings use the Application Name element.
Server=OurSqlServer;Database=AppDB;Integrated Security=SSPI;Application Name=Our Application Name
When you look at things like profiling, it will be much easier to distinguish.
Another good practice is to ensure that each application connects to it with its own set of credentials.
source to share
Perhaps you can consider existing third party apps?
Check out Quest Spotlight on SQL Server - it will do whatever you're looking for, I suppose.
Mark
(Disclaimer: I worked almost ten years ago at Quest, but not on this tool, not in this area of business. I currently have no connection with Quest other than as "quest alumni")
source to share