Automatic failover method of SQL Server database and database

Abstract

The invention relates to the technical field of database management, and discloses an automatic failover method of an SQL Server database and the database, and the automatic failover method of the SQL Server database comprises the following steps: collecting node data of a corresponding node, and reporting the node data to a Zookeeper cluster; whether election succeeds or not is judged through a distributed lock mechanism of the Zookeeper cluster, and when election succeeds, node data of the nodes are obtained from the Zookeeper cluster; judging whether the node fails or not based on the node data, when the node fails and the node is a main node, writing a failover node in a Zookeeper cluster, and initializing the state of the failover node to be a starting state; switching a main node and a standby node, transferring fault data, and updating the state of a fault transfer node to a waiting state after the fault data is transferred; and when it is detected that the fault of the original main node is recovered, re-establishing the mirror image relation of the main and standby nodes, performing data synchronization, and deleting the fault transfer node. According to the invention, the single-point fault risk of the witness server can be eliminated.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to an automatic failover method and database of a SQL Server database. Background Art

[0002] In current SQL Server high availability configurations, database mirroring is a common solution. By replicating the transaction logs of the primary database to the mirror database in real time, database mirroring ensures that if the primary database fails, it can quickly fail over to the mirror database, maintaining business continuity.

[0003] In the prior art, in order to achieve automatic failover, a witness server is configured to assist in arbitration between the primary database and the mirror database. The witness server monitors the status of the primary node and the mirror node to determine whether a failover is required.

[0004] Among them, the witness server itself may become a single point of failure source of the system. When the witness server fails, the entire high availability mechanism fails, resulting in the system being unable to automatically perform failover, causing business interruption and seriously affecting the reliability of the system. Summary of the Invention

[0005] In view of this, the present invention provides an automatic failover method and database for a SQL Server database, which can eliminate the single point failure risk of a witness server, simplify high availability configuration, and reduce the overall cost of the system.

[0006] In a first aspect, the present invention provides an automatic failover method for a SQL Server database. The SQL Server database includes a node, a failover processing engine, and a Zookeeper cluster. The node includes a primary node and a backup node. The failover processing engine is deployed on the corresponding node respectively. The method is applied to the failover processing engine. The automatic failover method for the SQL Server database includes: collecting node data of the corresponding node and reporting it to the Zookeeper cluster; judging whether the election is successful through the distributed lock mechanism of the Zookeeper cluster, and when the election is successful, obtaining the node data of the node from the Zookeeper cluster; judging whether the node is faulty based on the node data, when the node is faulty and the node is the primary node, writing the failover node in the Zookeeper cluster and initializing the state of the failover node to the starting state; performing master-slave node switching and performing fault data transfer. When the fault data transfer is completed, updating the state of the failover node to the waiting state; when it is detected that the original primary node has recovered from the fault, re-establishing the master-slave node mirroring relationship, performing data synchronization, and deleting the failover node.

[0007] In this implementation, a failover processing engine is installed in each node. When a primary node fails, the failover engine automatically implements failover based on detection results. If one failover processing engine fails, another one can complete the detection steps. This not only eliminates the single point of failure risk of the witness server, but also simplifies high-availability configuration and reduces overall system costs. Furthermore, by writing the failover node to the Zookeeper cluster and recording the failover progress, the recovery time of the failed node can be monitored. When the failed node comes back online, the mirror relationship is automatically reestablished, ensuring continuous data synchronization and high availability.

[0008] In an optional implementation, node data of the corresponding node is collected and reported to the Zookeeper cluster, including: checking whether the Zookeeper cluster has an instance node corresponding to the node; when the instance node does not exist in the Zookeeper cluster, creating the instance node corresponding to the node in the Zookeeper cluster; obtaining the node running status of the node and reporting it to the Zookeeper cluster, so that the Zookeeper cluster stores the node running status and reporting time in the corresponding instance node, and the node running status includes online status and offline status.

[0009] In this implementation, instance nodes are set up in the Zookeeper cluster, and the running status and reporting time of each node are recorded. The status of the node is monitored by the running status, and the status of the failover processing engine on the node is monitored by the reporting time. This can be used to monitor whether the node is faulty and provide a basis for subsequent failover.

