Evolution History of technical solutions for database architecture

SQLServer2008 is mature in terms of high data security, high performance, and high availability, these technologies and solutions are constantly upgraded and changed with the increasing business and data access pressure of many companies. At the same time, they have undergone various tests and proved that they are mature and reliable, the following are some technical solutions and changes in this area.

SQL Server is mature in terms of high data security, high performance, and high availability, these technologies and solutions are constantly upgraded and changed with the increasing business and data access pressure of many companies. At the same time, they have undergone various tests and proved that they are mature and reliable, the following are some technical solutions and changes in this area.

SQL Server is mature in terms of high data security, high performance, and high availability, these technologies and solutions are constantly upgraded and changed with the increasing business and data access pressure of many companies. At the same time, they have undergone various tests and proved that they are mature and reliable, the following describes the technical solutions and change processes.

Phase 1:

Streaking age:

Advantage: The biggest advantage of streaking is simplicity and low cost.

Disadvantage: Once a server encounters a problem, it is troublesome to recover. If the access pressure increases, the server may be overwhelmed.

Phase 2:

Single Database + Mirror + BackUp solution:

Note: There are two methods for Mirror: synchronous and asynchronous. The synchronization method ensures data consistency between the master database and the Mirror database, and does not require the Enterprise Edition. However, the performance of the master database is greatly affected;Asynchronous mode is supported by the Enterprise Edition.Most of the time can ensure data consistency, but a small part of the data may be lost, but the impact of its master database is relatively small.

Advantage: This solution provides reliable protection for the data in the master database. If a problem occurs in the master database, the slave database can be restored in a short time, especially when the database is very large (it takes a long time to recover from the backup), the service can be restored as soon as possible, and the Mirror side can generate snapshots, which can be used for businesses with low real-time requirements.

Disadvantage: Mirror may affect some of the performance of the master database (the asynchronous mode has little impact, the front-end needs to change the access IP address (or change the IP address of the slave server to the IP address of the master server ),You also need accounts and permissionsAnd job and other information migrationIn the past.

Single Database + Replication + BackUp solution:

Advantage: The Replication end can provide front-end access, which can store read operations in the slave database, share some of the pressure on the master database, and provide the database backup function, however, such backups may cause data loss.

Disadvantage: it cannot provide secure data protection, which may affect the performance of the master database.

Phase 3:

Single Database + Replication + Mirror + BackUp solution:

Advantage: this solution is a combination of the preceding two solutions, which can not only solve the data protection problem, but also provide the read/write splitting function.

Disadvantages: There are both Mirror and Replication on the master database. This method has a great impact on the master database, and it is proved that,Mirror and Replication are deployed on the same machine. If one problem occurs, it may affect the other..

Phase 4:

Cluster (dual-A) + BackUp solution:

Note: In the figure, the rectangle represents storage, and the two servers form A dual-A cluster.

Advantage: the Cluster ensures that all data and services can be switched to another server when a problem occurs on one server. The switching time is short and service access can be restored as soon as possible.

Disadvantage: Dual-A clusters generally require better configuration and high price. Because data is stored in storage, the cluster cannot protect data. Once data or storage encounters problems, data needs to be restored from the backup;The SQL Server Cluster cannotProvides Load Balancing Functions.

Phase 5:

Cluster (dual-A) + Mirror + BackUp solution:

Note: Dual-A cluster is added with the Mirror protection of the databases on two servers.

Advantage: this solution can provide reliable data protection, whether it is a server fault or a storage fault, it can ensure data security, and the data recovery time is relatively short.

Disadvantage: Mirror consumes part of the performance of the master server. If two more Mirror machines are added, the cost will increase. If there is a storage problem, the quick recovery solution is to enable the Mirror machine, you may need to redo the cluster later.

Phase 6:

Cluster (dual-A) + Mirror + BackUp + Replication + single distribution solution:

Description: Dual-A cluster, Mirror protection, single distribution machine and read/write splitting solution.

Advantages: the cluster and Mirror can fully protect data security, and read/write splitting can improve the overall performance of the system.

Disadvantage: The cost is high. A single distribution machine has a single point of failure. If the distribution machine has a problem, it will need to be rebuilt. At this time, both reading and writing will be concentrated on the master database, causing a high pressure.

Cluster (dual A) + Mirror + BackUp + Replication + dual distribution solution:

?

Advantage: Compared with a single distribution machine, there is no single point of failure. Even if a distribution machine has a problem, the read/write splitting mechanism can continue to run.

Disadvantage: the cost increases and the maintenance is more complex.

Phase 7:

Cluster (dual-A) + dual storage + BackUp + Replication + dual distribution solution:

Advantages: the dual-storage solution effectively protects data and avoids the impact of Mirror and Replication on the master server while running in the master database, saving master server resources and facilitating recovery.

Disadvantage: the cost increases.

Phase 8:

Cluster (dual-A) + dual storage + BackUp + Replication + dual distribution + SSB asynchronous solution:

The main advantage of this method is to asynchronously process data streams to relieve the pressure on the master database with instantaneous high traffic. This solution is complicated and is not described for the time being.

Phase 9:

Split businesses and data, adopt distributed databases, and use databases that can use the Server Load balancer cluster function.

This document roughly describes the changing stages of the database architecture as the company develops and the server pressure increases. Of course, we should choose the technology based on the company's actual situation, it is also possible to skip some stages directly, and adopt more efficient solutions (if the cost is acceptable). Therefore, the selection of technologies and solutions should be flexible based on the actual situation.