Database migration may involve many different types, such as hardware platform migration, one technology to another (relational to relational, relational to NoSQL), on-premise to cloud, etc. In this posting, I outline some key steps at high level to a successful database migration, assuming the migration involves changing DBMS platform.
- Assessment and discovery – this is extremely important. In the initial discovery phase, the cost of correcting a mistake is fairly low. This involves understanding of the workload, platform, configuration, etc. There are some tools to help such as migvisor, but the followings are a minimum list of things to find out:
- Detect usage of proprietary features of source database (e.g. Oracle RAC). This ensures the new platform completely achieve functionalities from the old platform;
- Detect usage of incompatible schema objects between old and new platform. This includes data types, UDFs, Triggers, Custom Views, partitions, etc;
- Review the database hardware environment, such as CPU, storage (size, capacity) and Memory;
- Review the workload characteristics of the source database, including number of unique queries, query complexity, analytical or transactional workloads;
- Review the DR/HA requirement of source database (e.g. RPO/RTO, replication, backup schema)
- Understand the applications connecting to the source database
- Decide target database engine from the following aspects:
- support of advanced features
- support of schema objects (e.g. sequence, UDF, triggers, DML, stored procedures)
- compatibility of SQL syntax with existing engine
- application support readiness
- IT staff background and training
- Define success criteria with customer
- From database perspective
- Schema objects converted successfully
- Functionality test plan
- Data validation (source and target are identical)
- Define goals for performance test
- Integration test with applications
- From applications perspective
- Unit test: applications component do not break
- Performance test on application
- Integration test should ensure existing integrations remain functional
- From deployment perspective
- migration pathway: online vs offline. Online would of course be preferred by add to the complexity of professional service activities;
- Assess impact of migration activity: migration may impact performance of the source database;
- Contingency plan during migration
- From database perspective
- Environment Setup on new platform
- Schema conversion:
- Map data types;
- Address missing features;
- Develop scripts with syntax
- Deploy database engine target (size, partition, etc)
- Deploy application for testing
- Testing and failover – ensure DR and HA on target (e.g. Use of load balancer)
- Schema conversion:
- Data Migration
- One-time load: in this approach, the new system is not ready until the migration from old database to new database is completed; once migration is completed, the system goes live on the new system. This approach requires application downtime for production cutover. The business either operates exclusively on the old system, prior to the cutover; or exclusively on the new system, after the cutover. There is no period where the customer operates on both systems.
- Realtime / change data capture: in this approach, there is a period of time where the customer operates business on both systems. This minimizes risk on customer operation but brings challenges with data synchronization, depending on the requirement, customer might choose to run synchronous replication between systems or asynchronous (queued, periodical or even manual)
- Capture changes on source and stream the delta to target
- Minimum to zero downtime production cutover
- Testing after migration
- Backfill plan
- Migration Validation
- Execute acceptance testing
- Compatibility and performance validation
- Rollback Strategy
- Reverse replication
- Rollback plan: ideally every step should be undoable all the way back to the beginning, with tradeoffs