We have tried to list a number of areas that the DBA should be looking at during a database’s migration. To help the process, answers to a number of questions should also be sought:
- How many databases are involved in the migration?
Obviously migrating ten different databases will entail more effort and planning than migrating only one database.
- What is the nature of the database(s) being migrated?
This should help you decide the instance where the database should be migrated to. For example, you would not want to install a data-mart in a high traffic OLTP system. Similarly, you will not probably be bothering about backups and disaster recovery if your migration is part of a regular refresh process in a test environment.
- If the database is already in production, what timeframe will be available for the migration?
Backup/restore or detach/attach will take some time and you would want to minimise this time as much as possible. Also, you may want to know what services and applications are accessing the source database and how they need to be stopped. For example if you are migrating a web based application, you will probably want to talk with your system administrators about shutting down Apache or IIS. Users will need to be made aware of any system unavailability.
- Do you need to consider the size and growth characteristics of the database once it has been migrated?
If the database has been growing fast and it needs to be migrated because the existing hardware resources are insufficient for the performance required, you will probably be changing the database file size, growth increments etc. once it has been migrated. You will also probably be tuning CPU and memory in the new instance.
- Has the destination server enough hardware resources (disk, CPU, memory, network capacity) to serve the new, migrated database? Will the database be competing for resources with any existing database application?
Management may want you to install a new instance for the database in a server where one or more instances could already be running. You need to be aware (and make management aware) that additional instances will consume additional hardware resources.
- If the destination server is a separate physical system, does it have an optimal disk configuration?
If your old database server had separate physical disks for data, log, backup and tempdb files, you probably would not want to migrate it to a server where only two physical drives of C: and D: are present.
- What other “peripheral” components will be involved with the migration of the database?
As we mentioned at the very beginning, migrating a database application can be a complex task. Nevertheless, you will still have to go through the process of migrating jobs, logins, custom error messages, file shares, reporting services solutions, SSIS or DTS packages etc.
- What user accounts will be accessing the migrated database? Will there be any new user accounts? Will there be any fewer accounts?
When migrating a development database to a production server, you may need to account for end-users who were not involved during the testing phase and whose user accounts did not exist in the development database.
- Will the migrated database have any special backup or disaster recovery requirements?
Business requirements may dictate a database to be backed up more frequently than other databases in the same instance.
- Is there any provision for post migration testing? Have test cases been developed?
You would want to ensure everything has been migrated and all configurations have been checked before switching the applications to the new database.