1

I have a set of data in a SQL database (SQL Server in one system, MySQL in another) and I need to synchronise the two.

There is a fair amount of relational data for each record.

  • The base object is an Account.
    • Each Account has one or more Contacts
      • Each Contact has one or more Services

There are more, but that's the sort of structure.

At the moment I am programmatically getting a list from SourceA, generating objects for the whole row, then getting the matching object from SourceB and seeing if anything is different.

Alternatively I could scrap the deep object creation, and just sync the Accounts table, then the Contacts table, then the Services table.

The problem is that with 5,000 accounts, 22,000 contacts and 45,000 services, it is taking HOURS for the sync to run. Some basic metrics imply it's the SQL queries that are taking all this time.

So: how do I synchronise two data sources like this?

(I did have a look at the Microsoft Sync Framework, but it seemed tricky to get into. If that's what consensus indicates is worth it, I'll invest the time to learn it)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cylindric
  • 5,858
  • 5
  • 46
  • 68

1 Answers1

0

See this SO answer for one alternative to Microsoft Sync Framework (an asynchronous messaging queue). That answer is about 3 years old.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185