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.

Improve this question
4
  • 2
    Is there an actual need to replicate the data to each of the stores? Is this for a vendor application, or is this a new project? Have you considered something like a SaaS model where the web/application servers are geographically located in different regions, with a Peer-to-Peer replicated copy of the database local to each web farm?
    – Jon Seigel
    Commented Jun 2, 2013 at 13:37
  • Hello Jon and thanks for the reply. This is a new project: it's a whole new version of an already existing application. I'm not sure if the SaaS model would suits our needs since global servers would be exactly identical (same data). However the databases peer-to-peer replication seems really appropriate for our case.
    – Seb_Lz
    Commented Jun 3, 2013 at 7:33
  • Okay. Let me mention now that you may not be able to get a good answer to your question because of specific requirements for this application. What I will say is that in general the fewer copies of the data you have, and the fewer databases with different data, the easier it is to manage the system. That's why I'm asking if doing the kind of distribution you're asking for is really necessary. I don't know how many stores you have now, but scaling the system that way may lead to limits or challenges in the future, instead of centralizing things, at least a little bit as I suggested.
    – Jon Seigel
    Commented Jun 3, 2013 at 15:15
  • Ok, I perfectly understand this and the replies I got already helped me in my thoughts. I think there are most likely quite a few different solutions to my problem. Since I'm not too sure about the best practices in this area, I wanted to make sure I wasn't going in a completely wrong direction.
    – Seb_Lz
    Commented Jun 4, 2013 at 13:37

2 Answers 2

Reset to default
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.

Improve this answer
2
  • Hello mrdenny and thanks for the reply. The replication should only happen between global servers (not between global and local servers). Each global server will modify its database due to interactions with local stores. Those changes should be replicated to every other global servers. In that architecture, there would be about 5-6 global servers.
    – Seb_Lz
    Commented Jun 3, 2013 at 7:19
  • It's doable, but it's a big design. This is one of those configurations that will need to be designed very carefully and put together very carefully with a big proof of concept to make sure that everything works correctly.
    – mrdenny
    Commented Jun 8, 2013 at 9:09
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.