Instant synchronization of multiple SQL servers

First of all, I am sorry if this is a duplicate. I was unable to find anyone who adressed this particular question, or I might have failed to realize it as I read it. Most likely I used the wrong search-phrases, as I am not all that familiar with the terminology.

We have 3 servers:

A. SQL-server

B. Alarm system

C. Website + API + Other

Server B and C has to connect to server A all the time, and there have been several instances of downtime which has resulted in a lot of customer dissatisfaction.

We want to have the following: (different letters to make commenting easier, though it may not :p )

W. Alarm system (with SQL-server)

X. Website + API (with SQL-server)

Y. Website + API (with SQL-server)

Z. Other (with SQL-server)

EDIT: X and Y are technically identical, but X would normally host the website, and our apps and 3rd party solutions would normally connect to the API on server Y, to ensure that a hickup is only felt through one channel. If X were to crash, then Y would serve both the website and the API until X was back online and vise versa.

It is vital that the SQL-servers are synchronized. If a client makes a change in the database through the website (server X) and then activates the Alarm system W (to which the change matters) then it must have allready been synchronized.

Each system is using only some of the databases and tables, to save on resources we could sync only the vital data instantly, and the rest every few hours or so. Edit: not an option

We will be using SQL Server 2014 on all servers (unless someone convinces me otherwise)

• Do we need any 3rd party software to accomplish this?

• We are obviously keen on using the most cost-efficient solution, both money-wise and resource-wise, but customer dissatisfaction must end at all cost, we have never lost a client and we don't intend to lose one for having been cheap.

• Would we have to use triggers, or should we, for the vital tables?

• Could we use views?

• That is t

I may be understanding this slightly wrong so I apologise if I am.

If you have two databases that you need to be identical and are on 2014 then use the AlwaysOn High Availability Group.

Since you're data centres are at separate locations use the Async mode

This will mean the database is kept completely up to date (all be it possibly with a few second delay) and you can have the secondary node as a read-only replica, this means that your alarm system can read into that database run all the checks etc you would normally.

the Always on system keeps everything up to date, so if the connection drops, when it comes back online it will merge over all changes

It also means that if your main centre goes down you can set it to automatically failover to the secondary, when the main datacentre comes back online it will re-sync with the (now) primary node, at which point you can fail it back over to your main centre.

You can run this on multiple databases, so we have our main DB and our Admin DB synced across our nodes, however what runs all the jobs and direct actions on each side is not replicated so can stay independent of each other

StackExchange Database Administrators Q#117285, answer score: 3