Chapter 3 - Planning Phase

On This Page

Introduction and Goals Introduction and Goals
Complete a Detailed Assessment of the Existing Environment Complete a Detailed Assessment of the Existing Environment
Develop the Solution Design and Architecture Develop the Solution Design and Architecture
Incorporate Design Considerations Incorporate Design Considerations
Validate the Technology Validate the Technology
Develop the Project Plans Develop the Project Plans
Create the Project Schedules Create the Project Schedules
Set Up the Development and Test Environments Set Up the Development and Test Environments

Introduction and Goals

In the Planning Phase, the team defines the solution in detail — what to build, how to build it, who will build it, and when it will be built. During this phase, the initial vision and solution concept defined in the Envisioning Phase is translated into practical implementation designs and plans for how to achieve it. Team members draw upon their expertise to create individual plans and designs in all areas of the project, ranging from security to budget to deployment. Likewise, individual team schedules and associated dependencies are identified. These plans and schedules are rolled into a master project plan by the Program Management Role.

The phase concludes when the project team agrees that the plans are sufficiently well-defined to proceed with development, and the team, business sponsor, and key stakeholders approve the functional specification and the master project plan and schedule, usually at a milestone meeting. The formal conclusion is marked by the second major project milestone, Project Plans Approved.

The key deliverables for the project team for the Planning Phase are:

  • Functional specification. The functional specification is the virtual repository of project and design related artifacts that are created in the Planning Phase. The artifacts are primarily a result of design activities during the conceptual design, logical design, and physical design processes of the Planning Phase. The artifacts can include models such as case diagrams, usage scenarios, feature list, user interface screen shots, database design, and so on.

    The key goals of the functional specification are to consolidate a common understanding of the business, design, and user requirements; break down the problem and modularize the solution logically; provide a framework to plan, schedule, and build the solution; and to serve as a contract between the team and the customer and stakeholders.

  • Master project plan. The master project plan is a collection of individual plans that address tasks performed by each of the six team roles to achieve the functionality defined in the functional specification. The master project plan documents the strategies the team roles will use to complete their work. The solution concept that the team developed in the Envisioning Phase provides high-level approaches that are developed into detailed plans in the Planning Phase. See Chapter 2, "Envisioning Phase," for the discussion of team roles.

  • Master project schedule. The individual team schedules apply a time frame to the master plan. The master project schedule synchronizes project schedules across the teams. Aggregating the individual schedules gives the team an overall view of the project schedule and is the first step toward determining a fixed ship date.

  • Updated master risk assessment document. The master risk assessment document that was developed during the Envisioning Phase is reviewed and updated regularly during the Planning Phase.

All of these deliverables are living documents, evolving as the project progresses. Though these documents can be modified, modifications must follow the change management policies set up for the project during the Envisioning Phase.

Table 3.1 lists the key activities that the team undertakes in the Planning Phase. The results of these activities are documented in this phase's deliverables.

Table 3.1: Major Tasks, Deliverables, Owners, and Job Aids


Deliverable where results are documented

Primary owner

Job Aids provided in this solution

Complete detailed assessment of the current environment

Functional specification

Program Management

Assessing the Environment Questionnaire

Develop the solution design and architecture

Functional specification

Program Management


Validate the technology

Functional specification



Develop the project plans

Master project plan

Team leads for all roles; plans consolidated by Program Manager

* Development Plan

* Test Plan

* Deployment Plan

* Pilot Plan

Create the project schedules

Master project schedule

Team leads for all roles


Set up the development and test environments


Development and Test


Reassess current project risks

Risk assessment document

Program Management

Migration Risk Exposure Rating Form

Complete a Detailed Assessment of the Existing Environment

To begin the migration process, an accurate and detailed assessment of the existing environment of your organization is essential. A high level assessment is carried out in the Envisioning Phase. The detailed assessment in the Planning Phase is a follow up activity.

The information collected by this assessment should be comprehensive enough to plan the specific details of the solution migration. For instance, a detailed assessment of the application will provide definitive information on whether existing test plans can be reused to test the solution. Without this detailed information about the existing environment, there is no way to accurately estimate the development, test, or deployment plans.

The key elements of the environment that need to be assessed in detail include:

  • Application

  • Database

  • Application infrastructure

Refer to the Assessing the Environment Questionnaire job aid to help you assess the main areas of the environment.


A detailed assessment should be performed for any application that is affected by the migration. A database application migration project assessment should not be limited to assessing the application alone; it should also include database connectivity. Assessment activities related to the database should include the following:

  • Note all database interfaces used by the applications and their types (OCI, ODBC, and so on).

  • Note the relationship between applications and databases. For example, note if a database supports multiple applications, or an application connects to multiple databases.

  • Identify all the database, schemas, and schema objects that the application interacts with.

  • Note the protocol that is used by the application to communicate with the database. Oracle supports TCP/IP, TCP/IP with SSL, and named pipes.

  • Identify all encryption methods employed by the applications with the databases.

  • Identify the security features of the database used by the applications, such as authentication, password features, single sign on, and so on.

  • Determine if there are any other network-related security authentication methods used with the current application. Common Oracle authentication methods include Kerberos, Radius, DES Algorithm, RSA, and Cybersafe.

  • Note the data sources in use, such as ODBC, OLE DB, and ADO.

In addition to questions related to database connectivity, here are some general application assessment questions that you may need to ask.

  • Identify the programming language used for the main application as well as any supplementary programs.

  • Identify the platform support (hardware and operating system), architecture, and component interfaces of the existing application.

  • Locate any existing information about the application. Is the source code available? Other items that may help plan for migration include test scripts, documentation, process flow charts, and use cases.

  • Record any dependencies and characteristics, such as third-party libraries, UNIX utilities, or path names. Also, determine if the UNIX application uses X Windows, Motif, or xrt libraries.

  • Determine the security architecture of the application. This information is important because features and implementations are different between Microsoft Windows® and UNIX platforms.

  • Record any encryption protocols currently being used. SSL and PGP are two common encryption methods.

  • Verify if the application uses daemon processes. This information is required because UNIX applications can be started in the background and will continue to run even after a user logs off the system. Using application services in Windows 2003 provides similar functionality.

  • Check for any file system dependencies. This information is required because UNIX and Windows use different file access methods.