[0010] In an optional embodiment, determining whether a node is faulty is based on node data, including: when the node operation status of the node is offline, determining that the node has a service failure; and / or when the unupdated time of the node's reporting time is greater than a time threshold, determining that the node has an instance node failure.

[0011] In an optional embodiment, the automatic failover method of the SQL Server database further includes: backing up the complete data of the primary node based on a first preset time interval; and backing up the transaction log of the primary node based on a second preset time interval; wherein the first preset time interval is less than the second preset time interval.

[0012] In an optional implementation, performing failover includes: comparing the transaction log of the new primary node with the backup transaction log to determine the unsynchronized transaction log; and restoring the unsynchronized transaction log to the new primary node.

[0013] In this implementation, the Zookeeper cluster and failover processing engine are used to achieve real-time monitoring of database instances and automatic master-slave switching, thereby resolving the single point failure risk brought by traditional witness servers. Data recovery is performed after switching to ensure rapid response and service continuity when a database node fails. This reduces the need for additional hardware, lowers system deployment and maintenance costs, and improves overall reliability and automation.

[0014] In the second aspect, the present invention provides a failover processing engine, which includes: a reporting module for collecting node data of corresponding nodes and reporting it to the Zookeeper cluster; an acquisition module for determining whether the election is successful through the distributed lock mechanism of the Zookeeper cluster, and when the election is successful, obtaining the node data of the node from the Zookeeper cluster; a fault detection module for determining whether the node is faulty based on the node data, and when the node fails and the node is the master node, writing the failover node in the Zookeeper cluster and initializing the state of the failover node to the starting state; a failover module for switching between master and standby nodes and transferring fault data, and when the fault data transfer is completed, updating the state of the failover node to the waiting state; a fault recovery module for re-establishing the master-slave node mirroring relationship, performing data synchronization, and deleting the failover node when the failure recovery of the original master node is detected.

[0015] In a third aspect, the present invention provides a SQL Server database, which includes: nodes, including a primary node and a backup node, for synchronizing data according to a mirror relationship; a Zookeeper cluster, for storing node data of the nodes; and a failover processing engine, deployed on the node, for implementing the automatic failover method of the SQL Server database of the above-mentioned first aspect or any corresponding embodiment thereof.

[0016] In a fourth aspect, the present invention provides a computer device comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the computer instructions to thereby execute the automatic failover method for a SQL Server database according to the first aspect or any corresponding embodiment thereof.

[0017] In a fifth aspect, the present invention provides a computer-readable storage medium having computer instructions stored thereon, the computer instructions being used to enable a computer to execute the automatic failover method for a SQL Server database according to the first aspect or any corresponding embodiment thereof.

[0018] In a sixth aspect, the present invention provides a computer program product comprising computer instructions for causing a computer to execute the automatic failover method for a SQL Server database according to the first aspect or any corresponding embodiment thereof. BRIEF DESCRIPTION OF THE DRAWINGS

[0019] In order to more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the specific embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0020] Figure 1 is an architectural diagram of a SQL Server database according to an embodiment of the present invention;

[0021] Figure 2 is an instance role conversion diagram of a SQL Server database according to an embodiment of the present invention;

[0022] Figure 3 is a flow chart of an automatic failover method for a SQL Server database according to an embodiment of the present invention;

[0023] Figure 4is a flowchart of another automatic failover method for a SQL Server database according to an embodiment of the present invention;

[0024] Figure 5 is a flow chart of another method for reporting data to a SQL Server database according to an embodiment of the present invention;

[0025] Figure 6 is a flow chart of another method for backing up data of a SQL Server database according to an embodiment of the present invention;

[0026] Figure 7 is a flow chart of another method for transferring and restoring data from a SQL Server database according to an embodiment of the present invention;

[0027] Figure 8 is a structural block diagram of a failover processing engine according to an embodiment of the present invention;

[0028] Figure 9 Schematic diagram of the hardware structure of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0029] To make the purpose, technical solutions, and advantages of the embodiments of the present invention more clear, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without making creative efforts shall fall within the scope of protection of the present invention.

[0030] SQL Server is a relational database management system (RDBMS) developed by Microsoft, used to store and manage data and support a wide range of enterprise applications. SQL Server offers a rich set of high-availability features, such as database mirroring. It is widely used in various commercial applications, particularly in environments with large data volumes and high concurrency, to ensure data security, integrity, and high availability.

