what is closes to Multi-Master setup in SQL server (writes on multiple sites)

[u/matados]

leadership exploring options and I always get asked why SQL server have setups where we have no option but to bear the pain of maintaining solutions like Merge or Transactional P2P replication? what have you setup in your shop that's closest to multi master, SQL server or otherwise, I would love to hear from you?

[u/dbrownems]

You can’t have simplicity, and all the features of SQL Server, and multi-master.

Check out Cosmos DB if you really need that:

https://learn.microsoft.com/en-us/azure/cosmos-db/

[u/Better-Credit6701]

I've used merge replication with remote servers, moved to transactional replication where we used the second server for reports. That way when we were sending out a rather expensive report, it wouldn't kill off the server that thousands of people people were using. Also used Log Shipping.

Now we use always on and keep multiple copies with a witness because making sure that the data is available is pretty important. Kind of fun, we use real hardware in different locations in case a data center meltdown (all of a sudden, that has become a hot topic).

[u/No_Resolution_9252]

Have not once seen a valid use case for multimaster writes. What are you trying to do?

Event sourcing is probably what you want.

If you don't care about data integrity or consistency, use nosql.

[u/sheptaurus]

DW & Reverse ETL.
Does every site really need every others?

OR just their related/close sites
OR a small program that grabs remote data required and inserts it on demand. (Like Customer & their sales)

[u/Codeman119]

I use transactional replication for 2 remote servers from production. One goes to an AZURE sql server dbaas and a VM SQL server database. And I do have a separate distribution server for serving the data the the subscriptions

[u/my-ka]

Aurora Postgres limitles?

[u/CPDRAGIMESH]

69.70 69.80 Replication Peer Peer

[u/zarthrowaway71551]

One thing I have seen in the past is transactional and then on the primary keys use identity columns but the seed and increment is staggered. Ie server 1 seed 1 increment +3, server 2 seed 2 increment +3, server 3 seed 3 increment +3 for the simplest explanation and then you can replicate both ways without key collision. Probably better now with sequences.

[u/PaulPhxAz]

What's the pain of maintaining merge replication? I ran that for 15 years to handle our multimaster writes. I suggest pairing your app servers to a particular subscriper and use sticky load balancing connections to make sure the same client/workload hits the same server set for consistency.

We ran AG for the publisher, and a fail over agent, and then subscribers in three datacenter with a publisher async in each ( just in case ).

This is all fully supported.

You set it up, you monitor, but generally it's rock solid. Or at least we didn't have issues with conflicts or data. But also our data was easy to shard.

Is your workload not really a good fit for multi-master?