For more information on assessing the application before migrating from UNIX to Windows, refer to the UNIX Application Migration Guide, available at


To migrate an Oracle database to Microsoft SQL Server™ successfully, the project team must assess the technical details of the current system.

The following assessment activities should be performed on the existing Oracle database:

  • Identify and record the database name and all configuration information.

  • Determine the type of database being used. Common database types include Decision Support System (DSS), Online Transaction Processing (OLTP), or a combination of the two.

  • Locate any existing design documents, such as data models, database creation scripts, or data definition language (DDL) specifications.

  • Record detailed information on existing database structures, including tables, views, stored procedures, indexes, users, and roles.

  • Identify and record the current location of database files, such as Storage Area Network (SAN), Network Attached Storage (NAS), or local attached storage.

  • Identify and record the disk space consumed by the existing database files. Also, obtain estimates of growth rate of the database files.

  • Evaluate the existing backup solution, including the frequency and type of backup (full, archive log, and so on).

  • Assess the database configuration information, including the sort order and character set.

  • Examine the frequency of data dumps and log dumps.

  • Determine the dependencies of the database on other databases or platforms. Common examples include distributed databases, replication, data marts, and data warehouses.

  • Identify and analyze the server side scripts in use for activities such as data loads, batch processing, reporting, and administration.

The Migration Analyzer tool, which is a part of the SQL Server Migration Assistant (SSMA), analyzes the source Oracle databases and produces vital statistics on the size and complexity of the migration. This tool can be downloaded from The beta version of this tool is available as of the date of publishing this solution. Version 1.0 of the tool is slated to be available in June 2005

Application Infrastructure

The application infrastructure can be broadly classified as server-related and network-related. Server-related infrastructure relates directly to the hardware and software on the server. Server-related items include operating systems, processors, memory, storage, tape drives, and file systems. Examples of network-related infrastructure include routers, firewalls, switches, domain name servers (DNS), and virtual private networks (VPN).

Security infrastructure is classified under both server-related and network-related infrastructure. Common items within the server security infrastructure are Network Information Services (NIS and NIS+) and Lightweight Directory Access Protocol (LDAP).

The following assessment activities should be performed for server-related infrastructure:

  • Check the capacity and performance of the servers and server clusters of the existing environment. Also verify if the clusters are situated in single or multiple locations.

  • The hardware configuration details need to be gathered for any dependencies that might affect the migration, including architecture (32-bit versus 64-bit, hyper-threading, hybrid), processor speed, number of CPUs, and the number of available sockets for upgrades.

  • Record any pertinent information about the RAM installed in the server, including size, configuration, and number of open slots available for upgrades.

  • Verify disk storage needs for database files and the disk space consumed for the existing database files. Identify the storage requirements, such as:

    • Amount of space needed for current and growth factors

    • Spindle RPM (disks: 15000 RPM versus 10000 RPM; SAN: 1 gbps versus 2 gbps)

    • Throughput (theoretical I/O, current I/O, anticipated I/O) for each element of the storage system

    • Disk size(s)

    • RAID levels

    • Storage Architecture (SAN, NAS, external storage, internal storage)

The following assessment activities should be performed for network-related infrastructure:

  • Identify the existing network protocols and network bandwidth.

  • Examine the routers, switches, and firewalls for the existing solution. Verify the configuration and location of firewalls and proxies.

  • Assess the network topology that exists in the organization.

  • Information about the network infrastructure needs to be identified, including the logical organization of the network, name and address resolution methods, and network naming conventions.

  • Analyze the network sites and the bandwidth between the sites. This information can be used to plan optimum strategies and schedules for installation and deployment.

  • Assess the trust relationships and policy restrictions of the existing solution.

Develop the Solution Design and Architecture

The Planning Phase of the MSF Process Model includes three design processes: conceptual, logical, and physical. These three processes are not performed in parallel. Instead, their starting and ending points are staggered. These processes are dependent on each other. The logical design is dependent on the conceptual design, and the physical design is dependent on the logical design. Table 3.2 compares the three design processes.

Table 3.2: Comparing Conceptual, Logical, and Physical Design

Type of Design



Conceptual Design

Views the problem from the perspective of the user and business

Defines the problem and solution in terms of usage scenarios and refined requirements

Logical Design

Views the solution from the perspective of the design/architecture team

Defines the solution as a logical set of co-operating objects and services

Physical Design

Views the solution from the perspective of the developers

Defines the solution components and technologies

In a migration project it is quite likely that you already have detailed design documents for the existing system. These documents can provide an excellent starting point for your design process. Enhancements can be made to account for the additional requirements and functionality for the new system.

Build the Conceptual Design

The first design process in the Planning Phase is conceptual design. Once completed, the conceptual design is used in the creation of the logical and physical design processes.

Conceptual design is the process of gathering, analyzing, and prioritizing business and user perspectives of the problem and the solution, and then creating a high level representation of the solution in the form of detailed requirements. The team gathers high level requirements during the Envisioning Phase and documents them in the form of use cases and usage scenarios. While producing the conceptual design, the team refines these requirements.

Develop Detailed Requirements

A conceptual design records the system requirements in terms of the following requirements:

  • Business requirements

  • User requirements

  • System requirements

  • Operational requirements

Each of these is described under the following headings

Business Requirements

Business requirements describe the organization's needs and expectations for the solution. These requirements exist at the managerial decision-making level and provide the context in which the solution will operate.

Some example business requirements include:

  • The database migration from UNIX to Windows should be completed in the given time frame.

  • The existing business rules and policies should be maintained on the new system.

  • Effort required for migration should justify the cost incurred.

  • The existing level of performance and functionality should be maintained after the migration, including disaster recovery, availability, and scalability.

User Requirements

