Planning for Consolidation with Microsoft SQL Server 2000

Article
01/28/2010

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.

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.

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.

Data Center 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, see https://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.

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 <user name> -P <password> -I <instance name if not default> -O <output file>

In the **sqldiag** command line, **-X** skips the error logs; you can use **–C** as well to get cluster information.

For each database, also document all the objects associated with that database (such as stored procedures, jobs, logins for both system and database users, and maintenance plans), even if they reside outside of the database itself. You will use this information later to determine other conflicts, such as duplicate stored procedure names and logins, or differences in naming standardizations. Run the same DBCC commands on each system so that you have a baseline and a consistent set of processes for each system. Document anything and everything — including linked servers, modified system databases, and anything else — even if you feel it may not be relevant, because you might discover later that this information is indeed helpful.

Gathering System Performance Metrics

Gathering performance metrics from your server is more than a one-time event. To truly understand what is going on with your system, you must collect performance metrics over a period of time, and then analyze the information.

Information and metrics about your systems, including processors, memory, disk I/O, and disk capacity, must be captured. Record your results in the Performance Information Worksheet, available in Appendix A.

At a minimum, we recommend gathering statistics about your systems for one business week. During that time, record numbers at times during which there is little to no load, medium load, and heavy load. If you only gathered numbers at times that reflected one extreme or the other, you would not gain an accurate picture of system use.

For additional tips on how to measure SQL Server performance, see "Monitoring Server Performance and Activity" in SQL Server Books Online.

Processor Metrics

Profile each server process that is running on your server. If SQL Server is the only major application running on your system, gathering performance metrics will be a straightforward task. If SQL Server is one of several major applications, it is important to understand how each process performs individually, as well as together as a system. When you test the various applications and can make distinctions between them, you can better plan your consolidation design.

For example, suppose you have SQL Server and a third-party software application running. Your overall CPU utilization seems high. You stop the third-party application, and then profile only SQL Server. By measuring SQL Server alone, you see that you have at most 30 percent CPU utilization, which means that the rest is made up by the third-party application and any other active server processes. If these additional applications or processes are mission-critical, it may not be possible to stop and start services to get specific performance numbers for each one individually.

If you cannot get the numbers you need to perform a full analysis, a situation like this may indicate from the start that the particular system is not a candidate for consolidation. In a well-managed environment, most or all of these performance metrics would have been recorded before the system was placed into production, solving this problem.

In Performance Monitor (or System Monitor for Windows NT® 4.0), capture the % Processor Time metric under Processor to give you the overall processor utilization. It does not specifically single out individual processors unless you configure the parameter to gather metrics for both the overall system and each individual processor. However, that metric does not measure the processor utilization of individual processes. Under Process, select % Processor Time to measure the processor utilization for the individual service, such as SQL Server.

When you perform your analysis, it is crucial that you understand the impact of consolidation. It is also critical to understand the effects on CPU utilization in the other server processes. The server processes on a single physical server designated as the consolidation server will be affected by applications and server processes such as SQL Server consolidated from other servers onto the consolidation server.

Memory Metrics

Gathering memory statistics is similar to collecting metrics on processor utilization. Each individual service on the server should be measured individually, and then the server as a whole should be measured.

It is very important when taking into account statistics for SQL Server to note how the memory is configured. Are you using dynamic memory? Is a static amount configured? Is extended memory used, such as AWE? That will help in understanding the statistics you gather.

Use the following counters in Performance Monitor to collect memory statistics:

Memory: Available Bytes
SQL Server: Memory Manager: Connection Memory (KB)
SQL Server: Memory Manager: Lock Memory (KB)
SQL Server: Memory Manager: Maximum Workspace Memory (KB)
SQL Server: Memory Manager: Optimizer Memory (KB)
SQL Server: Memory Manager: SQL Cache Memory (KB)
SQL Server: Memory Manager: Total Server Memory (KB)

Disk I/O and Capacity Metrics

Disks are the heart and soul of the database system. Use the Disk Configuration Worksheet in Appendix C, along with the actions covered in this section, to gather performance numbers for the disk subsystem associated with each SQL Server installation.

The diskperf Command

Run the following diskperf command line in a command window to be able to dump disk performance counters at a given moment, and store the results in a file. By default, the disk counters are enabled for Windows® 2000 and above:

diskperf –y

Performance Monitor Counters

In Performance Monitor, analyze the following disk counters:

% Disk time

Shows how utilized a disk is. If a disk drive is continually utilized at or near 100 percent, it is already overutilized and is probably causing SQL Server performance problems.

Average disk queue length

Indicates how many system requests are waiting for disk access, on average. 0 is the best value to have. If the value is sustained and above 2 or 3, you may already be experiencing a disk performance problem, if it's a sustained number (a brief blip does not indicate trouble). However, Average Disk Queue Length is not an accurate measure of disk I/O problems. It is listed here only for use in comparing pre-consolidation and post-consolidation values. Keep in mind that values will vary, depending on the brand and type of disk storage you use.

Average disk read/sec and Average disk write/sec

For log files, look for a value of 2 to 3 milliseconds, and random reads on average 6 to 8 milliseconds (peaking at most around 15 to 20). These values will vary, depending on your hardware configuration and your specified end goal for performance.

Remember the difference between the numbers for a physical disk under the PhysicalDisk category and the numbers for a logical disk under the LogicalDisk category.

Individual SQL Server Files

To get the specifics concerning performance for the individual files that SQL Server uses, perform the following steps:

Get the database ID number by issuing the following query:

SELECT * FROM master..sysdatabases

Get the names of the files by issuing the following query:

SELECT * FROM pubs..sysfiles

Get the file statistics by issuing the following query:

SELECT * FROM ::fn_virtualfilestats(dbid, -1)

where -1 shows all files for the given database ID (dbid).

Compare the values returned from the third query. If the value for IOStallMS divided by the sum of the values for NumberReads and NumberWrites from the third query is greater than the number you want for your log files, you have a log bottleneck.

Disk Capacity

One deceptively easy way to evaluate disk capacity is to check whether you still have enough disk space, based on your planned growth. But capacity is more than simply raw space. The disk subsystem must also be properly architected for your needs, both present and future. In a consolidation effort, you may be able to correct architectural problems with new hardware.

One of the main reasons to fill out the Disk Configuration Worksheet is to to see just how much disk space will be required. The worksheet helps you do this by adding together all the numbers of the services and databases proposed for consolidation. There are two ways to think about the calculation of disk space: as raw disk space and as physical disks. Raw disk space and the amount of physical disks actually needed are related, but separate.

Raw disk space is simply the physical amount of disk space needed. The database raw disk requirement is more than just the individual data and log files. It is also tempdb, free space, size of indexes, and anything related to SQL Server. You must also factor in for future growth requirements. When combining SQL Server instances, sizing tempdb becomes an important consideration. Growth estimates can only be determined if the growth patterns of each database have been tracked all along.

How many physical disks will you need to achieve the performance you require? To gain the full performance you want may not be affordable or feasible. Remember that the amount of raw space you need may be different than what is actually recognized by Windows and subsequently SQL Server. For example, if you have a 73.8-gigabyte (GB) physical disk, after formatting, how much space is recognized by Windows? You may need more physical disks than the raw calculation actually dictates.

Take the following example for a read-only database:

Assume each physical disk drive can handle 100 input and output operations per second. A page read returns 8 kilobytes (KB), and a read-ahead operation 64 KB. You have 1000 users who need to bring back .250 megabytes (MB) of information per second, which translates to a total 250 MB/sec of data that needs to be produced. Assume 60 percent page reads and 40 percent read-ahead:

(60x8 = 480 kb) + (40x64  = 2560 kb)

equals a total of 3.040 MB/second throughput for each drive. Dividing the total amount of data needed by the throughput of the drives, your database would require 83 individual disks to get the throughput you need.

For more detailed information on disks and any potential tradeoffs, see Chapters 4 and 11 in Microsoft SQL Server 2000 High Availability (Microsoft Press).

Gathering Additional System Statistics

This section lists SQL Server-specific statistics to measure and other common data to collate in order to analyze system performance and prepare for the consolidation issues most likely to confront you. For detailed information on most of the points raised here, see "Planning Considerations" later in this section.

SQL Server

For SQL Server 2000, each instance on the server has its own set of counters (designated by "SQL Server:" or a variation if it is a named instance). The following list is a top-level list of some of the most important things to measure; your environment may need to monitor more or possibly different counters. More information on some monitoring techniques for SQL Server can be found in Chapter 15 of Microsoft SQL Server 2000 High Availability.

SQL Server: General Statistics: User Connections (gives the number of users connected to SQL Server)
SQL Server: Cache Manager: Cache Hit Ratio (gives the ratio between cache hits and lookups for the SQL Server, which should in most cases be 90-100%)
SQL Server: Databases: Transactions/sec (gives the number of transactions started per second, either total for the SQL Server instance or for each individual database)
SQL Server: SQL Statistics: SQL Recompilations/sec
Process: Thread Count (monitor this for each instance)
Disk I/O counters (for more information, see Chapter 4 of Microsoft SQL Server 2000 High Availability)
SQL Server: General: User Connections (gives the number of user connections into a SQL Server instance at any given moment; use this to monitor maximum and minimum usage, as well as average uses)
SQL Server: Databases: Data File(s) Size (KB) (gives the size of the data files associated with a particular database selected; all user databases as well as tempdb and msdb should be monitored, because in a consolidated environment, you want to know how much each installation currently uses the system databases to provide information about growth)
SQL Server: Locks: Number of Deadlocks/sec (gives the number of deadlocks at a given second for different SQL Server entities)
SQL Server: Databases: Log File(s) Size (KB) and/or Log File(s) Used Size (KB) (these counters give the size of the physical log files and the actual used space respectively; should set done for all databases per server)
SQL Server: Buffer Manager: Procedure cache pages
SQL Server: Buffer Manager: Total pages
SQL Server: Cache Manager: Cache pages

System Databases

