I need Data Migration design.


  • I have 2 databases The databases are called   db1 and db2 on sqlserver .db2 is an extension of db1 so it has many more tables and design changes.
    I need to migrate data from database db1 the earlier version , to the database db2.
    Can you suggest me how to design. please note that i just  need design for documentation purpose to get it approved by client.

    Thanks in advance!!!

    Tuesday, January 07, 2014 6:51 PM


  • The most important part of the work is to get the table and column mapping correct.

    Beside getting the mapping correctly, you also need to consider the possibilty of key collisions. That is the key 7689 in db1.table1 may be a completely different entity than key 7689 in db2.table1. Hopefully you have natural keys (or at least natural-almost-keys) that can help you find collisions on surrogate keys.

    As for moving the data, that can be farily mechanical if volumes are modest. If the volumes are considerable, you may need to look into something smart. But since this is a one-off (I assume) it may not be worth to be too smart, so if it takes three days to migrate, it takes three days to migrate.

    Erland Sommarskog, SQL Server MVP,
    Tuesday, January 07, 2014 11:01 PM