User requirements define the nonfunctional aspect of the user's interaction with the solution. They help you to determine the user interface and performance expectations of the solution in terms of its reliability, availability, and accessibility. In addition, the user requirements help you identify the training that the users will need to effectively use the solution.

Some example user requirements include:

  • The user should be able to use the same or similar interface as the application running on the UNIX system to minimize user retraining.

  • The time taken to retrieve information from the system should not increase.

  • There should be comprehensive user manuals for the new system. Training manuals should also be considered for new users.

System Requirements

System requirements specify the detailed transactions and their sequence in the system. The requirements help the project team define how the new solution will interact with the existing systems. Some example system requirements include:

  • After the migration, the new system should continue to interoperate with applications as it did on the UNIX platform.

  • The system should not require a user credential other than the credentials passed from logging onto the corporate network (single sign on).

  • The system should support internal and remote users.

Operational Requirements

Operational requirements describe what the solution must deliver to maximize operability and improve service delivery with reduced downtime and risks. Some example operational requirements include:

  • The system should allow administrators to perform their tasks both on-site and remotely.

  • The system should be able to recover from critical failure without major impact and within service levels.

  • The system should include a process for managing the total throughput and response time within the stated service levels.

  • The system should be able to handle varying levels of user load and transactions. In addition, the site should be designed so that it can be modified and upgraded without affecting availability and performance.

  • Large clients should be separated with respect to database infrastructure to minimize the impact on maintenance and availability.

Build the Logical Design

The logical design is the second step in creating the solution design. After identifying the business and user needs in the conceptual design, the logical design defines how the different parts of the solution will coordinate and work together. The logical design defines the parts of the system, provides a framework to hold all the parts of the system together, and illustrates how the system interacts with the users and other systems.

While creating the logical design, the team takes into account all the business, user, operational and system requirements that state the need for security, auditing, logging, scalability, state management, error handling, licensing, globalization, application architecture, integration with other systems, and so on.

There are several ways to represent the logical design of a system. A commonly used visual modeling language is Unified Modeling Language (UML). A detailed discussion of UML is out of the scope of this solution. For more information on UML, refer to The Unified Modeling Language User Guide (Booch, Jacobson, and Rumbaugh 1999) and Use Case Driven Object Modeling with UML: A Practical Approach (Rosenberg and Scott 1999).

Figure 3.1 shows a sample logical design for a system using the Logical Object Model, as defined by UML. The sample design represents the system in terms of:

  • Objects. These are people or things in the system. "Order" is an example of an object in Figure 3.1.

  • Services. These are the behavior or functionality associated with any object. For example, "Track Status()" is a service provided by the "Order" object in Figure 3.1.

  • Attributes. These are characteristics or properties of the objects. For example, "Order ID" and "Ship Date" are attributes of the "Order" object in Figure 3.1

  • Relationships. These represent ways in which objects are linked with each other. For example, the object Order and the object Order line item are shown to have an associative relationship in Figure 3.1 by a thick line. This shows that they are related and a change to one affects the other.

The objects, services, attributes and relationships can be obtained from the detailed usage scenarios that were developed in the conceptual design phase.

The logical design represents a logical view of the complete system. Individual teams, such as those for User Interface (UI) development, the database development, and the application development can take this representation and start to build the detailed designs for their domain when making the physical design.

Figure 3.1 Creating a logical design for the application

Figure 3.1 Creating a logical design for the application

Logical Design Considerations

The logical design for an Oracle database migration project must take into account the changes that will occur as a result of migration from Oracle to SQL Server. The following items should be considered:

  • Are there any dependencies between client applications and database connectivity?

  • Do UNIX scripts support the client applications?

  • How are communications between the application and database secured internally and over the internet?

  • How is data distributed across multiple databases and multiple servers?

Examining the conceptual design of the client application will provide an understanding of how it operates and is structured. This information is used to create the physical design of the solution. Two additional design studies that may assist in creating the physical design are discussed in the following section.

High Level User Interface and Database Design

Using the objects, services, attributes, and relationships identified in the logical design of the system, the team might decide to create a high-level user interface and database design.

The list of objects and services gives the team an idea about the kind of functionality expected by the users. The team can use this information to design user interface elements such as buttons, text fields, and menu items.

Similarly, the object and attribute information of the logical design can be used to develop an initial database design equivalent to the Oracle database being migrated.

Build the Physical Design

Physical design is the process of describing the components, services, and technologies of the solution from the perspective of the development team. The goal of the design is to provide clarity and detail for each of the development teams to develop their components, be it the user interface, business logic, database, or infrastructure.

The physical design should include:

  • Class definition of applications.

  • Database schema for the solution.

  • Baseline deployment model that provides:

    • The network topology, showing hardware locations and interconnections.

    • The data and component technology, which indicates the locations of the solution components, services, and data storage in relation to the network topology.

  • Component specifications that include internal structure of components and component interfaces.

  • Programming models that identify implementation guidelines for threading, error handling, security, and code documentation.

A detailed discussion of physical design is out of scope of this solution. For detailed guidance on designing the data layer, designing the presentation layer, and designing the security specifications, refer to chapters 7 through 9 of Analyzing Requirements and Defining Microsoft .NET Solution Architectures (Microsoft Press 2003).

The physical design diagram allows you to see how all the components connect together. For completeness, it may include infrastructure elements outside of the scope of the project such as firewalls or network connections that are not directly related to the solution. It will help prove the security of the system, and may show potential throughput bottlenecks, or highlight single points of failure.

Figure 3.2 shows a sample physical design diagram. Your design considerations will require you to modify this example.

Figure 3.2 Creating a physical design for the application and database.

Figure 3.2 Creating a physical design for the application and database.

The following sections will help you plan for a migration by identifying the various components of the physical design and outlining the typical design considerations that are part of a typical migration.

Incorporate Design Considerations

While migrating, you may want to utilize some of the advanced functions that are incorporated into SQL Server and Windows Server™ 2003. Some references may exist for Windows® 2000 but the information also applies to Windows Server 2003. For more information, refer to the following resources:

Hardware Design Considerations

In addition to the advanced features available in SQL Server, there are many software tools available to assist in ensuring that the correct hardware is acquired. Some of these tools are listed here:

Validate the Technology

Parallel to the design process, the team will often validate the technologies being used in the solution. During technology validation, the team evaluates the products or technologies to ensure that they work according to specifications provided by their vendor and that they will meet the business needs for the specific solution scenario.

SQL Server Editions and Features

While Oracle is available in Personal, Standard, and Enterprise Editions, SQL Server 2000 is available in the following editions:

  • SQL Server 2000 Enterprise Edition (64-bit)

  • SQL Server 2000 Enterprise Edition

  • SQL Server 2000 Standard Edition

  • SQL Server 2000 Developer Edition

All editions of Windows® XP and Windows Server 2003 include support for SQL Server. Support exists for many earlier versions of Windows, as well.

As with Oracle, each edition is distinguished primarily by the availability of certain features, progressing from Personal Edition to Enterprise Edition. SQL Server also offers several different editions. Each edition provides functionality based on scale as shown in Table 3.3.

Table 3.3: SQL Server 2000 Editions Compared

OS Requirements and Scalability

Enterprise (64-bit) Edition

Enterprise Edition

Standard Edition

Operating System

* Windows Server 2003 Enterprise Edition

* Windows Server 2003 Datacenter Edition

* Windows Server 2003 Standard Edition

* Windows Server 2003 Enterprise Edition

* Windows Server 2003 Datacenter Edition

* Windows Server 2003 Standard Edition

* Windows Server 2003 Enterprise Edition

* Windows Server 2003 Datacenter Edition


* Up to 64 processors

* Up to 512 GB of memory

* Maximum database size of 1,048,516 TB

* Up to 32 processors

* Up to 64 GB of memory

* Maximum database size of 1,048,516 TB

* Up to 4 processors

* Up to 2 GB of memory

* Maximum database size of 1,048,516 TB

For a detailed preview of all the SQL Server features and supporting tools, refer to

For information on features by edition, refer to

While migrating to SQL Server, the choice of edition of SQL Server that is required to support the demands on the database has to be validated. Refer to the following resource for a whitepaper that can aid in choosing the appropriate edition of SQL Server:

Windows Server 2003

Windows Server 2003 is available in the following editions:

  • Windows Server 2003, Standard Edition. For departmental and standard corporate workloads.

  • Windows Server 2003, Enterprise Edition. For critical or heavy server workloads.

  • Windows Server 2003, Datacenter Edition. For high levels of scalability and reliability.

  • Windows Server 2003, Web Edition. For Web serving and hosting.

The goals of the Windows Server System are to promote operational efficiencies through simplified deployment, management, and security; to ensure high levels of dependability, performance, and productivity for application development; and to seamlessly connect information, people, and systems.

For more information on the different editions of Windows Server 2003, refer to

Technical Proof of Concept

After validating the technologies, the team creates a prototype project using a small database that contains a representative sample of the data, tables, and other objects found in the production databases. This process will validate the approach for the migration and provide useful experience when using the various tools that have been selected.

This prototype can also be used as a basis for a proof of concept, and ultimately the development of the solution itself. This initial proof-of-concept model often produces both answers and additional questions for the team. This information helps in the risk management process and identifies changes needed to the overall design that must be incorporated into the specifications.

Here are some candidates for the proof of concept that are typically part of this type of migration:

  • Performance of binary large objects (BLOB) in storing, accessing, and updating based on differences in their storage architecture between SQL Server and Oracle.

  • Migration of sequences. Sequences are not supported by SQL Server, but the functionality can be duplicated.

  • Replacing reverse key indexes in Oracle with regular indexes in SQL Server.

  • Converting Oracle packages to SQL Server stored procedures.

  • Recreating Oracle's profiles (resource and password) functionality using Windows security.

  • Developing Windows auditing functions to replace specific Oracle functionality (session, privilege, and object auditing).

Develop the Project Plans

The solution design that was created in the Envisioning Phase is used as the baseline while creating the project plan. The project plan also needs to consider the key success criteria identified in Chapter 2, "Envisioning Phase." The project plans include:

  • Development plan

  • Stabilization plan (includes test plan and pilot plan)

  • Deployment plan

  • Operations plan

  • Budget plan

  • Training plan

In addition to the above plans, a training checklist needs to be created that will identify the existing and required skills. This will help you to plan the training that needs to be provided when you do the work described in Chapter 20, "Operations."

The development plan, test plan, and deployment plan are covered in detail under the following headings.

Development Plan

The development plan describes different aspects of the development endeavor, such as tools required, methodologies and practices to be followed, schedule of events, and resources. The primary tasks of the development plan include:

  • Defining team roles

  • Identifying team, hardware, and software resources

  • Providing training to the team members

Refer to the development plan template provided with this solution to assist in creating your development plan.

In general, it is recommended that the elements described in the following sections be included in a development plan.

Development Objectives

These define the primary drivers that were used to create the development approach and the key objectives of that approach. The development objectives for a migration project differ from that of a new development. Most commonly, the key objectives are to:

  • Migrate the application with the least amount of change.

  • Create a SQL Server database which is almost identical in design and implementation to the source Oracle database.

  • Migrate the entire solution to a Windows and SQL Server environment with the least amount of change.

Migration List

This provides a detailed listing of the applications and databases that need to be migrated as part of the current project.

Overall Delivery Strategy

This describes the overall approach to delivering the solution. Examples of delivery strategy include staged delivery, depth-first, breadth-first, and features-then-performance. In migrations, the solution already exists and the delivery strategy consists of deploying the replacement solution.

Tradeoff Approach

This defines the approach for making design and implementation tradeoff decisions. For example, you might agree to trade features for schedule improvements, or to trade features for performance.

Key Design Goals

These identify the key design goals and the priority of each goal. Examples of design goals in a migration include interoperability between a UNIX application and the SQL Server database or rewriting the application using the .NET framework.

Development and Build Environment

