Introduction of SQL server2005 for database read-write separation _mssql2005

The size of the internet increases by one times every 100 days, and customers are looking for 7-day x24 hours of uninterrupted availability and faster system response times, rather than repeatedly seeing a site "Server Too Busy" and frequent system failures.

With the increase of traffic volume and the rapid growth of traffic volume and data flow, the processing performance and calculation intensity of each core part of the network increase correspondingly, which makes the single equipment cannot bear. In this case, if you throw away the existing equipment to do a large number of hardware upgrades, will lead to the waste of existing resources, and the next increase in business volume will lead to another hardware upgrade of the high cost of investment. Therefore, the load balancing mechanism arises.

For load balancing, the author often touches on the load balancing mechanism of Oracle. Let's start with a simple look at Oracle's implementation of load balancing.

Real application clusters is a dual-machine parallel server (8i and previous versions called Oracle Parallel Server,ops) to implement a multiple-machine shared database in a clustered environment to ensure high availability of applications, At the same time, parallel processing and sharing load can be realized automatically, and the error and no breakpoint recovery can be realized when the database is in trouble. It can automate load balancing, failover, and planning downtime to support high-availability applications. If a node in a parallel server fails, transparent application fault tolerance can automatically transfer the user to another node to continue running, and the application continues to execute without the user noticing. This increases the continuous availability of systems that are cyclical and aperiodic failures. The failure of a process can be completely transparently transferred to another node, which, by appropriately configuring, can specify that all queries are cached on the client, so that they can be reset on the transferred node.

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

new features of SQL Server 2005

Transactional replication of End-to-end topologies

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.