Application Testing - Oracle to SQL Server Migration

Application Testing Methodology and Process

When you migrate Oracle databases to SQL Server, in most cases you also have to convert embedded SQL statements in your applications written in Java, .NET, PowerBuilder, C/C++ or other languages.

Application testing is one of the most important phases of a migration project, and usually several testing approaches are applied to increase the testing coverage and quality:

  • Code Review
  • SQL Statements Testing
  • User Acceptance Testing (UAT)

Is Application Testing Required?

If you think that your application is very simple, and no testing required, see how even the simplest functions Oracle LENGTH and SQL Server LEN that return the length of a string behave differently.

Testing Scope and Goals

Migration testing is typically focused on the following areas:

  • SQL Testing

SQL Testing is used to make sure that the original Oracle and the converted SQL Server SQL statements bring the same results.

  • Performance Testing

Performance Testing is used to measure the execution time of Oracle and SQL Server queries, insert, update and delete statements.

Identify The Most Challenging Issues

Your application can contain a huge number of SQL queries that range from very simple to very complex. Some of them even do not require conversion at all, while others have to be completely rewritten when you migrate from Oracle to SQL Server.

It is critical to identify the most challenging SQL statements during the assessment, development or code review phase, and develop sophisticated routines for further testing of such statements using a large number of test cases with various input data.

Code Review - Design Testing

Code Review is performed by a migration architect or expert to see whether the migration guidelines and best practices defined at the Design and Planning Phase are actually implemented.

If you did not perform a comprehensive migration design, an experienced migration architect can validate the migration solutions you applied, and point to potential issues.

SQL Statements Testing

While during a Code Review you can easily identify the design issues, it is almost impossible to guarantee 100% testing coverage. Minor syntax errors, missed or mistyped database objects or identifiers, data type casting issues still may exist in the converted code.

SQL Statements Testing allows you to discover and resolve such problems. This type of testing is usually performed by a migration expert or SQL developers who extract SQL statements from the application and execute them in a SQL Query tool as standalone statements.

This kind of testing is highly effective to identify syntax errors in SQL statements.

You can extract SQL statements manually or use SQLExtract tool to automatically extract SQL statements from the application source code.

User Acceptance Testing (UAT)

User Acceptance Testing usually involves running the migrated database and applications, following various application use cases, and visually comparing the results that appear on the screens, in the reports etc.

SQLines Services

SQLines offers services to help you test database and application migrations from Oracle to Microsoft SQL Server. For more information, please Contact Us.