This describes the development and build environment and how it will be managed. Include information on items such as source code control tools, design tool requirements, operating systems, or other software installed. If a development environment for the existing application does not exist, it will need to be created. This situation is common when the development of the applications to be migrated was originally outsourced.

Development Tools

These tools are used to assist in the development and test environments. For a detailed discussion of tools, see the "Set Up the Development and Test Environment" section later in this chapter.

Guidelines and Standards

These list and provide references to all standards and guidelines to be used for the project. Standards and best practices could differ substantially from the current Oracle/UNIX environment. However, applying new standards will require a tremendous rewrite, which could greatly affect the migration timeline. It is recommended that such standards be applied only to the components that are being rewritten or modified.

Versioning and Source Control

This describes how versioning and source control will be managed. This section includes identification of the specific tools that will be used and how developers are expected to use them. Most source control tools work with code irrespective of platform. Due to the change in operating systems, new software may need to be acquired. For instance, if CVS or any other source control software is currently used in the UNIX environment, consideration should be given to migrating these functions to a Windows-based alternative, such as Visual Source Safe.

Build Process

This describes the incremental and iterative approach for developing code and for builds of hardware and software components. It also describes how the build process will be implemented and how often it will be implemented.


This provides a high-level description of the set of solution components and how they will be migrated. In a migration project, most of the solution components should already exist.

Configuration and Development Management Tools

This identifies all the development tools the team will use during the project. This includes tools for all steps in the project: development, testing, documentation, support, operations, and deployment.

Design Patterns

This identifies the design patterns or templates that the team will use for this project and their sources. The team can acquire design patterns from both external and internal sources or create new design patterns. This information will only be necessary in development plans that involve rewriting the application.

Development Team Training

This identifies the training necessary to ensure that the development team will successfully develop the solution. This is a critical necessity in migrations because it is not common to already have personnel in place that can adequately support both Oracle and SQL Server environments.

Development Team Support

This identifies the various types of support the development team will require, the sources of that support, the amount of support of each type that the team will require, and the estimated schedule for support. Support may not exist for the target Windows environment, and may need to be developed.

Stabilizing Phase Plans

During the Stabilizing Phase, the testing team conducts tests on a solution whose features are complete. Testing during this phase emphasizes usage and operation under realistic environmental conditions. The team focuses on resolving and prioritizing bugs and preparing the solution for release.

During the Planning Phase, the team typically creates the following plans that will be used during the Stabilizing Phase:

  • The test plan

  • The pilot plan

Test Plan

The test plan describes the strategy and approach used to plan, organize, and manage the project’s testing activities. It identifies testing objectives, methodologies and tools, expected results, responsibilities, and resource requirements. This document is the primary plan for the testing team. A test plan ensures that the testing process will be conducted in a thorough and organized manner and will enable the team to determine the stability of the solution. A continuous understanding of the solution’s status builds confidence in team members and stakeholders as the solution is developed and stabilized.

The Test Role in the MSF Team Model is responsible for creating the test plan. This team is also responsible for setting the quality expectations and incorporating them into the

testing plan. This template is available in the Tools and Templates folder in the zip file download of this guidance at* *

The key sections of a test plan include:

Forms of Testing to Be Performed

These include:

  • Code component testing

    In a migration, the scope of changes made to the existing application varies from minor connection string changes to a complete application rewrite. The goal of testing is to ensure that any component modified matches the original features and functionality. Instead of basing the test results on a series of requirements, the existing application can be used as a basis for comparison.

  • Database testing

    This includes:

    • Physical architecture of database. Have the data files, transaction logs, and other components that comprise the database been created correctly?

    • Logical architecture of the database. Have the tables, views, stored procedures, triggers, and other database objects been created successfully?

    • Data. Have the contents of the tables been transferred correctly? Do tables contain the correct number of rows? Is the data valid?

    • Functionality. Do the stored procedures, triggers, views, and other items comprising T-SQL code operate in the same manner as the original Oracle objects?

    • Performance. Do the response times and throughput meet requirements and match user expectations?

  • Infrastructure testing

    Infrastructure testing ncludes the development and production environments, as well as hardware, software, monitoring software, network, backup strategies, and disaster recovery plans.

  • Security testing

    There are two levels of security testing, network level and application level, that should be tested:

    • Network level. Test access and privileges

    • Application level. Ensure that the correct users and permission levels exist in the application and database.

  • Integration testing

    If the solution is deployed in phases, then test the integration of each phase. Each incarnation needs to be tested for acceptable integration with other components.

  • User acceptance and usability testing

    These tests can often be recreated from the original design solution. The migration should meet the same goals as the existing solution. Any new or modified functionality should also be tested.

  • Stress, capacity, and performance testing

    These tests are important because the migrated solution will reside in a new environment. The hardware, operating systems, and software will be very different than the existing solution. Complete testing should be performed on the solution. Stress testing can also be used to check database coexistence issues that may occur when there is a heavy load on the system.

  • Regression testing

    To ensure that the solution functions correctly while bugs are being corrected, a set of test cases should be available that can be executed as a regression test. Each time the test is run, the results should be logged and compared with the expected results. Testing performed on the original solution can be reused in most scenarios. Using the same data, test cases should perform identically between the existing solution and the new solution being tested.

Test Approach and Assumptions

Describe at a high level the approach, activities, and techniques to be followed in testing the solution. If different approaches are required for the solution’s various components, you need to specify which components will be tested by which approach. This approach is relevant to migrations, as not all components will have the same amount of change or transformation. The test approach should be tempered based on the amount of change.

Major Test Responsibilities

Identify the teams and individuals who will manage and implement the testing process.

Features and Functionality to Test

Identify at a high level all features and functionality that will be tested. Perform the following tasks on the most common problem areas in an Oracle to SQL migration project:

  • Ensure that the SQL Server edition acquired meets application requirements. For more information, see the "Validate the Technology" section in this chapter.

  • Verify that the database architecture follows best practices and will meet performance goals.

  • Ensure functions that have moved from the database into the application (and vice versa) during the migration are properly documented and tested.

  • Check any table design changes due to restrictions on SQL Server row size.

  • Test triggers that have been converted from Oracle to SQL Server.

