Database migration

Question

We are migrating a client's own database schema to our own (both SQL-Server). Most of the mappings from their schema to ours have been indentified and rules been agreed on if the columns don't exactly align (default values etc.)

Previously, depending on who was assigned the task, this has been done either with a mixture of sql scripts or one-off vb apps.

I was thinking there must be a application (commercial or otherwise) where you can assign these mappings/rules and have it stream the data across. Surely the setting up and configuration of this tool would be less than the creation of ad-hoc scripts...

Is there an app? Apart from the obvious 'be careful' any tips to mitigate the stress of a non-DBA porting one schema to another?

Answer 1

Not really. Problem is that whatever app you would have would be more a frameowrk anyway. Schema transfers, by definition, are partially tricky to do. And a tool doing half of the stuff is nearly useless - that is easy to do in SQL anyway ;)

Answer 2

I do this sort of thing every day. because of the custom nature of our software, i usually one-off the whole thing. about 70% of the time, i use ms access as an intermediary (the rest of the time, i write code to do it). i load the source data into ms access, massage it and then move it into our sql server tables. access is very well suited for this type of work. the main thing to be careful about is your keys and duplicate restrictions. i'm sure there are off-the-shelf apps to do data migration, for my use, however, i need more control and ms access allows me to reuse some portions of the data import while maintaining the ability to customize everything.

best regards, don

Answer 3

You can use database migrations in your code.

There are several .net data migration libraries that were inspired by ruby migrations. They define database changes in code.

Migrations allows a developer to manage rollout, and rollback, of database schema changes in a controlled and consistent manner.

Look at these:

• SubSonic migrations
• Rails Migrations for .NET
• RikMigrations
• Migrator.Net
• MachineMigrations

Answer 4

I use an Open Source ETL (extract/transform/load) tool called Talend Open Studio for tasks like this.

In short, Talend Open Studio (TOS) is a GUI code-generation tool, in which you plug in different components to import, munge, and export data--and the resulting code is portable Java or Perl. There are plenty of components to help you make decisions about non-standard data (or you could always add your own Java or Perl routines for the most complex parts).

TOS has proved to be well worth the time I initially invested in it... so I would definitely recommend it.

Answer 5

Try SQL Data Examiner. This tool allows comparing/synchronizing sql-query results, so you can write appropriate queries to both databases and compare/synchronize results returned by these queries.