Challenged by the lack of budget and/or ill-place spending I’ve come to the conclusion a custom system is required for this diverse SQL Server environment. In 2012 created a centralized database to manage the maintenance of all instances. It is far less expensive than third-party tools.Heck getting fully licensed on some of those tools would be as expensive as my salary.
The system includes SQL Agent Jobs that collect statistics from each instance and records those in a centralized database. Trying to keep locally created objects to a minimum only views are created in the master database under a defined schema so there is no confusion the source of the objects. The individual dynamic management views present an overwhelming amount of information and frankly, I don’t understand all of it.
All the data collected is presented in SQL Server Reporting Services (SSRS) reports. These reports are at the Enterprise level down to the database level. The son_db database in the Elite system is very important so special daily reports are created just for that server and others just as important. Subscriptions are sent to me and others where the reports identify problems like out of space issues, missed back-ups, etc.
- Weekly Powershell script to scan the network for servers running SQL Server services
- Daily job to inventory instances
- Daily Job collecting maintenance information
- Daily Full Backups
- Daily Log Backups
- Weekly baseline collection
I’m looking forward to building this package out and possibly adding a front-end. I’m not much of an application developer, so that may be more than a year away.