James Serra provides a number of options around distributed writes:
In SQL Server, scaling out reads (i.e. using Active secondary replicas via AlwaysOn Availability Groups) is a lot easier than scaling out writes. So what are your options when you have a tremendous amount of writes that scaling up will not handle, no matter how big your server is? There are a number of options that allow you to write to many servers (instead of writing to one master server) that I’ll call distributed writes. Here are some ideas:
-
Peer-to-Peer transactional replication (or Multi-master replication) with SQL Server. See Peer-to-Peer – Transactional Replication
-
Sharding in Azure SQL Database via elastic database tools which requires coding. See Building scalable cloud databases. You can also implement sharding in code for SQL Server
-
Merge replication in SQL Server. See Merge Replication
-
Create a messaging and queuing application in SQL Server Service Broker where all writes are placed on the queue and sent to different servers
Read on for more options and some additional thoughts around Cosmos DB. My first inclination would be to put Kafka in front of a distributed write system, but that’s my bias.