We all experienced how the number of database instances kept growing (especially SQL Servers) in the last few years.
Managing tens of server instances is commonplace, but more and more DBA’s are tasked with managing hundreds or even thousands of instances – and there is no end in sight for the growth of instances.
How does this affect us? Will the same tools and methods we used when managing a handful of instances scale up to meet the challenges of managing large database server estates, or do we need to develop new and more efficient methods and tools to meet the challenges?
In dbWatch, we have spent a lot of time working on this type of questions, working closely with some of our largest customers who typically have estates ranging from a few hundred to thousands of instances, mostly SQL Server, but always including a mix of other platforms such as Oracle, PostgreSQL, MySQL or others, on-premise or in hybrid cloud scenarios.
First, we thought making tools that would scale up to handle thousands of instances was enough. It isn’t. We still could not see the forest for all the trees. We needed new functionality that let you analyze and manage the entire estate or parts of it. So, one of the first conclusion we reached is that we need to differentiate monitoring/managing instances from managing the entire estate. It is simply two different tasks needing separate tools.
Managing and monitoring database instances focus on optimizing the performance and uptime of the individual instance. It is usually event-driven – alarms, warnings, or complaining users lead you to investigate and take corrective action. This is very often the everyday firefighting, so many DBA’s spend their time with, going from one instance and crisis to the next crisis.
Database server estate management is very different. The objective of estate management is to provide an overview of all resources, inventory, and insight information to aid in capacity and performance planning and to plan and prioritize DBA tasks and projects.
Overview: The first thing we need in estate management is an overview of all resources. That includes a full inventory of all servers and instances and information on each instance, such as version, memory, disk, CPU, and status. Automatic scanning of subnets for new instances will aid in creating and maintaining complete inventories.
Resource utilization: Compare the actual allocation of disk, memory, and CPU to the actual utilization of the same resource.
Performance indicators: For the entire estate, see which instances are suffering the most from heavy loads and which are lightly loaded, idle, or no longer used.
Based on all this data, you will need tools to analyze and utilize the information. You should be able to sort the data on any parameter, like pivot tables in excel, so you can find answers to questions like:
- Which servers are the most heavily loaded?
- How many SQL Server 2008 do we still have?
- Which servers are no longer in use or idled?
- Which servers need more memory the most?
- Which servers can we “steal” unused memory or CPU from?
Planning: We can also use this information to monitor long-term growth in resource consumption – disk, CPU, or memory- to predict future requirements and make plans and budgets accordingly. Short term, it will aid in allocating DBAs to the jobs that will be most beneficial to the overall resource utilization and performance of the estate.
Estate management is proactive. It is concerned with optimizing resource usage across the estate. You want to identify where you are short on resources, be it disk, memory, CPU, or other, and identify where you have under-utilized resources. Based on this, you can identify and plan reallocating memory or CPU resources, where you can consolidate or decommission servers and split out servers to improve performance.
What we now see on large estates is that we use the global dashboards to analyze the estate and plan work to optimize DBA time to work on individual instances where it will have the most value and effect.
This is a more structured and pro-active workflow that focuses on optimizing the estate's resource usage and DBA time.
This is a topic Per Christopher, one of our Sr DBAs, discussed in this webinar held for PASS DBA Fundamentals group. He also shows more examples of estate monitoring and management in these videos on estate capacity and performance management.