[0031] This application proposes an automatic failover method for a SQL Server database. It uses a Zookeeper cluster to record the operating status of the SQL Server instance and uses a failover processing engine to monitor and manage the master-slave switch, eliminating the dependence on the traditional witness server and effectively avoiding the risk of the witness server as a single point of failure.

[0032] According to an embodiment of the present invention, a SQL Server database is provided. Figure 1 , Figure 1 is an architectural diagram of a SQL Server database according to an embodiment of the present invention, such as Figure 1 As shown, the SQL Server database includes nodes, Zookeeper cluster, and failover processing engine.

[0033] Among them, the nodes include the master node and the backup node. Figure 1 In SQL Server, nodes are SQL Server instances, which are divided into primary nodes and secondary nodes. A failover processing engine is deployed in each node.

[0034] See also Figure 2 , Figure 2 4 is an example role conversion diagram of a SQL Server database according to an embodiment of the present invention.

[0035] When the master node is online, the failover processing engine creates an instance node corresponding to each node in the Zookeeper cluster. The failover processing engine collects the node data of the corresponding node, reports it to the Zookeeper cluster, and saves it in the corresponding instance node.

[0036] In one implementation, the node data includes instance status and reporting time.

[0037] The node operation status includes an online state, namely ONLINE, and an offline state, namely OFFLINE.

[0038] The reporting time is the timestamp when the node running status is reported.

[0039] The failover processing engine determines the node's operational status by monitoring node data within the instance node. If a failure is detected on the primary node, it triggers an automatic failover through the ZooKeeper cluster, promoting the backup node to become the new primary. When the failed node comes back online, the mirroring relationship between the primary and backup nodes is automatically re-established to ensure continuous data synchronization.

[0040] Specifically, according to an embodiment of the present invention, an embodiment of a method for automatic failover of a SQL Server database is provided. It should be noted that the steps shown in the flowchart of the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0041] In this embodiment, a method for automatic failover of a SQL Server database is provided, which can be used in the above-mentioned failover processing engine. Figure 3Flowchart of a method for automatic failover of a SQL Server database according to an embodiment of the present invention. It should be noted that this embodiment does not replace the method described above if substantially the same results are achieved. Figure 3 The process sequence shown is limited. Figure 3 As shown, the process includes the following steps:

[0042] Step S301: collect node data of corresponding nodes and report it to the Zookeeper cluster.

[0043] The failover processing engine periodically checks the node data of the node where it is located, reports the node data to the Zookeeper cluster, and saves it in the Zookeeper cluster.

[0044] In one implementation, the failover processing engine creates an instance node in the Zookeeper cluster based on the information of the node, instead of reporting the node data of the node to the Zookeeper cluster and storing it in the corresponding instance node.

[0045] Step S302: Determine whether the election is successful through the distributed lock mechanism of the Zookeeper cluster. If the election is successful, obtain the node data of the node from the Zookeeper cluster.

[0046] When the failover processing engine is started, the failover processing engine of the master node and the failover processing engine of the standby node are elected, and the successfully elected failover processing engine is used for fault detection.

[0047] In one implementation, a distributed lock mechanism of the Zookeeper cluster is used to perform leader election in the failover processing engine of the master node and the failover processing engine of the standby node.

[0048] Furthermore, when the failover processing engine is successfully elected, the node data of the master node and the standby node are obtained from the instance node of the Zookeeper cluster.

[0049] Step S303: determine whether the node is faulty based on the node data. If the node is faulty and is a master node, write the failover node into the Zookeeper cluster and initialize the state of the failover node to the start state.

[0050] Determine whether the node data is abnormal. If the node data is abnormal, determine that the node is faulty. When the master node fails, execute the failover method of this application.

[0051] When the master node fails, a failover node, such as / failover, is written to the Zookeeper cluster and the failover node is initialized to the start state. That is, the status field of the failover node is initialized to "START", which indicates that failover is started.

[0052] Step S304: Perform active / standby node switching and perform fault data transfer. After the fault data transfer is completed, update the state of the failover node to a waiting state.

[0053] When the failover node is detected as in the Start state, a primary / standby node switch is executed. The standby node is promoted to the primary node to ensure server service availability and perform failover of data.

