Advantages and disadvantages of database high availability solutions

Original article translated from: Simplified. If the database can run continuously, of course, it is best. However, this is often costly. Sometimes it is even more advantageous than what it gets.

In the sqlserver2008 database, there are many high availability solutions. For example, you can use failover clusters, database images, log transmission, replication, and other means to improve the high availability of the database. Because there are many solutions, the database administrator has to master the advantages and disadvantages of each solution, and then select the appropriate solution based on the actual application of the enterprise. In fact, this is not only testing the advantages and disadvantages of the solution, but also testing the capabilities of database administrators. Diagnostic files to help database administrators solve problems

　　I. Analysis of Advantages and Disadvantages of database images.

Database Mirroring is a software solution that provides almost instantaneous failover to improve Database Availability. To put it simply, the database mirroring solution is to set up multiple databases to synchronize data among multiple databases. Different within the same period of time, there is only one production database (or the main database), while other databases are backup databases (also called image databases ). When the primary database fails, the system automatically switches to the backup database. In this case, the image database becomes the main database. Because the data between the main database and the image database is synchronized in real time, user access is basically unaffected.

The biggest advantage of the backup storage solution is that it provides almost instantaneous failover. However, the database image solution is different, and the impact on this "instantaneous" is also different. Database images can be divided into high security mode and high performance mode. In the high-security mode, the word "security" is used. Committed transactions are handed over to both partners for submission. At this time, although it is secure, the delay of transactions will be extended. In high-performance mode, the Department needs to wait for the backup storage to write logs to the hard disk for submission. This can maximize the performance of different database data. Comparison between Oracle and SQL Server database images

However, this solution also has some defects, most importantly, it has many restrictions. For example, you can only use standard servers. You can only use database snapshots to perform limited reports on backup servers. You can only use single and duplicate copies of databases. If you need other copies, you can use the log transmission function of the database while using the database image. It can be seen that several different solutions can be used together to attract long queues to improve database performance and high availability.

　　Ii. analyze the advantages and disadvantages of log transmission.

Like database images, log transmission is also a database-level operation. Generally, you can use Log transmission to maintain one or more backup databases of the corresponding production database. In log transmission, this production server is called the primary database server, and the backup server is called the secondary database. In the database mirroring solution, this production server is also called the primary database server, but this auxiliary database is called the backup database. Although their names are the same, they actually represent the same meaning. The log transfer configuration includes one primary server (including the primary database), one or more secondary servers (each server contains one secondary database), and one monitoring server. Each secondary server updates its secondary database at a specified interval from the log backup of the primary database. Log transmission involves the latency that users can modify between creating a log backup for the primary database on the primary server and restoring a log backup on the secondary server. Before failover, you must manually apply all unrestored log backups to completely update the secondary database.

Log transmission has obvious advantages. For example, you can define the data synchronization time as needed, for example, you can define the delay time as the time between backing up the log of the primary database on the primary server and restoring the log backup on the secondary server. This feature is useful in some specific application environments. In addition, a single primary database can support multiple secondary databases on multiple server instances. Detailed description of log problems in the database

However, in some cases, this log transfer solution is often not used separately. For example, you can use the log transfer solution in combination with a database image. In this case, the two solutions can leverage each other's advantages to achieve the purpose of complementing each other. For example, log transmission supports the flexibility of multiple standby databases. If multiple slave databases are required, you can use Log transmission Alone or supplement it as a database image. When these solutions are used together, the main database configured for the current database image is also the master database configured for the current log transmission.

　　Iii. Advantages and Disadvantages of failover Clusters.

A failover cluster consists of one or more nodes or servers with two or more shared hard disks. Each application will be installed in a cluster service cluster group called a Resource Group. At any time, each Resource Group belongs to only one node in the cluster. The Application Service has a virtual name unrelated to the node name, which is called the name of the Failover cluster instance. Applications can connect to a Failover cluster instance by referencing the name of the Failover cluster instance. The application does not need to know which node carries the Failover cluster instance. Compared with the above two solutions, this failover cluster solution is a hardware-based solution.

The failover cluster solution also has many restrictions. Some restrictions are the same as those of the database image solution. For example, both solutions can only use a single copy of the database; the implementation of the solution should be carried out within the scope of the server instance (in the image solution, the implementation should be done within the scope of the server ). In addition, there are some restrictions on the Stock Index Cluster solution. For hardware-based solutions, there are special requirements for hardware, such as requiring hardware to be a signed hardware, and preventing disk faults. The second is the limitation on the report function. The failover cluster does not support subsequent report functions, which is more or less regrettable for some database administrators.

However, this failover Cluster also has great advantages over other solutions. Such as automatic monitoring and failover. If the current node is unavailable, this solution can automatically detect such faults and perform failover between nodes. Assume that an SQL server instance can be configured on a node of the Failover cluster in the event of an operating system fault, non-disk hardware fault, or upgrade of the operating system or database system, it fails over to any other node in the disk group. In addition to automatic monitoring and failover, manual failover can be performed for specific purposes. For example, you can manually start another node when upgrading the database server. The most important thing is transparent client redirection.

　　Analyze the advantages and disadvantages of the replication solution.

In SQL Server databases, replication is also a common solution to improve availability. Replication uses the publish-subscribe mode. In this way, the master server (called the Publishing Server) can distribute data to one or more secondary servers (I .e. the subscription server. Replication provides real-time availability and scalability between these servers. It supports filtering to provide subscriber with a subset of data and partition update. The subscription server is online and can be used for reporting or other functions without query recovery. SQL Server provides three types of replication: snapshot replication, transaction replication, and merge replication. Note that when writing project documents, you must note that the names of the master server and backup server are different in different solutions. If the database administrator creates a document with Zhang guanli Dai, their meanings are basically the same even though they are only called differently.

I believe that the replication solution has two main advantages over other solutions. One is that data can be selected, that is, you can decide which data on the Publishing Server is saved in the subscription server. The subscription server allows a subset of the Publishing Server to contain part of its data, rather than all of its data. The efficiency of data synchronization is relatively high, because it may not be necessary to synchronize all data. Another advantage is the replication solution, which has the shortest time lag, that is, the most timely data synchronization.