Chapter 4 — Planning Phase: Database

Article
12/05/2007

Published: June 21, 2004 | Updated: October 29, 2004

Introduction and Goals

The previous chapter, "Planning Phase: Overview," set forth the principal tasks involved in planning. This chapter is concerned purely with planning the database migration. It discusses the major decisions to be made and the issues to consider in making these decisions. Detailed guidance is then provided to help you create each part of the solution design (conceptual, logical, and physical designs), which will become part of the functional specification. The focus of this chapter is thus on what the solution will be. Detailed project plans for how the team will accomplish the migration are covered in Chapter 8, "Planning Phase: Creating the Project Plans."

As previously mentioned, client applications that use the database may also need to be migrated. Planning for these migrations is addressed in the next chapter. Keep in mind that the database and client application migrations will need to be closely coordinated and, therefore, the planning activities should take place in parallel.

Planning the Database Migration

During the Envisioning Phase you learned that you must select between all databases or only a subset of databases for migration from Sybase to SQL Server. The choice you make will have an impact on planning the database migration approach:

Migrating all databases. If your data center comprises dozens of databases, and all of them must be migrated, your planning must include a detailed strategy for each database, including accounting for any dependencies that may exist between databases.
Migrating selectively. Your analysis and planning need only be based on those databases that are part of the migration. If there is more than one, you will still need to account for the dependencies between them. You will also need to analyze any dependencies between databases that are being migrated and those that will remain in the Sybase server.

Although you may have identified databases that, for business reasons, you hope to migrate during Envisioning, you cannot definitively state which migrations are possible until you have completed all of the investigative work included in planning. In either scenario, of course, there are also likely to be clients and applications that play a role in your decision process.

You should also take into account that there may be multiple database server instances; each managing a set of databases. For example, there may be a test or training instance and a production instance.

In fact, it is likely that your enterprise contains more than one database that it has targeted for migration. The characteristics of the databases that you want to migrate and the applications that use them vary, so that various strategies — and therefore different sequences of steps — will be required to migrate them. You must individually evaluate each Sybase database, taking into account characteristics of its structure and use, to determine whether there is a feasible, cost-effective path for its migration. You then create an individual plan for each database based on its inherent complexities.

Some of the complexities that you may encounter concern database objects, such as tables, views, stored procedures, and triggers. Each database contains its own unique set of objects, often with complex inter-relationships between them. In many cases, the original documentation associated with your databases may identify similarities that allow you to create plans that encompass several databases that contain similar objects.

Scoping the Work

Use the vision/scope document, the questionnaires, and the Sybase Migration Toolkit to perform the following steps, which are straightforward in nature and will enable you to move forward with the more difficult work of decision-making and issue resolution:

Identify all databases and instances that are required or scheduled for migration (the selected databases dictate which applications must be migrated).
Identify and document the owner of each database and save this in the configuration management system with contact information.
Identify the objects that you must migrate for each database by using the Sybase Migration Toolkit (SMT).
Identify all client applications associated with each database. For more information, see Chapter 5, "Planning Phase: Clients," and Chapter 10, "Developing Phase: Clients," of this guide.

Specific Planning Needs

The following list details specific planning concerns for each database. The data from which you will derive your answers is found in the questionnaire responses. Best practice calls for analyzing these responses instead of relying on subjective interpretation.

Consider replication in the current environment. What impact will replication have on the new database and what will the database's requirements be to support replication?
Examine backup and restore strategies in the current environment. What are the issues and requirements (if any) for the new database?
Ensure that the migrated database meets all security requirements, and that user logins are mapped from Sybase to SQL Server correctly.
Plan how to operate the migrated configuration. Consider what will be required to support and operate the new system. Examine where and how to reimplement existing best practices for SQL Server.

In general, your migration strategies should:

Provide an investigation plan that you can apply to each database. This plan should allow you to determine whether:
- Migration can be achieved and how it can be achieved (key issues, solutions).
- Migration cannot be achieved and why it cannot be achieved (key issues, roadblocks).
Meet the goals of the business requirements.
Deliver a supportable, growth-oriented migration deployment solution.

