What are the steps for migrating a database?

Database migration can broken into high level in 3 ways: 1) Database Migration due to Server/OS Change. 2) Database Migration from one database to another database. 3) Database Migration due to Database Version Change. Based on above mentioned type of migration we can plan our work.If it is database migration due to Server/OS change then one of the important thing to look for is supportability and performance.If we are going to change database from one technology to another then there will be additional factor which may comes like Datatypes,SQL support,HA Support,Performance.Whereas if we are going to have version change only then may be we have to look for new functionality which got introduced and how to leverage those functionalities.

Simple Steps to consider: What type of migration is best in your scenario? What is your rollback strategy? What is the downtime allowed by the application, SLA's, what can you get approval for? Might be a good idea to keep the change window longer, example: Start the migration change Friday night and keep it open until Sunday night just in case you run into issues. Do you need to encrypt database? Has your DR solution been tested? It just all depends on the type of migration, side by side or in-place upgrade. Side by side is a much safer approach and you can always rollback to the current environment. But Rollback is not always so easy, for example you can't restore the database to the previous version once you have upgraded.

Database Migration steps could be in-place or side by side. In place is bit easier but rollback is bit tricky and downtime /data loss could be there. Side By Side could be on same DB platform or cross DB technologies. In Side by Side Migration, rollback steps are pretty easy and minimal data loss if planned well. DB migration has various pre-steps as below: 1) DB assessment 2) Compatibility checks 3)Downtime window 4)Data Movement strategy 5)schema conversion if needed

I would add -depending on the complexity of the migration- do a PoC ! Sometimes when you migrate from one DB Engine to another there are differences that is better to spot in a PoC. Also if you can record some activity (24h on an invoicing day for example), and replay in the new engine to generate load and spot potential performance issues since not every DB engine behave the same and be one step ahead.

The database migrations would be complex project. We would need to define the scope and define the strategy of migration during initial period of project. The well pre-prepared plan would help execute the project effectively and timely fashion. There are 3 approaches in database migration involves. 1. In-place migration 2. Side-by-side migration 3. On-premise to Cloud migration either a. Cloud vendor managed DB as service b. Cloud virtual DB server migration 4. DB migration from one type to another type e.g. SQL server to Oracle server etc.

Whenever a DBA is required to plan for a migration strategy for mission critical systems that require the least downtime, its preferred to apply the following steps: 1-First of all the new setup has to be validated in terms of configuration, performance , and security. 2-Depend on the latest Full backup and restore it on the new database server with (No Recovery) 3-Run a differential backup and restore it again with (No Recovery) 4-Shutdown your application 5-Assure no connections open on your Database 6-Switch DB to single user 7-Run log backup 8-Restore the log backup with (Recovery) so the database is online 9-Change the database owner 10-start your application By this, you assure that minimal downtime is needed Hope it helps

In your migration strategy *always* have a rollback step, just in case your migration tool/script, whatever, fails and you are running out of time, or solving the issue will take longer than expected you need to be back on business asap, the only way is a "healthy rollback step" and that step is not only concerning DB but network, applications, jobs, users clearance, exposition (or not) to wan, etc. everything needs to be accessible and in working condition.

Before choosing which strategy to choose for migration it is better to confirm desired downtime which is available to perform this migration.Sometime migration tool cost and complexity get increased if it is meant to be done in minimum downtime.If your customer has liberty for some hours of downtime then plan your migration strategy accordingly.Based on downtime do confirm desired migration tool and do read about limitation of that tool also so that any constraint which is there should be made visible to all stakeholders.Automation and incremental approach should be the key thing needed if you want to minimize downtime.

You can consider following tools 1. The existing DB performance matrix, configuration settings, backups ready before migration 2. For on-premise to cloud migration there are different tools available with cloud vendors. You can use them. The strategy not only determines the type of upgrade that you will perform—in-place upgrade or side-by-side migration—but it also determines the upgrade method you use, how you test the upgrade, and what the upgrade success criteria are. The upgrade strategy combines these items with a recovery plan for rolling back the upgrade if you encounter problems that cannot be corrected during the upgrade process.

Key point for choosing a migration strategy is right tradeoff between downtime and cost. In general, lower the downtime, higher the cost. Zero downtime migration strategy and tools always cost more. But it is business decision about how much downtime they can afford. They can reduce the cost of migration if they are can accept the risk of few hours of downtime. Migration strategies are driven by technical folks, but its get input from Business owners.

It’s important to consider what data needs to be migrated, do you need everything, or would just the last 5 years be relevant. There are pros and cons to both options that should be carefully considered. Some things to think about when choosing what to migrate - will the old database still be accessible? - are there any audit/legislative requirements that require the whole dataset to be moved. - can you notify users to take down copies of their historical records prior to migration. - even if you decide to take everything reviewing data for duplicate records, test or other dummy records is worthwhile so that these things are not migrated if they are not required.

Based on your choosen migration strategy work on preparing your source and destination system.Do read official document and do check limitation of your migration strategy and work on it.Once you know all the limitation and prerequistie for this migration prepare one proper checklist which should include high level steps that you are going to perform and this checklist will evolve as you progress in this migration.In ideal world if you are going to migrate old database then it is better to clean database upfront by removing any unwanted users or tables.Do look on database cleaning as in long run it will definitely help.

Execution should be done first on sanity environment even though you have perform migration before but it is better to perform one sanity run so that you can be atleast 90% confident of whole migration approach.Never ever consider any environment similar even though everything look like some past migration which you have performed before.Just before execution do take object count which will be verify post execution and if possible create one dummy table and insert desired data with latest timestamp so that you are pretty sure that all latest data has been migrated sucessfully.

Ideally there should be proper signoff document created and it should have multiple stakeholder which will give signoff post migration execution.Having multiple stakeholder help you in catching something which may be missed by one person.Also those document may contain proper object count along with proper OS/database parameter along with CPU and RAM configuration which is expected as per Production workload.

Whenever possible, set a snapback point for the data just after go-live, for initial testing, preferably with a team of business-related testers and test data, rather than real-life data. This way, if a rollback is required, a simple snap to the recovery point, and additional recovery to the old configuration will not impact business data. It also allows for one last check before the complete handover, by testing, rolling back to the last point, and releasing to business with all data as it was on the old configuration.

Clearly identify new- and old-connection details for quick reference to support teams, additional or overlooked hard-coded connection strings, and baseline or inventory management documentation sets. A critical step often overlooked is to change monitoring applications to access the new environment and remove the old. A lack of monitoring after a migration causes a critical blind spot, and the additional insight from your monitoring dashboards will be critical in the stabilization period after going live.

Proper rollback plan should be implemented and if possible try to run sanity testing so that proper timing is noted.Along with that make proper post deployment parameter that has to be set so that target database will be production ready

In short: Create a plan with the customer Test the plan with the customer Have the customer sign off on the plan Execute the plan including the changes made during test. My exp. Is that the db migration is just on part of the migration. The applications and all connections is the rest. And the customer has to be aware that he has to test everything in the test migration. Just my 5 cents Frans

I will add a database migration plan (which is different from the migration strategy) and will include a rollback plan. The plan/rollback should be tested and validated.

Great …. Prior to migrate the live production database, we have to simulate the entire migration steps in Test Database ( A recent clone of production database) and it has to be tested by end users