Sql-server – Is a geographically distributed application with SQL Server replication a good idea

Architecturereplicationsql serversql-server-2012

We are currently thinking about a target architecture for a geographically distributed application.

This application tracks and schedules the movements of all material units across various production sites.

There will be two logical entities:

  • The first one is responsible of the operational/local management of a
    specific store – there are as many instances of this entity as there
    are stores
  • The second one is responsible of the global management (for example:
    units transfers between stores) and therefore communicates with all
    operational/local entities

Here is the logical diagram of the application:

Logical architecture

The implementation of this application cannot be directly derived from the previous diagram because we have to take into account the following constraints:

  • Stores are located in different areas (typically a few stores per
    area) and if the area is temporarily isolated from the global
    network, the system should still be functional for a limited amount
    of time.
  • The operational entity requires the global entity to work

Therefore, I thought about a new architecture where we would create identical instances of the global entity (same executables) with synchronized databases for each area:

enter image description here

I was wondering if anyone already used SQL Server replication to achieve the same kind of goal.

Is it the right way to proceed ? All recommendations/warnings are welcome !

Thanks a lot,
Sébastien.

Best Answer

Replication from one box to area servers, then having those area servers replicate to the store servers within that area is easy. Setting up the replication to feed from two different area servers to each store server isn't really going to be doable. You could probably hack it to work, but it wouldn't be all that easy.

You would be better off setting up the area servers with high availability so that the chance of the area server going offline in minimal.

Related Question