Many often you are required to perform database migration testing. The task isn’t easy but if you follow some instructions one by one then the task will be easy for you. In my professional experience I’ve executed three types of database migration testing. These are MySQL to SQL Express, pgSQL to SQL Server and SQL Server to pgSQL.
When you will execute the task then you will have to face many db specific challenges, but here I’m presenting only the cases that are very to common to every database migration task.
Following cases should be considered in database migration task
- Unless specified, the migrated database should have equal number of tables, views, stored procedures, user defined data types,… and equal number of columns for each of the mentioned objects.
- Unless specified, the migrated database should have same name and same letter case for the names of tables, views, stored procedures, user defined data types and columns.
- Unless specified, the column/parameter order in tables, views, stored procedures and user defined data types should be same as original database.
- The type and size including precision of every column should be same as original database.
- If migrated database doesn’t have exact type as original database then it should use the type which is most similar to original database. Special consideration should be taken for numeric, bit, binary, image and date/time [with/without time zone] related data types.
- The constraints Primary Key, Foreign Key, Unique Key, Default, Check, NULL and NOT NULL on column(s) of table(s) in migrated database should be same as original database.
- Unless specified, the constraint names on every table should be same as original database.
- The actions of CASECADE DELETE and CASCADE UPDATE of foreign key(s) should be same as original database.
- Unless there has any limitation in migrated database, then the migrated data should be same as the data of original database.