SQL Server Magazine

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," http://www.sqlmag.com, InstantDoc ID 95467, for more information.)

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.

End of Article

Prev. page     1 [2]     next page -->