asp.net realization of database read-write separation (sqlserver2005,oracle)

Here we focus on how SQL Server 2005 is load-balanced.

New features of SQL Server 2005

SQL Server 2005 strengthens support for transactional replication on an end-to-end (Peer-to-peer) topology. Peer-to-peer topologies support an unlimited number of publishers, and they can exchange transactions with each other.

Peer-to-peer topologies are a huge step forward for SQL Server. The multi-point server can now change the data and replicate the transaction to other publishers. This means that subscribers are no longer restricted to the primary reporting environment and can be distributed across the world through transactional load sharing. As the number of users increases, simply add servers to this group.

In addition to distributing the load, this topology increases usability. If the server for any one 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. The special reminder is that database mirroring can be used on SQL Server 2005 as long as it is officially published. However, mirroring is only supported for SQL Server's 1 Service Pack.

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

Software implementation of SQL Server 2005 load Balancing

Interlayer

To realize the load balancing technology of the database, we must first have a control terminal that can control the connection database. Here, it truncates the direct connection between the database and the program, and all programs access the middle tier, and then the middle tier accesses the database. In this way, we can specifically control access to a database, and then we can adjust each connection to the database according to the current load of the database. Benefits in two aspects: first, it successfully put the database into the intranet, better protect the security of the database. If the database is also on the public network, 1433 port is very easy to attack, so to protect the database and its connection, use the middle tier. It can better protect the database in the network. Second, all connections to the database can be controlled to make it easier for DBAs to manage data and see which connections are more resource-intensive in order to better optimize the code.

However, there are two points to note: First, you must be made into Windows service program. Windows development today, if a large integrated system, to make the service program more stable and more secure, so that even if the user does not log on to the machine, you can use. Second, multiple layers must be used. From the role of the middle tier can be seen, it takes all the database connections, so, once the problem, it will cause the entire system paralysis. So doing multiple layers is necessary so that if a bad one can log in to another.

Implement data synchronization of multi-database

With the middle tier, the next step is to set up the build 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 not synchronized, the data that the user reads from one server is different from the data read from the other server, which is not allowed. So you must implement data synchronization for a database. Here, set up a database for writing, set up two database to read out, 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 replication technology of SQL Server 2005, and select the tables that are about to be used. Notice that the analog user is selected on the connection and then the SA user is selected when sharing so that the data can be shared.

Second, to do the subscription service on two read servers, pay attention to the same thing, such a "write two read" database cluster is completed.

Secure backup of the database

In a large system, the system is not fault-free is very important, but in the system just now, if the server used to write a sudden failure, the entire 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 safe transfer of the database, so need to take out a separate machine to do the backup server, the full image to the server, so that even if the write server is broken, it can be automatically transferred to the backup server to ensure that the user is not affected.

This is actually equivalent to the disaster management of the server, but one thing to note is that in this mirror system, you have to have a server as a monitoring server to see which server is broken to quickly adjust after the machine goes wrong.

Return the status of the database

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

First of all, the read and write server is placed on 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 the current database status and other information sent back. Here you need to set a weight to measure the proportion 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 establishment and configuration of the database, the location of the middle tier, the following is done with software to achieve this load balance.

First of all, when a user has a database request, the first judge is read or write, if it is written, directly returned to the write server, so that when the write server write data, almost 3 seconds to return to the other two machines.

Second, when a read request is encountered, according to the data returned by the monitoring to judge, according to the right value to return a current most idle machine. It should be noted that at this time it is best to do a logger, to maintain the value of a period of time, you can allow the administrator to set up, better to do a few database pressure balance.

Finally, if the primary write server suddenly broke down, the program can automatically switch back to the server, with just the backup server as a write server, and then do an alarm system to inform the Administrator. Similarly, when the monitoring server found that the other two read servers are broken, it will automatically notify the administrator to deal with the abnormal situation of the server, which can ensure the stable operation of the system, and easy to manage and maintain.

In short, with software and Microsoft SQL Server 2005, some of the new technologies can easily achieve load balancing, so not only can 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> Get 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 Action Write </summary> <returns></returns> public static Idbhelper Sqlserverread () { return new Sqlserverhelper (GetConfig ("Sqlserver_ip_read")); } <summary> Link SQL Server database Action Read </summary> <returns></returns> public static Idbhelper Sqlserverwrite () { return new Sqlserverhelper (GetConfig ("Sqlserver_ip_write")); } <summary> Link Oracle Database Action Write </summary> <returns></returns> public static Idbhelper Oracleread () { return new Oraclehelper (GetConfig ("Oacle_ip_read")); } <summary> Link Oracle Database Action Read </summary> <returns></returns> public static Idbhelper Oraclewrite () { return new Oraclehelper (GetConfig ("Oacle_ip_read")); } } }

App.config

The code is as follows

Copy Code

<!-- 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; " />