As time goes by and software evolves, SQL Monitoring solutions are becoming more and more robust and more and more effective. Two of my favorite solutions Red-Gate’s SQL Monitor 3.0 and Idera’s SQL Diagnostic Manager. Yes they are direct competitors, but I tested beta version, trial versions and I think both solutions are stellar. However, as time goes on and as the economy continues to struggle, businesses are showing more and more reluctance on buying “un-necessary” 3rd party software. Of course “un-necessary” is a relative term, some of us DBA find these types of software critical for 24 x7 operations.
So in an endeavor to provide some of their functionality, I am planning on building my own SQL monitoring solution. Yes I know many have done such things, but call it a personal mission that I have wanted to accomplish for some time.
The hardest part was to determine if I wanted a SQL Solution (SSIS, SSRS) or a application solution (.NET, web GUI, application GUI, windows services, etc.). I believe I am going to make a hybrid of the two. I hope to use SSIS, custom stored procedures, SQL Agents jobs to collect data and store that data in a database. Then create a .NET 4.0 Web Application leveraging SSRS technology as well. If the Web app, is on a public server, or in the DMZ, then theoretically it could be available away from the office as well!
In the next chapter, I will map out the very basic information I want to collect from a MS SQL Server Instance and provide the T-SQL to get that information.