ASP. NET implements database read-write separation (sqlserver2005,oracle) "Go"

A lot of time we certainly hope that the database read and write and the program is separate, so that can improve the business and improve the performance of the server, let me take SQL Server 2005 as an example to introduce the database read and write separation method, you can refer to.

Let's focus on how SQL Server 2005 is load balanced.

New features for SQL Server 2005

SQL Server 2005 provides enhanced support for transactional replication on end-to-end (peer-to-edge) topologies. The topology of peers supports an unlimited number of publishers, and they can exchange transactions with each other.

One-to-peer topology is a huge step forward for SQL Server. A multi-point server can now change data and replicate transactions to other publishers. This means that subscribers are no longer confined to the primary reporting environment and can be distributed across servers in a global shared way for transactional workloads. As the number of users increases, simply add servers to the group.

In addition to distributing the load, this topology increases availability. If the server at any point is unreachable, the load is shared by other servers in the pool because each server has all the data collections available on all other servers.

Database mirroring and Snapshots

SQL Server 2005 introduces the concept of database mirroring to help achieve high availability. As a special reminder, database mirroring can be used on SQL Server 2005 as long as it is officially released. However, mirroring is only supported for SQL Server 2005 Service Pack 1.

A database snapshot is another feature introduced in SQL Server 2005. A snapshot is a clone of a database at a point in time. As long as you take a snapshot of the mirrored database, you can ask the user to query the snapshot. The snapshot generation usually takes only a few seconds, as it actually does not copy any data during this process. Therefore, to distribute the load to the primary and standby servers, you can mirror the database and then periodically take a snapshot of the backup server. You can also use snapshots to report on the primary server.

Software for load balancing of SQL Server 2005

Interlayer

To realize the load balancing technology of the database, we must first have a control side that can control the connection database. Here, it truncates the direct connection between the database and the program, which is accessed by all programs, and then by the middle tier to access the database. In this way, we can control access to a particular database, and then we can adjust each connection to the database according to the current load of the database. Benefits in two ways: first, it successfully put the database into the intranet, and better protect the security of the database. If the database is also on the public network, 1433 port is vulnerable to attack, so to protect the database connection with it, the middle tier is used. It can better protect the network in the database. Second, all connections to the database can be controlled to make it easier for DBAs to manage the data and to see which connections consume database resources to better optimize the code.

However, there are two points to note: First, must be made into Windows service program. Windows evolved to today, if the service is made more stable and more secure with an integrated large system, it can be used even if the user does not log on to the machine. Second, you must use multiple tiers. From the role of the middle layer can be seen, it undertook all the database connection, so, once the problem, it will cause the entire system to be paralyzed. So it is necessary to do multiple layers, so that if a bad one can log on to another.

Data synchronization for multi-database

In the middle tier, the next step is to set up a database cluster. For load balancing, the most important thing is that all server data is synchronized. This is necessary for a cluster because, if the data is out of sync, the data that the user reads from one server is different from the data that is read from another server, which is not allowed. Therefore, the data synchronization of a database must be implemented. Here set up a database for writing, set two for the read out of the database, because according to statistics, generally speaking, 70% of the database operation is read operation.

First, make a publisher on the write database, primarily based on the SQL Server 2005 replication technology, and select the tables you are going to use. Note that you want to use the impersonated user on the connection and then select the SA user when sharing, so that the data can be shared.

Second, in the two read server to do the subscription service, pay attention to the same things, such a "write two read" database cluster is complete.

Secure backup of the database

In a large system, the fault-free system is very important, but in the system just now, if the server used for writing suddenly bad, the whole system will have problems, so it is necessary to make a backup.

Database mirroring is a strong launch of SQL Server 2005, it is to achieve a secure transfer of the database, so it is necessary to take out a separate machine to do the backup server, the full image is written to the server, so that even if the write server is bad, it can automatically transfer to the backup server to ensure that the user is not affected.

This is actually equivalent to the implementation of the server disaster recovery management, but it is important to note that in such a system, there must be a server as a monitoring server, in order to see which server is broken, to quickly adjust after the machine error.

Status of the callback database

The database server is complete, the overall machine cluster architecture has been built, the next task is to configure the program.

First, in the read and write server put a monitoring program, it must also be Windows services, so more stable, second, it can be set to every 30 seconds or a certain time, the server's CPU, memory, network card traffic and current database status and other information sent back. Here, you need to set a weight to measure the respective proportions of information such as CPU, memory utilization, and so on. In this system, it is recommended that CPU utilization prevail.

Load balancing for middle-tier implementations

So far, all the preparation has been completed, including the database setup and configuration, the middle tier location, the following is the use of software to achieve this load balancing.

First, when a user has a database request, the first to determine whether to read or write, if it is written, directly back to the write server, so that when the write server finished writing data, almost can return to the other two machines within 3 seconds.

Second, when a read request is encountered, according to the data returned by the monitoring to determine, according to the weights just returned to a current most idle machine. It is important to note that at this time it is better to do a recorder, to maintain a period of value, you can let the administrator set themselves, better to do a few database pressure balance.

Finally, if the main write server suddenly broke down, the program can automatically switch the backup server, using just the backup server as a write server, and then make an alarm system to notify the administrator. Similarly, when the monitoring server discovers that the other two read servers are broken, the administrator is also notified automatically to handle the abnormal situation of the server, which can ensure the stable operation of the system and is easy to manage and maintain.

In short, with the software and Microsoft SQL Server 2005, some of the new technology, can easily achieve load balancing, so not only can not be implemented without hardware, easy to control the administrator, more conducive to DBA management database, timely detection of problems.

Code

The code is as follows

Copy Code

<summary> Database Service Factory Version: 2.0 <author> <name>shecixiong</name> <date>2013.05.28</date> </author> </summary> public class DataFactory { <summary> Getting the connection string from configuration information </summary> <param name= "Key" ></param> <returns></returns> private static string GetConfig (String key) { return confighelper.getappsettings (key); } <summary> Link SQL Server database Operation Write </summary> <returns></returns> public static Idbhelper Sqlserverread () { return new Sqlserverhelper (GetConfig ("Sqlserver_ip_read")); } <summary> Link SQL Server database Operation Read </summary> <returns></returns> public static Idbhelper Sqlserverwrite () { return new Sqlserverhelper (GetConfig ("Sqlserver_ip_write")); } <summary> Link Oracle Database Operation Write </summary> <returns></returns> public static Idbhelper Oracleread () { return new Oraclehelper (GetConfig ("Oacle_ip_read")); } <summary> Link Oracle Database Operation Read </summary> <returns></returns> public static Idbhelper Oraclewrite () { return new Oraclehelper (GetConfig ("Oacle_ip_read")); } } }

App.

copy code

<!--Whether the connection string is encrypted-->         <add key=" Constringencrypt " Value= "false"/>         <!--connection string-->          <add key= "Sqlserver_ip_read" value= "server=192.168.1.11;database=rm_db; Uid=sa; Pwd=qwertyuiop "/>         <add key=" Sqlserver_ip_write "value=" Server =192.168.1.10;database=rm_db; Uid=sa; Pwd=qwertyuiop "/>         <add key=" Oacle_ip_read "value=" Data source= IP1; User Id=system; PASSWORD=QWERTYUIOP; " /> <add key= "Oacle_ip_read" value= "Data source=ip2; User Id=system; PASSWORD=QWERTYUIOP; " />