Best high availability path for my need

Question

I have studied a lot about high availability in SQL Server, in its most diverse options, that SQL Server has. But so far I could not find the solution that better meets my need.

My setting:

I need to serve a chain of stores, in case 30 stores, I usually work with my bank on cloud and everyone works online. The problem is that the network has to be managed in a unique way (just a database, with all the movements, etc.. etc.), but the stores have serious infrastructure problems regarding the internet, many interruptions in the service.

In addition to frequent service interruptions, when active, the connection is of poor quality, which makes me wish to work primarily offline and "synchronizing" the data to an online database. I know this is not a high availability solution. But I’m a little lost as to which solution I should walk.

1. SQL Server has some solution that helps me with this?
2. Working offline in this my situation is ideal?
3. Which way to go?

Answer 1

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

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

Casual synchronization

A solution for this is that each store works as if it were completely independent, with its own database, sending when possible a part of your data that is of interest to someone else elsewhere - in this case, a centralized database.

The appeal of Microsoft SQL Server that can help you with that is the replication. In this architecture, each store has its own database server and when 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 your movement and receive only the registration of products or updated price rules, IE, It is not necessarily a mirror - each store does not need to receive the movement of all the other.

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

If you design the system and the database predicting 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.

A possible approach when designing a casually connected distributed system

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

• This suggestion may be a little counterintuitive: try to draw the whole system as if it were single and centralized - The system and database that runs in each shop is identical to the one that runs on the hub. This simplifies development and makes deployment more flexible, allowing fully online, completely offline or more than one central (regional centers), for example.

• Try to make everything transparent to the system - it doesn’t need to know if the data being read has been entered locally or synchronized remotely.

• Remember that not having high availability connection is a business decision, so the business area has opted for another type of investment (synchronization via infrastructure tools and resilience plans). If the system tries to cope alone 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 depends on the central office and should also be aware of the synchronization routine of this data (for example: pricing rules are entered into the central office the previous afternoon to be available in stores in the first hour of the day).

• Invest heavily in resilience and synchronization monitoring. Store users need to be alerted, for example, that prices for the day have not been synchronized, so the responsible user can for example request a fax and enter manually with prices, repeating the work that has already been done at the center, as would be done in case there is no central.

Regarding the high availability in each environment (central and stores), although it seems to me secondary in the question, you can select one of the options of SQL Server, as cluster or mirroring database.

Answer 2

SQL Server has some solution that helps me with this?

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

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

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

Even with the correct diagnosis and prognosis, when you implant it may find that it does not work as expected. So it’s tricky to crave that you should wear something.

Working offline in this my situation is ideal?

Only you know this. Is everything working well? Then it seems to be enough. No need to look for the ideal if you are helping yourself.

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

Which way to go?

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

Roughly you have to assemble your application to not depend on the central database being working. Whether this is feasible or ideal I do not know. But it is certain that this is necessary.

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

There is no miracle. Just as you can’t run the latest World of Warcraft on a 386, you can’t 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 concentrate the data centrally (it does not seem to be the case by the report) then it is easier. But I think you’re already doing it. You may not accept the drawbacks of this but I don’t think there’s anything that can be done besides ensuring good synchronization.

I imagine you’ve read the documentation. I have doubts if any of them solves your problem. I think you’ve figured it out.

I’m sorry if this doesn’t answer exactly what you want but it’s not easy to give an expressive answer in a generic question.

If in the middle of the process you have more specific problems, they’d probably give you good questions. I hope others can give some more relevant information that are not nonsense suggestions.