Data migration methods, steps, and experiences

Data migration methods, steps, and experiences
Data migration methods, steps, and experiences

In projects, we often encounter historical data migration problems after the system is completely replaced, to show respect for the historical work of the customer, not to mention the need to retain a lot of data.
So how to migrate historical data?

System Analysis:
1. Analyze the original Business System
Accurate to general system function modules and general processing procedures
2. Analyze existing business systems
Accurate to general system function modules and general processing procedures
3. Analyze their differences and differences
A rough analysis of the differences between the two business systems helps determine the workload and Work Progress
4. analyze users' requirements for old data
Only by analyzing the requirements for old data can we migrate the data blindly and completely.
5. analyze users' processing rules for old data

The old data processing rules are generally divided into the following types:
1. Basic data is usually easy to migrate. The data format is simple, but it affects all the relevant business data. The focus is on the primary key and unique key of the data.
2. Importing historical data is only for reference. This type of data is easy to import.
2.1 Historical data only
This type of data is easy to process, and can be imported at one time. Incremental data will be imported later.
2.2 process data
This type of data can be completed only after the record is completely closed. incremental import and data update are required, and development of related Query Interfaces is also required, to ensure that the old data can be queried in the new system.
3. Historical Data with large changes in the table structure of the new and old systems
The workload of this type of data is the heaviest, so we need to carefully study the data structure of the new and old business systems.
1. Try to collect the relevant design documents of the original system through Party A's organization. This is very helpful for data analysis. After all, it is not reliable to analyze the data through human perception and observation.
2. Observe the relevant data on the original system to understand the data changes and the relationship between the data in the data table (it is helpful for fields that are hard to understand)
3. Compare the data difference between the old and new systems. If the data is unreliable, we recommend that you handle it by 2.2.

System design:
1. After the system analysis is completed, the related data is classified, basic data, pure historical data, and historical data with great changes
2. start with something simple and give yourself confidence.
3. Compare the data dictionary of the relevant table in an excel table, and outline the corresponding fields, conversion logic, dependency, and redundancy in the new system table if necessary, after the data migration is complete, clear it.
Key points:
Matching of Field Types in different databases, such as SQL Server text, should correspond to clob in oracle, but would rather convert it into several varchar2, which is easier to implement.
For the primary key issue, the consistent data types should be maintained as much as possible, and oracle sequence or SQL Server identity int should be used as much as possible if they are inconsistent. However, after the migration is complete, pay attention to the update of sequence values.

System implementation:
1. PL/SQL or T-SQL, DTS, SSIS or PowerBuilder pipeline as the way of data migration
2. Migration of related business logic, data integrity and business-related stored procedures, triggers, function rewriting, and even SQL Rewriting
3. Compile the business query page

After that:
1. There are no special or advanced methods for data migration
2. Analysis of business data is the most critical and requires constant testing on new systems.
3. Data Migration technology is mainly implemented through SQL, stored procedures, and even cursors. The priority is also as follows:

Another kind of data migration is just the database migration or heterogeneous database migration.
Database migration requires migrating from one server to another for performance expansion. You can use the Database Export and Import or Backup Recovery Tools to process the migration, of course, we also need to consider the initial values of some sequence fields after migration.
Heterogeneous Data migration, that is, migration from one database platform to another database platform, can be achieved using ETL tools or SQL, but pay attention to the migration of business logic, that is, stored procedures, functions, triggers, and so on.