Fog Creek Software
Discussion Board

Welcome! and rules

Joel on Software

Synching data between two DBs with conversion?

We are in the process of converting a VB app to VB.NET, however the original creators of the Data model made some assumptions that need to be rectified.

We want to make the changes to the data model for our .NET release, however the .NET release is only an add-on module for the original app. So the concept we had is having two databases deployed - one with the existing data model our VB app can run off of, and one with the new data model our .NET can use. We would sync the two using something like DTS with transformations to get the data between the two into the correct fields.

Has anyone had to go through this type of migration? If so, did you use any tools or tricks of DTS to help make the transition smoother? We would need to sync the databases every 5-10 minutes, so experiences/concerns with that would be helpful. Thanks!

Monday, May 10, 2004

This sounds like insanity to me.

I would look very hard for another way to handle your issues.  Either suck it up and write the .NET client to the old data model, or force an upgrade to the old client so that it runs against the new schema.  Running two different databases and trying to keep the data synced is something I would not do except as an absolute last resort.

I could be wrong, of course.  If you still think you want to do this, you probably want replication, not DTS.  Look into "merge replication", it will handle a lot of the work for you, but don't say you weren't warned.  :)

Matt Conrad
Tuesday, May 11, 2004

I'm with Matt here, the proposal does sound a bit bizarre. Only updating every five minutes sounds like the databases will be out of sync most of the time, which is a very dangerous state of affairs.

You *might* get away with it on a low-grade reporting database, but it doesn't sound like that is the case here. Even then, you may end up with two reports counting the same thing and getting different results. Don't allow this possibility to occur as no amount of IT hand-waving and explainations about databases being out of sync will convince a manager with two reports that don't agree.

I assume you have investigated using views to translate between one schema and the other (looked at both ways) ?

If views won't solve it, then I suggest you create an abstraction layer for your .NET code to make the old schema look like your new schema. Even if it incurs a performance penalty, this is better than modifying the old VB app (sounds like that should not be developed further if you're actively replacing it).

There must be a solution to this, that allows the old VB app, and its data, to remain intact, while adding the new functionality.

Steve Jones (UK)
Tuesday, May 11, 2004

Thanks for the good replies. Having a view look at the table might be one way to do it. The challenge we have is that this is a production product, and our goal is to begin moving business logic from SQL to the .NET classes without interrupting the existing production enironament.

For now, the .NET module would be used only to purchase orders created in the other system, so real-time syncing isn't necessary as it would probably be a day or two at least before the module was needed for an order.

I know it sounds crazy (in fact, if I were replying to this I would say the same thing) but I'm just looking for a temporary solution so we don't have to pull weeks of all nighters to get this out the door.

The main reason I wanted to use DTS is for the transformation capabilities. For example, in our prod database we have a table that captures customizations to the product, like color. Ideally color should be an attribute of the product, not a seperate table, so we wanted to move it there. But, if we do that, we have to go back and rewrite our Prod front end - a several week job plus testing. Any reporting coming out the .NET module would be related to classes within that.

Thanks again!

Tuesday, May 11, 2004

I am rusty on repl, but I believe that you can incorporate DTS transformations as part of your article definitions w/ replication.

Matt Conrad
Tuesday, May 11, 2004

I would definitely try to avoid having two databases storing essentially the same information. All the previous suggestions for how to avoid this are good, and I can't add much to them, except that you could maybe use triggers on one set of tables to replicate data changes to your other set of tables.

However, I would say avoid DTS for any scheduled production data transfer work of any complexity. Error handling is poor at best, and at worst is completely ignored. I have seen steps fail, and DTS then proceed to the next task along the "success" workflow path. Unless you have really simple requirements of DTS, use something else for this synchronisation - maybe a C# app, maybe a Perl script, but definitely something with robust error handling.

Mark B
Friday, May 14, 2004

I should qualify the "don't touch DTS" thing a bit - I am talking about DTS packages. Ad-hoc transformations from a single data source to a single target have always worked fine, so you may be OK running those on a scheduled basis. I would still recommend only having one data store though.

Mark B
Friday, May 14, 2004

*  Recent Topics

*  Fog Creek Home