0

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.

Improve this question
1

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?).

Improve this answer
6
  • Hi Sean thank you for your answer. We tried to work around this already for example when the middle tier application detects the intra-site failover, it tries to fire a customized stored procedure at target servers doing the clean up and re-establish bidirectional transactional replication. Our current approach may work if we can quickly sync the two database prior to the re-setup of the bidirectional transactional replication. Can you please advice on how to quickly bring two databases in sync? – user3444882 Mar 29 '18 at 13:37
  • @user3444882 That's not the way it needs to be architected for your business requirements. You're worried about "resetting" after a failover which is incorrect. You want to have the middle tier deal with transactions and commitment on each individual instance of SQL Server so that all of the instances stay in sync. This will cause high amounts of transactional overhead but it'll get what you want. The only other option is to revisit your business requirements and see if they really do need to write at both sites simultaneously. – Sean Gallardy - Retired User Mar 29 '18 at 19:02
  • I guess I forgot to mention that even though both sites can be written simultaneously, they are not touching the same rows in a table and yes it is in the requirement that both sites can write even though the one site will be doing more intense work. – user3444882 Mar 30 '18 at 2:19
  • @user3444882 If it's writeable there is no guarantee of what you just said other than someone's "promise" they won't do it. That's not something I'd just assume to be the case. If it's just data munging then use a separate database on each instance to work with data but only update it in one place. Again, you're looking for a very specific architecture that isn't a normal use case for most rdbms - some solutions exist but it's not going to be 1-1 with all of your requirements. – Sean Gallardy - Retired User Mar 30 '18 at 12:24
  • We are also the people developing on the application level logic such that each entry in a table is distinguished by its gateway, ensuring that two sites wont be touching the same row ever. So I did some reading about the AlwaysOn Failover Cluster Instance and my understanding is that if have one set up on each site and somehow managed to add a peer to peer tranactional replication between the two. Do you think such solution is feasible? I cannot seem to find any article on the internet mentioning the combination of these two technologies. Thank you. – user3444882 Mar 30 '18 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.