Read-write separation improves SQL Server concurrency

Transferred from: http://www.canway.net/Lists/CanwayOriginalArticels/DispForm.aspx?ID=476

In some large web sites or applications, a single SQL Server server may have difficulty supporting very large access pressures. A lot of people at this time, the first thing to think about is a solution to the performance problem-load balancing. Unfortunately, all versions of SQL Server, including the March 2012 release of SQL Server 2012, do not provide this functionality.

There are two ways to extend a single SQL Server server to address performance bottlenecks:

First, distributed database. extend and distribute databases to multiple servers, distributed across multiple servers to store different data, and address performance bottlenecks by distributing data and access pressure across multiple servers. Take a large e-commerce website database as an example, you can distribute data to multiple server storage according to the Business module or function module. This method is difficult for the program transformation, the design is more complex, this article does not introduce.

Second, read and write separation. read/write separation is a common design of database systems for medium-sized applications that provide non-real-time query functionality, extend performance, and increase concurrency by synchronizing data from the primary server to other SQL Server servers.

The benefits of database read/write separation are as follows:

1. Reduce the contention for CPU, memory, storage, and network resources of the primary server by separating "read" operation and "write" operation to different database servers;

2. When the main server additions and deletions are improved, the query server query is not affected, and the congestion is reduced, and the concurrency is improved.

3. When the application submits a report request, unreasonable query request, will not result in a long lock table;

4. The establishment of disaster-tolerant replicas and even the remote disaster recovery, in the event of disasters, can reduce the loss of data;

Most of the enterprise internal applications, may not need such a large architecture, a primary database server and a query server is usually able to meet the requirements of read and write separation. And the application is easy to adjust:

1. Set two database connection strings in the application configuration file, one point to the primary server and one to the query server;

2. Adding or deleting changes or real-time queries use a connection string to the primary server;

3. Allow non-real-time queries and report requests to use a connection string that points to the query server.

So how does the data on the primary database server sync to the query servers? What is the interval of synchronization? When the primary server fails, can the query server take over the user's request? What are the technical requirements?

SQL Server provides three technologies that can be used for read-write separation implementations: Log shipping, transactional replication, and new features in SQL 2012 always on technology. The comparison of these three technologies is as follows:

SQL Server Log Shipping

SQL Server Replication

SQL Server always on

Read-write separation improves SQL Server concurrency