Identify any objects in system tables that are not part of a default installation.
Identify any objects in system tables that have been modified since default installation.
Look for duplicate names.
Review each object for continued relevance in a consolidated environment.
Review each object to determine whether duplication exists at a more granular level.
Review each object for explicit references, such as path names, server names, and task names.
Search for non-default objects found in tempdb.
Do not address the use of tempdb; this issue is addressed in "Planning Considerations" later in this section.
Do not address tasks found in msdb; this issue is addressed in "Planning Considerations" later in this section.

Collation and Sort Order

Determine the sort order of the source server.
Identify any objects that deviate from the collation setting of the server.

Security

Collect and detect duplicate logins.
Determine whether trusts are required across domains.
Determine whether guest is an active user.
Collect data on logins with administrative rights.
Collect security settings and permission structure for the public group.
Determine whether specific registry permissions are required.
Determine what permissions are required for extended stored procedures, particularly xp_cmdshell.
Determine which accounts the SQL Server services are running under.
Understand the security model that client is implementing.
Compare the security models of the source servers and the target server.
Collect database options to ensure that read-only, single-user and dbo-only settings are maintained during migration.
Collect the encrypted passwords to be transferred to the new server.
Compile a list of all NT and SQL logins that have admin access.

Server-wide Configuration Settings

Determine whether any SQL Server configurations have been modified (changed from the default) since installation.
Monitor context switches after migration, primarily to decide whether fiber mode is necessary.
Determine whether there are requirements for XML stored procedures and OLE Automation objects.
Establish what statistics are inaccurate when running in fiber mode.
Determine whether the worker threads are exceeding the maximum value allocated.
Collect error messages that have been added to sysmessages and sysservermessages.
Establish whether any system-supplied error messages have been modified.

Existing Environment

If you are not upgrading client or application machines with the SQL Server 2000 tools, which ship with MDAC 2.6, verify that MDAC 2.5 or earlier clients can connect to a default instance of SQL Server 2000.
Collect the host names that connect to named instances of SQL Server.
Identify applications for which the connection string is hard-coded, including connection objects such as COM objects and ODBC data source names (DSNs).
Identify whether any there are any connections other than TCP/IP (exclude internal connections connecting via named pipes).
Identify all applications that are known to have hard-coded parameters.
Identify all applications for which vendors are no longer accessible.
Collect information that describes the types of workloads that each application generates.
Determine the domain that the central server will reside in.
Identify the domains of the source servers.
Identify the domains of the connection sources.
Collect the trust relationships between the central server and all connection sources.

Health Checks

Execute DBCC statements and scan logs for errors.
Review error logs and event viewers.
Determine whether any procedures must be added to cache before opening the environment to the user community.

Multiple Versions

Collect all versions of servers, binaries, and compatibility modes of the databases.

Excessive Use of tempdb

Determine the maximum space used by the tempdb for source servers.
Establish the amount of memory used for procedures,

Database Options

Determine how much space has been assigned to devices on source servers.
Determine how much space is actually used.
Determine the maximum space used for the transaction logs.
Examine snapshots to assess the growth rate over the last week, month, quarter, six months, and year.
Analyze whether the backup strategy currently in use at the source servers is in line with the strategy to be used at the target server.
Determine whether there are any non-default requirements for connection settings, ANSI settings, and any other settings.
Collect the current database options.

Planning Considerations

This section provides the in-depth planning considerations that must be worked out for any consolidated environment:

Single Instance Versus Multiple Instances

Disk Subsystem
Memory
Networking
Processor
Security and Logins
High Availability
Replication
Migrating Objects
Administration
Chargeback
System Databases
Collations and Sort Orders
Other Technical Considerations

Once you gather the necessary information for each element, you can perform the analysis that will let you put the proper plans in place.

Single Instance Versus Multiple Instances

Instances were first introduced in SQL Server 2000. One instance of SQL Server 2000 is equivalent to one installation of SQL Server on your machine. Prior to SQL Server 2000, you could only have one installation of SQL Server per physical server.

Before you can consider any other technical aspect of consolidation, you must decide whether consolidation will occur on one instance of SQL Server with many databases, or on multiple instances, each with its own set of databases.

There are two types of instances: default and named. A default instance is analogous to the current functionality of previous versions of SQL Server. A named instance of SQL Server is exactly what it sounds like: a SQL installation that you have given a unique name. SQL Server 2000 supports up to 16 instances on one physical server or in a Windows server cluster. This is the tested and supported limit under the 32-bit version of SQL Server 2000; in theory you are limited only by the resources you have on hand. However, you are permitted only one default instance, so older applications that cannot handle named instances may have problems.

Each instance gets its own set of core binaries, but all instances share underlying components such as MDAC, Microsoft Distributed Transaction Coordinator, and a single Microsoft Search service. Therefore if there are multiple instances of SQL Server in an environment, each instance can remain at its own service pack level with the exception of any shared components. This is a major benefit for consolidation. However, there is another consequence as well: any actions that affect one of the shared resources will affect all systems. For example, if you need to install a SQL Server 2000 service pack that requires a physical reboot, you will affect all other services running on that server. Because of this, be sure to take into account both availability and service level agreements when designing consolidated SQL Server systems.

Instances and Performance

A single instance means less administrative overhead in most cases. With only one instance to manage, the complexity of a SQL Server system can be easily managed and localized. It also ensures that "automatic" settings such as using dynamic memory will be easier to consider because there are no other processes to contend with.

Multiple instances allow for each instance to cater to a different set of rules, service level agreements, and so on. Multiple instances also mean more complexity when it comes to system design and management on all levels. You must analyze whether the rewards of dedicated memory and separate processes (with the exception of shared components) are worth the tradeoff in your situation.

When thinking about single versus multiple instances, your decisions concerning disk I/O, processor, and memory are the key considerations. Consult the relevant sections in this paper, as well as other resources, to ensure that when combining multiple workloads on one physical server or a server cluster (which in this case is multiple instances of SQL Server 2000), they will work well together. You can promote success by performing proper capacity planning and applying solid performance metrics. When you profile each individual SQL Server installation's usage of disk, processor, and memory, you get a snapshot of its usage patterns. Combining that information from all SQL Server installations will tell you what can and cannot be handled — within reason. If you are moving to better hardware, the numbers might not be representative of performance or every bit of usage on that system, but it will provide a reliable starting point.

For example, each instance gets its own memory, including cache for stored procedures. If one application uses most of the procedure cache, it will affect the performance and availability of another. So in your consolidation effort, you must determine whether it is better to put hundreds of databases under one instance or to spread the databases out over multiple instances. ("Memory" later in this section delves into this question in more depth.)

Application Compatibility with Instances

The architecture of SQL Server 2000 has changed substantially with the introduction of instances, in particular named instances. Be sure to test older applications to ensure that they still work properly with SQL Server 2000, regardless of whether you are using named instances.

Although named instances do technically require MDAC 2.6 (or later) in order to allow applications and users to connect to SQL Server 2000 named instances properly (support for named instances was introduced with MDAC 2.6), there are some possible workarounds listed later in this section for working with named instances if you do not have MDAC 2.6 or later. If an application cannot connect to a named instance, here are a few troubleshooting suggestions:

Install MDAC 2.6 or later on the server hosting the application, or on each client computer if the application is run directly by the client. MDAC 2.6, which ships with SQL Server 2000, contains support for named instances. Installing MDAC 2.6 does not guarantee that the application will work with named instances, though. Testing each application on one or several computers is recommended before rolling the applications out to large numbers of users.
After updating the client tools to SQL Server 2000, use the SQL Server 2000 version of Client Network Utility to create an alias to the named instance that the application can recognize.
If it is not possible to update the client tools to a SQL Server 2000 version that installs MDAC 2.6 or later, do not use the name of the SQL Server named instance, but connect directly to the IP address and port number associated with that instance. Configure the alias in the SQL Server 7.0 or 6.5 version of Client Network Utility.
Update application code to support named instances. If the application already includes hard-coded server names, database names, and paths to the application, you face bigger issues than just named instances. The application code may be out of scope of the project. You face migration issues and possibly availability issues because the application is inflexible.

If you are keeping all of your older applications on default instances, they will probably perform just as they do now, but it's wise to double-check them as well.

Disk Subsystem

How your disk subsystem is architected will be one of the defining factors in determining whether you are able to use multiple instances for your system. The disk architecture affects every aspect of SQL Server, from performance to availability and everything in between. The basic rule: the more spindles, or physical drives, you have servicing your I/O requests, the less contention you will encounter. But that is easier said than done. The cost involved with many spindles can become prohibitive very quickly.

You need the proper amount of disk space, which is determined by the interaction of many things (including data size, tempdb size, log size, Microsoft Distributed Transaction Coordinator (MS DTC) usage, full-text indexes, and any other disk-related aspect). You must also factor in the space for the SQL Server binaries, which are a lesser concern because they are placed on the system disk.

How will you combine your applications and databases, given that they may have conflicting I/O characteristics? That is a serious challenge faced by customers both big and small. Read-only reporting databases have different I/O requirements than a heavily used OLTP system with many writes and a smaller amount of reads. Do not take this configuration lightly: you may initially save cost by consolidating, but if you are left with underperforming applications, the consolidated environment will be a failure.

When you are considering the architecture of your disk solution, your decisions about file placement will greatly affect both performance and availability. No matter what, log files should always be separated onto physically separate disks. Logging is a different form of disk access than reads or writes: it is a sequential write, and if possible, you never want the head of the drive to be lifted. Putting logs on their own dedicated disks not only increases performance, but it also increases availability. If the database goes down and you have a backup as well as access to the log, you can usually restore your database to the point of failure. However, do not assume you should put multiple logs on one drive. That would defeat the purpose, because logs expect a continuous write where the head never picks up. If you place multiple logs on one drive, depending on activity you could affect the performance and other aspects of your solution. Such a setup may be unrealistic from a cost perspective, but you must know the risk and figure out your tradeoffs (both technical and non-technical), because a 1:1 ratio from disk to log is not usually possible.

Placing backup files on disks used by data and logs is not recommended, either. Performing backups on either data or log disks tends to slow performance of the live databases. Writing backups, especially medium to large ones, to the same disk as a heavily used read and write system will definitely affect performance. Correlate all of the I/O numbers you collect prior to placing more files such as backups on your disks, and you will see how much you are demanding from your disks during an operation. Another consideration here is the physical storage requirement for backups. Do you have enough disk space to place your backups? Where will they go?