Identifying Database Migration Issues

The following list identifies the common issues that arise when migrating a database from Sybase to SQL Server. For the following issues, you will find information in the Appendices:

Syntax conversion for stored procedures and triggers (see Appendix C).
Syntax conversion for other Transact-SQL statements (see Appendix E).
Data migration (see Appendix F).
Schema manipulation (see Appendix B).
Features that must be implemented at the SQL Server instance level instead of at the individual database level, such as security (see Appendix A).

You should also identify how much of the migration process can be automated and which tasks will need to be performed manually. The Sybase Migration Toolkit provides tools for generating scripts that can automate many tasks, including schema extraction from Sybase and schema import into SQL Server. Tasks requiring manual attention include user account migration, script migration, Transact-SQL conversion, and data migration.

One further complication concerns the location of stored procedures in Sybase. Sybase allows stored procedures that can be accessed system-wide to be held in the sybsystemprocs database. You should take to time to analyze any dependencies between the objects in the database being migrated and such global stored procedures — these stored procedures will need to be migrated to the SQL Server database.

Logical Design Considerations

The logical design for a database migration project must take into account the changes that will occur as a result of moving from Sybase to SQL Server. At a minimum, you should consider the following key items:

Dependencies between databases and sources of data used to populate or link with databases.
Use of UNIX scripts in the existing Sybase environment.
Use of extended stored procedures, including xp_cmdshell,
How data in the databases is secured.

Figure 4.1 illustrates the entire migration process, including development and testing, and highlights some of the logical elements that must be accounted for in the logical design.

Figure 4.1 Sybase database migration process

Dependencies and Data Sources

When determining the scope of the Sybase database migration, there are many interdependencies that you must consider. Review the server, database, application, and job and scripts questionnaires to help determine the strategy that you will use to migrate the targeted Sybase databases and their supporting client applications.

You must also determine whether there are any data sources that supply information to the Sybase database that is targeted for migration. The job and scripts questionnaire results should provide information about these data sources.

UNIX Scripts

UNIX system administrators and Sybase database administrators typically use scripting languages, such as Perl, the Bourne, Korn, and C shells, to provide administrative support for databases and applications. Shell scripts are often scheduled for execution by the UNIX cron utility to perform some type of batch processing, data manipulation, data loading, or maintenance task. In many cases, these scripts use one of the Sybase utility programs, such as isql or bcp, or one of the Perl extensions, such as DBI or Sybperl, to connect to the database server.

For example, Sybase database administrators might write a Korn script that uses isql to execute a Sybase batch script that performs a database consistency check on a database.

You must identify all of the scripts that are used with the Sybase databases that you are migrating and evaluate them to determine the strategies that you will have to use to migrate them to SQL Server. The job and scripts questionnaire provides the names of the databases that each script uses so that you can identify the scripts associated with the databases that you are migrating.

The following options are available for migrating UNIX scripts to operate against SQL Server running under Windows:

Rewrite the scripts using an appropriate Microsoft technology. Typically, this will mean using Windows Scripting Host (WSH) and Microsoft® Visual Basic®, Scripting Edition (VBScript) to convert the batch jobs. Further information about WSH is available at https://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/wsconwhatiswsh.asp.
Migrate the scripts with SFU 3.5, enabling you to run UNIX scripts on the Windows platform. Convert the scripts to use appropriate replacements for the Sybase utility programs which are not available under SFU: FreeTDS provides tsql and freebcp as open source replacements for Sybase isql and bcp; Perl, and the DBI interface (through DBD::Sybase) can be configured to connect to SQL Server through FreeTDS instead of Sybase. You may also use the older and less well supported Sybperl modules, which can also work with FreeTDS.
Perl scripts may run under ActiveState’s port of Perl to Windows.

Security

You must determine which of the available security strategies best implements your existing security policy and provides at least equivalent support to that of the Sybase database that you are migrating. Data collected from the server, database, and application questionnaires provide information about the security model currently in place in your production environment and will help you determine the impact of the migration on your enterprise security model.

