E-commerce Summary (iii) building the master-slave architecture of the database

This time, has been summing up the e-commerce system related basic technology and architecture, wrote a lot of things. But still found a very important, very basic aspect is not mentioned, that is the database read and write separation of the master-slave architecture. Perhaps after the development of a large mature company, the master-slave architecture is outdated and replaced by a more complex database cluster. But as a small e-commerce company, the master-slave architecture of the database should be the most basic. Any large system architecture is constantly evolving. The master-slave architecture is the most basic architecture in the database architecture. So after studying the master-slave architecture, we can understand more complex architectures.

　　Why first read and write separation?

For a small web site, a single database server may be able to meet the requirements, but in some large web sites or applications, a single database server may be difficult to support large access pressure, upgrade server performance, the cost is too high, it must be scale-out. There is, the library, read, write is the operation of a database, after more data, the database read, write performance will have a great impact. It is also a challenge for data security and system stability.

　　What are the benefits of database read/write separation?

1. Separate read and write operations on different databases to avoid performance bottlenecks on the primary server;

2. When the primary server writes, it does not affect the query performance of the query application server, reduces the congestion and improves concurrency;

3. Data has multiple disaster recovery copies, improve data security, and when the primary server failure, can immediately switch to other servers, improve system availability;

The basic principle of read and write separation is to allow the primary database to handle transactional increment, change, delete operations (INSERT, UPDATE, delete) operations, and to process select queries from the database. Database replication is used to synchronize changes caused by transactional operations to other slave databases. In SQL, for example, the main library is responsible for writing data and reading data. The Read library is only responsible for reading data. Each time there is a write library operation, synchronize updates to the Read library. Write library on one, read library can have more than one, the use of log synchronization to achieve the main library and multiple read library data synchronization.

　　One: SQL Server read-write detached configuration

SQL Server provides three technologies that can be used for the implementation of data synchronization between master and slave architectures: Log shipping, transactional replication, and new features in SQL 2012 always on technology. Their merits and demerits, the specific people go to Baidu, here to provide a log delivery method of data synchronization, address.

　　Two: C # database Read and write operations

C # 's request database operation, the database of a single database and a master-slave schema is still different. Master-Slave architecture of the database, in order to ensure data consistency, the general main library readable writable, from the library is only responsible for reading, not responsible for writing. So, the actual C # when requesting the database, still have to be treated differently.

1. The simplest is: Configure two database connections, and then in each database call location, distinguish the read-write request corresponding database server, such as

　　　　

2. The second solution is to determine whether the SQL statement is a write statement (INSERT, UPDATE, Create, Alter) or read statement (Select). Demo Download (PS: This demo for my summary, and the actual production of the DLL is not the same, but the principle is the same, everyone bytes wrap it up. ）

   　　　  /// <summary>        ///depending on the database statement, select the appropriate DB/// </summary>        /// <param name= "SQL" ></param>        /// <param name= "CommandType" ></param>        /// <returns></returns>         Public StaticDB Selectdb (stringsql, CommandType commandtype) {            BOOLRedirect2writabledb =false; SQL= SQL. Trim (). TrimStart ('\ r'). TrimStart ('\ n'); if(SQL. IndexOf ("UPDATE", StringComparison.OrdinalIgnoreCase) >=0) Redirect2writabledb=true; if(SQL. IndexOf ("DELETE", StringComparison.OrdinalIgnoreCase) >=0) Redirect2writabledb=true; if(SQL. IndexOf ("INSERT", StringComparison.OrdinalIgnoreCase) >=0) Redirect2writabledb=true; if(SQL. IndexOf ("CREATE", StringComparison.OrdinalIgnoreCase) >=0) Redirect2writabledb=true; if(SQL. IndexOf ("ALTER", StringComparison.OrdinalIgnoreCase) >=0) Redirect2writabledb=true; ////If it is a stored procedure, the default is to take writable DB.             if(Redirect2writabledb | | commandtype = =commandtype.storedprocedure) {returnDbconfiguration.writabledb; }            Else            {                intRandom =Generaterandomnumber (); intDbindex = random%DBConfiguration.ReadDBs.Count; returnDbconfiguration.readdbs[dbindex]; }        }

At the same time, increase the associated database configuration

<?xml version="1.0" encoding="utf-8" ?>< connectionstring>  <writabledb>data source=192.168.  99.242; Initial catalog=dbtest; uid=sa;pwd=test123; multipleactiveresultsets=true</writabledb>  <ReadDBs>    <db>data source=192.168 . 99.241; Initial catalog=dbtest; uid=sa;pwd=test123; Multipleactiveresultsets=true</db>  </ReadDBs></ConnectionString>

E-commerce Summary (iii) building the master-slave architecture of the database