SQL Server implementation of database read and write separation introduction

For load balancing, the author often touches the load balancing mechanism of Oracle. Below we focus on how SQL Server 2005 is load balanced, interested friends can refer to the next ha

The size of the internet increases every 100 days, and customers want to get 7 days of 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 business volume, as well as the rapid increase of traffic volume and data flow, the processing performance and computing intensity of each core part of the network increase correspondingly, which makes the single device can't bear at all. In this case, if you throw away the existing equipment to do a lot of hardware upgrades, will inevitably lead to the waste of existing resources, and the next increase in business volume, will lead to another hardware upgrade of the high cost investment. As a result, the load balancing mechanism emerges.

For load balancing, the author often touches the load balancing mechanism of Oracle. Let's take a quick look at the implementation of Oracle's load Balancing scheme.

Real application clusters is a dual-machine parallel server (8i and previous version called Oracle Parallel Server,ops) to enable multi-machine shared databases in a clustered environment to ensure high availability of applications. Meanwhile, parallel processing and load sharing can be realized automatically, and the error-free and non-breakpoint recovery of database can be realized. It automates load balancing, fault repair, 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 without the user's awareness. This increases the continuous availability of periodic and non-cyclical systems that fail. The failure of a process can be completely transparently transferred to another node, and, with proper configuration, you can specify that all queries are cached on the client so that they can be reset on the transferred node.

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

new features for SQL Server 2005

Transactional replication of end-to-end topologies

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.

SQL Server implementation of database read and write separation introduction