If you can take advantage of your disk subsystem's cache, configure it to match the work you are doing. Again, this is harder in a consolidated environment when you may have different workloads. From another hardware perspective, you may want to consider using different channels to segment I/O.

Using RAID will also help your availability and performance. Striped mirrors will provide you with excellent performance and availability, but may prove costly. RAID 5 is the most common option for many environments. However, if you have a heavy I/O intensive application, RAID 5 may not be the best choice, because it decreases availability and is slower on write performance. Buying another disk subsystem may seem expensive in the short term, but it is more than likely to save you money over the long term.

After you have considered the individual user databases, where will you place tempdb, and how will you size it? You are no longer concerned about just one database's usage of tempdb — you may have many! These considerations are crucial for the success of your deployment. Ensure that the placement of tempdb will not affect the performance of other databases. Set the proper size so that the database is not always growing simply to catch up.

Clearly, understanding your hardware is very important to designing a proper configuration. Work with your preferred hardware vendor to purchase and configure a disk subsystem that meets your performance and availability goals.

For more detailed information on configuring disk subsystems with SQL Server 2000, see Chapter 4 of Microsoft SQL Server 2000 High Availability.

Memory

Your memory requirements will depend in part on your operating system version, because each version has its own memory specifications. As with other aspects of consolidation, you must first collect specific benchmarking and application/system profiling data to make an accurate decision about your memory needs. Here are some of the main factors to examine:

Operating system options
Procedure cache size
Extended memory requirements
For extensive information on memory, see Chapter 14 in Microsoft SQL Server 2000 High Availability.

Operating System Options

Based on your initial assessment of your overall memory needs, you will need to choose the appropriate operating system. The following table shows the maximum amount of memory supported by each operating system underlying SQL Server.

Operating System	Maximum Memory Supported
Windows 2000 Advanced Server	8 gigabytes (GB)
Windows 2000 Datacenter Server	32 GB
Windows Server 2003 Enterprise Edition (32-bit)	32 GB
Windows Server 2003 Enterprise Edition (64-bit)	64 GB
Windows Server 2003 Datacenter Server (32-bit)	64 GB
Windows Server 2003 Datacenter Server (64-bit)	512 GB

For additional assistance in determining your operating system setup, see "Processors" later in this section. For more information on how much memory is consumed by the various types of objects, see "Memory Used by SQL Server Objects Specifications" in SQL Server Books Online.

Procedure Cache Size

The biggest consideration for applications and databases when it comes to memory and SQL Server usage is the usage of SQL Server's procedure cache. This decision will in turn dictate whether to use one instance or multiple instances for your consolidation. With one of the 32-bit operating systems running a 32-bit version of SQL Server, you can access at most 2.6 GB of procedure cache (the exact amount also depends on which memory options and operating system you use; for details see "32-bit Advanced Memory Requirements" later in this section).

If you are planning to consolidate multiple databases, each with many stored procedures, you could easily run out of procedure cache. This configuration will definitely affect performance, and will require you to plan for multiple instances. If you do not yet know whether this is the case in your situation, you will discover it during the testing phase.

32-bit Advanced Memory Requirements

A consolidated environment usually requires more than 2 GB of memory, which is the base memory support provided by the 32-bit versions of SQL Server. To access any memory above 2 GB various specific options — some operating system-based, some SQL Server-based — are required.

If you plan to use large amounts of memory in your consolidation solution, make sure that your chosen configuration includes hardware that supports this. Make sure the hardware is listed on the Hardware Compatibility List, and cross-reference it with the manufacturer's specifications.

As noted in "Operating System Options" earlier in this section, your operating system choice will determine the maximum amount of memory you can possibly utilize. The standard Windows 2000 (without Addressing Windowing Extensions [AWE] enabled) can only use a maximum of 4 GB of physical memory, due to limitations of 32-bit memory addressing. By default, 2 GB of this memory allocation is dedicated to the operating system and 2 GB is dedicated to the application. As a result, even if a server were configured with 8 GB of memory, anything beyond 4 GB would be virtually unusable unless you set the appropriate options.

You must also make a few configuration decisions in deciding how much memory to give each individual SQL Server instance. First, find out how much memory your current installations are using. You should know these numbers prior to migration, so you can correlate the anticipated memory usage. With this number, you can figure out how the combined workloads might work together and from that arrive at the minimum amount of memory necessary for a particular instance.

If you are using only a single instance of SQL Server, memory is of less concern. You must still ensure that you have the right amount of memory, because no other SQL Server instances will be fighting for any type of resources, which includes overall system memory usage.

A multiple-instance configuration is much more challenging, and increases in difficulty with each instance. If you also plan to use failover clustering, you must evaluate how each instance will behave in a failover.

Accessing Memory Beyond 2 GB

There are a few options for accessing more than 2 GB of memory:

Physical Addressing Extension (PAE) (in the boot.ini file) allows all 32-bit versions of Windows to access more than 4 GB of memory. The /PAE parameter is typically used with AWE (but AWE does not depend on PAE because AWE can be set for less than 4 GB).
Address Windowing Extension (AWE), a combination of operating system configuration and SQL Server configuration, allows an application such as SQL Server to address additional physical memory. If you enable AWE, you must set a maximum amount of memory for the instance; memory is no longer dynamic. AWE does not require PAE, but in most cases the two are used together. (AWE within SQL Server can be set without PAE, but there is no real value in doing this because accessing memory less than 4 GB can be handled in other ways.)
/3GB parameter (in boot.ini) allows a 32-bit instance of SQL Server to use up to 3 GB of memory, and always reserves an additional 1 GB of memory for Windows. You can combine /3GB and AWE, but do so only after you have evaluated why you are taking that step. Usually you will use just one or the other.

The following table summarizes how to configure extended memory settings, based on the amount of memory you are configuring for use with SQL Server.

Memory Requirements
4 GB or less	4 GB to 16 GB	More than 16GB
/3GB switch	/3GB enabled	/3GB disabled
	AWE enabled	AWE enabled
	/PAE enabled	/PAE enabled

Note: If you are enabling AWE or PAE memory, we strongly recommend that you test the configuration prior to bringing the servers online in a production capacity. Also, you may not want to use /3GB along with the combination of AWE and /PAE. Evaluate your configurations and needs carefully, and test them extensively.

These three memory options are enabled with two different mechanisms, the boot.ini file and the sp_configure stored procedure.

The boot.ini File

The /3GB parameter is a switch enabled through the boot.ini file. After you have installed Windows 2000 Advanced Server, modify boot.ini to add /3GB to the path, as shown in bold format in the following example:

multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows 2000 Advanced
Server" /3GB /basevideo /sos

PAE is also enabled via a switch in the boot.ini file. Open boot.ini, and then add the /PAE parameter to the path, as shown in bold format in the following example:

multi(0)disk(0)rdisk(0)partition(2)\WINNT="Windows 2000 Advanced
Server" /PAE /basevideo /sos

AWE: Address Windowing Extensions

AWE is the support built into the operating system as a way of exposing extended memory to Win32®-based applications. With AWE, a memory-intensive application can now run much more efficiently under SQL Server 2000, thereby increasing performance. Windows 2000 Advanced Server and Windows 2000 Datacenter Server introduced the enhanced AWE API. AWE allows applications to access large amounts of physical memory. Due to limitations of 32-bit memory addressing, Windows NT® 4.0 and Windows 2000 without AWE enabled can use only up to 4 GB of physical memory. By default, 2 GB of memory is dedicated to the operating system and 2 GB of memory is dedicated to the application. With a /3GB parameter in the boot.ini file used by the operating system, an application such as SQL Server can access up to 3 GB of memory, and the operating system is reduced to 1 GB of memory. As a result, even if a server were configured with 8 GB of memory, anything beyond 4 GB would have been virtually unusable before AWE was introduced.

AWE requires an application, such as SQL Server 2000, that can be coded specifically for AWE, which is done through an API. AWE support within SQL Server 2000 must be configured using the awe enabled option in the sp_configure stored procedure. This option is set on a per-instance basis. By default, awe enabled is set to 0 (off). Enabling AWE support in SQL Server 2000 also requires some additional operating-system configuration. For more information, see "AWE Memory" in SQL Server Books Online.

The sp_configure Stored Procedure

AWE is enabled within a SQL Server 2000 query by a call to sp_configure, as shown in this example:

EXEC sp_configure 'awe enabled', 1
RECONFIGURE

When you implement AWE memory, consider these issues:

The instance of SQL Server does not dynamically manage the size of the memory address space used.

When AWE is enabled with SQL Server 2000, if the max server memory configuration option is not set, SQL Server grabs the total memory available (except 128 MB to allow the base operating system to function), potentially depriving the operating system and any other processes that would be running on the same server.
After it has been initialized, AWE memory holds all the physical memory acquired at startup until it is shut down.

If AWE is enabled and is taking too much memory, SQL Server must be shut down to reconfigure AWE, causing downtime (which makes a high-availability option such as failover clustering less available). Because the memory pages used by the instance of SQL Server are taken from the nonpageable pool of Windows memory, none of the memory can be exchanged. This means that if the physical memory is filled up, SQL Server cannot use the page file set up on a physical disk to account for the surplus in memory usage.
Once the max server memory option is configured, set the working set size to 0.

For more information about configuring AWE memory on your server, see "Using AWE Memory on Windows 2000" in SQL Server Books Online, as well as Appendix A, "Technical Resources," later in this paper. For more information on clusters and memory, look at the example as well as the information noted in Appendix A.

Memory Example 1: Four Instances of SQL Server (Clustered or Standalone)

Whether clustered or not, SQL Server can use only the resources that are available to it. Consider the following example:

You currently have four SQL Server instances, each of which currently consumes 33 percent of the resources on its server (in this case, it can mean memory, but you may in some cases extend this example to include processor, disk, etc.). You then consolidate the four workloads, either in a failover cluster or onto a single server. The new maximum capacity, 100 percent, is less than the combined capacity of the original four servers separately (33% X 4, or 132 percent).