SQL Server 2000 security options are based on the Windows Server 2003 security model. Before implementing a new security scheme, you should have a clear understanding of the concepts of domains, global groups, local groups, and user accounts as they apply in the context of Windows Server 2003 security with the Active Directory® directory service.

You should also consider the following security issues:

Security certification. Business needs may impose a requirement that the migrated environment be certifiable to a particular level. Your design should ensure that the overall solution is certifiable to the required level. SQL Server 2000 itself has been evaluated and has met the C2 security certification for the evaluated configuration from the United States Government. For more information about the configuration for a C2-compliant system, see https://www.microsoft.com/technet/archive/security/news/c2eval.mspx.

You may also consider the role of Certified Information Systems Security Professionals (CISSP), a widely accepted qualification for security consultants, in formulating and correctly implementing an appropriate security policy.
Transitivity of authentication. Some environments require only a single authentication operation between a client and the database server; other environments may implement multiple tiers of clients, application servers, and database servers such that a single transaction requires authentication to flow between more than a single pair of systems. Your design must provide support for the required degree of transitivity of authentication. Kerberos is the primary authentication mechanism on Windows Server 2003 networks. Delegation is the capability to pass security credentials across multiple computers and applications. With each "hop" between computers, the user’s security credentials are preserved. SQL Server 2000 fully supports Kerberos, including the capability to accept delegated Kerberos tickets and then delegate these tickets further using Windows Server 2003 domain controllers and Active Directory. This affects remote stored procedures as well as distributed queries. For more information about Windows Server 2003 security, see https://www.microsoft.com/windowsserver2003/technologies/security/default.mspx.
Virus protection. Principles of sound security dictate that all systems, including those not exposed directly to the Internet, should be fully secured. This includes safeguarding database servers from potential viruses. Your design should provide for virus protection of the SQL Server system as well as protection from more traditional security attacks.

Various third-party Web sites can also be helpful when planning your security model and its affect on your policy.

Authentication Modes

SQL Server 2000 offers two authentication modes for secure access:

Windows Authentication
Mixed Mode authentication

Authentication applies to the SQL Server instance instead of individual databases — all databases on the same SQL Server instance must use the same authentication mode. The following sections provide a brief discussion of each mode. For in-depth information about SQL Server security, refer to Microsoft SQL Server 2000 SP3 Security Features and Best Practices at: https://www.microsoft.com/sql/techinfo/administration/2000/security/securityWP.asp.

Windows Authentication Mode

The recommended practice is to use Windows Authentication mode. This is the default authentication mode in SQL Server 2000. In this mode, SQL Server relies solely on Windows to authenticate a user. Windows security identifiers (SIDs) track Windows-authenticated logons and allow the database administrator to grant logon access directly to Windows users or groups.

Note Login security integration operates with all supported SQL Server network protocols.

Mixed Mode Authentication

In Mixed Mode, users can be authenticated either by the Windows authentication process or by SQL Server authentication. In SQL Server authentication, user names and password pairs are maintained within system tables in the SQL Server master database. If the client is unable to use a standard Windows domain logon (for example, if the client computer is not running Windows), SQL Server requires a user name and password pair and compares this pair against those stored in its system tables. Connections that rely on user name and password pairs are known as nontrusted connections.

Physical Design Components

The physical design for a Sybase to SQL Server database migration project must be based on the following components:

Hardware used to host the database
Operating system required to support the database
Network protocols needed by client applications to communicate with the database
Method for installing SQL Server 2000
Mechanisms and supporting software required by SQL Server to interact with any third-party data sources
Tools to use to perform the migration

The following sections describe these items in more detail

Database Server Hardware

You must determine what type of hardware to use to support the SQL Server database after migration. You can review the results from the server questionnaire to discover the type of hardware that is supporting the existing Sybase database as a starting point. After you have identified the type and configuration of existing hardware, you can map it to an appropriately-sized Windows platform. At this stage, you might also consider soliciting help and guidance from a hardware vendor to size a compatible Windows platform. Remember that a highly-available, high-performance database will require multiple computers, large volumes of memory, and many disks.

You also must consider that you will need:

