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.