To match the original capacity, you will need to either use multiple servers to consolidate (for example, two workloads on each of two physical servers) or configure more than two nodes in a clustered situation.

sql2k03

Memory Example 2: Memory and Failovers

Building on the previous example, consider a 32-bit clustered environment with two nodes. On each node you have one clustered instance of SQL Server 2000, for a total of two SQL Server virtual instances. Each instance is given 7 GB of memory using AWE (out of a maximum 8 GB per server).

Then a situation arises that should fail an instance from one node to another, but the operation does not work. Why? Because 7 GB plus 7 GB does not equal 8 GB; it equals 14 GB! As demonstrated in the previous example, you cannot exceed 100 percent of capacity without affecting server performance. And because you have enabled AWE on a 32-bit platform, the memory is not dynamically managed.

To achieve your intended capacity, you will need to reconfigure each instance so that both can work under one system in the event of a failover. You must cap memory at some point that allows room for two instances plus the operating system. With an even split, you would configure 3.5 GB of memory per instance, leaving 1 GB for the operating system.

Now consider the same situation except that you did not enable PAE or AWE, and you are using dynamic memory. A similar consequence will occur: when the instance fails, it will grab as much memory as it needs. If you have two or more instances, all of which are memory-intensive, they will contend for your resources. In this case as well, unless you know definitively that a failover will not cause a performance hit (except maybe an initial one), be sure to physically limit the amount of memory you give to each instance, to ensure that you will encounter no failover problems.

64-Bit Memory Utilization

The 64-bit version of SQL Server changes the traditional rules for memory. With SQL Server 2000 64-bit, all memory can be dynamic — you are not limited from the start by just 2 GB of memory. Under 64-bit, set a minimum amount of memory for use with a SQL Server instance, which would be the minimum amount of memory needed, and let dynamic memory handle memory requirements from that point. In certain cases, you may need to set a maximum. Either way, 64-bit opens up a whole new world of possibilities for resource utilization and consolidation.

Note: Whether you are using a 32-bit instance with AWE or a 64-bit instance, it will take some time to zero out the amount of memory you have designated. Please take that into account, especially if you are using failover clustering.

Networking

When you consolidate, more network traffic will be flowing to one SQL Server. To make effective decisions about handling your new networking requirements, you need to know how many concurrent users are usually connected to the SQL Server at various times, especially under maximum load.

In terms of memory, you must take into account all the network connections in addition to the memory required for normal SQL Server usage (for procedure cache, queries, and so on). Each connection consumes 12 kilobytes (KB) of memory, which must be added to the network packet size and then multiplied by three:

12 + (3 x packet size) = connection memory requirement

This calculation will help you determine whether to consolidate on one instance or multiple instances.

Once you know how much memory is taken up by user connections for each database, examine network bandwidth. Pose the question: Can the proposed networking plan handle what is coming in and going out on the server? For example, suppose you have one 10-megabit (Mb) card, but your overall throughput requirement is 20 MB. Do you add another network card and IP address (both of which add a bit of system overhead)?

Or instead do you get a higher-bandwidth network card and ensure that the network itself can handle the load?

Other networking issues include:

Port usage What TCP/IP port is each SQL Server installation using? Upon installation, SQL Server 2000 grabs a dynamic port, and not necessarily port 1433, which is the case for previous versions of SQL Server. This is partially due to the instance support, because multiple instances cannot share the same port. Generally the first instance (whether named or default) will be assigned to 1433. UDP port 1434 is reserved for SQL Server as well. Use Server Network Utility to assign a known, static port to each instance.
Domain connectivity Does the SQL Server installation require domain connectivity (a failover cluster) or does it have processes that use other SQL Server instances (such as log shipping)? Are all the SQL Server installations in the same domain? Mixed domains can cause problems in consolidation.
Domain accounts Do you have SQL Server installations that use domain accounts for the service accounts, or do they use local administrator accounts that are only relevant on the specific server? Are those installations all in the same domain? How many servers are using a local service account? In the consolidation effort, you can resolve these issues by standardizing the way you configure the administrator account and connectivity.

However, standardizing your configuration in these ways does not solve ownership issues: object ownership, for example, or how SQL Server Agent job execution is assigned to a user. Those more complex issues, among others, will also need to be resolved.

Processors

There are a few considerations when determining how many processors the new servers will contain. As with disk and memory, you must gather benchmarking and application/system profiling data in order to make an accurate decision. Here are some of the main factors to examine:

Processor affinity For the purposes of consolidation, processor affinity lets you constrain an instance of SQL Server to a subset of available processors on the server. For example, if you have four processors on the server, you can restrict SQL Server's usage to three. To restrict processor usage for a particular instance, use the affinity mask option of SQL Server. (For more information on the affinity mask option, see "affinity mask Option" in SQL Server Books Online.) This will (for the most part) prevent one SQL Server instance from consuming all the available resources, leaving only a fraction of resources remaining for other instances. This option is a double-edged sword. The negative aspect is that the resources remaining in the fraction may remain idle if there is no other workload available to benefit from the unused capacity. Therefore, before using processor affinity, think through the implications of using processor affinity to manage the processor allocation, including the downside of potentially not fully utilizing the resources.
Connection affinity This allows an instance to always direct a set of client connections to a specific SQL Server Scheduler, which in turn manages the dispatching of threads on the available processors. There is only a narrow set of conditions under which connection affinity provides a noticeable performance benefit. The downside of using connection affinity is similar to that discussed above for processor affinity. If misused, connection affinity can prevent full utilization of all the available resources.
Degree of parallelism If you are moving an application from a system with just one or two processors to one that has six or eight processors to handle increased load, each query may now perform differently due to a new execution plan. In such a case, you may need to alter the configuration settings of the max degree of parallelism option, and possibly also the affinity mask option. Proceed with care, because when combining multiple workloads in this way, tuning these two options will affect everything running under that instance.
SQL Server parameters only In all cases but one, do not use any other tool besides SQL Server to manage your processor resources. Other tools may not produce the effect you want. All processor-related adjustments should be done with the SQL Server parameters. The only exception is to use Microsoft Windows System Resource Manager (WSRM) in Windows Server 2003, but only for configuring the percentage of the processor that a specific instance of SQL Server can use.

Based on your processor needs, you will need to choose the appropriate operating system. There are both 32-bit and 64-bit versions to choose from. Besides the number of processors that each version supports, it is important to take into consideration the variations in processing power between a 32-bit and a 64-bit processor. Your decision will be based on more than processor speed, and the results you will see in your implementation will vary. Keep in mind that 64-bit processors do not guarantee that your application will perform ten times better, or even twice as well. Testing your application against a 64-bit platform is the only true way to show the improvements that you will gain from going to 64-bit.

The following table shows the maximum number of processors supported by each operating system underlying SQL Server.

Operating System	Number of Processors
Windows 2000 Advanced Server	8
Windows 2000 Datacenter Server	32
Windows Server 2003 Enterprise Edition (32- or 64-bit)	8
Windows Server 2003 Datacenter Server (32- or 64-bit; you need a minimum of 8 processors)	64

For additional assistance in determining your operating system setup, see "Operating System Options" under "Memory" earlier in this section.

Security and Logins

Security is an obvious concern. Are there different standards for all of the applications, databases, and servers being consolidated? And if there are, are they in conflict with each other? Or, for example, perhaps something like IPSec is required, but you are consolidating to a cluster where IPSec is not designed for failover (see Appendix A for a reference to the relevant Knowledge Base article), so the application will need to be changed or possibly not consolidated.

How applications and users actually log in to and access SQL Server is the biggest area of concern for security in a consolidation effort. User migration may consume a great deal of planning time. Already mentioned in "Networking," earlier in this paper, were domain connectivity and domain accounts, but here the focus is on the actual logins. Do you have servers that have a mix of both Windows Authentication and Mixed Mode? Will that introduce risk when workloads are combined?

Also, security changed somewhat since SQL Server 6.5 and earlier. SQL Server 7.0 introduced the concept of roles, so you may want to rethink how security is done for a particular application. This may lead you into scope creep, which you want to avoid, so do not consider or implement a new security plan if it will impede the consolidation effort.

Here are some issues to consider with logins:

Plan sufficient time for your login and user migration planning, testing, and implementation. This is more time-consuming than generating scripts and applying them to the target environment: you will need to scrub the logins to ensure that the right ones are brought over, that old logins can be deleted, and so on.
Give users only the rights they require. Suppose you have a login named JoeJ at the SQL Server level who has sysadmin privileges on one database server, generic user privileges on another database server, and dbo privileges on one specific database located on another server. Are all these privileges appropriate when you consolidate onto fewer servers? Do you want JoeJ to have sysadmin privileges on those instances? When consolidating, you do not want to accidentally give one user more rights than necessary. Take this into account when combining workloads.
A consolidated system cannot permit multiple different users with the same login name, across different database servers. Until now, this was not a problem. It will take good communication to resolve this issue.
Do not permit a person to maintain different passwords across different servers that are now being consolidated. You can either assign a new password, choose one of the current passwords yourself, or ask the user to select which password to use.
Consider the BUILTIN\ADMINISTRATORS login. Do you need it? Do you currently remove it from all installations? If you do not remove it, but are considering doing so, what will the impact be if you do? Does any third-party software require it?
Consolidation means that you will probably have more users connected to the server. What permissions will you grant to public and guest logins?
Your system may include numerous inactive or outdated logins, such as past employees. Only migrate the logins that are in active use. Remember that adding and dropping logins affects the transaction log.
If Windows Authentication is used in your system, do the logins come from different domains? Will you need to set up two-way trusts at the Windows level?
If the application using the database currently uses or requires sa access, that should be evaluated. Applications should not utilize the sa user, since it has full access to every database on SQL Server. It is a general SQL Server security best practice to not allow sysadmin access to any user or application that does not need it. As long as the user (or users) of the application is the dbo for that database, in most cases that should be sufficient to administer and do what is needed in that database.
Have you locked down group policies on Windows Server instances since the server was rolled out? How will that affect the new consolidated environment? For example, have you locked down the policies needed for clustering, if that is the desired end goal?

High Availability