A development server on which to perform the initial migrations
A quality assurance (QA) server on which to perform most of the testing
Knowledge of whether the migrated database application requires clustering or any other high availability capabilities
Servers for any nonproduction operations support environments (such as training)

Chapter 6, "Planning Phase: Building the Development and Test Environments," provides further details on these subjects.

All hardware used should be certified to support Windows Server 2003. For more information, see https://www.microsoft.com/windows/catalog/server/default.aspx?subID=22&xslt=cataloghome&pgn=F48EF880-9FBB-4423-B6E4-B73CD5DBF4BF.

Operating System

You should determine which version of the Windows operating system to use. Review the server and database questionnaires to help determine the operating system that meets the requirements of the migrated database. This guide recommends Windows Server 2003 as a baseline Operating System, but do you need Standard Edition, Enterprise Edition, or Datacenter Edition? See https://www.microsoft.com/windowsserver2003/evaluation/overview/default.mspx for more details.

It is important to consider how the operating system supports resources such as multiple CPUs, memory and shared memory, disk storage, and availability.

Network Protocols

SQL Server supports communications using a number of different network protocols, including TCP/IP, Named Pipes, NWLink IPX/SPX, AppleTalk ADSP, and Banyan Vines. Named Pipes are used predominantly by client applications running under Windows across the local area network.

Note Do not confuse the Named Pipes protocol under Windows with named pipes created in the UNIX file system using the mknod command.

TCP/IP sockets supports local and long distance clients; SQL Server can be configured to listen to requests routed through a remote proxy server. TCP/IP is the most commonly used protocol because it is the standard protocol of the Internet. TCP/IP is also the default protocol used by most Sybase client applications running under UNIX.

Note Client applications redirected to SQL Server through FreeTDS use TCP/IP.

SQL Server allows communications on all protocols to be encrypted, at the cost of the additional processing required.

SQL Server 2000 Enterprise Edition provides support for System Area Network (SAN) protocols using the Virtual Interface Architecture (VIA). SANs are used to build high throughput, highly-available database clusters.

SQL Server additionally has a Multiprotocol network library that uses the Windows remote procedure call (RPC) mechanism to communicate over TCP/IP, Named Pipes, and NWLink IPX/SPX.

You should examine the requirements of client applications to ascertain which network protocols they use. By default, the Named Pipes and TCP/IP protocols are enabled automatically with SQL Server. If you have client applications that communicate using any of the other protocols, you must enable these protocols manually and ensure that you have the appropriate operating system network drivers installed

Note Do not enable any protocol you do not currently use; this is a security vulnerability.

SQL Server

You should plan your installation of SQL Server carefully, taking into account factors such as the need for clustering and high availability.

For detailed information on planning a SQL Server installation, see the SQL Server Plan page at https://www.microsoft.com/technet/prodtechnol/sql/default.mspx.

For information on SQL Server Capacity Planning, see the following link: https://www.microsoft.com/technet/prodtechnol/sql/2000/plan/sql2kcon.mspx.

Requirements for Heterogeneous Environments

You must determine whether the migrated database requires interaction with other non-Microsoft platforms. The database questionnaire results provide information to help you determine the type of host integration that you must provide.

Microsoft Host Integration Server 2000 provides comprehensive bidirectional services for integrating Windows with existing systems. For example, Host Integration Server contains an Open Database Connectivity (ODBC) Driver and OLE-DB Provider for DB2. These can connect to DB2 on OS/390, AS/400, AIX, and Windows, through the Systems Network Architecture (SNA) protocol and Transmission Control Protocol/Internet Protocol (TCP/IP). For more information about Host Integration Server, see https://www.microsoft.com/hiserver/default.asp.

Tools to Use

You should use the following tools in your solution:

Microsoft Windows Services for UNIX 3.5 (SFU)
The Perl scripting language installed on UNIX or Windows/SFU platform development and test environment workstations
DBI and DBD::Sybase, the Perl modules for connecting Perl Scripts to Sybase databases. The Sybperl module may also be used if you have legacy applications that use it.

The Physical Design Diagram

