Chapter 6 — Planning Phase: Building the Development and Test Environments
On This Page
Introduction and Goals
Defining the Development and Test Environments
Specifying the Development and Test Environments
Building the Development and Test Environments
Testing the Development and Test Environments
Introduction and Goals
This chapter describes how to approach the creation of the different lab environments that you require for the development, testing, and staging of the databases and applications that you are migrating. It discusses hardware, software, and configuration. Although each of these environments is different, they share common features. Specifically, each environment requires access to a Microsoft® SQL Server™ database, and includes client hardware as appropriate. This chapter covers guidelines for:
Defining the requirements for each environment.
Specifying appropriate hardware and software.
Building the environment.
Testing the environment.
The planning decisions that you have made regarding which databases and applications you will migrate, and how you will migrate them, will help you define the environments that you require to develop the solutions. For more information about the tools and facilities that are available to support these environments, as well as about the kinds of questions that you should ask during this stage of planning, see the UNIX Application Migration Guide at http://msdn.microsoft.com/library/en-us/dnucmg/html/ucmglp.asp.
Defining the Development and Test Environments
The first step in defining the environments for developing and testing the migration from Sybase to SQL Server is to understand the purpose of each environment, and to define its boundaries. The following discussion should help you do this.
Each environment should be isolated — both from the other project environments and from the live system — as far as possible. Developers and testers should use copies of the production system (including hardware) to avoid inadvertent changes to the production system. Some resources, such as the configuration management, source code control, and bug tracking systems, should be shared between the environments.
When it is impossible to provide copies of the production hardware, developers and testers should be limited to read-only access to the production system. It is very easy (and very common) to mistakenly run a program that could have a serious business impact on the wrong system.
Note It is good practice for any script or update procedure to check the value of **@@servername** and prompt to continue if it is a production environment.
The environment used for testing the migrated Sybase databases and any client applications should reflect the infrastructure used in the production environment. Information about the production environment is gathered while creating the logical and physical designs. Defining the tools and services to support these environments can include items such as SFU, Sybase client software, FreeTDS or unixODBC, and SQL Server client drivers.
This chapter deals with the following three lab environments:
Development environment. This environment incorporates a small network of computers used to design and build the migrated database and applications. It will include a build system.
Test environment. This environment also comprises a small network of computers used to test the migrated database and applications.
Staging environment. This environment is used to support the migration of the database and client applications from Sybase to SQL Server. It will be used for the final acceptance testing, parallel running, and so on. This environment should reflect the final, post-migration, production environment as closely as possible.
Note The staging environment is a copy of the post-migration production environment, and will usually remain in place after the migration is complete. It will allow the support team to reproduce errors for investigation without affecting the actual production environment.
These lab environments are shown in Figure 6.1.
Figure 6.1 Development and test environments
For each environment, you need to define the hardware and software requirements to support it. In addition, you need to determine interoperability and management requirements. For example, you will need to answer the following questions:
Will the lab need to be part of a network domain for testing or other purposes?
Which network protocols will it require?
Which tools will be shared between the environments, and how will sharing take place?
What type of support will each environment require?
Note The development environment should not share databases with the test environment. For consistency, you should be able to build test databases from scratch so that each test run takes place based on the same data set contents.
The data in any development or test database should be scrubbed if it is copied from production data. Personal data, such as names, credit card information, and e-mail addresses should be removed. However, if you change all names to “John Doe," this may affect index performance, and so any scrub process requires careful design.
If the database migration is taking place in parallel with a migration of a UNIX application, the two parts of the migration project may share development and test environments. In this case, you should take into account the needs of both migrations when defining the requirements for the environment.
The following section defines the specific needs of each environment type.
The development environment is used to conduct activities to design and build the migrated database application. Developers will also run unit tests in the development environment.
You should, therefore, consider the need for the following:
Word processing and office automation tools to specify the migration requirements, document unit test results, and to communicate with other members of the migration team.
Analysis tools to enable the structure of the existing database to be discovered, extracted, and optimized.
Database and application modeling tools to define the required structure of the migrated database and how it relates to its application environment.
Software development tools, such as Microsoft Visual Studio®, to support the creation of code and its compilation, execution, and debugging.
Software management tools, such as software for source code management and problem-tracking.
UNIX interoperability tools such as Microsoft Windows® Services for UNIX (SFU) 3.5, which provide UNIX shell scripting, development environments for C and C++ compilers, assemblers, linkers, and debuggers.
The Sybase Migration Toolkit (SMT) for scripting the translation of Sybase schema objects and Transact-SQL statements to their SQL Server equivalents. The SMT also requires the following software:
Perl (either ActiveState under the Windows subsystem, or Perl 5.8.3 under SFU) and the Digest-MD5 Perl module
The DBI and DBD::Sybase modules
The dbschema.pl perl script
Note For more information about installing and configuring the SMT and locating its prerequisite software, see Appendix H, "Sybase Migration Toolkit Installation Guide." Also see http://www.microsoft.com/services/microsoftservices/default.mspx.
The Sybase client software and drivers needed to connect to the original Sybase database. The SMT requires a connection to the Sybase database to extract information about the objects being migrated. Follow the procedures in Appendix H, "Sybase Migration Toolkit Installation Guide," for configuring connectivity to the Sybase database.
Developers whose principal experience is in the Sybase environment should take the time to familiarize themselves with the tools and facilities to develop SQL Server applications in Windows. Doing so will help both the migration and the subsequent development and maintenance of the SQL Server database application.
The development environment should be physically separated from the production and test environments. However, because some resources, such as Bug Tracking and Configuration Management, need to be shared with the Test team, a suitably configured firewall is usually sufficient.
The hardware used in the development environment often differs from hardware in production: the performance requirements are different, the disk space requirements are different, and the security requirements are different. However, there should be a sufficient set of systems available to allow developers to unit test any particular feature, such as a domain controller.
You must define the test environment to be used for various testing, including system integration, security, high availability, and user acceptance.
You should also consider the need for the following:
Word processing and office automation tools to document test scripts and test results and to communicate with other members of the migration team.
Testing tools; for example, scripting tools to enable the repetition of tests.
Performance-monitoring and analysis tools to enable performance issues to be identified and diagnosed.
Load-testing tools to enable you to simulate real-world use of the migrated application.
The system testing environment may require hardware that is identical to or at least close to hardware in the production environment.
For example, the system testing environment may require a domain controller to emulate the production security environment. Also, the test lab should be kept separate from any production environment. Ideally, the test lab should be physically separate, that is, it should exist on its own unique network, but it would suffice to keep the two environments separate by using a firewall. The reasons for this separation are:
The test environment could use production environment resources inadvertently, causing the test results to be flawed.
The test application could inadvertently damage the production application; for example, by overwriting data.
The operator or user of the test environment could inadvertently damage the production environment; for example, by deleting elements of the production environment.
Certain types of testing (for example, load testing) can have adverse effects on the service levels that the production environment is capable of providing.
The test environment is required to share some resources with the development environment, such as Bug Tracking and Configuration Management. Typically, access to such shared resources is controlled through a firewall.
In some cases, the system testing environment might also be used as a staging environment.
Note For more information about creating a test lab, see Chapter 4, "Building a Windows 2000 Test Lab," in the Windows 2000 Server Resource Kit Deployment Planning Guide at http://www.microsoft.com/windows2000/techinfo/reskit/dpg/default.asp. Although this document is part of the Windows 2000 Resource Kit, the principles it contains apply equally to Windows Server 2003.
This type of environment is used for migrating data from Sybase to SQL Server systems and for testing clients that are migrated from UNIX or Windows to SQL Server systems. You could use this environment to perform final data, security, or functionality checks before deployment to the production environment.
The staging environment should match the production environment — that is, the final destination for the migrated database application — as closely as possible. In some cases, you may also use the system test environment for staging if the test environment is suitable in terms of performance, storage, and connectivity. Acceptance Test Plans may call out the requirements for the staging hardware requirements.
The staging environment will also be critical after deployment, when it can be used by the support and development teams to identify and reproduce issues in production without affecting the production system itself.
Specifying the Development and Test Environments
After you have defined the functions required of the development and test environments, you will be able to specify what is required in terms of hardware, software, and communications. With specifications in hand, you can then determine whether sufficient facilities already exist, or if you must design and procure or build a new lab.
The hardware requirements for the database and client applications merit careful planning. You also need to assess any differences in performance requirements of the processor architectures involved — many UNIX implementations are based on RISC processors, whereas Windows uses the Intel CISC (IA-32 processors) and EPIC (IA-64 processors) architectures. You can choose to increase memory, CPU, and disk space for the migration and in anticipation of future development or business growth plans.
Note The choice of whether to use a 64-bit processor for the SQL Server computer will depend on the scalability and memory requirements of the database you are migrating. The 64-bit version of SQL Server 2000 should be used for high-throughput applications that need fast access to large amounts of data. The 32-bit version of SQL Server 2000 can address up to 4 GB of data in memory; requirements to access more than this amount of information will require that SQL Server transfers data between memory and the hard disk. The 64-bit version of SQL Server can directly address up to 512 GB of data in memory under Windows Server 2003. This allows SQL Server to process multigigabyte databases far more quickly than in the 32-bit environment. The 64-bit version of SQL Server is ideally suited to multidimensional data warehouse/analysis databases, as well as databases supporting enterprise resource planning and supply chain management applications (such as SAP R/3) that can buffer information about large numbers of concurrent users in memory.
The 64-bit version of SQL Server 2000 supports 32-bit and 64-bit client applications.
The test hardware requirements are largely based on the information that you collected during your assessment of the current environment and recorded in a Systems Environment Data Archive (see the assessment discussion in Chapter 3, "Planning Phase: Overview.") Hardware for testing UNIX clients should use the specifications of the existing machinery as a basis. However, for projects migrating applications away from UNIX, converting UNIX hardware specifications to the equivalent personal computer client specifications can be challenging because of the different CPU architectures, system methods, and so on.
On the server side, you must assess high availability requirements, which may include clustering (provided by Microsoft Cluster Services), backup servers (log shipping or replication), and geo-clustering provided by storage area network (SAN) hardware.
It may be useful to bring in hardware vendors to help determine the hardware requirements for the testing and staging environments. Several hardware vendors provide tools for estimating hardware requirements. For example, Hewlett-Packard provides three sizing tools for various SQL Server 2000 scenarios (you will need to register with HP to use these tools):
HP ProLiant Server Consolidation Tool for Microsoft SQL Server. This tool allows users to assess the possibility of consolidating multiple installations of SQL Server into a single instance running on the same computer. This tool can be accessed online at http://h71028.www7.hp.com/enterprise/cache/70724-0-0-225-121.aspx.
HP ProLiant Transaction Processing Sizer for Microsoft SQL Server 2000.This tool enables users to determine the optimum hardware configuration for SQL Server supporting a high-volume transaction processing workload. You can download this tool (registration required) from http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,536-6-100-225-1,00.htm.
HP ProLiant Sizer for Microsoft SQL Server 2000 Data Marts. This tool allows users to ascertain the best hardware configuration for SQL Server running supporting data marts. You can download this tool (registration required) from http://activeanswers.compaq.com/ActiveAnswers/Render/1,1027,5276-6-100-225-1,00.htm.
Occasionally, there may be some hardware requirements that are too expensive to duplicate in the development and testing environments. For example, if the application contains complex computer-telephony integration (CTI), it may require physical links to an automated call distribution (ACD) system that exists in production. If it is impossible to rent, borrow, or otherwise obtain equivalent hardware, you will need to explore, with the hardware vendor, methods for partitioning that system so that the necessary integration can be safely tested. It may also be necessary to develop simulators for these external components to allow for development and initial testing to proceed.
Some of the software that you are likely to need in the development, test, and staging environments includes:
Microsoft Windows Operating System and service packs. This should be Microsoft® Windows Server™ 2003 or Microsoft Windows® XP with the most recent updates installed.
UNIX. You will need the relevant version of UNIX to test client applications remaining in the UNIX environment. Additionally, because the migration project is not intended to alter the functionality of the application, you should be able to compare the migrated system functionality directly with the original system.
SQL Server and service packs. The edition of SQL Server 2000 that you choose will have an operating system requirement. Windows XP only supports the Developer Edition of SQL Server 2000, but Windows Server 2003 supports both the Developer Edition and Enterprise Edition.
Utilities. Your development, test, and staging environments will require certain utilities and scripting tools, such as Perl.
SFU 3.5. SFU provides all the features of a traditional UNIX operating system, including pipes, hard file links, UNIX networking, and UNIX graphical support through the X Windows System. It also provides UNIX and POSIX.2 utilities such as ksh shell, awk, and vi.
Connectivity libraries. Depending on the application migration strategy being used, these will include libraries such as FreeTDS or unixODBC. If you are using unixODBC, you must also obtain an appropriate ODBC driver, such as the FreeTDS ODBC driver, or EasySoft’s ODBC-ODBC bridge.
A bug-tracking tool. A good bug-tracking tool records information about bugs that are discovered during testing. The tool you choose should be capable of assigning each bug to a developer and should provide a workflow-based mechanism to resolve and close the bug. Products such as the Action Request System from Remedy, DevTrack from TechExcel, or the open source Bugzilla system offer suitable functionality. Details about the use of the bug tracking system are discussed in Chapter 11.
Source control. Source code control is a critical component of your development environment. Microsoft SharePoint Portal Server provides configuration management with code line source control capabilities. Microsoft Visual SourceSafe™ is another alternative. If all the development is under SFU 3.5 or UNIX systems, the Revision Control System (RCS) or Concurrent Versions System (CVS) are also possible candidates.
Microsoft Visual Studio. The Visual Studio development suite provides developers with comprehensive tools for designing and building distributed applications for Windows.
SQL Server client tools. These tools are included on the SQL Server compact disc.
Test management tools. These include test and automation tools (such as Rational Rose by Rational Software, SilkTest by Segue, or Winrunner by Mercury Interactive).
Sybase Migration Toolkit (SMT). This tool is used for assessing and transforming Sybase database schema objects and Transact-SQL statements to a format that is recognized by SQL Server. The SMT also requires ActiveState Perl, Sybperl, and the dbschema.pl script. Details of this are given in Appendices H and I.
Sybase client software and drivers. This software is required for the SMT in the development environment, and it will also be needed for client applications not using FreeTDS running under UNIX. The client software and drivers are available from Sybase.
Interoperability requirements may come into play in some testing scenarios when there is a need for password synchronization and resource or data sharing between computers. For example, setting up password synchronization requires SFU user name mapping in conjunction with single sign-on daemon (SSOD) and password authentication modules (PAM) on UNIX. Similarly, for data sharing, you can use a Samba client for Windows or a network file system (NFS) server for SFU.
Network Software Requirements
You do not require additional networking software for Windows XP or Windows 2000 Professional because these operating systems have built-in network software. Additional network software is required only if you are using Banyan VINES or AppleTalk ADSP. The NWLink protocol of Windows Networking provides Novell NetWare IPX/SPX client support.
The appropriate UNIX network drivers are necessary for client applications running under UNIX.
Table 6.1 lists Internet requirements related to using SQL Server 2000.
Table 6.1: Internet Requirements for SQL Server Use
Microsoft Internet Explorer 5.0 (or later) is required for all installations of SQL Server 2000 because it is used by Microsoft Management Console (MMC) and HTML Help. A minimal install is sufficient, and Internet Explorer is not required to be the default browser.
If you are using the Connectivity Only option and not connecting to a server that requires encryption, Internet Explorer 4.01 with Service Pack 2 is sufficient.
Microsoft Internet Information Services (IIS)
If writing Extensible Markup Language (XML) applications, see "System Requirements for the IIS Virtual Directory Management for SQL Server Utility" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/intro_8ut5.asp.
Note TCP/IP must be enabled at the operating system level before installing SQL Server 2000. Computers running client applications using FreeTDS must also have TCP/IP enabled.
Building the Development and Test Environments
Specification, creation, and setup of the migration's development and test environments usually follows a complete build of (or integration with) the network, server, and data source environments. Both the development and test platforms must be based on configurations that support the intended work and software tools. There may be more than one of either. A high-level sequence for building the environments includes the following steps:
Build, configure, and test the physical network and servers.
Design and diagram the network and services configuration.
Select, acquire, and configure individual servers.
Select, acquire, and configure network components.
Install, integrate, and test system components.
Install operating system software for each server. See the Windows Server 2003 installation documents and release notes.
Install and configure UNIX on the appropriate server, if client applications are to remain running under UNIX.
Install Services for UNIX for each Windows host, as necessary. See the SFU 3.5 and utilities installation documents and release notes.
Install SQL Server 2000 software for each server, as necessary. See the SQL Server 2000 installation documentation and release notes.
Note If you install SQL Server 2000 under Windows Server 2003, you will be warned that Windows Server 2003 does not support earlier versions of SQL Server 2000 service pack 2. You can continue and safely install SQL Server 2000, but you should apply the most recent service pack for SQL Server as soon as the installation has finished. You can download the service packs for SQL Server 2000 at http://www.microsoft.com/sql/downloads/default.asp.
Install FreeTDS and unixODBC under SFU or UNIX, as necessary. See the documentation in Appendix J, "FreeTDS and unixODBC Installation."
Install the Sybase client utilities and configure connectivity to the Sybase server.
Install the Sybase Migration Toolkit and utilities for each server, as necessary. See the documentation in Appendix H, "Sybase Migration Toolkit Installation Guide."
Build, configure, and test any data sources.
Build, configure, and test the client applications.
Populate each environment with the information it requires.
Perform a final check of all components and overall system readiness.
You will need to populate each environment with the information that is necessary to support the migration. For the development environment, this will include the source code of any database access routines, as well as any supporting scripts, configuration data, and documentation. Source code and other file-based information should be carefully managed; for example, you should check such files into a source code control system such as SharePoint Portal Server.
During the migration, you should keep the old and the new database applications up to date with each other. Testing of the migrated application may uncover longstanding bugs in the original application. If you are piloting or staging the migration, updates applied to the original system should be carried forward to the migrated system.
Note Installation of each environment requires a suitably sized and configured Windows-based infrastructure. This includes the necessary hardware and software for the development servers and the developer's desktop computers. Implementing a Windows network is covered extensively in Microsoft and third-party documentation and is not repeated in this guide.
Testing the Development and Test Environments
Testing should take place according to the testing guidelines described in Chapter 8, "Stabilizing Phase." You should conduct the following tests:
Installation tests. These tests ensure that the software that is required for the development can be installed and upgraded successfully.
User tests. These tests ensure that the resulting environment is usable.
Operational tests. These tests ensure that the development environment meets the criteria defined for its day-to-day operation and management; for example, backups and reconfiguration.
The tests of the development, test, and staging environments may include a subset of those specified for the migrated application. This will be particularly true for the staging environment and for the SQL Server computer used by the development and testing environment.
Testing the SQL Server Installation
When the SQL Server installation is complete, you can test the installation and connect to it locally by running the osql utility from the server.
To test the installation of SQL Server 2000, follow these steps:
Ensure SQL Server is running. If necessary, you can start it from the command prompt by typing:
net start mssqlserver
Connect to SQL Server by typing:
osql /Usa /P password
Substitute the SQL Server system administrator password for password. If osql connects successfully, the following osql prompt appears:
If osql cannot connect, it will display a diagnostic error message.
Type the following query at the osql prompt:
The osql utility returns the server name, as shown here:
1> **SELECT @@SERVERNAME**
(1 row affected)
Verify that this is the correct server name.
Quit the osql utility by typing:
Testing Sybase Connectivity
You can use the following procedure to determine whether the development computer can connect with the Sybase server hosting the databases to be migrated. This procedure uses the isql utility, supplied with the Sybase client software.
Note You can also connect to Sybase from FreeTDS, using the tsql utility, as described in the following procedure.
To test connectivity to the Sybase database, follow these steps:
Connect to Sybase by typing the following statement at a command prompt:
isql –Uusername –Ppassword -Sserver
and FakePre-0676887c086f4b2b92b1f42946b212c4-6319aa543cb34c798e610cd7226922cb are a valid user name and password for accessing Sybase, and FakePre-d85faa5da50a41108fe0dde7efbd73e7-bc83ebed12b8441ca443b1cb6851266b is the Sybase directory services name for the database.
If **isql** connects, this **isql** prompt appears: 1\>
Verify that you can connect to the database to be migrated by executing the following commands:
is the name of the database to be migrated.
Type the following simple query:
The isql utility returns the server name, as shown in the following example:
1> SELECT @@SERVERNAME2> GO------------------------------- FOXHOUND(1 row affected)1>
Quit the isql utility by typing:
Testing the FreeTDS Configuration
If appropriate, you can use the following procedure to determine whether FreeTDS has been installed and configured correctly. This procedure uses the tsql utility, supplied with FreeTDS, to connect to SQL Server.
To test FreeTDS connectivity to SQL Server, follow these steps:
Connect to Microsoft SQL Server by typing the following statement at a shell prompt (under SFU or on the client UNIX computer):
tsql –Uusername –Ppassword -Sserver
and FakePre-9347ce1cccae4f72a02cde0fffb657d9-b848373c15c246feac2db702ce278d9f are a valid user name and password for accessing SQL Server, and FakePre-820601a67da844d3b447864f4643c5d4-6c8e6187ef8942609b0a60bce006feb4 is the name of the SQL Server entry in the freetds.config file (see Appendix J, "FreeTDS and unixODBC Installation").
If **tsql** connects, you will see a number of messages indicating the locale, character set, and language setting **tsql** is using followed by the **tsql** prompt: 1\>
Type the following simple query:
The tsql utility returns the server name, as shown in the following example:
1> SELECT @@SERVERNAME2> GOWOLFHOUND1>
Quit the tsql utility by typing:
Testing the unixODBC Configuration
If you are using unixODBC, you should ensure that the unixODBC library and ODBC driver you are planning to use are correctly configured. Appendix J, "FreeTDS and unixODBC Installation," describes how to install the unixODBC library. The procedures for installing the ODBC driver will vary according to the manufacturer of the driver, and the details of this installation are beyond the scope of this guide.
When you have installed the ODBC driver, you can use the /usr/local/bin/isql utility, supplied with unixODBC (not to be confused with the Sybase isql command), to test the connection to SQL Server.
Testing the Development Environment
After the development environment has been constructed, carry out a series of tests to ensure developers have correct access to all the tools and facilities that they need. These tests are best carried out by simulating the day-to-day activities of the developers. You should at least perform tests to:
Test communications protocols. Test the ports and protocols required for network access to services required during the migration.
Access the database server. Test the developers' ability to access the development database server from both the command prompt and from the database application being migrated.
Access development applications. Test the developers' access to development applications, such as code management tools, modeling tools, and office applications.
Access shared resources. The bug tracking and configuration management systems should be accessible from both development and test environments.
Edit, compile, link, and execute code. Test the developers' ability to edit, compile, link, and execute code relating to the migration.
Deliver an application. Test the developers' ability to deliver an application from the development environment to the test and staging environments.
Testing the Staging Environment
Client applications that have been migrated or redirected must undergo final connectivity testing in the staging environment before deployment. All other testing should be completed first. The tests will ensure that the client applications connect to the correct database and that the connectivity software has been configured correctly.
Interim Milestone: Development and Test Environment Set Up
At this stage, you have successfully configured the development and test environments and installed the software needed to perform the database migration.