locked
How to sync two SQL2005 databases in a hub-spoke, N-tier architecture

    Domanda

  • The scenario I'm trying to setup is the following:

    1. Many local databases need to synchronize with one central database. This would be a hub-spoke architecture.
    2. I need to do this over WCF because of the distributed nature of the local databases.
    3. The local databases are full-blown SQL 2005 databases, not SQL compact.

    This would seem a pretty standard scenario, but I have not been able to find a way to do this in the Sync Framework. These are the problems I have encountered:

    1. The SyncAgent class does not support SqlSyncProvider for the LocalProvider, it only supports SqlCeClientSyncProvider. However SyncAgent does support ServerSyncProviderProxy for the RemoteProvider. Some would say: Just use the SyncOrchestrator instead of SyncAgent. Well, this creates another problem:

    2. The SyncOrchestrator class does support SqlSyncProvider for the LocalProvider, but it does not support ServerSyncProviderProxy for the RemoteProvider. It seems like the RemoteProvider has to derive from KnowledgeProvider and the ServerSynchProviderProxy class does not.

    The above is kind of a dead-lock situation. What I have been able to reason from this is that a hub-spoke architecture is only supported between a SQL server on the hub side and SQL Compact on the spoke side. If you want to do SQL to SQL, then this is only supported in a peer-to-peer architecture.

    Can this really be true? I would really like to use the hub-spoke architecture because I think it makes the server-side code easier. Implementing my needs in a peer-to-peer fashion and then in real life using it in a hub-spoke architecture seems to create a lot of possible concurrency issues because the peer-to-peer model is not intented for this.

    What recommendations do you have for solving my problem? Somehow it has to be possible to synchronize to full SQL servers in a hub-spoke architecture

    Thanks
    Jakob

    martedì 8 dicembre 2009 16:32