Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to write a DAO for database migration

 
Brian Storenson
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi, I need to write a DAO to extract data from an old sql server 2008 database. The data will eventually will be passed to other parts of the program that will then pass it to the new websites application layer. The new website will then take the data and use it to create new members for its database. We don't want to loose any data. The old DB has a lot of stored procedures. I've read a wiki on data migration, and looked at some postgres docs on data migration.

What do I need to research next to do this data migration? I have a big script that I created on sql server. I'm stuck right now. Thanks.
 
Brian Storenson
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh yeah, I am now working on making a connection to an old version of the DB. I am going to use JDBC and sql statements. That is where I'm at right now. Thanks.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Unless your database schema is trivial, I would think about using an ETL tool if I were you e.g. Talend is free. Writing a data migration by hand is quite a painful process, especially if you have to apply different rules to data in the target database. You often end running each stage in the process many times and tweaking the code until you finally get your data to migrate correctly. If you don't know much about SQL or the specific features of your two databases, then you may find it a steep learning curve.

Using an ETL tool will make this process of extracting the data, transforming it (if necessary) and loading it into the new DB easier, more maintainable and reproducible.

But if you think your migration is straightforward enough to code by hand, I would start by migrating the data model i.e. the empty tables, including primary/foreign keys, constraints etc. You also need to watch out for things like sequences e.g. PostgreSQL sequences are used to provide unique numeric values for primary key columns. But you must remember to re-set the sequences after your migration so they start counting from the highest value+1 in the corresponding data column.

If your data model is changing significantly, I would suggest you consider setting up a staging area in the target database, and simply copy your source tables and data into there initially. Then you can perform all the transformations and load the data into the target schema on a single platform. But this will of course mean that all your processing is done on the same server which may impact other users.

Stored procedures will have to be re-written. I never use SQL Server but their procedures won't run on PostgreSQL, so you'll need to convert them to a language that PostgreSQL supports.

Finally be prepared to do a lot of ad hoc SQL to check counts of records, ranges of values, etc. And test everything thoroughly. Once your old system is gone, you're in trouble if you screwed up the data migration.
 
Brian Storenson
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Chris! I didn't think of looking for an open source ETL tool. These hints will be helpful
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic