A 2017 Data Migration Research study, showed that, according to 61% of the respondents, on average three or more legacy systems were involved in some form of data migration. This suggests that a large number of companies are engaging in data migration. In the same study, it was reported that 69% of migrated projects were successful, so what about the other 31%? The big question then is: how much of this outcome is a result of a lack of good testing practices?
Lack of testing was actually listed as one of the reasons that data migration projects fail. Advancing with any data migration is a critical and high risk undertaking. So based on our experiences, we shine a light on five (5) of the most important things to consider when conducting effective database migration testing for project success.Â
Fail to Plan, Plan to Fail
You know what they say, “Failing to plan is planning to fail”. This especially holds true when it comes to planning for a huge undertaking such as a data migration activity. Performing this test planning will uncover some of the answers to some crucial questions. Therefore, engaging with key stakeholders and the team that will be involved in the migration should be a top priority. Another aspect of planning is creating a test plan. It will highlight things such as: what is the timeline and testing efforts for the testing activities, what are the possible risks involved in this type of project, the rollback procedures, who will be involved in this etc. Claire demonstrates in her article “The One Page Test Plan” how we can construct a simple but valuable test plan.
Know your Environment
The availability and provisioning of the test environments and suitable tools are definitely factors for consideration. It is in the test environment that you will be performing a thorough review of the migration activities. This will include the legacy (the existing database that is being migrated) and the destination (the newly migrated database) environments. Therefore it is crucial to have both these databases running in parallel so that if any clarification is needed we can always revert to the legacy to check anything that may need checking. It is best to test with a replica of the production environment data as well. Using data from the real world helps with test coverage in detecting worst case scenarios, which could be missed when using limited amounts of fictional data within a regular test environment. Therefore when considering the test environment, a copy of production data can be crucial.
Now you also have to think about the tools that you will be using to complete some of the checks involved in a data migration. The test tools we used within our data migration project were Aqua Data Studio and Apache JMeter (we will discuss these further on in the article).Â
How are we going to test it?
Knowing the types of tests to perform is the next important aspect of the database migration process. Some of the main testing that might need to be performed, that may change based on your own context, is:
Schema comparison test on the legacy and the destination database
A database schema represents how the data will logically exists in the database management system. A schema comparison should be done to ensure that the legacy and destination databases have similar syntaxes. For example, during one migration project I was involved in, we learnt that PostgreSQL and IBM DB2 databases format their dates differently and this difference in syntax required code changes for the application. We used the Aqua Data Studio Schema Compare Tool for our database migration project as comparing schemas manually for hundreds of database tables would be time consuming and more prone to errors. The use of a schema comparison tool will definitely make life easier in comparing and ensuring that the destination schema is logically the same as the legacy one. This helps ensure that the data will be stored in the way that was intended and there are no gaps or missing data when using the source database. So ensure you do your due diligence and compare your schemas. Bottom line - Ensure you compare!
Data Level Validation Testing
One of the things we learnt when conducting migration testing was that some of the data types and functions of DB2 do not directly match those of PostgreSQL. A list of some of the differences can be found here. This is quite common with other DBMS as well, hence checks for data mapping is important for the testing and database teams to do.
Another check you might want to do is row count check. This is to check that the number of rows in all tables of the legacy and target matches correspondingly. To go a bit further, also verify that the data in both databases are the same. This will analyze if the data integrity is maintained and ensure that there is complete accuracy between the source and destination data.
Application Level Validation Testing
It can be essential to conduct end-to-end testing of the system(s) related to the database migration. However, since testing is inexhaustible it is in our best interest to employ a risk-based approach to our testing strategies. In doing this, we ensure that adequate functional testing of the major aspects of the system is completed. The critical areas that would have been affected by any SQL code change after the migration should be the main focus. Also, pay attention to integration points within the systems, this is to verify that the new database connection is provided to all the components of the application.Â
Non-functional Testing
Some level of non-functional testing should be executed on the migrated data. This can involve (but not limited to) load testing and database security testing. This is necessary to determine if the performance of the target database is the same or even better after the migration. We also want to examine the security measures in place to prevent malicious database attacks on the target data. Some of the things we would want to focus on are:
-Â Â Â Â Identify the main and most frequent SQL statements and procedures used by the database. Then perform load tests that utilise them using a tool like Apache JMeter.
-Â Â Â Â When measuring the results of a load test, you must first establish a baseline for comparison. The baseline might be the on past performance analytics on the legacy database. Another appropriate baseline could be a threshold that the team agrees to based on industry standards.
-Â Â Â Â Execute database vulnerability scans utilising scanner tools.
-Â Â Â Â Checking with the vendors that the database server is patched with the most recent update.
The afterparty support
The team should be ready to support post migration activities that can include liaising with clients after the rollout of changes to ensure that the most critical aspects are working as expected. Some organizations may choose to test the changes in production but ensure that this is done properly and the risks are managed. Monitoring the performance and speed of the application and database may also be done. Another thing to do after the migration is to surveil application and database logs to see if any problems are detected. If there are showstopper errors that prevent functionalities of the system, you might want to consider the rollback strategy that you would have highlighted in your planning (hence another reason to plan carefully).
Don’t forget the documentation!
For us documentation is something that many people overlook, not appreciating the value it can bring. Good documentation serves as a means to record key activities and decisions taken in the lifetime of a project. These records can be used to assist in future projects of a similar nature. It also has the ability to get new people on the project up to speed sooner rather than later. Those are just a few of the reasons we believe it’s best to have the proper documentation done along every step of the way during the process: the test cases, the database test scripts, the test results, the architecture design document etc. When you create your documents at every step, it promotes living and breathing documents that can and will be used during and beyond its initial design. It is also important to highlight lessons learnt and recommendations coming out of a data migration project so that they can be taken into consideration for future projects.
Conclusion
As you go forward with getting ready to do a migration, take it in strides. Planning is a great way to state your risks, and familiarizing yourself with the tools you’ll be using. Ensure you are clear on the types of testing that you will be doing and how each will benefit you and get you one step closer to a successful migration. Your post migration steps are also very necessary as you need to have your team ready to monitor and test critical components affected by the migration activities. Additionally, remember to document your process so that you may make references to the project in the future. Finally, acknowledge your plans are just that, plans! Life is unpredictable and that applies to your data migration project as well. There may be instances or events that are not covered in the plan. It is important, however, that you and your team are flexible and are willing to navigate the changes as they come. Having a retrospective can help the team to learn from mistakes made and allow for growth.Â
With this information we hope that you will go forth and conquer your database migration project.