Items to include when testing application migration include:

  • Code changes

  • Security requirements

  • User connectivity and remote access

  • Connection string changes

  • Functionality changes. Consider both application and database for specific items. Some database items will affect the application.

Type of Required Hardware

The type of hardware for the test setup should closely resemble the architecture that is proposed for the production environment. The availability of hardware may depend on its type, its cost, and any other environmental factors that need to be considered at the location of the test setup. In situations where data does not exist to make a decision on the production data, the test environment has to be planned to capture such information. In some situations, using virtual machine software (such as Microsoft Virtual Server 2003) to recreate test environments and perform software testing is cost-effective.

Location of Test Setup

Decide on the location of the test setup. If development, testing, and production are in physically distant (possibly offshore) locations, then decisions have to be made on how to set up the environments for optimal performance for each group. For example, if the test and production locations are far apart, creating copies of a large production database across a WAN can be an issue. Also, the test environment should be isolated and should not interfere with any production activity.

Resources Required

Provide for the different equipment required for the setup, such as power points; racks or stands; storage media, such as disks; and a backup system. Software, services, and tools for the test environment have to be procured and set up as per the instructions provided by the test team.

Expected Results of Tests

Describe the results that should be demonstrated by the tests. This information includes expectations of both the solution team and the testers. This section also defines whether the results must be exactly as anticipated or whether a range of results is acceptable. The expected results originate from the existing solution. The migrated solution should perform the same or better than the original system.


Describe the materials that must be made available or created to aid in conducting the tests and for presenting test results. In many migration situations, existing test scripts can be reused.

Testing procedures and walk-through

Describe the steps the testing team will perform to ensure quality tests.

Tracking and Reporting Status

Define the information that test team members will communicate during the testing process. This section defines the specific test status information that will be created and distributed. This information normally includes status information for each test case and the probability of completing the test cycle on schedule.

Bug Reporting Tools and Methods

Describe the overall bug reporting strategy and methodology. This section also defines what will qualify as a bug in the code, product features, and documentation.


Identify the major test cycles, tasks, milestones, and deliverables. This section also describes who is responsible for each test cycle and its tasks. In addition, it identifies the expected start and completion date for each test cycle and the tasks within that cycle.

When planning for test cases, identify the sections of code that have changed during migration and identify the functionality provided.

Create test cases based on these functionality changes. When changes are minimal, such as a Java application that only requires the database connection string to be modified, the test case should ensure that the application is connecting properly to the SQL Server database. In most migrations, updates to connectivity and APIs represent a majority of the changes.

In situations where the APIs have been changed, test cases should be created to test this component change. These cases should test database queries and updates performed by the application to ensure that results are as expected. Gather various scenarios of usability and user interaction and repeat for each component. Query results and database updates can be checked against the same functionality in the existing solution.

Refer to the test plan template to assist in creating your test plan. This template is available in the Tools and Templates folder in the zip file download of this guidance at

Pilot Plan

The pilot plan describes how the team will move the candidate release version of the solution to a staging area and test it. The goal of the pilot is to simulate the equipment, software, and components that the solution will use when it is active. This plan also identifies how issues discovered during the pilot will be solved. The pilot plan includes details about how to evaluate the pilot; the results of the evaluation will facilitate a decision whether to move the solution to production.

Project teams often conduct one or more pilots to prove the feasibility of solution approaches, to experiment with different solutions, and to obtain user feedback and acceptance of proposed solutions. Pilot solutions implement only those subsets or segments of requirements of the functional specification that are necessary to validate the solution.

Note Some projects may not require conducting a pilot. For example, if there are few changes to an application, the consensus may be not to perform a pilot program. However, even in situations where an application is ported, it is important to remember that the solution include the application and the new SQL Server database back end.

Because the back end database, environment, and platform will change in the new solution, a pilot is recommended for highly critical applications. Pilots are also recommended for applications where specific performance criteria, such as response times or throughput, must be met. Pilot programs can also be useful in situations where there is a redesign in the migration and the components work differently on the new platform.

In addition, piloting can provide operational and deployment personnel practical experience with new technologies and can benefit the actual deployment schedule and operational transition schedules.

The pilot plan provides the means to validate the business requirements and the technical specification prior to deploying the solution into production. Planning the details of the pilot ensures that the participating project teams identify their roles, responsibilities, and resource requirements specific to pilot development, testing, and deployment activities.

Refer to the pilot plan template for assistance in creating your pilot plan. This template is available in the Tools and Templates folder in the zip file download of this guidance at

Deployment Plan

During the Deploying Phase, the team deploys the solution technology and components, stabilizes the deployment, transitions the project to operations and support, and obtains final customer approval of the project. Planning for deployment starts in the Planning Phase by developing a detailed deployment plan. This plan should be developed by the Release Management Role with assistance from the rest of the team. See the deployment plan template in the Tools and Templates folder in the zip file download of this guidance at

Key elements of the deployment plan are described in the following sections.

Deployment Scope

This section describes the solution architecture and scale of deployment. The deployment team needs to consider deployment of the database, server, and the client applications. Each has its own challenges that require a different process and set of tools.

  • Seats. This describes the magnitude of the deployment in terms of sites, number of workstations, countries and regions, and other relevant size factors.

  • Components. This lists and describes the components to be deployed and any critical dependencies among them.

  • Architecture. This describes the solution’s architecture and how it might affect deployment.

Deployment Tools

The database and server deployment will often be on one or a few machines. They can be deployed manually. However, the clients may have to be deployed over hundreds of computers across the corporate network. This may require the need for software distribution and deployment tools. These tools are discussed in the "Deploying the Client Application" section in Chapter 19, "Deploying Phase."


Backing up of the solution and the source control of scripts must be carried out. The items that need to be backed up for which you need to plan include a snapshot of the source code of the existing solution and the migrated solution. Include the version information and build information.