[0054] The faulty data is the transaction log that is not backed up on the standby node.

[0055] Furthermore, after the failover data is transferred, the state of the failover node is updated to a waiting state, that is, the state field of the failover node is updated to "WAITING", which indicates that the failover operation is completed.

[0056] Step S305: When it is detected that the original master node has recovered from the failure, the master-slave node mirroring relationship is re-established, data synchronization is performed, and the failover node is deleted.

[0057] When it is detected that the state of the failover node is in the waiting state, the original master node is further detected to determine whether the original master node has recovered from the failure. After the failure is recovered, the mirror relationship between the switched master node and the backup node is re-established, data synchronization is performed between the master node and the backup node, and the failover node is deleted, indicating that the node switching is ended.

[0058] The automatic failover method for a SQL Server database provided in this embodiment implements failover processing engines by setting up failover processing engines in the nodes. When a primary node fails, the failover engines automatically implement failover based on the detection results. When one failover processing engine fails, the detection steps can be completed by another failover processing engine. This not only eliminates the single point of failure risk of the witness server, but also simplifies high-availability configuration and reduces the overall cost of the system. Furthermore, by writing the failover node to the Zookeeper cluster and recording the failover progress, it is possible to monitor the recovery time of the failed node. When the failed node comes back online, the mirror relationship is automatically rebuilt, ensuring continuous data synchronization and high availability.

[0059] In this embodiment, a method for automatic failover of a SQL Server database is provided, which can be used in the above-mentioned failover processing engine. Figure 4FIG. 1 is a flowchart of another automatic failover method for a SQL Server database according to an embodiment of the present invention. It should be noted that if substantially the same results are achieved, this embodiment is not limited to the above. Figure 4 The process sequence shown is limited. Figure 4 As shown, the process includes the following steps:

[0060] Step S401: collect node data of corresponding nodes and report it to the Zookeeper cluster.

[0061] Specifically, see Figure 5 , Figure 5 The present invention is a flowchart of another method for reporting data to a SQL Server database according to an embodiment of the present invention.

[0062] The failover processing engine checks whether the instance node corresponding to the node exists in the Zookeeper cluster. If the instance node does not exist in the Zookeeper cluster, the failover processing engine creates the instance node corresponding to the node in the Zookeeper cluster. If the instance node exists in the Zookeeper cluster, the failover processing engine directly proceeds to the next step.

[0063] Detect the node's running status in real time, and report the node's running status to the corresponding instance node in the Zookeeper cluster according to the preset time, and store the node's running status and reporting time in the instance node.

[0064] The node running status includes the online status ONLINE and the offline status OFFLINE, and the reporting time is the timestamp of the node running status report.

[0065] Step S402: Back up the complete data and transaction logs.

[0066] The complete data of the master node is backed up based on a first preset time interval, and the transaction log of the master node is backed up based on a second preset time interval, wherein the first preset time interval is smaller than the second preset time interval.

[0067] Specifically, see Figure 6 , Figure 6 The flowchart of another method for backing up data of a SQL Server database according to an embodiment of the present invention is shown.

[0068] Determine whether the current instance is the master node. If it is the master node, determine whether there is a valid complete backup. If the complete part is invalid, determine whether to start the complete backup strategy. When the complete backup strategy is started, back up the complete data of the master node according to the first preset time interval.

[0069] Furthermore, when there is a valid complete backup, it is determined whether to start the log backup strategy. When the log backup strategy is started, the log data of the master node is backed up according to a second preset time interval.

[0070] Full data backups are performed less frequently, while transaction log backups are performed more frequently. This is used to recover transaction logs that were not written to the mirror repository after a failover. A higher log backup frequency minimizes data loss after a failover. The specific frequencies, namely the first and second preset time intervals, are set based on the recovery time objective (RTO) and recovery point objective (RPO).

[0071] Step S403: Obtain node data of the node from the Zookeeper cluster.

[0072] Specifically, see Figure 7 , Figure 7 The flowchart of another method for transferring and restoring data of a SQL Server database according to an embodiment of the present invention is shown.

[0073] First, the failover processing engines on the primary and backup nodes perform a leader election. Specifically, the Zookeeper cluster's distributed locking mechanism determines whether the election is successful, that is, whether the current service is the leader. If the current service is the leader, the election is successful, and the node data is obtained from the Zookeeper cluster.

