http://www.microsoft.com/TechNet/boes/bo/sql/technote/sq201.htm (PC Press Internet CD, 03/1996)
| Updated: March 13,1996 |  Go To TechNet Home Page |
Introduction
This paper focuses on the migration of applications from ORACLE 7 and SYBASE System 10 database servers to Microsoft® SQL Server™, client-server database management system. Topics covered in this session include:
The first section presents a detailed exposition of the issues that plague application migration. A solution for application migration is presented in the form of an applications architecture that is both robust and portable. In the second section, we take a close look at the characteristics of ORACLE 7 and SYBASE System 10 databases and to what extent they match or differ from the Microsoft SQL Server characteristics. In the final section, we examine the challenges of migrating data from an ORACLE 7 or a SYBASE System 10 database to a Microsoft SQL Server database.
Return to the Top Application Migration
The section on application migration includes a detailed discussion of the following topics:
Each of these topics will be presented in detail in the following sub-sections.
Application Migration Challenges
Serious challenges face any effort that is undertaking to migrate an application from an ORACLE 7 or SYBASE System 10 backend to a Microsoft SQL Server. The challenges can be grouped into the following categories:
Each of these categories will be presented in detail below. In this section, we will also present a migration strategy that is composed of the following steps:
Poor Client / Server Application Design
The analysis and design of Client-Server applications differ from traditional mainframe systems and PC-based applications. Frequently, in Client-Server systems it is necessary to include an additional stage to the System Development Life-Cycle (SDLC). This stage is known as the Microsoft Solutions Framework. In this stage, as depicted in Figure 1, the design of the architecture for data, application, and technology is explicitly evaluated and choices are made.
Figure 1: Microsoft Solutions Architecture
The goals for the solutions framework are:
A complete treatment of the Microsoft Solutions Framework and what it entails is beyond the scope of this presentation. It is necessary, however, to bring into the presentation two factors that are a fundamental part of the migration strategy: The overall application architecture and the choice of API (part of the technology architecture)
The overall application architecture, as depicted in Figure 2, breaks up an application into three distinctive layers: user services, business services, and data services. The benefits from this separation include:
Figure 2: Application Architecture
Different Transaction Models and Locking Schemes
Table 1 summarizes the characteristics of the transaction models of ORACLE 7, SYBASE System 10 and Microsoft SQL Server. The table shows that in terms of migrating an application from ORACLE 7, changes have to be made to reflect different syntax and different mechanisms for transactions. Migrating an application from SYBASE System 10 to Microsoft SQL Server, on the other hand is relatively straightforward.
Table 1: Transaction Models
Table 2 presents the locking mechanisms available in ORACLE 7, SYBASE System 10, and Microsoft SQL Server and the differences between them. The table also shows that while there is a high degree of compatibility between System 10 and SQL Server, the compatibility between ORACLE 7 and SQL Server is low.
Table 2: Locking Mechanism
Proprietary APIs
Database software vendors have always been inclined to develop their own proprietary application programming interface (API) to their database engine. Oracle, SYBASE, and Microsoft are no exception. Figure 3 represents the API architecture of the different vendors. Its fairly obvious that using vendor- specific APIs, the business services layer of the application has to be heavily modified in the migration of an application from one database engine to another.
Figure 3: Vendor APIs
Table 3 presents a comparison of the APIs from Oracle, SYBASE, and Microsoft. Based on the findings, SYBASE and Microsoft APIs, as can be expected, are highly compatible, whereas Oracle and Microsoft APIs are not.
Table 3: API Comparison
To avoid the liability of vendor specific API, application developers should focus on using a new set of APIs offered by Microsoft. The Open Database Connectivity (ODBC) is an merging Microsoft Windows® standard for nonvendor- specific database APIs. ODBC is one of the cornerstones of a larger effort for vendor independent APIs for Microsoft Windows known as the Windows Open Systems Architecture (WOSA). Figure 4 illustrates the architecture of ODBC.
Figure 4: ODBC 2.0 Architecture
As a further hedge against the peculiarities of vendor specific APIs and as an important component of a migration strategy, developers should consider using the Microsoft Open Data Services toolkit. As illustrated in Figure 5, this toolkit allows all the backend database engine servers, Oracle, SYBASE, and Microsoft, to exist in the "shadow" of Open Data Services (ODS). Clients talk through ODBC to ODS, which in turn talks through ODBC to the different backend servers. ODS is, in essence, the client manager of SQL Server packaged as a separate library. Such a configuration offers several advantages:
Figure 5: Open Data Services (ODS)
Network Transport
A different picture appears when a comparison is drawn between the network transports of the three companies, see table 4. Conversion from Oracle and SYBASE to Microsoft is easy.
Table 4: Transport Comparison
Tool Selection
Before we can discuss what application development tools should be used for ease of migration, we need to reflect on the cost versus scalability of tools depending on whether they are off-the-shelf, vendor specific, or generic 3GL or 4GL. Its clear from Figure 6 that while 3GL-and 4GL- tools tend to scale better with the complexity of the application, they are also more costly for development and maintenance.
Figure 6: Scalability and Cost of Tools
The best solution for application developers, as presented in table 5, is to use either low-level generic tools such as Microsoft Access database and Microsoft Excel spreadsheet or 3GLs and 4GLs with ODBC as opposed to using Vendor- specific APIs or tools that are very difficult to migrate.
Table 5: Tool Comparison
In summary, we have shown in this section that it is quite feasible to migrate Oracle and SYBASE applications to Microsoft SQL Server 6.0 provided that they have been developed using the correct application framework, have used ODBC and ODS, and have been written with the proper tools.
Return to the Top Database Migration
Once the application migration issue is resolved, developers can focus on the challenges surrounding database migration. Unfortunately, the list of challenges, as presented below, is not small and includes:
In the following section, we will deal with each of these challenges in detail. The challenges are being presented in the order in which we think an application developer should tackle them.
Space Management
Table 6 presents the differences between the space management implementation of Oracle, SYBASE. and Microsoft. While both Microsoft and SYBASE use a similar mechanism for storage allocation, a device, Oracle implements a different mechanism, a tablespace.
Table 6: Space Management
Despite the differences or similarities, in both cases, regarding Oracle and SYBASE, it is necessary to set up new devices under Microsoft SQL Server to migrate the database schema.
Datatypes
In terms of datatypes, SYBASE and Microsoft offer, as shown in Table 7, similar datatypes, whereas Oracle offers different types, which have to be manually mapped. Some difficulty may arise in the mapping of Oracle varchar. SQL Server varchar is limited to 255 bytes. Character information of length greater than 255 bytes is implemented as text in Microsoft SQL Server. Developers must exercise caution when migrating Oracle varchar types, because they should be mapped to either varchar or tex,t depending on their length.
Table 7: Datatypes
Rules and Defaults
When it comes to the topic of migrating the objects rules and defaults life gets easier. As shown in Table 8, rules and defaults only exist in SYBASE and Microsoft and their implementation is the same.
Table 8: Rules and Defaults
Tables
The migration picture becomes more challenging when the migration of table structures is addressed. Despite their claims for American National Standards Institute (ANSI) compatibility, there are enough syntactical differences between the Oracle implementation, on the one hand, and the Microsoft or SYBASE implementation on the other, to warrant caution when table structures are migrated. Table 9 reflects some of the syntactic differences and, in particular, the use of the ON CASCADE clause in ORACLE 7, which at this point must be converted to a trigger for Microsoft SQL Server.
Table 9: Tables
Indexes
In migrating indexes, the developer will be challenged by not only the syntax but also the indexing mechanism. While Microsoft and SYBASE both implement a regular and clustered index they do not incorporate a hashing or sequence indexing mechanism. If a clustered index has been defined for a table, hash or sequence indexes can be defined as regular (no-clustered) indexes. Table 10 also shows the syntactical changes that have to be made to migrate the Oracle database schema to Microsoft SQL Server.
Table 10: Indexes
Stored Procedures and Triggers
All three database vendors, Oracle, SYBASE, and Microsoft, as reflected in Table 11, now offer stored procedures and triggers. The language used in each case, however, differs. Oracle uses PL/SQL while Microsoft and SYBASE use Transact-SQL. In the next section, we will review in detail the semantic differences between the languages. In this section we will focus our attention on the implementation of stored procedures and triggers. Both Microsoft and SYBASE view stored procedures as independent functions that can be invoked with input and output parameters and return a value. Oracle, on the other hand , allows the definition of functions and procedures that are assembled into packages. As far as their use, however, they are very similar, and it should not be too difficult to convert Oracle packages to Microsoft. As far as triggers are concerned, there is a major difference. In the Microsoft and SYBASE implementation of a trigger, it is always invoked after the action on the table has taken place. In Oracle's implementation, a developer can specify whether a trigger should be invoked before or after. It is highly recommended for ease of migration to avoid the use of triggers with a BEFORE clause.
Table 11: Stored Procedures and Triggers
Control of Flow
The control of flow language of the three database vendors is very similar. There are, however, as presented in Table 12, some slight nuances that would require a careful rewrite of the Oracle statements to Microsoft SQL Server, particularly in the implementation of loops with FOR.
Table 12: Control of Flow
Optimizer Hints
While it may be possible to map the use of optimizer hints from Oracle and SYBASE to Microsoft SQL Server, it is highly advisable that a migrated application should be re-optimized in the target environment. There are significant improvements in the implementation of the optimizer for Microsoft SQL Server that would justify such an effort.
Table 13: Optimizer Hints
Cursors
There are several differences in the implementation of engine-based cursors between Oracle and Microsoft. The main syntactic difference is in the declaration of TYPE in Oracle that does not exist in Microsoft. I, however, a developer should follow the application migration recommendations and use ODBC as an API, then the differences in the engine-based cursors will be masked by ODBC.
Table 14: Cursors
Security
The main changes that have to be made to an Oracle database schema when it is migrated to Microsoft SQL Server is to alter the use of role to group. The granting and revoking of permissions is close enough that it should not be a major area of concern.
Table 15: Security
Data Integrity
Remember the days when Oracle published an advertisement in which they would emphasize the difference between their database engine and the SYBASE engine based on the number of lines it required to define integrity? Well, those days are over. Despite the fact that for strict ANSI compliance, Microsoft SQL Server did not have to implement declarative integrity, since it was only a recommended feature, it is now available. With SQL server 6.0, Microsoft provides a superset of data integrity capabilities. As reflected in Table 16 all the capabilities in ORACLE 7 and SYBASE System 10 are available and more.
Table 16: Data Integrity
Backup and Recovery
The final subsection deals with the issue of migration backup and recovery procedures from ORACLE 7 to Microsoft SQL Server 6.0. With the enhancements available in Microsoft SQL Server 6.0, it is highly recommended that the backup and restore procedures be revisited and redesigned. Features such as high speed parallel backups and single table should have a significant impact on the backup and retrieval policies.
Table 17: Backup and Recovery
This subsection concludes our discussion of the database schema migration. Although schema migration from Oracle is not a "slam dunk," it is by no means insurmountable. Following carefully thought out migration steps, each of the Oracle and SYBASE objects can be migrated successfully to a Microsoft SQL Server schema.
Return to the Top Data Migration
In this third and final section, we will address the challenges associated with migrating the data from an Oracle or SYBASE database to Microsoft SQL Server. As we will discuss in this section, there are several alternatives for fast and efficient migration of data. The issues addressed in this section include:
Each of these issues will be addressed in detail in the following subsections.
Data Formats
Converting data from one database engine to another must not only take into account the different data representation, but also that the data might be coming from a system in which the lower order byte is presented on a magnetic medium before the high order byte (little endian). Byte ordering and swapping is familiar to any developer moving data from one computer system to another.
Table 18: Data Formats
Datatype Conversion
Beyond the byte swapping issue we found that the easiest way of moving data between an Oracle and SYBASE database engine and Microsoft SQL Server is to export the data into ASCII format and use the Microsoft SQL Server Bulk Copy or Transfer Utility to import the data. For attaining maximum speed, it is recommended that the ASCII exports be done in fixed field formats.
Table 19: Datatype Conversion
Data Migration Tools
Both vendors, Oracle and SYBASE, provide tools for exporting data. SQL*PLUS from Oracle can do the job although the issues that are highlighted in Table 20 must be taken into account. To transfer data from a SYBASE System 10 database to a Microsoft SQL Server, it is possible to use the Microsoft Transfer Manager, which simplifies the task.
Table 20: Data Migration Tools
Using Access with Upsizing Wizard
Before discussing third-party software tools that could be of assistance in migrating the data, it is worthwhile to turn our focus to Microsoft Access. Microsoft provides an Upsizing Wizard for Access. The Upsizing Wizard is capable of migrating Microsoft Access tables and relationships to a Microsoft SQL Server schema. Once tables are defined in Microsoft SQL Server it is a trivial task to migrate data from a Microsoft Access table (residing on a Microsoft Access MDB file) to a Microsoft SQL Server table. So what is the catch? While it is trivial to import tables from Oracle to Microsoft Access, the relationships which are defined in the Oracle schema must be manually mapped in the Microsoft Access environment. Only after the relationships have been manually mapped should the Upsizing Wizard be invoked. While this approach looks very appealing it is probably best for small-to- medium sized databases. It is not a practical approach for large tables.
Table 21: Using Access with Upsizing Wizard
3rd Party Data Migration Tools
Several third party database administration and case tools can be used to facilitate the migration of the schema from an Oracle or SYBASE backend to a Microsoft SQL Server implementation. Table 22 lists some of these tools. Obviously, if the schema has been designed using a tool such as ERWIN, then it would be fairly easy to generate a new schema for Microsoft SQL Server from the same definition.
Table 22: 3rd Party Migration Tools
In summary, migrating data from an ORACLE 7 or SYBASE System 10 server, while tedious, does not seem to be insurmountable.
Return to the Top Conclusion
This paper has presented a migration strategy for ORACLE 7 and SYBASE System 10 applications to Microsoft SQL Server. The strategy consists of adopting an application framework that is amenable to migration and using the "correct" development and migration tools. The migration strategy is based on developing a layered architecture for the candidate application. Furthermore, the strategy uses ODBC as a database API for the business services and ODS as component of the data services. As every experienced developer knows, the use of off-the-shelf components as part of the migration strategy enhances the probability of success.
We have also proposed a process for migrating the database schema. Each object and component of the database schema is addressed in detail.
Finally, we have presented the tools and techniques for migrating the data itself.
As for the future, it is always "greener". In the months and years to come, we should be prepared to see several new developments that will facilitate the migration process. Some of the possible developments include:
Return to the Top References
Managing the Migration to Client-Server Architecture
Analysis and Design of Client-Server Applications
Solutions Development Discipline
© 1995 Microsoft Corporation.
THESE MATERIALS ARE PROVIDED "AS-IS," FOR INFORMATIONAL PURPOSES ONLY.
NEITHER MICROSOFT NOR ITS SUPPLIERS MAKES ANY WARRANTY, EXPRESS OR IMPLIED WITH RESPECT TO THE CONTENT OF THESE MATERIALS OR THE ACCURACY OF ANY INFORMATION CONTAINED HEREIN, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW EXCLUSIONS OF IMPLIED WARRANTIES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU.
NEITHER MICROSOFT NOR ITS SUPPLIERS SHALL HAVE ANY LIABILITY FOR ANY DAMAGES WHATSOEVER INCLUDING CONSEQUENTIAL INCIDENTAL, DIRECT, INDIRECT, SPECIAL, AND LOSS PROFITS. BECAUSE SOME STATES/JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF CONSEQUENTIAL OR INCIDENTAL DAMAGES, THE ABOVE LIMITATION MAY NOT APPLY TO YOU. IN ANY EVENT, MICROSOFT'S AND ITS SUPPLIERS' ENTIRE LIABILITY IN ANY MANNER ARISING OUT OF THESE MATERIALS, WHETHER BY TORT, CONTRACT, OR OTHERWISE SHALL NOT EXCEED THE SUGGESTED RETAIL PRICE OF THESE MATERIALS.
 |
Click Here to Search TechNet Web Contents | TechNet CD Overview | |
Microsoft TechNet Credit Card Order Form At this time we can only support electronic orders in the US and Canada. International ordering information. |
| Go To TechNet Home Page |
©1996 Microsoft Corporation |  Go To Microsoft Home Page |