Application data synchronisation - running the new app in tandem with the legacy

Question

We're in the process of replacing a legacy system. There is going to be a period of time with both the applications running in tandem. Users will be able to use either system and the challenge is to be able to keep their databases in sync with each other.

The new system is ASP.NET and the legacy is VB6. Both are running on a SQL Server database. It is unclear at this time if the databases will be in the same server room, let alone the same country.

The two solutions on the table so far are:

1. Web services that sit on each machine and is called by the other application.
   • Need to modify the Save method on the base class(es?) for the native objects. This is invasive and could be a problem when it comes to switching it off.

2. A single windows service that polls each database and works out what's changed and forwards adapted updates as appropriate.

   • Need to change the schemas in both applications to ensure that they have a LastModified (DateTime) on all tables so we can do a periodic SELECT at any given interval.

Both solutions seem reasonable. Both solutions have pros and cons. The business has asked for no more than a 2 second delay(!) between updating one system and seeing it in the other. That's possibly a stretch target but it's something to aim for.

Others that have been suggested but rejected (I'm willing to reconsider) are:

• Database triggers (blugrh)
• BizTalk or other bus (seems like a sledge hammer and is too complex for a switchover solution)
• Modifying all the stored procedures (noooo.)
• SSIS (don't know enough about this yet)

Appreciate any thoughts you may have.

EDIT: N.B. The schemas are completely different.

Answer 1

In the end this was solved with a webservice. It worked really well.

Answer 2

What you describe here makes me feel like in the middle of a nightmare! I think you should first begin to make clear to everybody that it is impossible (or at least extremely expensive) to think of being able to allow the users to update all of the data through 2 different application with 2 different databases during the whole transition process! I am not even talking about the 2 seconds delay ...

According to me, the basic strategy should be to gradually switch data update rights and possibilities from the legacy to the new app. Users will be able to see the data from both sides, but will be able to update it only through one of the apps.

(incidentaly, this method will also force the users to switch gradually to the new version, avoiding expected and annoying resistance issue already exposed by @HLGEM)

Once this rule is clearly accepted, you could then implement the following steps.

1. Set all procedures allowing data transfer from legacy database to new database. I guess you'll need to run them a few times in the coming months...
2. Set all procedures allowing data transfer the other way (reverse data transfer)
3. Here you should have identified homogeneous groups of tables than can be moved together. Merge the previous code in a way that you'll get for each of these groups a "data transfer" procedure and a "reverse transfer" one.

Then, for each of these groups

4. Put your update restrictions through code or at database level
5. Run your "data transfer" procedure
6. Organise your "reverse transfer" procedure as a trigger in the new database

I guess the first kind of data you'll be able to transfer will be lists that do not contain any foreign keys.

Working this way, you will gradually switch from a situation where you have

• read/write legacy app + read-only new app

to

• read-only legacy app + read/write new app.

Answer 3

Personally I would reject the idea of users using either system simulataneously. How are you going to resolve the issue if user 1 changed record 1 on system 1 and user 2 changed record 1 in a different way on system 2?

Further, if you don't require people to use the new system, they won't. Resistance to change is very very strong in most organizations.

I would suggest instead that you rollout the new system and require all to use it and hourly send data to the old system in case you need to revert for any reason.

I see no reasonable way to get a 2 second synch. That is a ridiculous requirement and the business side should be told so in no uncertain terms.

Sometimes you just have to fight back when busines users want something unreasonable.

Answer 4

2 seconds, that is a really tight timeline, and I'm guessing that your windows app solution just might not cut it, not if there are hundreds of changes or anything at one time, and the poll time has to be almost every second to hope to make it within 2.

Are the databases using the same structure? If so, I'd look at implementing replication.

Edit

After the comment and addition that the schemas are entirely different, I have to say that really I see two sets of operations.

1. Modify the data storage options IN the app to make inserts/updates/deletes in both tables. Advantage: Immediate, no external process to share. Disadvantage: have to modify all code, hard to disable etc.

2. Create a sync application as you mentioned, to sync changed data. Advantage: can simply disable after transfer done. Disadvantage: very complex to write especially if there are a large number of tables. Also, not as fast, 2 seconds is going to be VERY hard to accomplish