[0074] Step S404: determine whether the node is faulty based on the node data. If the node is faulty and is a master node, perform a failover.

[0075] If the node's running status is offline, the node is considered to have a service failure, meaning the SQL Server service on the current node is unavailable. If the reported time of the node is not updated for longer than the time threshold, the node is considered to have an instance failure, meaning the current instance is unavailable. A node is considered to have a failure if at least one of the above two failure conditions is met.

[0076] Furthermore, when the failed node is the primary node, the failover process is triggered, the failover node is written in the Zookeeper cluster, and the state of the failover node is initialized to the starting state.

[0077] like Figure 7 As shown, the failover processing engine continuously monitors the failover node in the Zookeeper cluster, that is, the failover node, and determines whether the status of the failover node is "START". When the status of the failover node is "START", an alarm is triggered to notify the database administrator DBA so that the database administrator can repair the primary node.

[0078] At the same time, the switchover between the primary and standby nodes is triggered, and the failover command is executed to promote the standby node to the primary node to ensure service availability.

[0079] Compare the transaction logs of the new primary node with the backed-up transaction logs to identify any unsynchronized transaction logs, and restore them to the new primary node. Update the failover node's status to "WAITING."

[0080] In this implementation, the failover processing engine uses the distributed lock mechanism of the Zookeeper cluster to automatically perform fault detection and master-slave switching, ensuring that when the failure of the backup node is detected, the system can automatically promote the master node to the active state to achieve seamless failover.

[0081] Step S405: When it is detected that the original master node has recovered from the failure, the master-slave node mirroring relationship is re-established, data synchronization is performed, and the failover node is deleted.

[0082] like Figure 7 As shown, the failover processing engine continuously monitors the failover node in the Zookeeper cluster and determines whether the status of the failover node is "START". When the status of the failover node is not "START", it determines whether the status of the failover node is "WAITING". When the status of the failover node is not "WAITING", it determines whether the status of the original primary instance is online. When the status of the original primary instance is online, it indicates that the original primary node, that is, the original SQL Server instance, has recovered. The restore mirror relationship command is executed to re-establish the primary-backup node mirror relationship, synchronize data between the primary node and the backup node, and delete the failover node in the Zookeeper cluster, indicating that the failover and mirroring session recovery are complete. When the status of the original primary instance is offline, it indicates that the original primary node has not recovered, and step S303 is continued.

[0083] In this implementation, when the original master node is detected to have recovered, the system can automatically rebuild the master-slave mirror relationship without manual intervention, achieving automatic synchronization of the master and standby databases, further improving the system's automated management capabilities.

[0084] In a specific implementation, in a typical enterprise environment, SQL Server is used as the database management system. SQL Server instances are deployed on two servers, configured as the primary and secondary nodes. A Zookeeper cluster is also deployed to coordinate and manage the failover process.

[0085] The specific steps include:

[0086] Step a1, Initialization and Configuration: Install and configure SQL Server's active / standby mirroring feature to ensure data synchronization between the primary and standby nodes. Deploy a three-node Zookeeper cluster to ensure high availability and reliability. Install the failover engine on each SQL Server node. This engine runs as a daemon process and is responsible for status reporting, fault detection, and failover.

[0087] Step a2, Status Reporting and Backup: The failover engine checks the SQL Server instance's operating status every 30 seconds. If the instance is ONLINE, it reports this status and the current time to the / instance node in the Zookeeper cluster. For example, when the primary SQL Server instance is operating normally, the failover engine updates its status to "ONLINE" and records the reporting time in the Zookeeper cluster's nodes. To ensure data integrity after a failover, the system performs a full data backup every 24 hours and a transaction log backup every 10 minutes. This backup data is used to recover unsynchronized transactions during the failover process, ensuring data consistency.

