Database and Application Migration Testing

Testing validates database and application migration, and the best results are achieved when using both dynamic and static testing approaches.

Dynamic Testing Static Testing
Running the software Analyzing the source code without execution
Validates functional requirements (features) Verifies the source code, prevents potential problems (checklist)
Provides tangible results (features work or not) Code quality assessment only, no guarantee of execution
Tests cover features Tests cover coding patterns
Test coverage depends on the number of features and diversity of use cases Test coverage depends on the number and diversity of coding patterns
Validates only parts of the code that are actually executed Verifies only known issues in the code
Validates output based on valid or invalid input Verifies internal structures, can find standard violations, not optimized code, use of deprecated or not recommended features that do not directly reflect on the output
Single test typically checks only one feature or use case Single test checks one type of potential problems, but in the entire code
Mostly expensive to set up and run, and may require compilation, installation and configuration of the dependent software Low cost to set up and run, does not require neither compilation nor installation of any dependent software
May require considerable time (days, weeks, months) to run all tests Does not require significant time to run all tests

Dynamic Testing

Dynamic testing means running the converted software based on specific test cases and validating the results.

Although dynamic testing provides the most tangible results (you can clearly see whether features work or not), generally it takes considerable time and effort to achieve high test coverage.

Unit Testing and Concurrent use of both legacy and new software are the most popular approaches of dynamic testing of migration.

Unit Testing

Unit testing involves execution of converted queries, stored procedures, functions etc and comparing the output as well as table data in the source (legacy) and target (new) databases.

But using this approach you have to take into account the following:

  • Non-deterministic Results

In database-driven applications, the results of a query or procedure execution greatly depend not only on the input parameters, but also on the initial state of the data in the database.

Moreover, every procedure call typically changes the database, so the subsequent calls even with the same input parameters can produce different results.

For this reason, you need to develop special routines to restore the database state and to have consistent testing results.

  • Workflow Consistency

Applications execute SQL statements and procedures in a particular order, and their execution in a random order and without following a particular workflow during the testing may not produce meaningful results.

This means that the scope of unit testing can be quite limited, and may not allow you to run comprehensive functional tests.

  • Test Coverage

To achieve high test coverage you need to create unit tests for each procedure, and to use different input parameters to cover all branches of code.

Depending on the number of procedures, this can require significant effort and time as well as deep understanding of internal structures and logic.

Concurrent Use of Legacy and New Software

Probably the easiest way to check whether the migrated system works properly is to try the same features in the legacy and new applications and compare the results. You can compare the output visually in the applications, or you can compare the databases after you have performed some actions.

Since this approach tests end-user features, it provides the most meaningful test results. Also when automated dynamic testing is not used, it is easier to compare the legacy and new applications rather than use functional specifications (although the specifications, if they exist, can ensure more comprehensive testing).

Similar to unit testing, to achieve high test coverage, you need to test all application features and use as many different inputs as possible.

Even after the migration, to minimize migration risk, it is recommended to use both systems for some period of time until you are confident that the new database and application are stable.

Static Testing

Static testing means testing the migration without application execution by analyzing source code, finding various patterns that can potentially lead to run-time errors.

Statics testing can be extremely helpful. For example, you may have logical errors in SQL code such as:

  -- WHEN NULL works in Sybase correctly, but in SQL Server or Oracle i.e. this condition is never true, 
  -- although it is syntactically correct
 
  CASE col1 WHEN NULL THEN ...    
  col1 = NULL

With dynamic testing, you need to have a huge number of test-cases to catch such errors. But the creation of tests is time-consuming tasks, you need to analyze many input values, flow of control statements, have relevant data set and so on.

At the same time, using automated static testing, you can easily find such problems in the code. You do not need data, you do need to set up a run-time environment and so on.

Static testing does not replace dynamic testing, moreover it can offer huge benefits when used together with dynamic testing.

For example, performing dynamic testing by running an application feature, you may find an error in converted SQL or application code.

But the same error may exist in other parts of the application. Once found, static testing allows you to find out and report on all occurrences of incorrect code and reduce overall testing cost and effort.

Schema and Procedural SQL Migration Testing

Database migration testing consists of several phases, some of them are automated while others are performed manually:

  • Error Free Execution in Target Database (Automated)

After you have converted SQL scripts, you can create database objects (tables, procedures, triggers etc) or execute SQL scripts in the target database server. Its compiler can help you easily identify various syntax issues, if any.

Although this is the fastest and easiest way to find conversion problems, it is not comprehensive as it does not identify design and logical errors as well as errors that can appear only during run-time execution of stored procedures or functions (dynamic SQL execution i.e.)

  • Code Review by Migration Expert (Manually)

Assuming that the conversion was mainly performed by a tool, at this phase a migration expert reviews the source and converted code trying to identify potential design and logical issues in the conversion.

The expert can review the full code comparing the source and target code line by line and finding any issues remained unconverted or not properly converted.

Or if there are large number of lines in SQL scripts, the expert can review the critical, representative and the most challenging SQL and procedural SQL code only. He/she can use the assessment report to identify these areas.