This ensures that the environment can be recreated later, if required. It also aids during problems, especially when a fallback is implemented. In a phased migration, backups of the source should be performed between phases, as well as any other critical junctures, as deemed necessary. If a partial rollback is needed, backups can save time.

Deployment Schedule

This identifies the critical dates and anticipated schedule for the Deploying Phase.

Deployment Resources

This identifies the workforce that will be needed to complete the deployment and the sources of the personnel.

Solution Support

This describes how the users will be supported during the deployment.

  • Help desk. This describes the support provided to users and applications by the help desk team, including support for direct user questions and application issues, and also in-depth support for new or difficult issues.

  • Desktop. This describes any changes in current workstation application support that might be required during deployment.

  • Servers. This describes any changes in current server support that might be required during deployment.

  • Telecommunications. This describes any changes in current telecommunication support that might be required during deployment.

Coordination of Training

This describes how end-user and support staff training is coordinated with the deployment schedule.


The necessary information, such as the dates for deploying and completing the migration must be communicated to the customers and all those who may directly or indirectly be affected by the migration. The deployment plan should be made available to the entire team so that each person is well informed of what actions they are responsible for and when it should occur. This is especially critical in large migrations that have to be undertaken over a grueling schedule, such as a weekend.

Work Schedules

To prevent the business operations from being affected by the deployment, work schedules should be set accordingly. The work schedules should consider training the operations personnel and end users.

Resource Availability

The resources in the production environment should be prepared for the solution deployment. The infrastructure, servers, and workstations should be prepared for the deployment. The necessary installation files should be ready to conduct a smooth and timely deployment. Spare disk drives, cables, network cards, power points, stands, and backup power supply should be made available in case of failure in hardware during the deployment. In many instances, such precautions are not taken until a solution is already deployed. Hence it is important to produce a plan to capture all these tasks.

  • Assembling the team. The rollout team should be assembled and the training to perform the deployment should be provided. The rollout team members could be from the project team, but the team should also include members from the operations staff because they need to develop a thorough knowledge of the functioning of the solution. Alternate team members should also be selected to prepare for contingencies.

  • Training the team. The deployment team should be given the necessary training and privileges to overcome the difficulties that could arise during the rollout. The different installation manuals and documentation should be provided to the rollout team. A special help desk should be set up for the users and the rollout team to revert to while deploying the solution.

  • Documenting unplanned procedures. During the rollout, some procedures may have been implemented which were a part of the rollout plan. The deployment team may need these procedures to stabilize the deployment or correct unforeseen situations created due to the execution of unplanned procedures. These emergency procedures should be documented and include the cause and effect of the procedure adopted.

    Because there are risks involved, the risk management plan has to be reviewed and updated. The risk management template is available in the Tools and Templates folder in the zip file download of this guidance at

Deployment Strategies

The main strategies that need to be considered for deployment include:

  • Cutover strategy. The critical area in a migration project where the existing system is shut down and the new system is ready to be implemented in the production environment. The cutover strategy focuses on the tasks, activities, and time required during the final days of the project. The cutover strategies available are:

    • Straight cutover. This strategy enables migration of the database from one environment to another without taking any intermediary steps. This option is used when downtime does not limit the migration, and the migrated database does not contain critical information.

    • Parallel cutover. Replication is a strategy that is used by organizations that do not have the adequate downtime required to build and migrate a database. A database is created in the new environment and data is migrated on a continual basis. A process is put in place to replicate the current database changes into the new database synchronously or asynchronously.

    • Serial cutover. This option enables the database to be migrated in phases. This approach is usually implemented when multiple databases need to be migrated or data within the database is portioned based on business functions. The serial cutover strategy is difficult to implement when the database is complex and interdependent with other infrastructure elements.

  • Phase-out strategies. In large, complex migrations, the solution is rolled out in phases. This enables the team to assess and reduce the business risks. Phasing out enables the deployment to take place in small and manageable parts. There are two types of phase-outs:

    • Vertical phase-out. In vertical phase-out, modules are rolled out one at a time. This implies that both the source solution and the target solution interoperate. The advantage is that the solution is rolled out in manageable parts. The disadvantage is that data bridge programs between the two solutions have to be deployed at each phase-out.

    • Horizontal phase-out. The horizontal phase-out covers the rolling out of the solution in phases over the different geographical areas. While implementing a horizontal phase-out, the differences in the environment conditions of the business and applications should be taken care of.

  • Fallback strategies. A fallback is necessary if the migration encounters difficulties or delays that impacts business. This could result due to the following reasons:

    • Unfeasible migration strategy

    • Undocumented requirements or procedures

    • Insufficient testing

    • Disasters

    • Unforeseen situations

    Each phase and each action in the rollout has to be associated with an impact to determine if a fallback is required whenever that action cannot be successfully completed. Dependencies for every action should also be mapped to foresee the impact on tasks further down the chain. Every precaution to ensure that the fallback is successfully completed has to be undertaken. For example, in case of a fallback, the licenses of the existing solution need to be still valid, and the encryption certificates need to be valid.

    A fallback plan should enable the solution to be rolled back to a consistent and stable state. Workaround strategies or contingency plans should be tried before deciding to fallback on the original database. The fallback strategies to be implemented depend on the cutover strategies that are selected. The fallback strategy should accommodate for any transactions that were executed since the start of deployment. To select the optimal fallback strategy, a list of the possible failures needs to be documented. The different fallback strategies available are:

    • Fallback after straight cutover. In case of a fallback in straight cutover, the UNIX environment needs to be frozen at the time the cutover started. Thus, in case of a fallback, it is possible to restart the UNIX environment with minimal effort. A fallback may require re-establishment of broken sessions between the customer computers and the database. To reduce downtime and rework, during the fallback, checkpoints have to be established before clients are migrated. Also, a subset of clients should be cut over and tested before the rest of the clients. In addition, adequate time should be provided for a fallback with minimal user disturbance.

    • Fallback after parallel cutover. The fallback process involves reversing the direction of replication that was used in the cutover, that is, SQL Server to Oracle database. Consequently, the Oracle database is updated for the period of the cutover. The client side is the main technical area to be addressed during the fallback. Migrating the clients in smaller batches can ease the pain of reverting to the original environment.

    • Fallback after serial cutover. The fallback is similar to that in a straight cutover, with the entire migration viewed as a collection of smaller cutovers. The serial or phased cutover enables you to fallback to a previous consistent state without having to undo all changes. You are also able to solve problems in small increments.