[0088] Step a3, fault detection and triggering failover: The fault detection module uses the distributed locking mechanism of the Zookeeper cluster to elect a leader, ensuring that only one node (primary or standby node) executes the fault detection logic. The leader node obtains the status and reporting time of the SQL Server instance from the Zookeeper cluster. If the status of an instance is OFFLINE or the reporting time exceeds the threshold of 1 minute, the system will determine that the instance is unavailable and create a new node under the / failover node with the status field set to "START", triggering a failover. For example, the leader node detects that the status of the primary node SQL Server instance is OFFLINE and the reporting time exceeds the threshold of 1 minute, so the fault detection module creates the / failover node on the Zookeeper cluster and sets the status to "START".

[0089] Step a4: Perform failover and recovery: The failover engine monitors changes in the / failover node status. When it detects a "START" status, it triggers an alarm to notify the database administrator (DBA) and automatically promotes the standby node to the primary node. The failover engine then performs a transaction log recovery operation on the new primary node, restoring any unsynchronized transaction logs from before the failover to the new primary node to ensure data integrity. After the failover is complete, the / failover node status is updated to "WAITING," pending recovery of the original primary node.

[0090] Step a5, Restoring the Original Primary Node and Cleaning Up the Process: When the failover engine detects that the SQL Server instance on the original primary node has returned to the ONLINE state, it reestablishes the primary-backup mirroring relationship to ensure continued data synchronization. The / failover node in the Zookeeper cluster is deleted, completing the failover and recovery process.

[0091] The automatic failover method for the SQL Server database of this application can achieve the following effects:

[0092] 1. Reduce hardware and operation and maintenance costs: By eliminating the use of witness servers, additional hardware investment and maintenance costs are reduced, making system deployment simpler and more economical.

[0093] 2. Improve system reliability: The architecture without a witness server avoids single point of failure, improving system reliability and overall operational stability.

[0094] 3. Improve fault response speed and reduce manual operations: The fault detection and recovery process is fully automated, reducing the need for manual intervention, significantly improving fault response speed and system resilience, and ensuring service continuity.

[0095] This embodiment also provides a failover processing engine, which is used to implement the above-mentioned embodiments and preferred implementations. Details already described will not be repeated here. As used below, the term "module" may refer to a combination of software and / or hardware that implements a predetermined function. Although the failover processing engine described in the following embodiments is preferably implemented in software, implementation using hardware, or a combination of software and hardware, is also possible and contemplated.

[0096] This embodiment provides a failover processing engine, such as Figure 8 As shown, including:

[0097] The reporting module 801 is used to collect node data of the corresponding node and report it to the Zookeeper cluster.

[0098] The acquisition module 802 is used to determine whether the election is successful through the distributed lock mechanism of the Zookeeper cluster, and when the election is successful, obtain the node data of the node from the Zookeeper cluster.

[0099] The fault detection module 803 is used to determine whether the node is faulty based on the node data. When the node is faulty and the node is the master node, the failover node is written into the Zookeeper cluster and the state of the failover node is initialized to the start state.

[0100] The failover module 804 is used to switch between the primary and standby nodes and perform failover data transfer. When the failover data transfer is completed, the state of the failover node is updated to the waiting state.

[0101] The fault recovery module 805 is used to re-establish the mirror relationship between the master and backup nodes, perform data synchronization, and delete the failover node when it is detected that the original master node has recovered from the fault.

[0102] In some optional implementations, the reporting module 801 includes:

[0103] The check unit is used to check whether the instance node corresponding to the node exists in the Zookeeper cluster.

[0104] The creation unit is used to create the instance node corresponding to the node in the Zookeeper cluster when the instance node does not exist in the Zookeeper cluster.

[0105] The reporting unit is used to obtain the node running status of the node and report it to the Zookeeper cluster so that the Zookeeper cluster stores the node running status and reporting time in the corresponding instance node. The node running status includes online status and offline status.

[0106] In some optional implementations, the fault detection module 803 includes:

[0107] The fault detection unit is used to determine that a service fault exists in the node when the node operation status of the node is offline; and / or to determine that an instance node fault exists in the node when the non-updated time of the node's reporting time is greater than a time threshold.

[0108] In some optional implementations, the failover processing engine further includes:

[0109] The backup module is used to back up the complete data of the master node based on a first preset time interval; and to back up the transaction log of the master node based on a second preset time interval; wherein the first preset time interval is smaller than the second preset time interval.

[0110] In some optional implementations, the failover module 804 includes:

[0111] The switching unit is used to compare the transaction log of the new primary node with the backup transaction log to determine the unsynchronized transaction log.

