October 26, 2015 at 9:04 pm
Hi There,
I got an opportunity to work on data migration from one db design to another.
We have 2 different database design for same business. Now client wants their data in improved design. So we need to migrate the data from old design to new design.
1. Table design is different between both designs
2. I have mapping document which has info like, which column from old design is equivalent to the new design.
3. Both designs are in different different servers.
there are situations like,
> one table split into several tables
> some tables combined into single.
Im very new to this kind of work, Please suggest me some ideas or techniques to achieve this efficiently
October 27, 2015 at 12:34 am
Not sure what your asking here , if you already have source and destination table structure is your client simply expecting you to move the data or refine the destination structure and then load.
Either ways when merging or splitting table watch out for loss of detail or roll up of data. How does the client expect procedures to work after the migration? Is someone working on refactoring them?
A simple 1 is to 1 import can be achieved by using view to abstract the underlying table joins that might be needed. best place to start will be understand all the PK table and load them first , then go for FK tables and when loading make sure to watch out for skipped identity values , default etc.
October 27, 2015 at 12:55 am
Jayanth have rightly pointed about the consequences related to FK .
If you are not bothered about those and only want to move data better right custom script to move data or you can create SSIS package for the same. Hope you dont have to truncate or change datatype when you migrate.
October 27, 2015 at 10:05 am
It might help to create VIEWs on old tables, with the column names for the new tables. Then you can
INSERT INTO NewTable
SELECT NewColumn1, NewColumn2, ...
FROM NewViewOnOldTable
If the mapping is not quite right you can change the view accordingly, and not have to change a whole load of other code.
Also, I would create VIEWs on the New Tables, with the old table names, so that there is some backward compatibility for anything that needs it. Assuming that there is a one-to-one mapping of columns in a way that makes this possible.
You won't be able to create the views, using the old table names, until the old tables have been dropped. But you could create them with temporary names, and then rename them after the original tables are dropped.
October 27, 2015 at 10:26 am
What are some of the issues you're encountering? There isn't really a perfect formula for migrating data without knowing more about the data sets you're working with.
October 27, 2015 at 10:56 am
Are we simply talking about what tool or technique to use for migrating data from an old transactional database into a new transactional database (ie: SSIS) ?
http://www.amazon.com/Server-2012-Data-Integration-Recipes/dp/1430247916
Or are we talking about wanting to import from both databases into something like a data warehouse so there is a consolidated view for analysis and reporting?
http://www.amazon.com/The-Data-Warehouse-Toolkit-Dimensional/dp/1118530802
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply