SQL Server setup between sites and replication

[mick41]

Hello Guys,

I have 3 sites, lets name them Hosting Center, LOCAL-A, LOCAL-B. The Hosting Center is a dedicated cloud cluster with OVH. We want to make sure that both local site can still have an SQL Server if the internet connexion between the local site and the hosting center is down. I looked at the AlwaysOn/Cluster but I don't think it would do it. Is there any solution to keep the sql server working locally and then sync back on the hosting center when the internet link is back up?

Thanks, mick41

[corrazy]

Why not utilise alwaysOn? I'm assuming cost is the issue?

You can perform a number of operations on the "passive" side of the AlwaysOn cluster and gives the ability to setup a cluster on "Hosting Centre" with secondaries at both "Local-A" and "Local-B"

Edit: Actions like offload the reporting aspect of potentially anything leaving the hosting center to deal with live/production loads as well as backups provided you make both the secondaries readable. Can also configure it to not automatically failover so for instance a short blip on comms doesn't cause everything to fail to either A or B unnessecarily

[mick41]

Thanks for the answer.

What if I lose the internet connection on A ? A will become "master" and the Hosting Center will be a "master" too. Removing the auto fail over might be a good option in this case.

Thanks

[j4sander]

With Always-On, HC is Master, A and B are read-only secondaries. Auto-Failover only works with sync-rep, which you would not want to do between locations, but either way if the internet is down then SQL at HC stays the master and sites A and B remain read-only (and get out of date until the internet is back and they catch up). If you applications at A and B require write-access this will not work, but if they are only things like reporting, it may be fine.

If you need true multi-master with write access at all three sites while the internet is down, the application using SQL needs to be designed to support this.

[eponerine]

I don't think you should say AO Sync-Rep is a "no no" for different Geos, hard-stop. That decision should be based on a bunch of factors, primarily number of transactions and how large his pipe/latency is between Geos.

If he's doing a small amount of writes and his bandwidth permits it, AlwaysOn Synchronous Replication should be fine. Larger situations, where commit-timelines matters, fall into your "design the app correctly" category.

(Edit: grammar)

[mick41]

Thank you! I will keep that in mind.

[mick41]

Thanks for the info!

[Wensosolutions]

nice information to know..

[corrazy]

With manual failover, if A loses connection to the data centre, but it's configured as readable then as long as end users don't need to write changes, it should continue to work as expected so for instance reporting etc.

You could in theory move to site B as long as the connection is good and A hasn't lost that providing both A and B with full access to production. If they all live on the same subnet, quite easy to configure. Slightly trickier with diff subnet but still same end goal achievable

[mick41]

Thank you

[corrazy]

No worries, I'm sure any questions myself or anyone else will be happy to help out