7

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.

Jon Seigel
  • 16,922
  • 6
  • 45
  • 85
Seb_Lz
  • 71
  • 2

2 Answers2

3

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.

mrdenny
  • 27,106
  • 2
  • 44
  • 81
0

It depends on how many areas do you have. If you have a lot of these than management of database replication between these is going to be a nightmare.

I’d try my best to have all data in one centralized place with every possible high availability option on. If that’s not possible you might try with only two or three global servers and not one per each area.

Gunter Wesley
  • 91
  • 1
  • 2