How muthukkumarankaliyamoorthy/sql-automon-free-monitoring-tool works
Project type and purpose
• This is a database-centric monitoring solution (“SQL Server AutoMon”) built entirely in T-SQL and SQL Server Agent jobs.
• Purpose: centrally collect performance and health data (CPU, RAM, disk, backup status, etc.) from many SQL instances via Linked Servers, store it in two databases (DBAData and DBAData_Archive), trigger alerts by Database Mail, and serve SSRS reports and trend graphs.
File structure
• Top-level directories and files:
• Load_into_DBAdata – scripts to seed and load inventory tables in DBAData (ETL staging).
• SSRS – Data-warehouse-ready views and stored procedures used by SSRS for trend dashboards.
• Other-Scripts-Copied – reference copies of third-party scripts (dbatools, legacy T-SQL utilities).
• Workouts, Other_DBS – documentation and scripts for specific scenarios; not part of core run-time.
• 1_0_1_… , 2_… , 3_… , etc. – numbered data-collection or management stored procedures and queries.
• README.md, LICENSE – project overview and legal.
• Configuration artifacts:
• DBA_All_Servers, DBA_All_Operators tables define targets and recipients.
• @PROFILE_NAME and @EMAILIDS parameters in each script for Database Mail.
• Job_creation_DBA_automon_SP.sql 定义 Agent jobs.
• CMS scripts for Central Management Server registration.
README for additional insights
• Two databases required:
• DBAData (current data)
• DBAData_Archive (historical data)
• Continuous data collection via Agent jobs and Linked Servers.
• Alerting via Database Mail profile.
• Reporting integration via SSRS (trend reports).
Main components and their interactions
a. Components
• AutoMon SQL Server Instance
– Databases: DBAData (OLTP), DBAData_Archive (archive)
– SQL Agent (schedules jobs)
– Linked Servers to each target instance
– Database Mail (configured mail profile)
• Target SQL Servers
– Expose performance data via built-in DMVs and sys tables
• SSRS Report Server
– Connects to DBAData for dashboards
• DMZ or corporate mail server / SMTP
• (Optional) Central Management Server (CMS) for server registration
b. Relationships / Data flows
1. SQL Agent triggers Job → runs numbered SP scripts.
2. Each script uses Linked Server to pull metrics from Target SQL Server.
3. Data loaded into staging tables in DBAData (via Load_into_DBAdata scripts) or direct into production tables.
4. Archive process moves old data from DBAData to DBAData_Archive.
5. Database Mail routines in stored procedures send alert emails based on thresholds and operator table.
6. SSRS server queries DBAData to render trend graphs and capacity planning dashboards.
c. Architectural patterns
• Central collector pattern – one “collector” instance polls many targets.
• ETL pipeline – staging, load, transform, archive.
• Scheduled jobs as orchestrator.
• Linked Server abstraction for remote connectivity.
d. Key technologies
• Microsoft SQL Server (Agent, Database Mail, Linked Servers)
• T-SQL Stored Procedures and Scripts
• SSRS (Report Builder, Data Sources)
• SMTP / Database Mail profile
• CMS (optional) for central management registration