Many developers have decided to migrate Oracle-based applications to Microsoft SQL Server. SQL Server provides a powerful client/server platform for delivering critical business applications efficiently across multiple environments. It combines the power, scalability, and manageability of the Microsoft Windows NT operating system with the reliability and advanced capabilities of high-end, client/server database management.
The application migration process may appear complicated. There are many architectural differences between each RDBMS. The words and terminology used to describe Oracle architecture often have completely different meanings in SQL Server. Additionally, both Oracle and SQL Server have made numerous proprietary extensions to the ANSI SQL standard.
From an application developer's perspective, Oracle and SQL Server appear to manage data in very similar ways. The internal differences between Oracle and SQL Server are significant, but if managed properly, have minimal impact on a migrated application. The internal differences have substantially more impact on a DBA or system administrator.
If your application is in the planning stage, you can reach many design decisions that make writing a multiple DBMS program quite easy. If your application is already built, you may need to consider many migration alternatives. This paper highlights these issues and alternatives and recommends methods in which you can achieve the results you want.
The most significant migration issue that confronts the developer is the implementation of the ANSI SQL language standard and the extensions that each DBMS has to offer. Some developers often decide to only use standard SQL language statements, preferring to keep their program code as generic as possible. Generally, this means restricting program code to the Entry level SQL of the ANSI-92 standard, which is implemented consistently across many database products, including Oracle and SQL Server.
This approach may produce unneeded complexity in the program code and can substantially affect program performance. For example, Oracle's DECODE function is a nonstandard SQL extension that is specific to Oracle. SQL Server's CASE function is a SQL extension that is part of the ANSI standard beyond Entry level and, therefore, not implemented in all database products.
Both the Oracle DECODE function and the SQL Server CASE function are capable of performing sophisticated conditional evaluation from within a query. If these functions are not used, the program itself is forced to perform the evaluation. This often requires that substantially more data be retrieved from the DBMS.
Procedural extensions to the SQL language can also cause difficulties. The Oracle PL/SQL and SQL Server Transact-SQL language are similar in function, but different in syntax. There is no exact symmetry between each DBMS and its procedural extensions. Consequently, you may decide not to use stored programs such as procedures and triggers. This is unfortunate because they can offer substantial performance and security benefits that cannot be duplicated in any other way.
The use of proprietary development interfaces introduces additional issues. The conversion of a program using the Oracle OCI (Oracle Call Interface) often requires a significant investment in resources. When developing a multiple DBMS application, it is recommended that you consider using the Open Database Connectivity (ODBC) interface.
The ODBC standard has been intentionally designed to work with numerous database management systems. ODBC provides a consistent application programming interface (API) that works with different databases through the services of a database-specific driver.
A consistent API means that the functions a program calls to make a connection, execute a command, and retrieve results are identical whether the program is talking to Oracle or SQL Server.
ODBC also defines a standardized SQL language, which is based on ANSI SQL and uses standard escape sequences to specify SQL functions that perform common tasks but have different syntax in different databases. The ODBC drivers are capable of automatically converting this ODBC syntax to either Oracle-native or to SQL Server-native SQL syntax without requiring the revision of any program code. In some situations, the best approach to take is to write one program and allow ODBC to perform the conversion process at run time.
ODBC is not a magical solution for achieving complete database independence, while getting full features and high performance from all databases. Different databases and third-party vendors offer varying levels of ODBC support. Some drivers just implement core API functions mapped on top of other interface libraries. Other drivers, such as the SQL Server driver, offer full Level 2 support in a native, high-performance driver.
If a program just sticks to the core ODBC API, it will likely forgo features and performance capabilities with some databases. Furthermore, not all native SQL extensions can be represented in ODBC escape sequences (for example, the Oracle DECODE and SQL Server CASE functions).
Additionally, it is common practice to write SQL statements to take the best advantage of the DBMS optimizer. The techniques and methods that work with Oracle do not necessarily work with SQL Server. The ODBC interface is not capable of translating performance-based query-writing techniques from one DBMS to another.
There is nothing in ODBC that prevents an application from using database-specific features and tuning for performance, but this requires the application to write some database-specific sections of code. The good news is that ODBC makes it easy to keep program structure and the majority of program code consistent across multiple databases.
To assist you in implementing a step-by-step migration from Oracle to SQL Server, this paper has been organized into the following sections:
Each section includes a basic overview of the relevant differences between Oracle and SQL Server. It also includes conversion considerations, SQL Server advantages, and multiple examples.