One important aspect of consolidation is increasing the availability of your SQL Server solution. SQL Server 2000 has two primary methods of achieving high availability as built-in features: failover clustering and log shipping. Replication can also be used to achieve availability in some cases.

Failover clustering is the method that lends itself best as a primary form of availability. It provides an automatic failover to another server in the event of a problem. Planning and configuring a failover cluster is quite similar to planning for consolidation, when it comes to thinking about processor, memory, and disk usage. Another way in which failover clustering lends itself to consolidation is that a single platform is made available in the same way, and can also have a certain number of nodes, which are servers participating in a server cluster, to service availability. It allows you to plan your consolidation effort much more easily. For example, if you employ a four-node cluster, you will be able to better balance resources, assuming no failures as well as more memory available to you (see "Memory" earlier in this paper).

SQL Server 2000 Enterprise Edition is required for failover clustering. The following table lists the number of nodes supported by SQL Server 2000 Enterprise Edition for each version of the Windows operating system on which SQL Server 2000 runs.

Windows operating system	Maximum nodes supported by SQL Server 2000
Windows 2000 Advanced Server	2
Windows 2000 Datacenter Server	4
Windows Server 2003 Enterprise Edition (32-bit) and Windows Server 2003 Datacenter Server (32-bit)	4
Windows Server 2003 Enterprise Edition (64-bit) and Windows Server 2003 Datacenter Server (64-bit)	8 (requires 64-bit version of SQL Server)

If you already have a form of high availability configured, such as failover clustering or log shipping, that will be a consideration during the consolidation planning process. For example, if you are currently log shipping a database, will it continue to be log shipped in the consolidated environment? Or will you consolidate the log shipping tasks to have more than one database going to a warm standby server? How will that affect availability?

For more information on SQL Server 2000 high availability, see Appendix A, "Technical Resources."

Replication

If replication is configured on any of the databases being considered for consolidation, you will certainly need to include this as part of your planning. Each database, or publisher, needs a distributor. However, a 1:1 ratio of publishers to distributors is probably unrealistic in a consolidated environment. Therefore you will need to do some up-front capacity planning to ensure that the distributor can handle multiple publishers. Disk I/O will be affected greatly, followed by processor and memory, especially depending on the type of replication you have implemented. This also means that the distributor will need to exist in a completely separate instance or (most likely) on a different server, to ensure its performance and availability.

You can consider a remote distributor as well. Remote distributors for various publishers can be consolidated under one SQL Server instance, but distribution databases per publisher should be distinct. One instance with a publisher and distributor may have been acceptable in a non-consolidated environment, but it will not be acceptable in a consolidated environment.

You must also consider the base snapshot directory and its location per publisher. Will you share one per SQL Server instance, or use a second one?

Consider the subscribers as well, because they will also be affected if you move publishers and distributors and must disable replication during the consolidation process.

Consolidation of replication (or log shipping) is similar to consolidating databases, with a few different variables.

Migrating Objects

One of the hardest tasks for any upgrade, migration, or consolidation effort is to migrate objects, such as alerts, SQL Server Agent jobs, and DTS packages.

You can easily script alerts, jobs, and operators. However, before applying them to the consolidated SQL Server, ensure that there are no naming conflicts. When you migrate to the new server, be sure to associate the proper user with each SQL Server Agent job.

You have a few options for migrating DTS packages to other servers, including saving them as a file or re-creating them. If you move the packages, do not assume that they will run just as they did before — you might need to make modifications to them. Also ensure that the package is compatible with your current version of SQL Server: as of SQL Server 7.0 Service Pack 2, Microsoft changed the format from the way it was in SQL Server 7.0 RTM and Service Pack 1. Because the format changed when Service Pack 2 was introduced, you would have to completely recreate the package under SQL Server 2000. Remember to add time in your testing phase for testing and possibly fixing these types of issues.

Note: For a list of resources to assist you in the migration of these objects, see Appendix A, "Technical Resources."

Important: The 64-bit version of SQL Server cannot run DTS packages. You can execute DTS packages under 32-bit SQL Server 2000 against 64-bit instances with no problems. See "Differences Between 64-bit and 32-bit Releases (64-bit)" in the 64-bit edition of SQL Server Books Online, as well as "Features unavailable on 64-bit versions of the Windows Server 2003 family" in Windows documentation.

Administration

Prior to migration, think about the following considerations, which will affect the eventual administration of each consolidated SQL Server instance:

Server-wide server settings will definitely need to be reconciled before bringing the new consolidated server online. Each individual instance of SQL Server might have its own settings, and one setting can impact a database in many ways, so coming up with the final, global set of server settings will be crucial.
Similarly, if you use any startup options, those will also have to be reconciled.
For elements such as replication or SQL Server Agent jobs existing in msdb, do you have any history that is still valid and would need to be migrated?
How will your maintenance plan change as a result of adding multiple databases or instances? For example, how will your entire enterprise backup strategy change? Will one backup now conflict with another and affect performance? Is your maintenance window for the server now smaller because of increased load?
Watch msdb growth, because with more databases, it may increase in size due to more statuses and histories being written (depending on what functionality of SQL Server you are using).

Chargeback

Chargeback is the process of assessing cost to utilization of the hardware, so business units or customers can be charged appropriately. If chargeback is a consideration, you have a few options. One is to use SQL Server; its use is documented in Appendix B, "Implementing Chargeback Using SQL Server." There are also third-party tools such as ARMTech from Aurema to assist you in your cost accounting for system resource usage.

System Databases

System databases are an important consideration in a consolidation effort. The system databases contain data and structures that are relevant to the entire server installation. Unlike user databases that can exist independently of each other, system databases are closely associated with each other as well as with the user databases from the source server.

How will master, model, and msdb be configured? Now that you are combining multiple servers, each with its own copy of the databases, you cannot just restore multiple copies, because the last restore will overwrite the previous copy. Remember that msdb includes all your jobs, alerts, and operators (as well as history for some things), so plan on a size of at least 45 MB plus add on the additional amount per instance as well as about 10 percent overage.

Besides the space requirements noted above, you need to analyze master, model, tempdb, and msdb to detect and document duplicate database objects. This will extend from logins, stored procedures, user-defined data types and tasks, through to the specifics of of objects. For instance, an object of the same name will represent or perform actions particular to source server, or an object of a different name will represent or perform actions that accept modifications at server level. This becomes particularly difficult when modifications have been made to these databases outside of logins. Remember to include any and all startup stored procedures.

You must identify things that are not part of a standard SQL Server build or installation, whether modifications or new objects. Review each object for relevance in a consolidated environment. Determine whether duplication exists at a more granular level. Check each object for explicit references, such as path names, server names, and task names. Do not address tasks found in msdb, because those are handled separately and must be dealt with on their own. Only search for non-default objects found in tempdb. (The use of tempdb is dealt with in "Collations and Sort Orders" later in this section.)

Collations and Sort Orders

The collations and sort orders between each server considered for consolidation must be taken into account during consolidation efforts. It is more likely that databases of varying collations will co-exist in a consolidated environment than in numerous stand-alone servers. Certain applications depend on the collation and sort order setting of the database. Therefore, you might encounter unexpected results after migrating to a consolidated environment. If the applications are not set properly, they will not work after the switch to the consolidated environment.

Temporary tables will be created in tempdb with collation settings of tempdb if the collation syntax is not included in the Transact-SQL. Passwords in a case-insensitive SQL Server instance are converted to uppercase before being stored or used. Passwords in a case-sensitive SQL Server instance are not converted to uppercase. Because of this difference, passwords originally encrypted on a case-sensitive server and later transferred to a case-insensitive server cannot be used unless all alphabetic characters in the password are uppercase.

For more information about this behavior, see "Effect on Passwords of Changing Sort Orders" in either the SQL Server 6.5 or 7.0 Books Online, or see "Selecting Collations" in SQL Server Books Online.

Other Technical Considerations

This section addresses technical factors to consider prior to migration that are not covered elsewhere:

Linked servers

Are you currently using linked servers to enable connectivity and queries between servers that may no longer exist after consolidation? Are you changing domains that may also affect linked servers? If you consolidate, how will consolidation affect the application? Will queries need to be rewritten? How will you migrate your linked server settings?
Shared resources

As has been mentioned a few times, certain resources, such as MS DTC and the underlying Microsoft Search service that powers full-text searching, are shared among all instances and even other applications. Resource sharing will be a concern in a consolidated environment as you add more databases and resources that utilize shared resources.
Upgrade and migration rules

Upgrade rules and migration rules for general applications and databases continue to apply in a consolidation effort. Remember to take into account running of features like DBCCs, reviewing logs, and updating statistics, and fix any issues prior to moving the database. Also remember that, depending on the physical layout (locations of the data and log files for your consolidated SQL Server), what you do to one database may affect others.
System tables

If you have modified any of the system tables, those modifications may not work with the consolidated SQL Servers. Also, Microsoft does not recommend that you modify the system tables or add objects to system databases like msdb or master. Place any customizations or objects that you create in user databases.
Collation and sort order conflicts

Are there collation and sort order conflicts between the current SQL Server instances and the proposed consolidated SQL Server environment? This is an easy item to miss if the planners, implementers, and DBAs are not communicating. Resolve any conflicts prior to consolidating.
Extended stored procedures

Does the database to be consolidated use extended stored procedures? If so, how will that affect other databases and potentially other instances? Remember that an extended stored procedure is similar to a coded application, so if it contains a memory leak and you consolidate multiple applications onto one SQL Server 2000 instance, you might be putting other applications at risk.
Service packs

Service packs are a big consideration for a consolidated environment. If you have one SQL Server at one service-pack level, and another at a different level, is that OK? With SQL Server 2000, you can have mixed service-pack levels for the individual instances, but any shared components (such as MDAC) will be at the latest version. However, with Windows Server 2003, you must be at SQL Server 2000 Service Pack 3 or later. This will definitely affect any consolidation effort that will happen on any version of Windows Server 2003. For example, if you have three instances installed under Windows 2000, one can be SQL Server 2000 Service Pack 1, one can be SQL Server 2000 Service Pack 2, and one can be SQL Server 2000 Service Pack 3. In this case, all shared binaries and files will be at the SP3 level, but the individual databases and binaries that are not shared will be at their respective service pack levels.
XML stored procedures