[0112] The transfer unit is used to restore the unsynchronized transaction log to the new primary node.

[0113] The further functional description of each of the above modules and units is the same as that of the above corresponding embodiments and will not be repeated here.

[0114] The failover processing engine in this embodiment is presented in the form of a functional unit, where the unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that executes one or more software or fixed programs, and / or other devices that can provide the above functions.

[0115] The embodiment of the present invention also provides a computer device having the above Figure 8 The failover processing engine shown.

[0116] See also Figure 9 , Figure 9 is a structural diagram of a computer device provided by an optional embodiment of the present invention, such as Figure 9 As shown, the computer device includes: one or more processors 10, a memory 20, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. Various components utilize different buses to communicate with each other and can be installed on a common mainboard or installed in other ways as needed. The processor can process the instructions executed in the computer device, including instructions stored in or on the memory to display the graphical information of a GUI on an external input / output device (such as, a display device coupled to the interface). In some optional embodiments, if necessary, multiple processors and / or multiple buses can be used together with multiple memories. Equally, multiple computer devices can be connected, and each device provides part of the necessary operations (for example, as a server array, a group of blade servers, or a multi-processor system). Figure 9 A processor 10 is taken as an example.

[0117] The processor 10 may be a central processing unit, a network processor, or a combination thereof. The processor 10 may further include a hardware chip. The hardware chip may be an application-specific integrated circuit, a programmable logic device, or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general purpose array logic, or any combination thereof.

[0118] The memory 20 stores instructions that can be executed by at least one processor 10, so that the at least one processor 10 executes the method shown in the above embodiment.

[0119] The memory 20 may include a program storage area and a data storage area, wherein the program storage area may store an operating system and application programs required for at least one function; the data storage area may store data created based on the use of the computer device, etc. In addition, the memory 20 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some optional embodiments, the memory 20 may optionally include a memory remotely located relative to the processor 10, and these remote memories may be connected to the computer device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0120] The memory 20 may include a volatile memory, such as a random access memory; the memory may also include a non-volatile memory, such as a flash memory, a hard disk or a solid-state drive; the memory 20 may also include a combination of the above types of memory.

[0121] The computer device further includes an input device 30 and an output device 40. The processor 10, the memory 20, the input device 30 and the output device 40 may be connected via a bus or other means. Figure 9 The bus connection is taken as an example.

[0122] The input device 30 can receive input digital or character information and generate key signal input related to user settings and function control of the computer device, such as a touch screen, a keypad, a mouse, a trackpad, a touch pad, an indicator stick, one or more mouse buttons, a trackball, a joystick, etc. The output device 40 can include a display device, an auxiliary lighting device (e.g., an LED), and a tactile feedback device (e.g., a vibration motor). The above-mentioned display device includes but is not limited to a liquid crystal display, a light emitting diode, a display, and a plasma display. In some optional embodiments, the display device can be a touch screen.

[0123] The embodiment of the present invention also provides a computer-readable storage medium. The above-mentioned method according to the embodiment of the present invention can be implemented in hardware, firmware, or implemented as a computer code that can be recorded in a storage medium, or implemented as a computer code that is originally stored in a remote storage medium or a non-temporary machine-readable storage medium and downloaded through a network and will be stored in a local storage medium, so that the method described herein can be stored in such software processing on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only storage memory, a random access memory, a flash memory, a hard disk or a solid-state drive, etc.; further, the storage medium can also include a combination of the above-mentioned types of memory. It can be understood that a computer, a processor, a microprocessor controller or programmable hardware includes a storage component that can store or receive software or computer code. When the software or computer code is accessed and executed by a computer, a processor or hardware, the method shown in the above embodiment is implemented.

[0124] A portion of the present invention may be applied as a computer program product, such as a computer program instruction, which, when executed by a computer, can call or provide the method and / or technical solution according to the present invention through the operation of the computer. Those skilled in the art should understand that the form in which the computer program instruction exists in a computer-readable medium includes, but is not limited to, a source file, an executable file, an installation package file, etc. Accordingly, the way in which the computer program instruction is executed by the computer includes, but is not limited to: the computer directly executes the instruction, or the computer compiles the instruction and then executes the corresponding compiled program, or the computer reads and executes the instruction, or the computer reads and installs the instruction and then executes the corresponding installed program. Here, the computer-readable medium may be any available computer-readable storage medium or communication medium that can be accessed by the computer.