For more information and assistance in developing your deployment plan, refer to the deployment plan template.

Create the Project Schedules

Based on the functional specification and the project plan, the team creates a project schedule that defines the release date of the solution. The team may choose to divide the schedule functionally. For example, different sub test teams will work on testing different features of the solution. Each subteam will create its own schedule.

A release date is decided after integrating the master project schedule with the schedules from each team lead. A release date enables the team members to prioritize functions, assess risk, and plan effectively.

Estimating the Effort

According to the various functional components, each task is allotted to a team that is developing the component. Each team prepares plans based on their role and deliverables. Finally, all the plans are reviewed and dependencies between the plans are outlined. These various plans are then merged into a master project plan.

Estimating the teams' effort can be accomplished as follows:

  • Each team leader prioritizes the functions, roles, and responsibilities for the entire team.

  • The team members are required to estimate their performance for each task assigned. The time given to perform an individual task should vary from one half day to one week. If the time duration estimated is short, then the overhead in managing the task may be too high. If the time estimated is too long, then managing the scope of work becomes too difficult.

  • Perform bottom-up estimating to create accurate schedules. This method enables the individual performing the task to estimate the time taken to complete it. The estimate is usually based on the individual's experience of performing a similar task. In addition, individuals that estimate time plans for their work feel more accountable for their work schedules.

  • Implement risk-driven estimating, which enables the team members to perform the difficult and highest risk tasks first. It permits individuals to minimize risk and provides excess time to mitigate risk.

When scheduling a migration, there are many time-related elements to consider. For instance, the development and test environment need to be set up before starting the Developing Phase to maximize team productivity, mitigate risks, and avoid delays. It is also recommended to migrate the database before migrating the application. This is due to object-level differences between the Oracle and SQL Server databases that may need to be modified.

Migration environments also impact the schedule. For instance, projects that involve porting the application, such as Java, can be accomplished relatively quickly. By contrast, migration environments that require rewriting the application, such as Oracle Forms and Pro*C, will need a longer development schedule. Also, it is more challenging to migrate data in OLTP environments than in batch processing environments.

Skills can also affect the scheduling. In an optimal migration, a database administrator on the team is skilled in both SQL Server and Oracle environments. If current database administrators are only familiar with the Oracle environment, extra time should be allowed for job training and skill acquisition. The same applies to application developers. In addition, pilot testing and migrating smaller applications before the main application may be beneficial to the project by providing the entire project team with experience that may prove helpful while migrating the main application.

After you have defined a schedule, you can allocate resources to it. You should:

  • Update the project plan with detailed allocation of resources.

  • Check and configure availability of resources, identifying any possible overuse of resources.

  • Establish a baseline and track progress on tasks and budget.

Set Up the Development and Test Environments

The final tasks for the Planning Phase are setting up the development and test environments. Completing these tasks allow for a smooth transition into the Development Phase.

In many ways, the development and test environments are similar. Both are modeled on the solution environment, and both are used to refine the solution. There are also differences between the functions of these two environments.

The development environment is used to develop the migration solution for the database, client application, and server applications. In addition, the environment may go through changes as the solution is developed.

By contrast, the test environment is more strictly controlled to ensure that it emulates the production environment. The test environment is used to complete all necessary testing for the database and application. The test environment must be as similar as possible to the production environment so that stress and scalability tests will have a measure of accuracy.

Often in migration projects, the development environment is used for testing once the Developing Phase has ended. In most situations, the test environment requires a higher level of performance than the development environment, and additional hardware will need to be added.

If you are reusing equipment, it is best to reload all operating systems, applications, and data. This ensures that testing results will not be skewed by a system setting or error left from the development environment.

The test environment also has different requirements than the development environment. For instance, testing tools should be installed for tasks such as recording performance factors, or to increase the load during stress testing.

The tools required for development and testing are:

  • Database modeling tools for reverse engineering existing databases and generating data definition statements for the new environment. The more popular modeling tools are AllFusion ERwin from Computer Associates and ER/Studio from Embarcadero.

  • Software development tools such Visual Studio .NET or Visual Basic .NET, Perl, Python, PHP, and Java

  • Software management tools for source control and bug tracking — Visual Source Safe and Concurrent Version Systems (CVS) are the popular ones for source control. PVCS from Merant and ClearQuest from Rational can be used for bug tracking.

  • An application debugger (if one is not bundled with the application development tool). Most applications come with their own built-in debuggers.

  • Software for administering Oracle and SQL Server. Both Oracle and SQL Server offer Enterprise Manager, which contains a comprehensive set of tools for all administrative tasks. There are several third-party tools available, such as Toad and SQL Navigator from Quest, PL/SQL Developer from Allround Automations, Unicenter SQL-Station from Computer Associates, and Rapid SQL from Embarcedero.

  • Database and data migration tools. Tools that are aimed specifically at Oracle to SQL Sever migration are available from Microsoft.

  • UNIX interoperability tools such as SFU, FreeTDS, and so on.

  • Scripting tools for writing scripts for testing. Perl is a popular scripting language that is portable across UNIX and Windows. Windows Script is available from Microsoft.

  • Performance monitoring and analysis tools for the various components of the solution. Perfmon is a versatile tool for monitoring the Windows system and it can capture statistics for any application that runs on a Windows computer. Filemon and Netmon can be used specifically for the file system and internet traffic.

  • Scheduling tool for automating tests and for capturing statistics. Windows scheduler can be used for this purpose. Autosys from Computer Associates and Control-M from BMC are also popular.

For additional information on testing, refer to Chapter 18, "Stabilizing Phase."


Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows

Update Notifications

Sign up to learn about updates and new releases


Send us your comments or suggestions