Introduction to SQL Server read-write separation implementation scheme

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 slave servers.

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

1. By separating "read" and "write" operations on different database servers, the contention for CPU, memory, storage and network resources of the primary server is reduced;

2. When the main server is improved, it does not affect query server query, reduce the occurrence of congestion and improve concurrency;

3. The establishment of disaster-tolerant replicas can even achieve geo-disaster recovery, reducing data loss in the event of disasters.

In order to achieve database read/write separation, the application needs to make the following adjustments:

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

2. Additions or deletions or real-time queries using a connection string pointing to the primary server;

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

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

Introduction to SQL Server read-write separation implementation scheme