You should be able to draw a picture of your physical design that maps to your logical design model. The physical design should show all of the physical components of the solution and how they nest or connect.

The diagram in Figure 4.2 is an example of physical design:

Figure 4.2 Sybase database migration physical design

Validating the Technology

It is important to establish the feasibility of your chosen migration strategy, tool selection, and target platform. The following sections discuss how to establish feasibility.

Technical Proof of Concept

You should consider conducting 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 your approach and provide useful experience when using the various tools you have selected.

Baselining the Environment

After you identify the Sybase databases that you will migrate, you should gather baseline performance statistics from the UNIX operating system and the Sybase database server. Usually, the Sybase database administrator works with the UNIX system administrator to capture performance statistics to create an application performance profile. After you migrate the Sybase database to the SQL Server platform, you can create a second application performance profile and compare the two against one another. You should capture the following baseline statistics:

CPU
Disk I/0
System memory usage
Number of database user connections
Database memory usage
Procedure cache usage
Data cache usage
Number of transactions per second

Note Many of these statistics can be obtained using the Sybase monitor server if it is installed and operational on the Sybase server.

Interim Milestone: Technology Validation Complete

At this stage, you have verified that the mechanisms and tools to be used for migrating the Sybase database to SQL Server will operate as expected, and that it is feasible to migrate the selected databases.

Updating the Risk Assessment

The process of creating the solution design is likely to reveal project risks that were not yet apparent when the team performed its initial assessment of risks. It should also enable the team to specify risks more precisely, based on the information that has been gathered and the decisions made during Planning. This, in turn, will enable the risk owners to update their mitigation and contingency plans. The contribution of proactive risk management to the project's success in such a complex project as a Sybase database migration cannot be overemphasized. The team should revisit and update the risk list and management plans on an ongoing basis. At a minimum, this reassessment should occur at the end of each phase.

Creating the Functional Specification for the Solution

If available, review the original functional specification for your current system to develop a clear understanding of the business problem that the database was originally designed to solve and how the features of that solution were implemented.

It is not uncommon for applications to grow “organically” over their life, and the current system may bear little resemblance to that in the original functional specification. There may be a collection of updated functional specifications of varying degrees of quality to deal with. If you do not have an adequate functional specification for the existing system, you should produce one as a part of this step.

Note If the original functional specification is not available or inadequately describes the current environment, this should be identified as a risk in the Risk Assessment document.

Sybase to SQL Server migrations are not an exact science. From a technical standpoint, the two products have some similar features, yet they also have some key distinctions. For more information about the differences between Sybase and SQL Server, see Appendix B, "Architectural Differences."

When you encounter a Sybase feature that is implemented differently in SQL Server, you should document the deviation. For example, some UNIX Sybase database administrators implement remote procedure calls (RPCs). To do so, they configure Sybase servers to communicate with a remote server and invoke a remote stored procedure. SQL Server handles this task differently by implementing linked servers for remote stored procedure calls. Note, however, that the new functional specification should not deviate from the original functional specification except in those areas where the migration team chooses to solve a technology challenge differently.

The Solution Feature Set

You should migrate the existing Sybase databases in their entirety. The solution feature set should be identical to the feature set that the current environment provides. In some instances, business requirements dictate that the migrated system must provide additional services beyond those of the existing system. Doing so increases project risk and expense, especially with respect to testing. If new features or capabilities are required, you should not accommodate them during development of the migration solution, but leave them to a subsequent development project.

Note Functional enhancements are not recommended during the migration project itself; they should be considered as a post-migration step. Changing the functionality will make it substantially harder to prove that the migrated system works as well as the old system, and greatly increases the risk for the project as a whole.

Interim Milestone: Functional Specification Baselined

At this point, you have produced a detailed functional specification that describes which databases you are going to migrate, and how you are going to migrate them. The functional specification should also define the baseline capabilities and performance that you can use to evaluate the migrated databases.

Download

Get the Solution Guide for Sybase/UNIX to SQL Server 2000: Database Engine Migration and Application Interoperation Guide

Update Notifications

Feedback

Send us your comments or suggestions