XML stored procedures are not supported in fiber mode, so use thread mode or a separate instance to avoid error 6604.

Tools for SQL Server Consolidation

Depending on which version of SQL Server you are starting from, different native SQL Server-based tools are available to assist during the consolidation effort. Other requirements will also drive decisions about what to use, such as the window of time that is open to perform the migration. Whatever tool you decide to use, realize that each has its own considerations to take into account.

SQL Server 6.5 to SQL Server 2000

The database formats of SQL Server 6.5 and SQL Server 2000 are incompatible. When migrating from SQL Server 6.5 and SQL Server 2000 as part of a consolidation plan, it is not possible to use the backup and restore process to create the database on the target SQL Server 2000 instance. You can consider using flat files or the Upgrade Wizard when planning your migration from SQL Server 6.5:

The use of flat files is one tried-and-true method of migrating data from one platform to another. The BCP (bulk copy program) utility has been in SQL Server since version 4.21a, and now there is not only the command-line version, but the Transact-SQL command BULK INSERT. In some ways, this will be easier to plan than using the Upgrade Wizard, and in others, harder. For example, for this migration you must create all your databases in SQL Server 2000 with the proper size, and devise a process to migrate the users. BCP only takes care of the data migration. Any other elements, such as indexes and views, must be recreated after the bulk insert, because configuring indexes prior to inserting data may slow the process down.

SQL Server 2000 has a built-in Upgrade Wizard to assist you with your migration from SQL Server 6.5. It takes into account all aspects of your SQL Server 6.5 configuration, including users. However, the Upgrade Wizard is not appropriate for all migrations to SQL Server 2000. For more information and details around the specifics of using the Upgrade Wizard, see "Upgrading Databases from SQL Server 6.5 (Upgrade Wizard)" in SQL Server Books Online.

SQL Server 7.0 to SQL Server 2000

If you are migrating from SQL Server 7.0 to SQL Server 2000, you have more options at your disposal.

Backup and restore needs no long introduction. It is possible to take a SQL Server 7.0 backup and restore it under SQL Server 2000; the restore process will upgrade the database along with things such as rebuilding statistics.
BCP and BULK INSERT work the same as they do with a 6.5 or 2000 database: you use flat files to import data into a database. With BCP or BULK INSERT you must havevanother method for importing users, objects, and other objects that are not core data.
The Copy Database Wizard uses the attach and detach functionality of SQL Server to perform the migration. The database files are detached, copied, and then attached to the target server. When going from SQL Server 7.0 to SQL Server 2000, the attach process upgrades the database to SQL Server 2000; however, statistics are not automatically rebuilt by this process. That may be a consideration when deciding between backup/restore and the Copy Database Wizard. Also, if the collations are different between the SQL Server instances, especially if you have an international application, then you must reconcile the differences or possibly consider that this may not be able to be consolidated. (For example, if you use char or varchar data types, you cannot store and retrieve any data except what can be represented by the code page on the destination SQL Server; Unicode would be a workaround.)
With log shipping it is possible to send and then apply transaction logs from a SQL Server 7.0 SP 2 database to a SQL Server 2000 database. This is one of the better migration options, because it not only provides a fallback plan by leaving the source server in place, but the amount of downtime incurred will most likely be minimal.

SQL Server 2000 to SQL Server 2000

The same options are valid for SQL Server 2000 to SQL Server 2000 migrations as for SQL Server 7.0 to SQL Server 2000 migrations. Because all databases are at the same version level (except for service pack differences) and do not need to be upgraded, less work needs to be done. Concerns about collations and service packs are still valid, but you are not likely to be affected by overall server behavior and functionality differences and syntax changes because you are going from one engine version to the same version of the engine at the final destination.

Conclusion

Consolidation is a process with many benefits at all levels, from upper management to end users. Because of that, its scope is wide reaching, and it affects everything that the consolidated systems themselves touch — not just technology, but people and process as well. However, do not perform consolidation merely for consolidation's sake: make the proper decisions. Proper planning and testing is the only way to ensure that the end result is successful. With some standalone systems, you may be better off leaving them alone. Knowing each system's profile will greatly enhance the effort, and also help you gain a better understanding of your environment. The end result of any consolidation should be a victory from an economic, human, and technology perspective.

For more information:

https://www.microsoft.com/sql/

Appendix A: Technical Resources

Consolidation Links

General SQL Server and Windows

Useful Microsoft Support Knowledge Base Articles

Note: Knowledge Base articles are updated and added often, so search https://support.microsoft.com for others that may apply specifically to your environment.

General Hardware/Windows/Clustering

Memory

Moving Databases

Logins

DTS

SQL Mail

Antivirus Software

Log Shipping

Failover Clustering

Security

Appendix B: Implementing Chargeback Using SQL Server

This section shows you how to implement chargeback using SQL Server and its tools. The solution involves two stored procedures, sp_ChargebackProc and sp_ChargebackAnalysis, both of which are provided after this text.

Step One – Install the Stored Procedures

The two stored procedures used by the audit process are sp_ChargebackProc and sp_ChargebackAnalysis. sp_ChargebackProc does the actual auditing through logout events, which can be used to capture reads, writes, and CPU utilization over the duration of each user connection. For each user connection, a single logoff record is captured, assuming the user logs out at some point. For persisted connections, no information will be captured by this event. The overhead for tracing should be approximately 5 percent.

sp_ChargebackAnalysis helps you analyze the trace report generated by sp_ChargebackProc.

To have SQL Server use these procedures easily, generate them in msdb.

Step Two – Configure sp_ChargebackProc

The sp_ChargebackProc stored procedure must be started when SQL Server is brought online. To do this, execute the following Transact-SQL statement:

exec sp_procoption @ProcName='sp_ChargebackProc',@OptionName='startup',
@OptionValue='true'

sp_ChargebackProc writes audit logout data to an external file name. You must configure the procedure for it to work properly. For more details, see the code later in this appendix. Configurable options include the location of the trace file, the trace file name, and more.

The stored procedure captures the following trace events for each logout: EventClass (which will be Audit Logout), DatabaseID, NTUserName, HostName, ClientProcessID, ApplicationName, LoginName, SPID, Duration, StartTime, Reads, Writes, CPU, DBUserName, and ServerName.

Each user connection should have one logout event. If the user connection never logs out, utilization information for this user will be missed unless the user connection is forced to logout using the KILL command.

sp_ChargebackProc writes messages to the error log anytime a trace is started, stopped, or when a trace file is renamed. If the trace file name already exists when sp_ChargebackProc is run, it automatically renames the file, appending YYYYMMDD.HH.MM before creating the new trace file, and logs the event along with the associated login name.

Note: Ensure that the sp_configure option scan for startup procs is set to 1 to allow this procedure to be executed at startup. You must stop and start SQL Server for this option to be enabled.

Step Three – Analyze the Trace Files with sp_ChargebackAnalysis

Once you have generated trace files, you will want to analyze them. Chargeback analysis can be done in a number of ways, such as by application or NTUserName. sp_ChargebackAnalysis loads the trace file into SQL Server and performs the chargeback analysis. Parameters to sp_ChargebackAnalysis are @TraceID, @TraceFile, and @TraceAggegateBy.

@TraceID is used to display trace information for a given traceID. Before analysis can be done, the traceID must be stopped and removed as follows:

execute sp_trace_setstatus 1,0 -- stops traceID 1 execute sp_trace_setstatus 1,2 -- removes traceID 1

@TraceFile is required to prepare the chargeback analysis report. The trace must be stopped and removed before the trace file can be read by sp_ChargebackAnalysis (see above).
@TraceAggregateBy breaks down utilization (reads, writes, CPU) by either "database", "ntusername", or "application." The utilization report contains a breakdown of reads, writes, total IO, percentageIO, cpu, and percentageCPU.

Below are some examples of executing sp_ChargebackAnalysis with their corresponding output:

exec sp_ChargebackAnalysis NULL,'c:\MyLogoutTrace.trc','application'
exec sp_ChargebackAnalysis NULL,'c:\MyLogoutTrace.trc','ntusername'
exec sp_ChargebackAnalysis @traceFile='c:\MyLogoutTrace.trc'
,@traceAggregateby='database'

Caveats

When implementing this solution for chargeback, there are some things that you must take into account to ensure that chargeback will work for your needs:

User connections must log out (or disconnect) for the audit logout event to occur. A kill command can be used to fire the logout event. A SQL Server shutdown will not trigger the logout event.
The external trace file name is defined in sp_ChargebackProc. If the trace file name already exists when sp_ChargebackProc is run, it automatically renames the file, appending YYYYMMDD.HH.MM before creating the new trace file.
A trace file cannot be analyzed (loaded into SQL Server) by sp_ChargebackAnalysis if the file is already in use by SQL Server. For example, if a trace is writing audit logout events to a trace file, it cannot be loaded into SQL Server. Before a trace file can be loaded by SQL Server, any trace referencing it must be stopped and removed using the following statements:

execute sp_trace_setstatus 1,0 -- stops traceID 1 execute sp_trace_setstatus 1,2 -- removes traceID 1

All currently defined traces can be displayed by either of the following statements:

execute sp_ChargebackAnalysis select * from ::fn_trace_getinfo(Default)

Lightweight pooling, also known as fiber mode, is not properly captured by this procedure. An alternate method of chargeback collection is required in these cases.

Sp_ChargebackAnalysis

create proc sp_ChargebackAnalysis (
      @traceId int=NULL
      ,@traceFile nvarchar(1000)=NULL
      ,@traceAggregateBy varchar(100)=NULL)
as
-- Author:    T. Davidson, SQL Server Development Customer Advisory Team
-- Description:    SQL Server Audit Logout events are traced to a file for
chargeback utilization.
-- Notes: A trace file cannot be analyzed (e.g. loaded into SQL Server) by
sp_ChargebackAnalysis
--    if the file is already in use by SQL Server. For example, if a
trace is writing audit logout
--   events to a trace file, it cannot be loaded into SQL Server.  Before
a trace file can be
--   loaded by SQL Server, any trace referencing it must be stopped and
removed using:
--        a.   execute sp_trace_setstatus 1,0   -- stop traceID 1
--        b.   execute sp_trace_setstatus 1,2   -- remove traceID 1
set nocount on
-- if no traceId provided, display all traces
declare @totIO Numeric(20,1)
   ,@totCPU Numeric(20,1)
   ,@totReads Numeric(20,1)
   ,@totWrites Numeric(20,1)
   ,@trc_cnt int
