Best high availability path for my needs

The question talks about high availability and centralization of remote data, and you soon realize that you can not count on a high availability connection.

If the stores do not have a high availability connection, and you already said that they do not, then there is no option but to either work offline or casually connected right?

Casual Synchronization

The Microsoft SQL Server feature that can help you with this is Replication . In this architecture, each store has its own database server and when they are online they synchronize their data with a central database.

You can choose what to send and what to receive, so that each store can for example send only its movement and receive only the product registration or updated price rules, ie it is not necessarily a mirroring - each store does not need to receive the handling of all the others.

You also have the option to implement a similar mechanism on its own.

If you design the system and database for this architecture, you are free to use any synchronization or integration mechanism, and you are free even to modify this mechanism in the future without major changes to the rest of the system. / p>

A possible approach when designing a casually connected distributed system

The good news is that a well-designed database and database will transparently support this occasional synchronization. Some tips:

• This suggestion may be somewhat counterintuitive: Look for the whole system as if it were unique and centralized - the system and database that runs in each store is identical to the one that runs in the central. This simplifies the development and makes the deployment more flexible, allowing stores to be completely online, completely offline or more centrally (regional exchanges).

• Make sure everything is transparent to the system - it does not need to know if the data being read has been entered locally or synchronized remotely.

• Remember that not having a high availability connection is a business decision, so the business area opted for another type of investment (synchronization via infrastructure tools and resiliency plans). If the system tries to handle itself with the consequences of a physical limitation (lack of internet) the project will fail.

• What information will be synchronized (what stores receive and what they send) is a business decision . See that the system allows this for the simple fact that it was designed as if everything were local.

• Key users should be aware of what information is dependent on the exchange and should also be aware of the data synchronization routine (for example, price rules are entered in the central office the previous afternoon to be available in stores in the first hour of the day).

• Invest heavily in resiliency and synchronization monitoring. Users of the store need to be warned, for example, that prices for the day have not been synchronized, so the responsible user can for example request a fax and manually enter prices, repeating the work that has already been done at the exchange, such as would be done in case there is no central.

As for high availability in each environment (central and stores), although it seems secondary to the question, you can select one of the SQL Server options, such as cluster or mirroring databases data.

  

Does SQL Server have any solutions that will help me with this?

It's not easy to say. These things need in-depth study and no one on the internet can properly tell you something that really helps.

As far as I know there is nothing ready in SQL Server. It has a number of features that used together can make it a little easier, but you will have to create your solution, it will be laborious.

I reinforce that the first step is to have the problem very well defined, to analyze everything that is going wrong.

Even with the correct diagnosis and prognosis, when you are deploying, you may find that it does not work as expected. So it's tricky to point out that you should use something.

  

Is working offline in my situation ideal?

Only you know it. Is everything working well? So it seems to be enough. You do not have to look for the ideal if you are attending to everything.

Are you having problems? So you need to diagnose in detail what is happening and then look for prognostics.

  

Which way to go?

Again only you can answer this, any answer that tells you the way to follow will be only an opinion, it will have no relevance whatsoever.

Roughly you have to build your application so you do not depend on the central database to be working. Whether this is feasible or ideal I do not know. But this is certainly necessary.

If you can not solve the infrastructure problem you will have to give up something. It may be that synchronization, if well done, does not cause any major problems, but it may be that it will not make some things you want.

There is no miracle. Just as you can not run the latest World of Warcraft on a 386, you can not maintain real high availability with poor infrastructure.

I doubt you can solve the synchronization problem with SQL Server features only.

Of course if in the background the databases are decentralized in the units and only concentrates the data centrally (does not seem to be the case by the report) there it is easier. But I think you're already doing this. Maybe you do not accept the drawbacks of this but I do not think there is anything that can be done other than ensuring good timing.

I assume you have already read documentation . I have doubts if any of them solve your problem. I think you've noticed that.

I'm sorry if this does not exactly answer what you want but it's not easy to give an expressive answer to a generic question.

If you have more specific problems in the middle of the process you would probably be asking good questions. I hope others can give you some more relevant information other than meaningless suggestions.