TechNet Home >Products and Technologies >SQL TechCenter Home >SQL Server 2000 >Plan
Planning for Consolidation with Microsoft SQL Server 2000
Published: July 1, 2003 | Updated: July 30, 2003
SQL Server Technical Article
Author: Allan Hirt
Contributors: Tom Davidson and Shaun Tinline-Jones
Technical Reviewers: Prem Mehra, Will Sweeny, JC Armand, Shaun Tinline-Jones, Johnson Noel, Cathan Cook
Applies To: All versions of Microsoft® SQL Server™ 2000
Summary: This white paper is the first in a series of papers focused on server consolidation with Microsoft® SQL Server™ 2000. It is designed as a prescriptive planning guide for both decision-makers and technical audiences alike. This is not an implementation or administration/operations guide for consolidation efforts; those topics are the focus of the other papers in this series.
On This Page
Introduction
Consolidation Basics
Technical Tasks
Conclusion
Appendix A: Technical Resources
Appendix B: Implementing Chargeback Using SQL Server
Appendix C: System Profile Worksheets
Introduction
Consolidation of computing workload, which includes servers as well as the business functionality that is implemented in applications, is not a new concept. Over the past few years, however, consolidation has moved to the forefront of the priority list for many businesses. Their goal is often to reduce costs in a time when budgets are much tighter and they need to achieve a better return on investment, from hardware and software to staffing and maintenance. The purpose of this white paper is to assist in consolidation efforts onto Microsoft® SQL Server™ 2000. Database migration from other platforms, such as Microsoft Access, Sybase, and Oracle, is not the main focus of this document; however, a good deal of the information provided here can assist in that effort as well.
Who Should Read This Document?
This document addresses the needs of multiple audiences. If you are a business decision-maker or a relatively non-technical person, read the section "Consolidation Basics" for a clear definition of consolidation as it relates to SQL Server 2000. This section is written from a general business perspective.
If you are a technical person, such as a DBA or a network administrator, read both "Consolidation Basics" and "Technical Tasks." Many of the concepts presented in "Consolidation Basics" are key to understanding the tasks you will need as you travel the path of consolidation.
Three appendices provide additional information and materials that can be helpful to both business decision-makers and technical administrators in planning your database consolidation initiative.
Top of page
Consolidation Basics
In simple terms, server consolidation is the process of condensing multiple physical servers, applications, and workloads to a smaller number of physical servers. When designed properly, fewer servers will provide at least an equal level of functionality and service, and quite possibly a greater level. Consolidation also typically gives a business the following advantages:
•
Centralized management
•
Optimized hardware resources
•
Standardization of platforms and processes
•
Greater return on investment
•
Reduced costs (possibly including staffing and administration costs)
Consolidation is not limited to large, enterprise customers. The concepts and processes presented in this paper can be utilized by customers of all sizes and in all industries. The primary goals are twofold: to "rightsize" your infrastructure to meet your current or near-term business requirements; and to provide an architecture that can expand efficiently as your business expands.
Forms of Consolidation
There are four basic forms of consolidation to think about. The two that relate most closely to SQL Server are physical server consolidation and storage consolidation. Logical consolidation also relates directly to SQL Server, but is conceptually different from physical server consolidation.
Physical server consolidation — reducing the number of physical servers — is often the initial motivation for a SQL Server 2000 consolidation effort. Because versions of SQL Server prior to SQL Server 2000 do not have the concept of instances, there is a 1:1 ratio from SQL Server installation to physical server in earlier versions.
Storage consolidation is somewhat more abstract. Storage consolidation may or may not involve server consolidation. It is the process of consolidating the disk storage for multiple servers onto fewer, or only one, high density storage device like a storage area network (SAN). Consolidating multiple servers almost always involves some form of storage consolidation, because one disk subsystem will subsequently host more databases.
Geographic consolidation is the process of taking servers that currently exist in different places and centralizing them in one location. It may occur as part of physical server consolidation, but it is more often a factor than a driver when planning, in many cases.
Logical server consolidation is also somewhat abstract, and may or may not involve physical server consolidation to some degree. When you consolidate multiple databases, and not whole servers, or multiple existing SQL Server 2000 instances onto one or several instances, this is a major consideration because the goal is to condense certain functionality. Logical server consolidation is not always geared for reducing the number of servers; it is sometimes used to group "like" functionality.
No matter what form of consolidation you eventually consider or implement, you must carefully think through all of the options in terms of both business and technology impacts. The reality is that any consolidation effort is ultimately a combination of the types listed above.
Important: Consolidation is more than a technical exercise — it is a business decision that will affect all areas of the business.
Drivers for Consolidation
Prior to performing any planning at the technology level, you must thoroughly understand the business case for the technology in question. Why should the consolidation process only include fewer servers and not the whole enterprise, or, if you are making a platform decision, why consolidate to SQL Server 2000?
IT Control Justification
Businesses must evolve information technology (IT) into an asset. As for any asset in a corporate portfolio, control of that asset is critical. One of the most important motivations for consolidating database servers should be to establish better control over IT operational resources. Too often, corporate IT resources are diffused throughout an organization, causing loss of control over IT operating costs. Or IT organizations grab for control, thereby losing the agility they need in order to best serve their departments. For each of the drivers discussed in this section, Microsoft recommends a balanced approach to the issue of control, combining centralized management with distributed capability.
Begin establishing control by performing a survey of your environment, to find out exactly how your business operates today. Here are four survey questions to get you started. They will help you pinpoint what your greatest needs are. Availability? Security? Scalability? And what others?
Question: Do you have a mix of SQL Server 6.5, SQL Server 7.0, and SQL Server 2000 servers? Do you have multiple database platforms (Microsoft SQL Server, Microsoft Access, Oracle, Sybase Adaptive Server, IBM DB2)?
It is not uncommon for one computing environment to have multiple mission-critical applications, each with its own back end of supporting servers, including a dedicated database server. This is often referred to as a "stovepipe" architecture, in which higher levels of the system architecture are dependent on their own underlying platform rather than sharing databases or database servers. A stovepipe system can evolve gradually over time, leaving a mission-critical application dangerously dependent upon an outdated database technology.
For example, imagine that you are running SQL Server 6.5 and one of your applications is mission-critical. Eventually SQL Server 6.5 will become unsupported. But it is not easy to upgrade to a later version like SQL Server 2000. Upgrading versions of your database platform, and possibly also the application that uses it, requires planning, money, and downtime. However, at some point, your 6.5 system will reach the end of its life, either because you have outgrown its capacity, or to meet a corporate requirement to stay on completely supportable platforms. In either case, being able to call a vendor‘s product support service and get the level of support your business needs is crucial.
This mix of various back-end databases, whether SQL Server or multiple different products, is not only challenging to those administering them, but also increases the risk and cost of maintaining skills, staffing, and related resources. Risks occur when DBAs are faced with remembering subtle differences between database platforms and versions. This situation becomes especially hazardous to operations and maintenance: if the wrong action is accidentally taken, causing a mission-critical system to become unavailable, the result can be quite expensive. By standardizing platforms and reducing the mix, you can reduce the overall risks and costs to your environment.
Question: Do you have many standalone SQL Server installations that seem to be underutilized?
Underutilization of your database server is difficult to define. Without proper benchmarks and performance analysis, there is no definitive way to confirm that a server is being underutilized. But if there is a perception that certain servers have excess capacity that is being "wasted," you may have a case for consolidation. If the raw numbers do not exist, you will need to perform this analysis; otherwise consolidation of a seemingly underutilized server may be the wrong thing to do.
Question: Do you have numerous departmental databases that are not centrally managed?
It is a common scenario in companies of all sizes to have many small to medium databases residing in various departments. Often, these databases or servers fall outside the scope of the systems that an IT department manages on a daily basis, so they do not meet corporate IT standards for design, implementation, and maintenance. Because they are outside IT‘s scope, a phone call like this can occur:
Department worker: "Hi. Our accounting package‘s database server is down."
IT support professional: "What database server?"
Having all of your company‘s databases centrally managed certainly makes life easier for nearly everyone (although the DBA‘s workload may increase), and allows the corporate IT policies and standards to be leveraged or established for functions like system architecture, security, backups, and system monitoring to be put into place.
Question: Are you unaware of how many database systems are up and running at any given moment? Is it a struggle in your environment to get a handle on your topology or technology infrastructure?
Even if all your database servers are centrally managed, you may not have an up-to-date accounting of every system in your environment. Which ones are up, which ones are down? Which ones are test, development, staging, or production servers? If you do not know the role or importance of a particular system, you cannot effectively monitor it. A blip may mean nothing, or it may mean that something bad is happening. Server consolidation can assist in this area, as it allows you a complete snapshot of an IT environment that is easier to understand.
If you can answer yes to any of these questions, you likely have justification for consolidating SQL Server systems.
While there are many reasons to consider consolidating servers, such as being able to manage fewer servers and reducing the number of supported platforms and configurations, all of these reasons lead to better total cost of ownership (TCO) and return on investment (ROI). The following are elements of the TCO reduction case:
Cost Justification for Consolidation
When considering server consolidation from a cost perspective, management at all levels has many factors to balance. Within a single company, have standards developed that overlap or even contradict each other? Are the components of the total computer system fully integrated? Is the data center performing at top efficiency? These and other factors, addressed within this section, present a complex set of concerns to analyze as you prepare to justify a consolidation proposal.
Note: Cost justification worksheets for SQL Server consolidation are available in Appendix C, "Business Worksheets."
Server consolidation enables better business integration among the various teams within your company, making each team much more responsive to current and future business needs. Better decisions are made as a result of more shared information, and having more known about the systems themselves.
However, despite the many benefits that can come with server consolidation, it must be stressed that you should not look at consolidation as the only answer to your server problems. As mentioned earlier, consolidation for consolidation‘s sake is not the right approach. Do not assume you can merely shift resources from one server to another; betting your business on fewer servers means you will need more sophisticated tools and processes to match. Examine all cost factors carefully. Do not incur so much risk that if something fails, the entire business may potentially be threatened with failure.
Standards and Processes
The various organizations within a company may use different processes to administer their systems. This might include varying monitoring software, standards for system availability, security processes, and more. This creates the potential for confusion and chaos when one business unit needs to work with another. In small and large corporations, standards need to be determined at the top of the business hierarchy and flow down. With a more centralized approach to server deployment and utilization, stronger and more regulated controls are typically put in place to ensure that the company is dealing with matters in a single unified way.
Computing Resources
Whether a business sponsor commits $1,000, $10,000, $100,000, or $1,000,000 on a hardware component, the expectation is that the component will be used heavily and properly. Unfortunately, many companies today purchase hardware and software resources based on speculation, possibly resulting in resources that are underutilized, or worse: not used at all. If you understand what components your company has and how those components are being used, you empower yourself to make better decisions about how to use the current platforms as well as to make smarter decisions concerning future hardware and software purchases. Server consolidation fosters standardized environments, which in turn leads to a better understanding of resources. Capacity management enables IT professionals to predict system usage over time, which will benefit everyone from the people who are tasked with supporting the operating system and network to the DBAs.
DataCenter Controls
Is your data center properly climate-controlled? Is there enough power for all the systems? Will there be enough space, power, cooling, network ports, and other resources for future growth? Which individual systems within the complete solution will be available in the event of a disaster? These are just a few examples of typical data center concerns. Ensuring proper climate control guarantees higher system availability — but it can also cost lots of money. If a data center is only one-third full, but you are paying exorbitant cooling bills, is the data center making or losing money for the corporation? Is all that extra space specifically designed for future growth, or was a planning mistake made? Consolidation generally reduces the amount of physical space that is needed for hardware, but it still must be taken into account when planning all other aspects of a data center. Thinking strategically helps determine what your overall costs really are.
Technical Skills
Skill sets will vary across a company, and even within a technology. Maintaining multiple sets of professional and technical skills across a myriad of platforms is an expensive endeavor. Consolidating to a single database platform, such as Microsoft SQL Server, streamlines knowledge costs and makes everyone more efficient. With one standard platform, your employees as a group can increase the range of their abilities within a smaller set of technologies, and as individuals they can specialize in knowledge and skills that will have bottom-line impact on the day-to-day business.
Licensing Arrangements
The concept of licensing costs is a simple one: the more individual servers you have with software on them, the more costly it is to obtain the proper licensing for the software on those servers (including both the operating system and the database software). This is a potential management headache when trying to resolve issues such as which server licenses are coming up for renewal, and when. From a short-term perspective, purchasing fewer but larger servers may seem like a more expensive investment. However, over the span of two or three years, the cost savings may be dramatic. In a consolidated environment such as one based on SQL Server 2000 Enterprise Edition, many products allow one licensing model per system, instead of requiring individual licenses per server (as you do with SQL Server 2000 Standard Edition). Such an arrangement allows you to invest in more flexible and scalable versions of a given operating system than were previously available to you. You can also purchase other options, such as enhanced support, that may not have been feasible in the past.
Backups and Offsite Storage
In a company with multiple systems, backup plans for each system may vary: for some, you may be using a centralized program that backs up over a network and ultimately to tape, while others may back up to disk and then to tape, and the rest may have local tape drives. Complicating any multi-system backup strategy is the need to handle the databases as well as the file systems and operating systems. Such a complex environment does not foster the regular testing of backup systems to ensure that they can be restored in a disaster recovery scenario.
Assuming that each server in a multi-system setting does have a proper backup plan which includes offsite storage, considerable physical space is needed to store all the tapes, and a large number of tapes must be purchased for all these servers. The cost of both these resources cannot be ignored. From an administrator‘s perspective, a consistent labeling method for these tapes is a necessity; otherwise, finding the proper backup in a disaster recovery scenario would be like finding a needle in a haystack. With these aspects in mind, having fewer servers to worry about may be a good thing. Consolidation will ultimately reduce the complexity of most backup plans, along with reducing the associated hardware and software costs. Management and testing of the disaster recovery procedures, including verifying the integrity of the backup media, will also become more feasible.
Security
Security is an ever-increasing focus for all businesses. This area encompasses not only securing of systems and data, but also securing your offices and data centers to ensure that no unauthorized access occurs. High security does not come cheap, and it must be enforced to ensure that the consolidated environment is well protected.
Usability
While cost and budgets are the proverbial bottom line, consolidation for the sake of consolidation is not the right approach. If your system costs are lower but the systems themselves are unusable, are you really saving money? Human aspects must also be addressed in the business case. How will consolidation help the end users and the day-to-day administrators of the systems that are being looked at?
IT Professional Staff
For DBAs and other IT professionals, consolidation may at first seem like a bad idea. Consolidating the multiple servers that they already know well into fewer systems with increased workloads will change DBA work behaviors and patterns. Even though functionality remains (or improves) and certain elements of their jobs may stay the same, these people must now be retrained to properly maintain the new configurations. Consolidation can also cause some concern that the company infrastructure is shrinking, leading to worries about job security and flexibility of the environment.
The positive aspects of consolidation for DBAs can be huge, because they can now take better advantage of existing skills, experiences, people, platforms, and tools. They can reduce their repetitive tasks and iterations. DBAs become more like trusted advisors, focusing on important corporate tasks like aligning data-processing policies and goals with the business goals and objectives of the company as a whole. As well, they will have more time for more rewarding tasks like performance improvements; providing feedback for capacity management; playing a more influential and involved role in the development cycle; and evaluating software to improve IT resource purchasing decisions.
From another standpoint, consolidation is aligned with the common goals of most IT shops: gaining control of IT resources, reducing complexity of IT infrastructure, providing flexibility to departments and business units, standardizing hardware and software configurations, reducing single points of failure to increase availability, escalating the importance of having truly qualified people to administer the systems, and increasing security.
Customers
The impact on your customer or end user is the most important aspect to think about from a business perspective. Your users are the reason you have implemented a system in the first place. If a consolidated infrastructure is not transparent — if it greatly affects the end user — you will decrease customer satisfaction. The goal should be to improve or maintain satisfaction, because consolidation should not affect the end user if at all possible.
The Consolidation Process
There is no Consolidation Wizard for SQL Server 2000. The decision to consolidate will be the end result of meticulous planning and thorough testing prior to any production rollout. To help guide IT initiatives such as consolidation efforts through a logical, staged approach, Microsoft offers the Microsoft Solutions Framework (MSF), a reference architecture of methods and practices. With the MSF approach, a consolidation effort moves through four phases: envisioning, planning, development, and deployment.
•
The envisioning phase in a SQL Server consolidation project is similar to other infrastructure-based projects. A team of people who represent business perspectives as well as the end users is put together to develop business goals and determine the scope of the project. The makeup of this team is described in the next section, "Phase One: Envisioning." During envisioning, the team gathers user profiles, develops a solution concept, begins analyzing risk, and determines the project structure. The envisioning phase culminates in the Vision/Scope Approved milestone.
•
Key deliverables during the planning phase are a draft functional specification, a draft master project plan, a draft project schedule, and a development environment. The functional specification includes design, usability, and deployment goals; the solution design; component specifications; project risks; and project standards. The master project plan includes the approach, dependencies, and assumptions of the project. The master project plan also contains other relevant plans such as a deployment plan, a pilot plan, a purchasing and facilities plan, a test plan, a capacity plan, a training plan, and a security plan. The planning phase culminates in the Project Plan Approved milestone.
•
During the development phase the team tests and pilots the solution, develops training material, and prepares to perform a deployment. Key activities in this phase include validating the technology, developing the proof of concept, testing, performing a pilot, and incorporating feedback from the pilot. The development phase culminates in the Release milestone.
•
The deploying phase is an active phase rather than an analytical one. In this phase, the team deploys the core technology, deploys site components, stabilizes the deployment, transitions the project to operations and support staff, and obtains customer sign-off on the project. After the deployment, the team conducts a project review and a survey of customer satisfaction. The deployment phase culminates in the Deployment Complete milestone.
The MSF approach is designed to be iterative, with each phase broken into a series of steps. For more information on MSF, seehttp://www.microsoft.com/technet/itsolutions/msf/default.mspx
Phase One: Envisioning
The envisioning phase contains four steps that must be performed.
Step 1 Form the consolidation team
The first step in the envisioning process is to put together the core consolidation team, the group that will be responsible for overseeing the process. Include, at minimum, the business sponsor, a representative from each application‘s development or implementation team (whether it is an internal custom application or a third-party application), the appropriate technical representatives from your company‘s administration teams (IT, operations, DBAs), and someone who represents the end-user voice. Other examples of people to include on the team are end users themselves, data analysts, and program managers. The people on the final team will be different for every environment, but the goal is the same: to have a team that represents all aspects of the company.
Step 2 Identify initial consolidation components
Once the consolidation team is assembled, its first task is to identify the applications, systems, databases, and servers to be considered for consolidation. The criteria for selecting these entities is determined by the team, and should be weighed against the impact on the business, cost, and all other aspects that influence consolidation. Be sure to start the consolidation effort with a narrow focus, for example on just one or two business units, departments, classes of applications, or a short list of specific applications. Taking on the complete enterprise (or in a smaller company, all servers) is not the best approach to consolidation. Make no mistake — any consolidation effort should be executed with an enterprise perspective. But starting small, allowing time to build an understanding of how the smaller effort fits into the big picture, and benefiting from the lessons learned, will assist in ensuring that your further consolidation efforts are highly successful.
Step 3 Determine guiding principles and goals
The section "Drivers for Consolidation," earlier in this paper, presents a general view of the strongest business motivations for consolidation. Now is the time to determine the drivers for your specific situation. What goals should be achieved when your consolidation project is undertaken? What are the risks if you proceed with consolidation? These are simple questions to ask, but how do you go about gathering this crucial information?
To reach decisions about the consolidation goals of your company, and the principles underlying the goals, schedule a series of broad-scope meetings. In addition to the core consolidation team, make sure that all other parties who will be involved in consolidation are represented in the initial meetings, from the business sponsor to some of the end users. Each affected group within the company must have its views represented, so that the principles that will govern the consolidation process will meet everyone‘s needs.
Out of these sessions, initial risks should also identified. All principles and risks must be properly documented.
The goal of this task is to gather feedback and clarify the resulting information: the expectations of the team, the current working knowledge of the environment (whether fact-based or perceived), constraints on consolidation, size and complexity of the consolidation effort in relation to the environment, and most importantly, the commitment to the project by management, who ultimately fund it. Identifying risks at this initial stage is crucial.
The following table presents some sample questions to facilitate discussion of the business principles necessary for the consolidation process. These questions are not the only ones that can be asked to determine your guiding principles, and probably they will not all apply to your environment. The questions range from non-technical to technical in nature, and some could be applied in both technical and non-technical situations. You might not be able to get complete answers to the more technical questions at this stage. ("Technical Tasks," later in this paper, deals with the technical aspects of determining guiding principles.)
Technical level Sample questions for determining guiding principles
Non-Technical
What business value do you believe will be gained from consolidation?
How did you reach the decision to consolidate the existing servers?
Have the IT and business stakeholders bought into the consolidation concept?
How much money is available for new hardware? What is the overall available budget for the entire consolidation effort?
Do you plan on eliminating or downsizing personnel as a result of this consolidation effort?
State any particular issues or information that you believe the core consolidation team must be made aware of. For example:
• Are there any corporate policies that may affect the server consolidation effort?
• Does the company have any "sole source" providers, which might be illegal in some governments and thus may restrict consolidation contracts in some way?
• Are there any corporate security policies that may inhibit or change the nature of a server consolidation effort by requiring physical isolation of systems and data from different departments, as in the case of LLPs?
Technical
Will other types of tools work after a consolidation effort?
How much will the data grow over time? What has been your projected versus actual growth (if known)?
How will maintenance be performed on the new system? How will maintenance change due to consolidation?
What are the dependencies of the components in the existing solution? For example: Are there external data feeds into SQL Server that may fail as a result of an availability problem or a move in a system?
What technologies are currently used in the applications and components of the solution, and which ones (current or additional) are desired? How will a change affect the solution?
Are any components of the current system already at or exceeding capacity — whether it is processor, memory, or disk?
Do you have proper development, test, and staging environments?
Both
Is chargeback for resource usage a need or a consideration? Will the tools currently in place to measure chargeback work after consolidation?
How many users do you expect to support concurrently by this solution in the short term? And in the long term?
How long do you intend to keep this solution in production?
What is acceptable performance from both an end-user and an administrative/management perspective? Spell out how you are defining performance — it could mean throughput, response time, or some other benchmark.
What is the current availability goal for each individual system? Each solution? Will each component meet the requirements in a consolidated environment?
What are the security requirements for both the application and the systems that make up the existing solution? Are they in line with corporate policies? Are there currently any conflicts between systems that may be consolidated together? How will you have to adjust the requirements due to consolidation?
What is the cost of developing, implementing, and supporting the new solution in the short term? In the long term?
What is the current actual cost of downtime for each individual system? The entire solution? What is the current cost of administering and running the server?
What service level agreements (SLAs) are in place, and how will they be affected by consolidation?
Where are the current servers located? Are they currently in the same data center? If not, how will changing location or geographic placement affect the entire group of users, performance, the SLA? What else will be affected as a result of changing location or things like domains?
What roles exist within the IT division? Do you currently have a dedicated capacity management role?
Do you have effective change control processes in place with the existing systems (whether they are being considered for consolidation or not)?
Do you have a testing/quality assurance team?
Are there dedicated DBAs, and if so how many?
What is the current ratio of SQL Server instances and databases to DBAs?
What different types of systems are being considered for consolidation: production, test, development? Others?
Will you employ new technologies such as clustering for high availability? How will that affect your employees from a skills, cost, and training perspective?
Do you have specific SLAs to maintain even with consolidation?
Are there currently multiple systems with conflicting SLAs that are targeted to be consolidated on the same machine?
Are certain systems required to be separated from others, for reasons such as performance or conflicting SLAs?
What is the desired end date for the project? Are there any constraints that may come into play, such as those posed by hardware or service vendors?
Are any of the systems mission-critical? How will that affect the other systems they may be combined with?
How much business in terms of both actual transactions (not database transactions) and revenue does each system generate?
List any references your company has used to date, such as other consolidation projects that have affected your interest in consolidation or influenced your analysis.
After a few initial broad-scope meetings, the core consolidation team should have an understanding of the complexity of the challenges that will be posed by server consolidation. As part of the initial meetings, devise the overarching guidelines that will carry through the entire project. You can also use them to measure the success of the consolidation effort when it is complete.
The following table shows an example of guiding principles for a consolidation project. They are based on practical experience with several installations.
Note: These principles are not to be taken as official Microsoft recommendations or best practices. Every installation has different motivations and goals for server consolidation and those should direct the development of the consolidation guiding principles.
Sample set of guiding principles for a consolidation project
Only complimentary SQL Server workloads will be consolidated onto one installation of Windows. For example, OLTP and decision-support (DSS) workloads will not reside on the same server.
Additional services, such as Microsoft Exchange, file servers, and print servers, will not be consolidated onto a SQL Server system.
When consolidating complimentary workloads, where possible the databases will be consolidated into a multi-database SQL Server 2000 instance. Random databases will not be put on an instance without careful consideration.
Only non-mission critical workloads will be consolidated initially. Mission-critical applications and servers will not be consolidated at first for two reasons: to gain experience with consolidation, and to minimize disruption to end users of mission-critical systems.
Applications and databases (including all their related objects) will be updated or converted to support SQL Server 2000 before they will be considered for consolidation. During the update or conversion process, functionality will not be enhanced, but will be maintained at the same level. Allowing the scope of the project to grow beyond the agreed-on measurements and parameters (known as scope creep) would affect the eventual outcome. Only bugs relating to incompatibilities will be fixed.
More than one SQL Server 2000 instance will be used when capacity or use characteristics dictate this requirement.
Multiple SQL Server instances may be used to isolate different workloads. However, the underlying server must be configured so as to provide as much isolation as possible.
If there are a significant number of naming conflicts, multiple instances will be used.
The consolidation process, and the consolidated systems after processing, will remain as transparent to end users as possible.
The consolidation process will drive standardization of system configuration, system administration, and system support.
Once the principles are complete, the core team responsible for consolidation — as well as others, such as those who will be responsible for deploying and maintaining the consolidated environment — should review them. Gather the feedback and revise the principles if necessary. This is an iterative process, worked on until all of the principles can be agreed upon, and then formally signed off by the team and the business sponsor who is responsible for the entire project.
Step 4 Document system configurations and performance
Before doing any formal business or technical planning for consolidation, you need to know as much as possible about the systems that are being considered for consolidation. Without that information, making decisive choices will be impossible. Once the consolidation team has agreed on the systems that will be considered, you can now gather any relevant information. How do you know you are not already at, or exceeding, the capacities of your system? That will definitely affect how components will or will not be consolidated, and what hardware will be purchased to support the final production system. This process is detailed in "Technical Tasks."
Note: For information on how to profile and gather information on your current SQL Servers, see Appendix B, "Implementing Chargeback Using SQL Server." For worksheets to assist you in the process, see Appendix C, "System Profile Worksheets."
Phase Two: Planning
Once the envisioning phase is complete, with the guiding principles agreed upon and documented, the work on planning the consolidation effort can begin. All efforts from this point forward will be governed by the guiding principles. There are two main parts to planning: designing what the consolidated servers will look like, and proving the design.
Step 1 Design the consolidated servers
When you start to design the consolidated environment, take into account every aspect of that environment, including administration and operations (including monitoring), performance, backup and recovery, chargeback (if necessary), disaster recovery, high availability, and security.
Do not assume you can simply use what you have in place for your current system. The proposed new environment will be different. With more databases and SQL Server instances per server in the new environment, you will have different rules and options that will need to be reconciled. At this stage, if you identify new tools that need to be built or acquired, document and plan for those, too.
Step 2 Migrate applications, users, and data
The paramount issue for the DBA is how to migrate the applications, users, and data to the new consolidated environment. Any potential SQL Server 6.5 and SQL Server 7.0 migration issues should be completely isolated and identified at this point, from Transact-SQL incompatibilities to system settings, and everything in between. Consider deploying SQL Server 2000 in 6.5 Compatibility mode to reduce migration issues. You may also need an intermediate staging server. Determine the order in which you will migrate the applications, users, and data. Resolve any conflicts in object names and location. Worry about the end user‘s experience, and how you will make the effort as transparent as possible to them. Also, how will you notify your end users about the move? How will business processes be affected?
Most importantly, avoid scope creep. You are consolidating, not adding new functionality or enhancements to applications or data schemas. Problems are likely to be identified during the migration planning; as long as they will not break in the consolidated environment, they should be left alone. Once you have consolidated, you may want to consider these types of things, but adding them at this stage will only cause the consolidation to take much longer than necessary.
Step 3 Test the Process
Prior to actually consolidating servers in a production environment, you must build and test all of the new procedures, tools, and the entire migration. The capacity and capability of the test environment should be the same as, or very close to, the final production environment. However, you may be constrained by factors such as budget or resources. The important goals of this phase are to isolate, identify, document, and fix any defects, errors, or problems that are encountered, so the upcoming production effort will hopefully be flawless. Devising the proper test plans will ensure a successful production rollout.
Step 4 Determine Risk
Establishing your risks (which may include potential barriers) is an important step to take early in the consolidation process. Risks may involve people, technology, or costs. Without knowing your risks, at the end of the exercise you will not be able to determine whether you were successful or a failure. The risks you determine can also be used as a set of measures. Mitigating risks is a separate task; some risks may not be able to be avoided, but if they are documented, you can at least take them into account as best you can.
Phase Three: Development
In the development stage, the plans are realized and implemented for the first time in a testing or a staging environment (or both). This stage requires completely separate hardware from the eventual production servers, because it is crucial to work out any potential problems before going live.
Ideally, the servers you use in this stage will have the same capacity and horsepower as their production counterparts. This situation may be unrealistic at some sites, due to budgetary constraints. The point to keep in mind is that the more closely your testing and staging environments mirror your production environment, the more successful you will be during the actual production migration.
There are at least three steps, and often four, during the development phase.
Step 1 Technology Validation
After the technology has been decided on and planned for, the chosen technologies must be fully tested o ensure that they will perform properly in the final production environment. Your testing should be done both alone and under load. This step should tell you definitively if your choices are appropriate, or if a new technology must be implemented. This is one of your last chances to modify the physical design of the consolidation effort.
Step 2 Proof of Concept
A typical proof of concept in a technology environment establishes that the final environment will work as expected. A proof of concept is exactly what it sounds like: proving your ideas in a controlled way, on a smaller scale. However, a proof of concept always encompasses the same scope as a full implementation, despite the smaller scale. This means that you take your overall vision, pare it down, and concentrate on a smaller portion that is representative of the final product.
After migrating applications, data, and users to a designated portion of the new consolidated SQL Server environment, put it through its paces. Use it as it would be used on a daily basis, and test the servers under load. You must ensure that the databases that are now consolidated under one SQL Server instance will work well together when all applications are experiencing high utilization; otherwise you will encounter performance issues in production.
If any coexistence issues arise after testing the proof of concept, document these issues, and possibly rethink the strategy for that particular application‘s databases. Mitigating risk at this stage is crucial, because it will directly affect the pilot.
Important: Remember to test not only the migration, but any administration plans, scripts, procedures, tools, and other elements that will be used in the eventual production environment. Testing only the migration process is not enough.
Step 3 Pilot
Finally, choose one of the servers that will be consolidated and make it be a pilot in your production environment. Even if the proof of concept was successful, you still need to prove that in a full-scale production capacity, the consolidation will benefit the entire business. As with the proof of concept, you must thoroughly test the pilot consolidation implementation.
Step 4 Rethink
If the pilot is a failure or is scrapped for other reasons, this is the time to figure things out. Follow the configuration plans and migration plans as they were documented. When problems arise, document them. If you find a fix, document the changes and revise the plans appropriately. Then retest as appropriate.
Phase Four: Deployment and Stabilization
Once you are confident that the proper hardware designs have been devised and the migration plans are well tested, it is time to build and deploy the production consolidated SQL Server system. The deployment phase involves three main steps: back-end deployment, application deployment, and stabilization.
•
During back-end deployment, you completely configure and test the backbone components of the system (hardware, operating system, networking, SQL Server, and so on).
•
During application deployment, you configure the databases and roll out the applications in the new consolidated environment.
•
After the rollout, stabilization is the point at which you deal with any final issues that arise. At some point, you reach the "go/no-go" point, where you cannot easily return to the old environment. Stabilization takes place through and beyond this stage.
Even if your plans are well tested, do not deploy all consolidation at once. Take a phased approach: deploy one consolidation, thoroughly test it, compare it with the original environment, and then finally retire the old environment. Only at this point should you consider consolidating another SQL Server, because if you do not completely verify that one consolidation went as you expected, or if other problems are encountered during another migration, you are likely to both obscure and add to the problems.
Top of page
Technical Tasks
The previous section of this paper, "Consolidation Basics," presents an overview of the consolidation process from a business perspective. This section maps those basic tenets to specific technical tasks and questions that those responsible for the technical aspects must consider for consolidation. These tasks and considerations fit squarely into the first two phases of the Microsoft Solutions Framework: envisioning and planning.
Note: For a related list of technical resources to assist you in the consolidation process, see Appendix A, "Technical Resources."
Envisioning Considerations
At this point, the technical people on the consolidation team (as well as anyone else who needs to be involved from a technical standpoint) must determine the technical guiding principles and goals for this consolidation project. Once that is completed, you must profile every aspect of the systems to be consolidated, and then gather system performance metrics and additional information. Following all these steps will lead to a successful consolidation effort.
Determining Guiding Principles and Goals
In addition to a set of business-driven guiding principles, you must also establish a technical set. With the technical guidelines to compliment the business guidelines, you will have guidance and criteria for success, and a solid basis of comparison between the "before" and "after" environments.
The technical team must work together to collect the necessary information. When the answers are consolidated, they must be matched up against the business guidelines. Are they in harmony with each other, or is there dissonance that needs to be reconciled? No consolidation effort can truly begin until both sides of the house are in agreement, or reach compromises on any potential issues.
The following table presents some sample questions to facilitate discovery of the technical principles needed for the consolidation process. As with the business set of principles, these questions are not the only ones that you can ask to determine your guiding principles, and some of them will probably not apply to your particular environment.
Area Sample questions for determining guiding principles
General Technical
What technical advantages do you believe you will gain from consolidation?
How did the unconsolidated deployments occur? What can be learned (positive and negative) from those deployments?
How many servers are to be consolidated?
On average, how many databases exist on the targeted servers?
Have the servers been identified?
Of the servers identified to be consolidated, are they currently geographically separated from each other?
Do you know the types of workload (such as OLTP, OLAP, DSS) on each system?
Do you know the requirements of all the third-party applications that may use SQL Server and are part of your consolidation plan?
Have the DBAs in your organization been formally trained on SQL Server in all aspects, including administration, high availability, and performance?
Backup and Restore
What are the current backup technologies used?
Capacity Management
What are the usage patterns of the targeted servers, including use of the system databases? Do you have a performance baseline of each server? Is this baseline broken down by business units, and by their respective systems?
Do you know the resource utilization for each server? Can you accurately assess whether the targeted servers are over- or underutilized?
How much will the data grow over time? What has been your projected versus actual growth (if known)?
Change Management
How do you manage database schemas, including stored procedures and functions? Do you keep them in some form of version control?
Configuration
Do you know all the information about each server (such as service pack levels, disk space usage, SQL Server configuration settings)?
Do you use administrative tools such as Alerts and SQL Mail? Do you have standardization across all servers?
What versions of SQL Server are these servers?
What versions of the operating system are used on these servers?
What technologies are currently used in the solution?
Connectivity
Do you know how many users connect to SQL Server, on average? What types of clients (such as handheld devices and fat clients ) connect to SQL Server? How do they connect?
Do the current servers reside in different domains?
High Availability
Are the targeted servers already employing some form of high availability such as failover clustering or log shipping?
What are the current high-availability technologies used in your company — does the consolidated environment match the corporate strategy?
Objects
Do you have any custom extended stored procedures that may affect other databases?
Do you have conflicting objects with the same name?
Do you have access to all source code for the applications accessing SQL Server as well as for all stored procedures (especially if you use stored procedure encryption)?
Replication
Is replication configured for any of the targeted databases?
Security
Do you have security policies and procedures documented for each server (physical hardware, operating system, SQL Server, and the applications)? Is each security policy in line with your current corporate policies?
Profiling the Systems to Be Consolidated
Each system that is being considered for consolidation must be profiled. Profiling involves much more than just noting the system name: every aspect of each server must be captured. This is done as part of figuring out whether workloads and systems can be combined, to check for incompatibilities due to configuration settings, application requirements, or other factors. Use the following set of worksheets from Appendix C, "System Profile Worksheets," to assist in the process:
•
System Information Worksheet captures the basic information about the system and the applications running on it.
•
Disk Configuration Worksheet captures how the disk subsystem is used and configured, and assists you in understanding any disk performance numbers.
•
SQL Server Information Worksheet captures the SQL Server-specific information for the SQL Server instances being considered for consolidation. When documenting each database here, note what type of workload is done (such as read/write and write).
These worksheets are offered as guidelines. You can easily extend them to include other information specific to your environment.
In addition to capturing general system facts through the worksheets, the specific settings for each application need to be captured. SQL Server provides a few methods to assist in the documentation of your systems:
•
Run the system stored procedure sp_configure to capture all of the configuration settings for SQL Server.
•
Run the system stored procedure sp_helpstartup on SQL Server 6.5, or sp_procoption on SQL Server 7.0 and 2000, to help determine the startup procedures and settings used by SQL Server.
•
Use the sqldiag utility to capture SQL Server system information. The sqldiag utility is usually used for diagnostic purposes, but this is one of its side benefits. Use the following syntax:
SQLDIAG –X –U
-P -I -O