if @traceId is NULL and @traceFile is NULL
begin
   print 'please provide either @traceID or @traceFile parameters'
   print 'example: execute sp_ChargebackAnalysis
NULL,''c:\logout.trc'',''database'' '
   select *
   into #traceDefs
   from ::fn_trace_getinfo(default)
   select @trc_cnt = count(*)
   from #traceDefs
   where Property=1
   print convert(varchar(10),@trc_cnt)+' traces are currently defined
for this server: '
   if @trc_cnt > 0
   begin
      select traceId,traceFile=convert(nvarchar(1000),Value)
      from #traceDefs
      where Property=2
      print 'you must stop the trace before you can load the
traceFile'
      print 'Example of stopping a traceID: execute
sp_trace_setstatus @traceID,0'
      print 'then you must remove the trace before you can load the
traceFile'
      print 'Example of removing a traceID: execute
sp_trace_setstatus @traceID,2'
   end
   return 200
end
if lower(@traceAggregateBy) not in ('database','ntusername','application')
begin
   print 'chargeback can be aggregated by either: database,NTUserName,
or application'
   print 'example: exec sp_ChargebackAnalysis
NULL,''c:\data\logout.trc'',''NTUserName'' '
   return 100
end
if @traceFile is NULL and @traceId > 0
begin
   select *
   into #traceDef
   from ::fn_trace_getinfo(@traceId)
   select TraceId,TraceFile=convert(nvarchar(1000),Value)
   from #traceDef
   where TraceId=@traceId
   and Property=2
   print 'you must stop the trace before you can load the traceFile'
   print 'Example of stopping a traceID: execute sp_trace_setstatus
@traceID,0'
   print 'then you must remove the trace before you can load the
traceFile'
   print 'Example of removing a traceID: execute sp_trace_setstatus
@traceID,2'
   return -99
end
if @traceFile is not null
   begin
   --you may want to create a permanent table in another database
   --drop table traceFile
   print '**** Chargeback Analysis by '+@traceAggregateBy + ' ****'
   SELECT * INTO #traceFile
   FROM ::fn_trace_gettable(@traceFile, default)
   select @totReads = 1+sum(reads)
      ,@totWrites = 1+sum(writes)
      ,@totIO=1+sum(reads+writes)
      ,@totCPU=1+sum(cpu)
   from #traceFile
   if lower(@traceAggregateBy) = 'database'
   select 'breakdown by database'=substring(db_name(DatabaseId),1,30)
      ,percentIO=cast(100*sum(reads+writes)/@totIO as numeric(20,1))
      ,totIO=sum(reads+writes)
      ,reads=sum(reads)
      ,writes=sum(writes)
      ,percentCPU=cast(100*sum(cpu)/@totCPU as numeric(20,1))
      ,cpu=sum(cpu)
   from #traceFile
   group by DatabaseId
   order by DatabaseId
   if lower(@traceAggregateBy) = 'ntusername'
   select 'breakdown by NTUserName'=substring(NTUserName,1,30)
      ,percentIO=cast(100*sum(reads+writes)/@totIO as numeric(20,1))
      ,totIO=sum(reads+writes)
      ,reads=sum(reads)
      ,writes=sum(writes)
      ,percentCPU=cast(100*sum(cpu)/@totCPU as numeric(20,1))
      ,cpu=sum(cpu)
   from #traceFile
   group by NTUserName
   order by NTUserName
   if lower(@traceAggregateBy) = 'application'
   select 'breakdown by application'=substring(ApplicationName,1,30)
      ,percentIO=cast(100*sum(reads+writes)/@totIO as numeric(20,1))
      ,totIO=sum(reads+writes)
      ,reads=sum(reads)
      ,writes=sum(writes)
      ,percentCPU=cast(100*sum(cpu)/@totCPU as numeric(20,1))
      ,cpu=sum(cpu)
   from #traceFile
   group by ApplicationName
   order by ApplicationName
end
go

Sp_ChargebackProc

/************************************************************/
/* Created by: SQL Profiler
*/
/* Date: 12/30/2002  02:20:51 PM
*/
/* Revision 1 - TD:  Rollover file option added to sp_trace_create
*/
/* Revision 2 - TD:  must run Sp_procoption to make proc auto execute */
/*                    during server startup
*/
/* Revision 3 - TD:  add automatic trace stop & removal
*/
/* Revision 4 - TD:  add automatic trace file rename -
*/
/*      appending date (yyyymmdd) & time (hh.mm)*/
/* Revision 5 - TD:  add raiserror log messages
*/
/************************************************************/
Create proc dbo.sp_ChargebackProc
as
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @TraceLive int
declare @user nvarchar(128)
set @maxfilesize = 1
-- Please replace the text c:\MyLogoutTrace,
-- with an appropriate filename
-- prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share
-- option value of 2 means rollover files with _n appended to the filename
--   e.g. c:\MyLogoutTrace_1 will be created when trace file >
@maxfilesize and so on
declare @traceFile nvarchar(128),@traceFilePath nvarchar(128),
@traceFileFullName nvarchar(128)
declare @appendname varchar(20),@cmd varchar(200)
select @user = loginame
from master..sysprocesses
where spid = @@spid
select @traceFilePath = N'c:\', @traceFile= N'MyLogoutTrace'
select @traceFileFullName = @traceFilePath+@traceFile
-- check to see if trace file is active.  if active, must be stopped,
removed, and renamed
select    @traceID=traceid
   ,@traceLive=count(*)   -- if trace file not active, returns 0
