Many people hold a misconception that Peer-To-Peer replication and active-active clusters can provide write scalability. This article will provide a very brief overview of both technologies and some details as to why these technologies are not capable of scaling write operations.
Peer-To-Peer replication will provide you with the ability to read and write from multiple nodes, which in turn provides read layer scalability and read/write high availability. Although read operations scale very well, write operations are not improved and the write performance would be similar to that of a single node. This is true because all inserts, updates, and deletes are written to all nodes in near real time.
An active-active SQL cluster is a term that is a little misleading as this technically doesn’t exist in SQL Server. It is really a multiple instance cluster since a SQL cluster can only have one instance running on one node at a time. To make the other node perform database operations, you would need another instance running on that node and manage them in such a way that they are always running on a different node from one another. As this scenario does not allow you to run a single database across more than one node, this technology will also fail to provide you with scalability of your database write operations.
There is no out-of-the-box functionality that will provide write scalability in SQL Server, but there are manual processes that can be undertaken to accomplish this goal. For example, you may have heard or seen the word sharding before during your quest for the holy grail of write scalability. Sharding is the technique of splitting up a database into smaller databases. Sharding the database is really the only way to provide scalability of write operations in your Microsoft SQL Server environment.
Ultimately, the limitation of how scalable you want your write operations to be depends on how much work you’re willing to put into splitting up your database and modifying your application so they can deal with the interaction between the multiple databases.
What has worked for you? Let us know!
