Suppose you have a MySQL database running on a hosted VM or local environment. You need to copy data from each table to Google Cloud MySQL. Some of the tables are quite large.
Here are the few steps you need to consider:
Pre-Requisites |
Connection to Source & Target – JDBC driver & connection settings |
User access and permissions |
Identify tables and number of records in the source database |
Exclude tables which are too big or not needed |
Checks |
Check source database is accessible |
Cross check schema in both systems and any field mappings or transformations requirement |
Data |
Reconciliation of number of records |
Comparing values of all fields for few records (Source and target) |
Steps |
Initial data – copy data from source to target |
Get maximum value of surrogate key of target table |
Copy records records from source table which are greater than max value of surrogate key in the target table |
Load the latest data into the target |
During load turn off referential constraints |
After successful load: |
Number of entries at target table should match with the table in source |
Cross check the values of few entries of the table at target with source – Values should match |
IMPORTANT POINTS / QUESTIONS: |
Negative testing |
Tools selection for ETL and scheduling |
What will happen when the connection between any two systems is failed? e.g. Source to Target what will happen? Any error code is expected? What is business expectations on these occasions? |
During load if insert step fails, what will happen? Any error code? What is business expectations on these occasions? |
ACCEPTANCE CRITERIA |
How a successful data migration will look like? |
Best practices and strategy for cloud data migration |