Ball park estimation for database migration from PA 6

OK, to be of any use, some context for this value needs to be established.

This figure was derived by migrating a database containing more than 14 million job records from a workstation running SQL server to a second instance of SQL server, running in a Virtual Machine on the same workstation.

In this configuration the Database Wizard was able to migrate upwards of 300000 job records/hr.

Note: all calculations are based on the number of jobs in the database, jobs are two table records and constitute the vast bulk of any database content, all other tables are trivial in terms of time requirements compared to jobs.

Note: Again this figure is highly variable, the hardware installed (or allocated in the case of a VM) has a role, when migrating between instances, the speed of the network (or virtual network) has a role. Finally the load on either machine will also factor into the time required.

Best practice: The best practice, is to mount the PA6 database into the same instance as the UM database, then perform the migration of data. This can be done either by detaching the .mdf and .ldf files on the PA6 database server, moving them and re-attaching them there, or by taking a backup on the old instance and restoring it on the new. With both databases in the same instance, we can eliminate the impact of the network on the process, and better control/mitigate the load on the server.  Although we have no hard figures for this type of migration you would realisitcally expect the throughput to increase substantially simply by eliminating the network and controlling load.  In this case the single most important factor will be the hardware, particularly memory on the SQL server. If you've used the same SQL instance for UM that you previously used for PA6, both databases will already be in that instance, and once you've licensed UM, you will be able to proceed directly to migrating the database under the most favorable conditions.

How did we do with this article?