When the number of SQL Servers grows along with your business, it is often difficult to keep up. A lack of coordination or database administrators can exacerbate problems associated with this growth. Consolidating and optimizing your servers will go a long way toward reducing the cost and complexity of your database environment. This brief is the first in a series of articles on optimizing SQL Server environments and SQL Monitoring, describing the initial steps to take toward consolidation.
This is not about the virtualization of database servers. This is about the steps you should take before you consider if and how best to virtualize servers. When you are done with consolidation, you have trimmed down your SQL Server farm to the optimum configuration and have the best possible starting point for virtualization. If you have already virtualized your environment, you can then consolidate your SQL Server instances.
The SQL Server Explosion
The number of databases using MS SQL Server has exploded since its launch of version 2005. Its increased capability and reliability have made it the platform of choice for more critical enterprise applications, especially given its Oracle competitor's high fees and complex licensing. Vendors typically recommend a dedicated SQL Server for their applications – often accompanied by excessive recommendations for size, functionality, and performance, along with unique versioning and patching requirements. The unchecked growth resulting from these separate requirements, along with the associated need for additional test, development, and training servers, results in a large, fragmented, and often unmanageable database environment.
This wasteful situation leads to unnecessary hardware, licensing, and administrative costs. Without enough in-house database expertise you are stuck following application vendor recommendations. With the help of an experienced and competent SQL Server database administrator (DBA), you can take back control of your server environment through a series of specific tasks.
The first step, probably the most crucial, is to establish all of the routines and processes necessary for collecting the relevant information and statistics about your systems. This information will serve as a basis for simulation, followed by the complete restructuring of the database environment. Restructuring consists of both consolidating necessary systems as well as phasing out redundant or unused servers. This step will have the largest impact on your server environment's final structure and function, so it is critical that all relevant information be collected as precisely and accurately as possible.
To achieve an optimal database environment, we recommend the following data collection procedures for each MS SQL Server instance. Collect statistics for:
- the amount of data being accessed (logical reads) on each entire instance
- the transaction volume of the ten most active databases per instance, and the totals on each entire instance
- memory usage (data cache) per database on the top 10 databases per instance
- databases with the least number (or zero) transactions per instance
- the number of connection per instance (and for ten databases with the most sessions)
- the filling ratio on the ten largest file groups per instance, and the total size of all database files
In the next briefs I will discuss the following:
- Implementing monitoring procedures.
- Important factors when consolidating databases
- Extracting relevant information for consolidation from activity- statistics
- I have 150 SQL Servers. How many do I need? How to simulate a new, optimal database environment
Read more about how dbWatch can help you optimize your SQL Server environment.
