The DBA Repository Project – What the heck is going on in my data infrastructure and how do I know it?
I manage nearly1,100 databases spread across 62 servers consuming over 7.2 TB. Most of my server instances are SQL Server but I have an Oracle server, a few MySQL servers and a Sybase server. Keeping an eye on all the disparate systems – making sure backups are performed correctly, automated jobs are successful and performance is up to snuff – can be a challenge sometimes. There are a number of great tools out there to help monitor the going’s on of my servers, Microsoft’s System Center Operations Manager and SQLSentry to name two. But they don’t do everything I want the way I want so I decided to make my own tool – the DBA Repository.
My goals with the DBA Repository are twofold:
- More easily troubleshoot day-to-day problems
- Proactively search for potential future challenges and solve them before they become expensive nightmares
I decided to focus on four primary aspects of database management:
- Backups/Scheduled Jobs
- Business Intelligence Utilization
Daily, I need to know that my backups successfully ran as expected and I need to quickly know what may have failed and how to correct it to prevent gaps in my backup protection. I also need to know that my customer’s scheduled jobs successfully ran when they were expected to run. My time in Information Technology has taught me one important thing; don’t rely on error reports … check for success.
I need to know all my servers are patched appropriately and if not why not. I need to know I am in license compliance. I need to keep up with industry best practices on server configurations to reduce the surface area for attack and know which servers may require a vulnerable feature so I can limit exposure to the rest of the enterprise in the event something is compromised. I need to easily see which servers may be a security liability and when a configuration change creates that liability so it can be immediately corrected.
I need to know which servers’ hardware is nearing its physical limits before my customers experience the results of an overwhelmed server which could be the result of an under performing virtual server or a server with too many databases/instances. I also need to be able to easily find performance problems when my in-house developers create a new application or if a vendor application is not functioning to the customer’s expectations. My organization is on a five year hardware replacement cycle and I need to know database servers’ performance trends over time to I can reliably determine what scale of hardware to purchase at the least possible price to accommodate five year’s growth.
Business Intelligence Utilization
My organization has really found the value of Business Intelligence over the last two years. We have about 30 employees sprinkled throughout our organization with varying degrees of technical skill. All of them use SQL Server Reporting Services 2008 (SSRS) to present data to management and staff of their various divisions. Many of them utilize SQL Server Integration Services 2005 & 2008 (SSIS) to move data between disparate systems or to populate data warehouses to aggregate data from myriad applications. A few of them use SQL Server Analysis Services 2008 (SSAS) to perform complex analysis on large amounts of data to solve business problems and quickly answer complicated questions posed by executive management and City Council. I need to monitor the exponential growth and utilization of these services to ensure we can handle the demands placed on the data infrastructure.
So, there’s a lot going on where I work and I need to make sure me and my infrastructure are positioned to accommodate current and future needs. This project will be created with only SQL Server tools. I decided not to do any C# programming so a DBA without programming training can maintain this tool without any additional education. This ongoing series of posts will illuminate what I build to answer my needs in the four categories above. Let’s hope I’m successful.
First things first – we’ll make a server list.