How to prevent data loss during intra-site failover We are looking to build a MS SQL server (either SQL server 2016 standard or enterprise edition) solution but are having some troubles. This post can be lengthy and if you think it is TLDR, our question is simply as:

Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data.  Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?

Starting here I will be trying to explain what we have tinkered with and the issues we are running into. We use SQL server 2016 developer edition for our testing. Note that it is treated as if it is a standard edition as of now.

Set up:

Two SQL servers per physical site are joined together in a Basic High Availability group with one being primary and one being passive secondary. Then we wrote some T-SQL script based on this link to set up bidirectional replications on the database level. For simplicity, let’s say we have Server 1 and Server 2 on physical site A and Server 3 and Server 4 on physical site B. Initially, Server 1 is primary on site A and Server 3 is primary on site B. The two are also syncing with each other which means whatever the change is written to Server 1’s database is replicated over to Server 3’s database and vice versa.

Issue:

So after the set up, we tinkered with intra-site fail over. For example, manually failing over from Server 1 to Server 2 at site A. what we observed is that during this failover, if any data in a table was modified or added from site A or site B. After we re-run our script to clean up orphaned replication and re-setup bidirectional transactional replication between Server 1 and Server 3, the data added/modified during intra-site failover will not be synced. For example, both Server 1 and Server 3 touched a table. Server 1 inserted a new entry Test 2 into the table and Server 3 inserted a new entry Test3 into the table.

Server 1 table view                                        

Aaa    Bbb
Test1  Test1
Test2  Test2

Server 3 table view

Aaa     Bbb
Test1  Test1
Test3  Test3

After the bidirectional replication script is executed on both Server 1 and Server 3, the added data is not synched. However, changing on Test 1 will be reflected on the other server. This makes sense because the script or bidirectional transactional replication doesn’t know how to deal with tables that have different data?

Essentially during intra-site failover, there is a short time window that both database can be accessed without bidirectional transactional replication yet. As a result, when the bidirectional transactional replication is set up again, two database won’t be fully synched with each other. This is not acceptable because requirement needs all four SQL server instance to be fully synched with one another. Pleas advice on which HA solution or combination of different solutions we should deploy our servers with.

Sorry for the lengthy post since I wanted to provide as much information as possible and thank you for your help.
The crux of your issue is:


  Given two physical sites, each site has two SQL servers with requirement that at any given time application is able to access one of its local SQL server instance to write/read data. Then the data need to be synchronized cross site and all four servers will contain synchronized data. Which SQL server’s HA solution or replication solution or combination of both should we pick to achieve such result?


The "one size fits all" answer is, the current offerings that SQL Server has won't fit into this. SQL Server doesn't work well in a multi-master zero data loss game. Other database solutions have similar issues where multi-master has the potential (like SQL Server) for data loss. I don't believe you're going to get a single bullet one size fits all solution here from the database layer itself.

The only way, with SQL Server, to get what you're asking for it to create the application or middle tier around this philosophy and put the onus on the application/middle tier to make sure all databases are in sync and let the application/middle tier determine how to handle what happens when they aren't (for example, a write fails on one database but 3 the other databases successfully committed - do you proceed?).