Is a geographically distributed application with SQL Server replication a good idea?

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:

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:

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.

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.

StackExchange Database Administrators Q#43623, answer score: 3