Read and write separation of SQL Server concurrency series

Database processing high concurrency two ways of vertical and horizontal differentiation

vertically differentiate [distributed database]: separate database by module.

Disadvantage: The design of each module of the system needs to be higher, cohesion and low coupling, the transformation is more difficult.

At the same time, if the data reached a new critical point, there is still the possibility of poor access performance, such as an order database, the order quantity reached 10 million orders.

horizontal distinction [Read and write separation]: The way of the sword, the main database (the real) and a number of query database (mirror), the primary database to take the deletion and modification and realtime query function,

The query server assumes a query with a large amount of data with conditions.

Read and write separation benefits
1. Detach the Read and write operations to a different database server. Reduce contention for primary server resources.
2. When the primary server additions and deletions are improved, the query server query is not affected. Reduce the occurrence of blocking.
3. Application long must submit a report, unreasonable query request, will not cause a long time lock table.
4. The disaster tolerant mechanism allows the query server to take over user requests when the primary server fails.

Practice
1. Set two connection strings in the application configuration. One point to the primary server and one to the query server.
2. Additions or deletions or real-time query the connection string to the primary server.
3. Allow non-real-time queries and report requests to point to the connection string of the query server.

master server Data Synchronization technical parameters

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

Personal questions and ideas:

Also see a practice is to divide 50 million data into a different database, each time you plug in the relationship between the data and ID is inserted into a routing database, query the first time from the routing database to query the database of this ID, and then to check the database data.

This approach is designed to solve the problem of excessive data size and seems to work well, with different data for each database. But what about the disaster-tolerant mechanism?

What are the two ways to choose?