from ::fn_trace_getinfo(default)
where value = @traceFileFullName
and property = 2
group by traceid
if @traceLive > 0  -- > 0 means trace file is in use. stop and remove
trace      
begin
   exec @rc=sp_trace_setstatus @traceId,0   -- stop trace
   if (@rc=0) raiserror ('Trace %d to %s stopped by
%s',10,1,@TraceID,@TraceFileFullName,@user) with log
   else raiserror ('Chargeback trace %d stop attempt
failed',10,1,@TraceID) with log
   exec @rc=sp_trace_setstatus @traceID,2   -- remove trace
   if (@rc=0) raiserror ('Chargeback trace %d removed by
%s',10,1,@TraceID,@user) with log
   else raiserror ('Chargeback trace %d removal failed',10,1,@TraceID)
with log
end
--    rename previous trace file, appending today's date and time
(yyyymmdd.hh.mm)
select @appendname =convert(varchar(20),getdate(),112) + '.' +
onvert(varchar(2),getdate(),14) + '.' +
substring(convert(varchar(20),getdate(),14),4,2)
set @cmd = 'rename ' + @traceFilePath + @traceFile + '.trc ' + @traceFile
+ @appendname + '.trc'
exec @rc=xp_cmdshell @cmd
if (@rc = 0) raiserror ('Trace file %s renamed to
%s%s%s',10,1,@traceFileFullName,@traceFilePath,@TraceFile,@appendname)
with log
--   create new chargeback trace
exec @rc = sp_trace_create @TraceID output, 2, @traceFileFullName,
@maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 40, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
-- filter out application named 'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace  id for future references
raiserror ('Chargeback trace %d to %s started by
%s',10,1,@TraceID,@TraceFileFullName,@user) with log
goto finish
error:
-- if the traceFile already exists, this will fail with ErrorCode=12
select ErrorCode=@rc
finish:
go

Appendix C: System Profile Worksheets

You may not require all of the worksheets presented in this section, because you may not be using all of these technologies or you may have already documented them.

Business Worksheet

Complete the two sections of the Business Worksheet to help with the non-technical analysis — the business decision-making aspects of consolidation that are discussed in "Consolidation Basics" earlier in this paper.

Current Annual Database Costs

Attribute	Value
Total Number of DBAs Currently Employed (salaried or contractor)
Total Number of Database Servers
Total Number of Databases
Ratio of Databases to DBA
Average Hourly Cost Per DBA
Hours Worked Per Year per DBA

Attribute	Hours Spent By DBAs	Cost (where applicable, multiply the hourly rate by the Hours Spent By DBAs column)
Current Annual Cost Of Licensing for All Database Systems (or those being consolidated)
Number of Hours Spent Performing Backups
Number of Hours Spent on Training/Readiness
Number of Hours Spent on Troubleshooting
Number of Hours Spent on Monitoring
Number of Hours Spent on Performance Tuning
Number of Hours Spent on Security
Number of Hours Spent on DB Planning and Design (including schema)
Number of Hours Spent on Disaster Recovery
Number of Hours Spent on Other DBA Tasks
TOTAL COSTS

Proposed Database Cost Benefits

Attribute	Current Costs	Proposed Costs	Reason for Savings/Benefits of Consolidation/Technology Enabler
Annual Cost Of Licensing for All Database Systems (or those being consolidated)
Number of Hours Spent Performing Backups
Number of Hours Spent on Training/Readiness
Number of Hours Spent on Troubleshooting
Number of Hours Spent on Monitoring
Number of Hours Spent on Performance Tuning
Number of Hours Spent on Security
Number of Hours Spent on DB Planning and Design (including schema)
Number of Hours Spent on Disaster Recovery
Number of Hours Spent on Other DBA Tasks
TOTAL COSTS

System Information Worksheet

Complete all sections of the System Information Worksheet to capture the basic technical information about a particular system, which is discussed in "Technical Tasks and Considerations" earlier in this paper.

Basic System Information

Complete the Basic System Information section for each set of IP attributes if this system has more than two IP addresses.

Attribute	Value
Server Name
Domain Name
# of Processors
Processor Class and Type
Total Memory and Memory Type (i.e. RDRAM, SDRAM, etc.)
Operating System Version (including service pack version and if possible, build number)
IP Address 1
NIC Manufacturer
NIC Model Number
NIC Driver Version
IP 1 Subnet
IP 1 – More Information	Network Name: Fill out the following if part of a cluster: Client access only (public network) All communications (private and public networks) Internal cluster communications only (private network)
Speed of NIC for IP 1	10 100 Other Value:
IP Address 2
IP 2 Subnet
IP 2 – More Information	Network Name: Fill out the following if part of a cluster: Client access only (public network) All communications (private and public networks) Internal cluster communications only (private network)
Speed of NIC for IP 2	10 100 Other Value:
DNS Information
WINS Information
Applications Currently Configured and Running on System
Pagefile Location
Pagefile Size
Network Type (T1, 56k, etc.)
Advanced Security	Kerberos SSL IPSEC
Is this server a domain controller?	Yes No If Yes: PDC BDC
If PDC/BDC, Total Number of Groups
If PDC/BDC, Total Number of Users
AWE/PAE/3GB Configured?
Date Configured
Date Placed Into Service

Server Cluster Information

Complete the Server Cluster Information section if this system is part of a server cluster.

Attribute	Value
Cluster Name
Cluster IP Address
Cluster IP Subnet
Total Number of Nodes
Cluster Domain Administrator Account
Cluster Domain Administrator Password
MS DTC Network Name (NT 4)
MS DTC IP Address (NT 4)
MS DTC Subnet (NT 4)

Network Load Balancing Cluster Information

Complete the Network Load Balancing Cluster Information section if this system is part of a network load balancing (NLB) cluster.

Attribute	Value
Cluster Name
Cluster IP Address
Cluster IP Subnet
Total Number of Servers
NLB Settings

Disk Configuration Worksheet

Complete all sections of the Disk Configuration Worksheet to document the disk configuration for a particular server, whether all disks are internal or external to the system.

Basic Disk Information

Attribute	Value
System Name
Number of Internal Drives
Size of Each Physical Drive
Speed of Physical Drives
Type of Disks	IDE SCSI Mixed

SQL Server Disk Information

Attribute	Value
Are Files/Filegroups used?	Yes No
Detail File/Filegroup Configuration per Database

Are Files/Filegroups used?

Yes

Detail File/Filegroup Configuration per Database

External Array Information

Copy and complete the External Array Information section for each external array that exists on this system.

Attribute	Value
Type of External Array (SAN/DAS)
Manufacturer
Model Number
Driver Version
Type of Disk Subsystem (SCSI/Fibre)
Total Number of Physical Drives
Size of Each Physical Drive
Speed of Physical Drives
Total Number of LUNs/Partitions/Volumes Used
Do Other Systems and/or Applications Share the Array?	Yes No
Disk Cache Size
Disk Cache Setting (i.e. read/write ratio)

Detailed Disk Configuration Information

Complete the table for each disk drive on the system. In the columns of the following table, Logical Disk indicates the drive letter that the operating system uses. Partition/LUN/Volume Label or Number is the number or label of a particular LUN on the disk subsystem. A LUN may contain more than one logical disk. Number of Physical Drives Used for LUN is the number of drives in the array that are used to create the LUN. RAID Level is the type of RAID used on the LUN. Total Space is the total amount of drive space available for use by the logical disk. Current Free Space is the total amount of drive space used by all files on the logical disk. Purpose is for detailing the specific use of a particular logical drive.

System Name:

	C:\	D:\	E:\	F:\	G:\	H:\
Logical Disk
Partition/LUN/Volume Label or Number (if on a SAN, DAS)
Number of Physical Drives Used for LUN
RAID Level (N/A if not used)
Total Space
Current Free Space
Files and Filegroups Used
Purpose

	I:\	J:\	K:\	L:\	M:\	N:\
Logical Disk
Partition/LUN/Volume Label or Number (if on a SAN, DAS)
Number of Physical Drives Used for LUN
RAID Level (N/A if not used)
Total Space
Current Free Space
Files and Filegroups Used
Purpose

	O:\	P:\	Q:\	R:\	S:\	T:\
Logical Disk
Partition/LUN/Volume Label or Number (if on a SAN, DAS)
Number of Physical Drives Used for LUN
RAID Level (N/A if not used)
Total Space
Current Free Space
Files and Filegroups Used
Purpose

	U:\	V:\	W:\	X:\	Y:\	Z:\
Logical Disk
Partition/LUN/Volume Label or Number (if on a SAN, DAS)
Number of Physical Drives Used for LUN
RAID Level (N/A if not used)
Total Space
Current Free Space
Files and Filegroups Used
Purpose

SQL Server Information Worksheet

Complete the relevant sections of the SQL Server Information Worksheet to document the SQL Server configuration for a particular server.

Basic SQL Server Information

Attribute	Value
SQL Server version
SQL Server name/type of instance (if SQL Server 2000)	Name: Instance Type: Default Named If Named, Name of Instance:
Instance # (if SQL Server 2000 and more than one instance on the server)
Location of SQL Server binaries
SQL Server administrator account (with domain name)
SQL Server administrator password
SQL Server Agent administrator account (with domain name)
SQL Server Agent administrator password
Authentication mode	Windows Mixed If Mixed, SA password:
Licensing mode	Per Seat Per Processor Value:
SQL services required	SQL Server Agent SQL Server Fulltext
Memory allocation	Dynamic Static Amount:
Number of processors used

SQL Server Failover Cluster Information

Complete the SQL Server Failover Cluster Information section if failover clustering is configured.

Parameter	Value
Virtual server name (for all versions)
IP Address 1
Network to use (IP 1)
IP 2 Subnet
Network to use (IP 2)
Data/Log disks allocated (one used during setup; others added as dependencies after the install)
Cluster definition/preferred owner order (nodes part of the virtual server definition)
Disk dependencies

SQL Server Log Shipping Configuration Information

Complete the SQL Server Log Shipping Configuration Information section if the built-in log shipping is configured. Customize the worksheet for your own version of log shipping.

Parameter	Value
Back up database as part of the maintenance plan
Primary server name
Secondary server name(s)
Database to log ship (on primary)
Directory to store the backup file (should be a valid UNC name)
Create a subdirectory under the UNC for each database
Delete transaction-log files older than a certain time period
Backup file extension (default is TRN)
Network share name for backup directory
Transaction-log destination directory (should be valid UNC on secondary server)
Create and initialize new database
Database load state
Terminate user connections in database
Allow database to assume primary role
Perform a full backup (if not using an existing DB)
Use most recent backup file (if not using an existing DB)
Transaction-log backup schedule (default is every 15 minutes)
Copy/load frequency (default is 15 minutes)
Load delay (default is 0 minutes)
File retention period (default is 24 hours)
Backup alert threshold
Out of sync alert threshold
Log Shipping Monitor server
Authentication mode for Monitor server
Generate a report
Limit number of history entries in the sysdbmaintplan_history table

SQL Server Replication Configuration Information

Complete the SQL Server Replication Configuration Information section if replication is configured. Copy the information for each type of replication.

Parameter	Value
Is this server a publisher, distributor, a subscriber, or a combination?
Type of replication	Transactional Merge Snapshot
Publications and publication type (push, pull)
Frequency

Performance Information Worksheet

The Performance Information Worksheet will help you record performance-related statistics.

General System Performance Information

Parameter	Value
Overall processor utilization
Processor utilization per processor
Total memory used
Number and type of processors
Amount and type of RAM

Network Performance Information

Include information gathered before and after consolidation for every database included.

Parameter	Value
Network Interface: Packets/sec
Network Interface: Packets Sent/sec
Network Interface: Packets Received/sec
Network Interface: Output Queue Length
Number and Type of NICs

Disk Performance Information

Include a diagram that maps physical disks to logical disks, along with the layout of physical data files, both before consolidation and after consolidation.

Parameter	Value
Physical Disk: Avg. Read Queue Length
Physical Disk: Avg. Write Queue Length*
Physical Disk: Disk Reads/sec
Physical Disk: Disk Writes/sec

SQL Server Performance Information

Parameter	Value
Minimum amount of memory used
Maximum amount of memory used
Minimum number of concurrent connections
Maximum number of concurrent connections
Processor utilization
Database growth (detail per database, rate of growth and how much it grows, i.e. 10% per month)

Other Application Performance Information

Parameter	Value
Minimum amount of memory used
Maximum amount of memory used
Minimum number of concurrent connections
Maximum number of concurrent connections
Processor utilization
Database growth (detail per database, rate of growth and how much it grows, i.e. 10% per month)

Performance of Selected Queries

For each system, define a set of queries crucial to performance of the system. Time these on the original system, and then time them on the consolidated system. This gives you a context with which to examine the statistical data collected above. Note that it is important to run these queries from a script or query analyzer, rather than from the application, in order to distinguish between database server performance and overall application performance.

One Entry Per Query or Script	Value
Query or procedure name Time elapsed Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run
Query or procedure name Length of time it took to run Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run
Query or procedure name Length of time it took to run Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run

Query or procedure name

Time elapsed

Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run

Query or procedure name

Length of time it took to run

Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run

Query or procedure name

Length of time it took to run

Total SQL Server transactions/sec (or batch requests/sec) on the entire server at time query was run

Planning for Consolidation with Microsoft SQL Server 2000

On This Page

Introduction

Who Should Read This Document?

Consolidation Basics

Forms of Consolidation

Drivers for Consolidation

The Consolidation Process

Phase One: Envisioning

Phase Two: Planning

Phase Three: Development

Phase Four: Deployment and Stabilization

Technical Tasks

Envisioning Considerations

Planning Considerations

Tools for SQL Server Consolidation

Conclusion

Appendix A: Technical Resources

Consolidation Links

General SQL Server and Windows

Useful Microsoft Support Knowledge Base Articles

Appendix B: Implementing Chargeback Using SQL Server

Step One – Install the Stored Procedures

Step Two – Configure sp_ChargebackProc

Step Three – Analyze the Trace Files with sp_ChargebackAnalysis

Caveats

Sp_ChargebackAnalysis

Sp_ChargebackProc

Appendix C: System Profile Worksheets

Business Worksheet

System Information Worksheet

Disk Configuration Worksheet

SQL Server Information Worksheet

Performance Information Worksheet

Additional resources