SQL Server Consolidation

One of the biggest trends I'm currently seeing with my clients is the desire for a consolidated SQL Server environment. However, just because my clients think they need to consolidate doesn't mean they're ready to do so, or even necessarily that they should. Before you consider implementing a consolidated SQL Server environment, you must assess your existing environment to determine whether planning a consolidation is even possible—or worth your time. (Because the analysis and assessment can be time consuming, you might want to obtain help; see the sidebar "Getting Help" for more information.) This article outlines the steps you need to take to gather the information necessary for making such an assessment. The solution that I discuss involves workload consolidation; for an alternative consolidation solution, see the Web-exclusive sidebar "Different Paths to Server Consolidation: Workload vs. Virtualization."

Driving Factors

Consolidation tends to be a top-down rather than bottom-up business decision. In most cases, upper management wants to do more with less and hopes to recover some costs. Thus, consolidating SQL Server is only part of a much larger transformation within a company. Another case in which a company might push for SQL Server consolidation is if its data center is at or near capacity and building a new data center isn't an option. However, the biggest driver for consolidation that I see on a regular basis is a situation you might be familiar with: SQL Server sprawl.

SQL Server has always been a Trojan horse in many environments—it sneaks in through the back door and becomes pervasive because it's so easy to install and deploy whenever an organization needs a database server. You can install SQL Server on everything from mobile devices through servers. A side effect of this phenomenon is that it leads to a mentality of one database per instance, with that solitary database housed on a standalone server that's typically underutilized and/or has poor availability. In some cases, a company's lone SQL Server machine is a desktop machine that was never meant for production. But because people started using the instance and liked the solution built on top of the database, that desktop machine became mission critical.

These days, you might be able to gain more significant performance increases for your SQL Server installations with a shelf full of blade servers than you can with a 4U server you purchased only three years ago. Because many companies house terabytes of data that require management, you need to carefully consider everything from hardware purchases to operations. Consolidation means more than just stacking multiple SQL Server instances or putting multiple databases in one instance. You need to take into account the amount of cooling needed to ensure that the servers don't overheat, as well as the overall energy requirements for the multiple servers in your data center.

To do consolidation right, you'll most likely need to buy new servers. Assuming that you can use your existing servers might be a bad idea. If you reconfigure the servers, you won't have a true fallback plan after you consolidate. Instead, consider repurposing existing hardware to refresh development, quality assurance (QA), or staging environments. As you assess your consolidation strategy, you must decide how much and what type of new hardware you need and determine the cost.

In addition, ask yourself whether each instance in your environment was deployed and is administered in the same manner. In my onsite experience with both large and small customers, not a single one has consistently deployed SQL Server in exactly the same manner—despite the fact that each organization has posted standards. Managing these environments is a nightmare for DBAs. If each instance has different sort orders, service pack levels, database options, configuration settings, and other differences, you must remember all the variations or you might do something on one server that would be detrimental to another. Consolidation can assist in solving these problems; in fact, it's often the catalyst to standardize deployments and other paradigms (e.g., operations, processes, administration) in an environment.

What You Don't Know Will Hurt You

The first and perhaps most important thing you need to begin consolidating your environment is information. You need more than just a day's worth of Performance Monitor statistics, because a day's worth of counters won't give you enough information on which to base decisions for a proposed consolidation. In addition, numbers tell only part of the story.

First, compile a complete list of the known servers or other machines on your network with SQL Server installed. Although most clients think they know their entire SQL Server inventory, they actually don't. After you have this list, use a third-party tool (e.g., Quest Software's Reporter) or a homegrown utility (using Windows Management Instrumentation—WMI) to query all of the machines on the network that have any kind of SQL Server resource (including products such as Microsoft SQL Server Desktop Engine—MSDE). Then, compare the lists to see whether any new SQL Server installations were discovered. If the lists don't match, you need to determine who owns the newly discovered instances, what they're used for, and why you didn't know about them.

Next, record all aspects of SQL Server (e.g., physical machine information such as processor type and speed, brand, and model; all OS settings, including driver versions, service packs, and patches; instance configuration, including all server-level options and settings, code page, security, jobs, and DTS or SQL Server Integration Services—SSIS— packages; database configuration, including database options, database size, amount of free space, and size of each file). Record this information for every discovered SQL Server machine and instance. This detailed information will ultimately help you determine which databases you might be able to combine with others on a specific instance of SQL Server, as well as which databases will work well together. The information you document will influence many aspects of your consolidation assessment and plan, including your decision regarding the final instance standard (e.g., the version of SQL Server, which forms of high availability you might deploy). The documentation will also serve as a reference if you move a database and someone needs to know where it came from and whether you moved, reconfigured, or recreated objects such as jobs, DTS or SSIS packages, and replication. This might be the only chance to capture a complete snapshot of your SQL Server environment.