[0125] Although the embodiments of the present invention have been described with reference to the accompanying drawings, those skilled in the art may make various modifications and variations without departing from the spirit and scope of the present invention. Such modifications and variations are all within the scope defined by the appended claims.

Claims

1. A method for automatic failover of a SQL Server database, characterized in that: The SQL Server database includes nodes, a failover processing engine, and a Zookeeper cluster. The nodes include a primary node and a backup node. The failover processing engines are respectively deployed on the corresponding nodes. The method is applied to the failover processing engine, and the method includes: Collect node data of the corresponding node and report it to the Zookeeper cluster; Determining whether the election is successful through the distributed lock mechanism of the Zookeeper cluster, and when the election is successful, obtaining the node data of the node from the Zookeeper cluster; Determine whether the node is faulty based on the node data; when the node is faulty and the node is the master node, write a failover node into the Zookeeper cluster and initialize the state of the failover node to a start state; Performing active / standby node switching and performing fault data transfer, and when the fault data transfer is completed, updating the state of the failover node to a waiting state; When it is detected that the original master node has recovered from the failure, the mirror relationship between the master and backup nodes is re-established, data synchronization is performed, and the failover node is deleted.

2. The automatic failover method for a SQL Server database according to claim 1, wherein: The collecting of the node data of the corresponding node and reporting it to the Zookeeper cluster includes: Check whether the Zookeeper cluster has an instance node corresponding to the node; When the instance node does not exist in the Zookeeper cluster, create an instance node corresponding to the node in the Zookeeper cluster; Obtain the node running status of the node and report it to the Zookeeper cluster, so that the Zookeeper cluster stores the node running status and reporting time in the corresponding instance node, where the node running status includes an online state and an offline state.

3. The automatic failover method for a SQL Server database according to claim 2, wherein: The determining whether the node is faulty based on the node data includes: When the node operation status of the node is an offline state, determining that a service failure exists in the node; and / or When the non-updated time of the reporting time of the node is greater than a time threshold, it is determined that an instance node failure occurs on the node.

4. The automatic failover method for a SQL Server database according to claim 2, wherein: The method further comprises: Backing up the complete data of the master node based on a first preset time interval; Backing up the transaction log of the master node based on a second preset time interval; The first preset time interval is smaller than the second preset time interval.

5. The automatic failover method of SQL Server database according to claim 4, characterized in that: The performing of failover includes: Compare the transaction log of the new primary node with the backup transaction log to identify the unsynchronized transaction logs. Restore the unsynchronized transaction log to the new primary node.

6. A failover processing engine, characterized in that: The failover processing engine includes: The reporting module is used to collect node data of the corresponding nodes and report it to the Zookeeper cluster; An acquisition module is used to determine whether the election is successful through the distributed lock mechanism of the Zookeeper cluster, and when the election is successful, obtain the node data of the node from the Zookeeper cluster; A fault detection module is configured to determine whether the node is faulty based on the node data, and when the node fails and is a master node, write a failover node into the Zookeeper cluster and initialize the state of the failover node to a start state; A failover module is used to switch between the primary and standby nodes and transfer the failed data. When the failed data transfer is completed, the state of the failed transfer node is updated to a waiting state. The fault recovery module is used to re-establish the mirror relationship between the master and backup nodes, perform data synchronization, and delete the failover node when it is detected that the original master node has recovered from the fault.

7. A SQL Server database, characterized in that: The database includes: Nodes, including master and backup nodes, are used to synchronize data based on mirroring relationships; Zookeeper cluster, used to store node data of the nodes; A failover processing engine is deployed on the node and is used to implement the automatic failover method for the SQL Server database according to any one of claims 1 to 5.

8. A computer device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the automatic failover method for a SQL Server database according to any one of claims 1 to 5 by executing the computer instructions.

9. A computer-readable storage medium, characterized in that The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the automatic failover method for a SQL Server database according to any one of claims 1 to 5.

10. A computer program product, characterized in that The method comprises computer instructions for causing a computer to execute the automatic failover method for a SQL Server database according to any one of claims 1 to 5.

Images