Clever Geek Handbook

App Data Sync - Launch a new app in tandem with legacy

We are in the process of replacing an outdated system. There will be time for both applications working in tandem. Users will be able to use any system, and the challenge is to keep their databases in sync with each other.

The new system is ASP.NET and the legacy is VB6. They both work in a SQL Server database. It is not yet clear if the databases will be in the same server room, let alone in the same country.

So far, two solutions are on the table:

  • Web services that are hosted on each machine and called by another application.
    • You must change the Save method in the base class (es?) For your own objects. It is invasive and can be a problem when it comes to disabling it.

  • A single Windows service that polls each database and develops customized updates and changes as needed.

    • The schemas need to be modified in both applications to ensure they have LastModified (DateTime) for all tables so that we can make periodic SELECTs at any given interval.

Both solutions seem reasonable. Both solutions have their pros and cons. The business asked for no more than a 2-second delay (!) Between updating one system and viewing it in another. It might be a stretching goal, but for something you have to strive.

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

  • Database triggers (blugrh)
  • BizTalk or other bus (seems like a sledgehammer and is too complex for a switch solution)
  • Modifying all stored procedures (noooo.)
  • SSIS (not enough to know yet)

Appreciate any thoughts you may have.

EDIT: NB The schemas are completely different.

+1


source to share


4 answers


This was eventually resolved with the webservice. It worked really well.



0


source


2 seconds is a very complex timeline and I am guessing your Windows app just can't cut it, not if there are hundreds of changes or anything at the same time, and the polling time should be almost every second to hopefully do it is within 2.

Are the databases using the same structure? If so, I would look at a replication implementation.

Edit



After commenting and adding that the circuits are completely different, I must say that I do see two sets of operations.

  • Change the data storage settings in the application to insert / update / delete in both tables. Advantage: immediate, no external process to exchange. Disadvantage: All code needs to be changed, difficult to disable, etc.

  • Create a sync app like you mentioned to sync changed data. Advantage: Can be simply turned off after the transfer is complete. Disadvantage: Very difficult to write, especially if there are a lot of tables. Also, not so fast, 2 seconds will be very difficult to complete

+1


source


Personally, I would reject the idea of ​​users using any system at the same time. How are you going to solve the problem if user 1 changed record 1 in system 1 and user 2 changed record 1 differently in system 2?

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

Instead, I would suggest that you implement a new system and require everyone to use it and send data hourly to the old system if you need to go back for any reason.

I don't see a sane way to get 2 second sync. This is a ridiculous requirement and the business side should be told so in no uncertain terms.

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

0


source


What you are describing here makes me feel in the middle of a nightmare! I think you should first make it clear to everyone that it is impossible (or at least very expensive) to think about being able to allow users to update all data across 2 different applications with 2 different databases during the entire migration process! I'm not even talking about a 2 second delay ...

In my opinion, the main strategy should be to gradually switch the rights and capabilities of updating data from legacy to the new application. Users will be able to see data from both sides, but will only be able to update it through one of the applications.

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

Once this rule is accepted, you can follow these steps.

  • Establish any procedures to migrate data transfers from the old database to the new database. I think you will need to run them several times in the coming months ...
  • Specify any procedures that allow data to be transmitted in a different way (postback)
  • Here you have to define homogeneous groups of tables that can be moved together. Combine the previous code so that you get a "data transfer" and a "postback" procedure for each of these groups.

Then for each of these groups

  1. Place upgrade constraints through code or at the database level
  2. Start the data transfer procedure
  3. Organize your "back translation" procedure as a trigger in the new database

I am assuming that the first kind of data you can transfer will be a list that does not contain foreign keys.

By working in this way, you gradually switch from a situation where you have

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

to

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


source






More articles:


All Articles