As part of Data Migration project, I followed following things.
1. Validation Scripts during development. This will play a vital role in validating data in Go Live and Production (post migration).
2. Reconciliation: There are two kind of reconciliation
- Technical reconciliation – As part of this, we store the record count of each table (i.e. Source table, Transformed table and Target table)
- Financial reconciliation - As part of this, we match the financial numbers as per business needs (i.e. accounts receivable - in old and new system, accounts payable - in old and new system and breakdown of transactions)
3. Actual Data Migration can be done in two ways/approach
- Bring the old data till last month or a specified date and then last month data during migration day. The advantage in this approach is that you will be migrating Delta on final day and business can do some validation with old data prior to migration day. In this approach we can keep system down time as minimal as possible.
- Bing Bang migration is a risk-based approach, because success of migration is unknown until migration day. Downtime window is large. In case of any error, migration team needs to fix it as soon as possible in order to proceed further on migration. In addition to these resources needs to monitor the migration flow and record count at the time of migration.
4. Error records needs to store in a separate table with relevant keys (i.e. the field that indicates uniqueness of the record). These records should be fixed and inserted back into new system after discussing with the business.
5. Additional measure needs to be taken while loading data in SQL Server. (i.e. SP_SpaceUsed ‘<tablename>’ to find the record count, Partition)
6. BDD feature in SQL Server – Balanced Data Distributor is used to read from the source table and write in target table simultaneously. This is because, read operation doesn’t take time but write operation takes more time.
7. Partition can play a vital role in improving the performance.
8. Indexes can improve the performance. I suggest to create Partition and then do Index on top of it.