Although most DBAs don't track historical information about each database, you need this information (e.g., database usage, growth patterns—including size of database backups). For example, if a database has been in production for two years, knowing its growth pattern can help you predict the kind and amount of storage space you might need in the future. Merely making best guess estimates isn't an effective method for sizing an environment. If you don't have accurate information, you might wind up undersizing or grossly oversizing your proposed environment. Undersizing means you'll run out of capacity quickly, possibly even before you finish the consolidation; oversizing might cost your company money that could have been better spent elsewhere.

You can't turn back the clock and create information that doesn't exist, so do your best to track down the information you need. If you're lucky, the information might exist elsewhere, such as in a central monitoring system that has been monitoring disk counters for two years but that you didn't have access to. Talk to your systems administrators to see if they have information about your database servers that you don't have.

In addition to having growth information, you need to understand each database, instance, and server's performance. Your company most likely has a centralized tool such as Microsoft Operations Manager (MOM), NetIQ AppManager, or Computer Associates' (CA's) Unicenter that's gathering these statistics. If not, you can use Performance Monitor counters to find this information. Before you set up your own monitoring, make sure you aren't duplicating any efforts. Although you could gather statistics from every available counter, doing so would be overkill—you'd have a volume of information that you wouldn't know what to do with. Instead, gather information from a select list of counters that gives you the biggest bang for your buck. The Web-exclusive sidebar "Using Performance Monitor to Record Counters," explains how to obtain the information you need. For SQL Server counters, you can also query SQL Server 2005's Dynamic Management Views (DMVs) or SQL Server 2000's sysperfinfo.

The statistics you need to pay attention to on the OS level are CPU utilization (for the server overall, as well as for each SQL Server instance; also, per processor, as well as the sum for all processors), physical disk, network throughput, and memory utilization (for both the server itself and for SQL Server). What to capture for SQL Server is a bit more subjective because you might want to monitor specific information for your organization. Two common pieces of information to capture are transactions per second (tps) for each database and the total number of user connections. In an assessment, you're most worried about information that gives you a specific result. In this case, you're not tuning performance; you're simply gathering information about your servers.

Pay particular attention to disk I/O because this information will help with your eventual storage configuration by showing you what your current disk performance looks like. Poor disk configuration, whether from performance or space issues, is arguably the most common problem I see at nearly every client. If you aren't already capturing performance information, start doing so—at all times of the day (e.g., busy, slow, weekends). You need reliable numbers to make solid consolidation recommendations.

Applications

One of the most important pieces of data you need in your consolidation arsenal is information about the actual applications. You might wonder why a DBA would care about applications—but you should care, because the applications largely dictate how you deploy the databases. You need to compile a list of all your applications, their owners, their service level agreements (SLAs), and their use in the environment. Two kinds of applications exist: those you buy and those you develop inhouse. Although some of the information you need about each type of application is different, many aspects are common to both types.

For example, you need to know the application's security model (Windows or SQL Server authentication) and whether combining the application's databases will work with other application databases you're considering pairing the original application with. For example, suppose you have two applications that use SQL Server authentication and have a login named Sally. In one application, Sally requires systems administrator (sa) privileges in SQL Server, whereas in the other application, Sally just has read-only access to its database. Because Sally's rights already include sa access, you'll most likely be escalating rights unnecessarily for Sally in the second application. This situation makes the two applications poor candidates for consolidating on the same SQL Server instance.

You also need to know which version of SQL Server an application has been tested against. This consideration is especially important because many DBAs will want to upgrade to SQL Server 2005 as part of the consolidation process. Not all third-party vendors have certified their applications against SQL Server 2005—and even if they have, they might not have certified applications against SQL Server 2005 Service Pack 1 (SP1) or SP2. So if you determine that your standard will be SQL Server 2005 SP2 and an application supports only the release to manufacturing (RTM) version, you either need to leave the application as is or deploy it in a consolidated manner using the RTM version because SQL Server supports mixed instances levels. If you don't determine these types of requirements during the assessment phase, by the time you start planning and deploying your consolidated environment, you'll end up with an instance of SQL Server that's incompatible with your application. Similarly, you probably haven't tested your inhouse applications yet against SQL Server 2005. If SQL Server 2005 will be the end state, assuming your applications will work is a big gamble. Even if your end state will be another version of SQL Server 2000 (e.g., SQL Server 2000 SP4), the same rules apply. You need to work with the proper internal teams to ensure that everything will work after consolidation occurs.

Finally, you need to be aware of each application's dependencies and make sure these dependencies are documented. A dependency can be anything from connection strings to other servers (possibly including everything in the chain up to your Active Directory—AD—server) that the application needs to run. You especially need to take into account how every application server connects to the database server so that you can reconfigure each application to work after consolidation. Never assume that moving a database to another SQL Server machine is a transparent process; you might affect the availability of other applications and servers. You should identify these risks during your assessment so that you can carefully schedule the database move when the consolidation is planned.

Performing the Assessment

If gathering the data sounds difficult, be aware that analysis can be even trickier. Ideally, analysis is performed by an experienced person (most likely, your most senior DBA or a consultant) who truly understands the environment, the applications, the business, and most importantly, SQL Server. Simply providing an inventory of SQL Server instances and crunching numbers based on performance metrics is inadequate and often employs faulty logic (e.g., just because Server A is at 10 percent CPU and Server B is at 40 percent CPU doesn't necessarily mean that you can combine the two servers). You must consider many factors that in some cases don't correlate very well—for example, you can't realistically compare a Pentium III processor at 50 percent utilization to a new quad-core processor). A good analysis ultimately results in a document that outlines a proposed consolidated environment.

Be careful that you don't propose an unrealistic consolidation that someone higher up will then expect you to deliver. Management typically wants to hear consolidation ratios such as 20:1 or 10:1. However, not every server, instance, and database can be consolidated; some might need to remain separate. Many third-party applications must be on dedicated servers or completely separated from other applications, which makes them tough candidates for consolidation. Other factors that dictate whether an application or database can be consolidated include SLAs, high performance requirements, and specialized requirements (e.g., split-mirror backups). I usually start by trying to help my customers cut their number of servers and instances in half, then whittle it down from there. Be prepared to defend your design against overzealous and idealistic managers.

As you perform your assessment, keep in mind that although consolidation will yield fewer physical servers or SQL Server instances, the number of databases you're managing will most likely remain the same unless some of your databases are decommissioned in the process. In other words, your job as a DBA won't get any easier—and in the short term, until you adjust to your new environment, your job will probably be more difficult because what you were familiar (and comfortable) with no longer exists. You'll need to be more vigilant about administration and monitoring because aspects of SQL Server that you previously didn't need to worry about affecting others will now have to share the same instance or server and most likely the same CPUs, memory, and disk I/O. For example, when on individual servers, your database backups might have all kicked off at midnight. In your consolidated environment, you might suddenly have multiple databases on the same server that all back up to the same drive simultaneously, causing over-saturation of your disk I/O. You'll need to adjust your administrative processes accordingly to avoid these conflicts. During the assessment phase, carefully consider the end state and remember that you'll have to live with the consequences of your consolidation decisions. (As a side note, be aware that consolidation can lead to outsourcing; see the Web-exclusive sidebar "Outsourcing.")

Part of your assessment should include an organizational readiness evaluation. For example, if you're proposing a new deployment standard for SQL Server instances such as SQL Server 2005 SP2 on clusters for availability, you need to determine whether all of your applications work with the new standard. You also need to ensure that all of your DBAs are trained in the new standard. In addition, you need to determine how your administrative and operational procedures and processes will change, as well as whether your current administration and monitoring utilities will still work—or what kind of alteration will be necessary for them to work in your new environment. Finally, you need to determine whether you have the budget to make all the necessary changes that will ensure a seamless consolidation.

At the End of the Day

SQL Server consolidation is an increasingly popular trend in the constant struggle to achieve more with fewer resources. However, you must approach consolidation objectively. Consolidation for its own sake won't benefit your company. Management might push hard for consolidation, but implementing it incorrectly will do more harm than good. Although consolidation is ultimately implemented as a technology solution, it is, at its core, a business strategy. As a DBA, you must understand your business' motivation for consolidation and work with, rather than against, those goals in your assessment and analysis. Only after you perform the analysis can you decide how to actually consolidate your environment. Taking the time to gather the proper information, performing the appropriate analysis, and making informed decisions leads to a consolidated SQL Server environment that can become a huge asset not only to your business but also to the DBAs who are responsible for managing the environment day-in and day-out.