Method and apparatus for database fault tolerance with instant transaction replication using off-the-shelf database servers and low bandwidth networks

[0002] This application claims priority from provisional application No. 60/160,606, filed Oct. 20, 1999.

[0003] The present invention relates to the field of high performance fault tolerant database systems using off-the-shelf database servers. More particularly, this invention provides non-intrusive non-stop database services for computer applications employing modern database servers.

[0004] Data service is an essential service for electronic commerce and information service applications. Among all different data service technologies, such as flat files, indexed files, multiple linked files or databases, database is most preferred. Database servers are complex software systems providing efficient means for data storage, processing and retrieval for multiple concurrent users. Typically, a database server is implemented on top of an existing operating system—a lower-level software system providing management facilities for efficient use of hardware computing and communication components. The correct execution of a database server relies on the correct execution of the operating system tasks storage systems, computer servers, networking equipment and operating systems. Therefore, failures of any one component can affect the normal operation of database service.

[0005] In general, the degree of fault tolerance of a database service depends on how quickly and reliability a database service can detect and recover from a failure or fault. The capability to fault tolerance can be evaluated in two ways: 1) ability to detect a database service failure; and 2) ability to mask a detected service failure in real time. Database service failure can be either hardware failure or software failure. In view of the tight binding among hardware, operating system and database server, it is often difficult to pin point where a failure started. Hardware related fault tolerance technologies use hardware redundancy to prevent component malfunctions. For example, redundant array of inexpensive disks (RAID) technology is well known and functions by replicating hardware disks and distributing replicating data over several disks, using disk mirroring and striping at the hardware level to increase the availability of data and to prevent data storage failures. Hardware related failures have become minimal due to the dramatic increase in the reliability of hardware components and the dramatic decrease in costs. Recent statistics show that software related failures account for the majority of database service failures. Hardware replication technologies can resolve hardware related problems, but cannot provide fault resilience to the operating system and database system malfunctions. Usually commercial database applications do not directly interface with hardware, but interface indirectly through the operating system. In general, database malfunctions of commercial database applications are not detected by hardware that will continuously operate irrespective of a database malfunction. There are few hardware-aware multi-processor database systems available that can detect hardware and database malfunctions directly, but they are prohibitively costly. They require extensive changes to database server software. They lack the continuous operation capability due to their inability to repair components while allowing database accesses. They also lack the continuous capability for it is impossible to pause and update one processor while letting users accessing the data through another processor. Examples of these systems include Compaq's Nonstop-SQL and Stratus Computer's proprietary database systems.

[0006] In a multi-users multiple redundant database servers environment, race conditions are major problems that cause database lockups and data content inconsistency in the multiple database servers. The ability to run multiple independent redundant database servers concurrently without a race condition is referred to herein as instant transaction replication. There are several database fault tolerance approaches to resolve such race conditions.

[0007] One method provides non-concurrent transaction replication using high speed replicated servers. U.S. Pat. No. 5,812,751 to Ekrot, et al. discloses a primary server and a monitoring standby server connecting to the same storage system. Once the primary system fails, the standby server will automatically take over the store system and assumes the operation previously performed by the failed primary server. This method also requires extensive changes to a database server software. More importantly, this method allows the data storage system to be the single point to failure. Any software malfunction that leaves any mark on the data storage contents will lead to an unrecoverable database system. A problem with the non-concurrent replication method is that the single point of failure is the shared storage system. As the user access frequency increases, multiple transactions can be lost in the event of the primary database server crash. This method also does not allow “on-line repair” or continuous operation of the database.

[0008] U.S. Pat. No. 5,745,753 to Mosher, Jr. discloses another non-concurrent remote data duplicate facility (RDF) that maintains virtual synchronization of the backup database with the local database. The RDF includes an extractor process executed by the local computer system, a receiver process and a plurality of update processes by the remote system. The extractor process sends sequential numbered audit trail records to the remote receiver process while the application performs online database restructuring in the local computer. The update process of the remote system, based on the incoming audit trail records, performs the same operation on the backup database. The traction manager stores a stop update audit record in the local audit trail where each online database restructuring successfully completes. Both local and remote processes use these sequential numbered audit records to cross check if any database operation is out of order. Acknowledge and redo commands will pass back and forth between the RDF and local databases. A crash of the local computer system can result in incomplete transaction logs for all replication servers rendering the entire system unrecoverable.

[0009] A conventional software method for computer system fault tolerance is file system replication or disk mirroring. A file system is the basic building block of any operating system for storage management, such as hard disks. A replicated file system uses a special storage device driver that maintains the identical appearance to user programs so they “think” the storage device remains the same while it duplicates each disk I/O request to both the local file system as well as remote backup file system. This method is highly transparent to users since it does not require any modification to existing application programs. In the event of the primary system failure, the replicated file system can be used to restart all services. U.S. Pat. No. 5,764,903 to Yu discloses a virtual disk driver used between a primary server and a secondary server which are mirroring over a network. That system uses a disk write request, also handles the control to operating system which in turn invokes the virtual disk driver. The virtual disk driver monitors both primary and secondary disk write operation, the control does not return to the calling application until the disk write is committed to both the primary and secondary disks. This method can protect most desktop applications, such as word processors, spreadsheets and graphics editors, from failure of the most of hardware fault and the operating system error. However it cannot protect the users from database server crashes, because the timing of database server command executor is not maintained.

[0010] U.S. Pat. No. 5,781,910 to Gostanian, et al. discloses distributed transaction database replicas. A single database manager employs an atomic commit protocol such as 2 phase commit (2PC) protocol. The basic idea behind 2PC is to determine a unique decision for all replicas with respect to either committing or aborting a transaction and then executing that decision at all replicas. If a single replica is unable to commit, then the transaction must be aborted by all replicas. The '910 patent further modified the 2PC protocol by designating one database server as the coordinator that is responsible for making and coordinating the commit or abort decision.

[0011] Such prior art systems do not resolve a variety of database sever fault recovery problems. For example, assume that there is a transaction containing sequence of record inserts and record deletes to tables with at least one index. This is very common in modern database applications since an indexed table allows faster search of its contents. Each insert or delete in an indexed table requires a re-indexing of the entire table. The file replication method will duplicate every disk update to both local and a remote file system. Assume further that the database server crashes after the database server's second phase commit but before the completion of the index updates. Some of the indexes will be corrupted rendering all queries dependent on these indexes useless. A file replication system can only replicate the problem.

[0012] It would be extremely beneficial to the electronic commerce and information service community to provide a fault tolerance database server system and method to allow automatic detection and recovery of all database software and hardware failures in real time using instant transaction replications. Such a system would satisfy true “zero closing time” of electronic commerce on a global scale.

[0013] The present invention involves the use of a database gateway to replicate database transactions in real time, and that provides automatic error detection and recovery for database service software and hardware failures. This invention also features on-line repair of database servers, that ensures continuous operation of the database services (such as Microsoft SQL Server®, Oracle®, Sybase®, DB2®, Informix® and others.) The invention can also be used to provide instant database transaction replication to centralized and geographically dispersed homogeneous or heterogeneous database servers.

[0014] One embodiment of the invention, multiple database clients connect to a redundant database server group via a database gateway that connects at least two database servers as a pair of mirror servers with the same database contents. Instant transaction replication guarantees all servers are synchronized in data contents. In such a system, every server is a backup to any other server in the same group. Therefore the reliability of the overall system increases proportionally as the number of redundant database servers increases. The database gateway can also be protected from its own failure by using a slave database gateway that monitors and protects the master database gateway in real time.

[0015] The basic principle of the present invention is replicating a database queries instead of just doing storage replication, since the majority of database queries are shorter than the storage updates generated by the query, the amount of the replicated data using this invention is greatly reduced. In both high and low bandwidth environments, the invention continuously monitors and intercepts the database inquiries between communication links and the database servers. It can redirect a communication link to a healthy server, if the connected server or the communication link reaches a designated failure level. This re-direction happens at the communication data packet level resulting in a repaired client-to-server connection data link in real time. The communication link interception and repair are completely transparent to the user applications. Since the database gateway does not keep the actual data, the hardware requirements of the database gateway is less demanding then that for a database server. A database gateway can be built using 100% off-the-shelf commodity components.

[0016] One object of the present invention is taking advantage of the concurrent processing of multiple database servers and multiple independent networks to balance the communication load among database clients and the servers, for instant database service failure detection and recovery including both hardware and software related malfunctions. The prior art does not address both malfunctions with high efficiency and reasonable cost.

[0017] It is another object of the present invention to provide performance enhancement by eliminating unnecessary waiting times due to the use of multiple redundant database servers shown in prior arts. The performance of the fault tolerant database service of the present invention is comparable to a non-fault tolerant database server where the clients connect directly to the database server without the database gateway.

[0018] Another object of the present invention uses algorithms to minimize the duplication client data and reduce the overhead of achieving fault tolerance processing in parallel. Therefore it is suitable for both high and low bandwidth networking environments.

[0019] It is another object of the present invention to isolate database clients from direct access to the physical data. Thus it allows adding security features, such as data access control and encryption, for the existing database system without changing a running database environment. This allows greater extensibility of a working database system to include clients and database servers connected only through the public networks, such as the Internet.

[0020] FIG. 1 presents the top-level architecture of the said invention.

[0021] FIG. 2A presents the internal workflow of a database service on a database gateway.

[0022] FIG. 2B presents the details workflow of a dedicated service thread.

[0023] FIG. 2C presents the workflow of a database server monitor.

[0024] FIG. 2D presents the internal structure of a query synchronizer.

[0025] FIG. 3 discloses the architecture for database gateway fault tolerance.

[0026] FIG. 4 presents the configuration of two database services in the same database gateway.

[0027] FIGS. 5aand 5bpresent the timing comparisons between READ-ONLY and update database services.

[0028] FIG. 6 presents the internal structure of an enterprise manager for configuration and tuning of multiple database services.

[0029] The overall block diagram of the present invention is shown in FIG. 1. In database network system, a database gateway 1 is installed between a group of redundant database servers 3 and network clients 5 that require database accesses. The database gateway can host a plurality of data services. Each database service captures incoming clients database queries 7, and automatically duplicates client's query into multiple streams 8, each for one of the database servers 3. One of the database servers 3a, 3b. . . 3nis designated as the primary server. Each of the database servers 3 also hosts a database server monitor that checks the database server activities. These monitors report errors and significant early warnings of the server to the corresponding database service. The database service monitors these messages and the status of each client-to-server connection 7,8 at all times. It can detect all database errors related to both hardware and software in addition to broken communication connections. As explained below, an automatic switch over occurs when a database server 3a, 3b. . . 3nis considered unreachable or unstable.

[0030] The invention does not address the client-side network connections 6 between the clients and the database gateway which are typically addressed by the client's network access provider. The invention is specifically devoted to the server-side connections 4 between the database servers and the database gateway to address server side failures and faults.

[0031] The database gateway is a stand-alone system running a commercial available operating system, such as Unix, with minimum basic functions of an operating system, such as memory management, file system, process and thread management, and TCP/IP networking protocol implementation or functional equivalent are required. The actual hardware requirement can be varied depending on actual implementation. For example, if the database gateway hosts two database services, its memory requirements would preferably be double of that for a database gateway hosting only one database service. Further, if an application requires two client-side networks and two server-side networks, there should be four network interface cards installed. The database service performs automatic data routing between the different networks. The overall network bandwidth can be four times of a single network. For larger scale applications, existing computer clustering techniques can also be used to further scale up the number of simultaneous clients by clustering multiple database gateways.

[0032] One embodiment of the invention provides a three state data service regarding to client access: stopped, paused and normal. In the stopped state, data service does not have any active connection. In a pause state, database service cannot accept new connections but may have active connections. In a normal state, database service accepts and may have all types of connections. The three states of data service, in conjunction with READ-ONLY control supported by the database servers, are designed to facilitate database server recovery. A database administrator is responsible for the state designation of a database service. Except for the normal state, all other states can be easily programmed by anyone skilled in programming.

[0033] FIG. 2A illustrates the components and basic functionality of the database service in the normal state. It shows the internal data flows between multiple database clients 5 and two database servers 3a,3b.Database service clients 5a,5bsend database queries through client-side network 6 to the database gateway I/O port 9. The database gateway 1 will create dynamic ports 10aand 10bfor direct dedicated communications with clients. The dynamic port 10ais dedicated to the data query issued by client 5a,and the dynamic port 10bis dedicated to the data query issued by client 5b.When the communication by a client 5aor 5bis terminated, the dynamic ports 10a,10bbecame available for use by other clients.

[0034] Upon receiving a data query request, the database service initiates two types of processing threads: a) dynamic service threads 11a,11band b) resident service threads 13, 14, and 15. The resident service threads include: 1) A service controller 13, responsible for service runtime parameter setup, start/stop/pause/resume, and other system configuration and maintenance tasks; 2) A master monitor 14, responsible for monitoring the status of the master database gateway using periodic echo tests or “heart beats”; and automatic take-over if the master database gateway does not echo back within a specified period; and 3) A resource recycle 15, responsible for collecting and returning the unused resources, due to an abnormal disconnection, back to the system.

[0035] The database service gateway 1 monitors on a existing communication port 9 client access requests. This port address is publicized to all clients which can access the database contents. This port is preferably customizable to different values reflecting the default settings of different database servers in order to hide the presence of the database gateway from the clients. The gateway 1 also creates connections 12a, 12b. . . 12nto all active database servers 3a, 3b. . . 3nin the group 3 through the coordinating query synchronizer 16. These threads have two main functions: a) to the database client, the thread must make it to “think” that the gateway is the actual database server; and b) to the database server the thread must make “think” it is communicating directly with a database client.

[0036] FIG. 2B illustrates the details of the work flow of the dedicated thread. The details of the work flow of a dedicated query thread in pseudo codes are shown in FIG. 2B. Step 30 sets up the communication with the client so it can communicate with the client directly without the intervention from the database gateway. It then tries to connect to all members 3a, 3b. . . 3nof the database server group 3, one being designated as the primary. Step 31 checks to see if the primary database server can be connected. If the primary database server cannot be connected, then the program designates a different active server as the primary server 32. The thread exits if it cannot find any usable active server. Otherwise, it marks all non reachable servers “disabled” and continues to step 34. Step 34 indicates that the thread enters a loop that only exits when a “server shutdown” or “client disconnect” signal is received. Other exits will occur at various errors. Step 35 reads the client query. If this connection is encrypted, this query is decrypted to yield a clear text by step 36. Step 37 processes client login for the active database servers 3a, 3b. . . 3n. Step 38 sends the query to all active database servers via the query synchronizer 16. Step 38 also includes the database server switching function similar to steps 31, 32 and 33, if the primary database server becomes unreachable or unstable during the transmission. Step 39 indicates that it is possible that the client's connection contains no transaction. Step 40 is only activated if this client connection contains transaction packets. Step 40 reads the primary server reply. It also contains the function to switch the primary database server similar to steps 31, 32 and 33, if the primary database server become unreachable or unstable during the transmission. Step 41 optionally logs the transmission. Step 42 checks if this database server has been designated as “READ-ONLY”—a performance enhancement switch. It sends a STOP processing signal to all non-primary database servers. This step conserves communication bandwidth between the servers 3 and the gateway 1, since only the primary server will return the requested READ-ONLY data, instead of all servers returning such information which occurs for other types of database commands.

[0037] Step 43 encrypts the server replies if this connection is designated as encrypted. Step sends the replies to the client. Step 45 indicates the need to read and throwaway replies from all non-primary servers. Steps 46-49 conclude this thread and return all resources to the system.

[0038] In addition to the normal operations, the database gateway can also be programmed to deny connection by pre-screening a requester's IP address, a function similar to firewalls. This provides additional protection to the database contents.

[0039] A database server monitor resides on each of the database servers which continuously monitors the server process status, and the client-to-server connections as shown in FIG. 2B. The database server monitor instantaneously detects failures of the database server and the communication and reports errors.

[0040] FIG. 2C presents a database server monitor 63 checking a database server's 3iprocesses 61 and activity/status log files 62 periodically reporting errors and significant warnings 60 to the database gateway 1 through the -server-side network 4. The implementation of the database service monitor is dependent on the design of the database server.

[0041] Once the database gateway establishes a dedicated thread for each data request 11a,11bfor the client's database transaction, a query synchronizer 16 places all data requests in a queue to guarantee that all database servers receive communication packets in the same sequence as illustrated in FIG. 2D.

[0042] The query Synchronizer 16 uses a software lock 70s1, 70s2 . . . 70sN for each of the database servers 3a, 3b. . . 3n. These locks are used to enforce the same packet sequence to be sent to all database servers. The software lock helps to prevent race condition, one of the major database problems. Race condition occurs if the multiple database servers process the identically sequenced queries differently. Therefore, it is necessary to require the database servers to enforce a first-come-first-serve policy for all processing threads. The query synchronizer and software lock implementation in the present invention eliminates race condition completely by queuing all database commands in the same order for all servers 3a, 3b. . . 3n.

[0043] Since the database gateway 1 acts as a buffer and a pre-process filter that performs no actual data manipulations nor saves any data, the hardware requirements of a database gateway 1 are less complicated as compared to a database server. Consequently, the probability of database gateway failure is comparatively lower than that for a database server. Nevertheless, to eliminate the single-point-of-failure, there is a need to protect the database gateway 1 as well.

[0044] Database gateway fault tolerance can be achieved using a technique known as IP-takeover. Another embodiment of the invention, shown in FIG. 3, illustrates a master database gateway 1 connected with a slave database gateway 80 via a client-side network 6. Preferably, the slave database gateway 80 has a special control over the master database gateway's AC power supply.

[0045] In FIG. 3, the master database gateway 1 is connected to network using one IP address 86. The Slave database gateway is connected on the same network using another IP address 87. The master gateway's AC power 84ais supplied by an AC power controller 82 which is software switchable by the slave database gateway 80 via a RS232 cable 83. The AC power controller 82 and the slave database gateway 80 obtain their AC power via supply lines 84band 84cfrom an final AC power source 85. The slave database gateway 80 sends periodic signals via IP address 86, 87 of network to the master database gateway 1 and demands an echo from the master database gateway 1. If the master gateway does not echo its signal within a certain period of time, the slave gateway 80 initiates a take-over by shutting down the AC power through a signal over line 83 to controller 82 and also replaces its own IP address 87 with the IP address used by the master gateway 86. Thus backup gateway 80 assumes the database gateway functions. The novel feature of this particular embodiment is that it avoids any potential network conflicts due to residual active threads still active in the partially crashed master database gateway.

[0046] The present invention treats a “READ-ONLY” type query that requires a large return data that is different from other types of database query. A database gateway 1 connecting to database servers 3a, 3b. . . 3n, hosts two types of database services for each fault tolerant database server, one for READ-ONLY queries and the other for update queries as illustrated in FIG. 4. In this example, one of the database services 10aiis designated as READ-ONLY and another service 10azfor all other types of update queries. The services 10aiand 10azmonitor commands and are respectively associated with dedicated thread for each data request 11aiand 11az.

[0047] As explained in step 45 of FIG. 2B, the update database service thread 11 will attempt to read and discards replies of all non-primary servers which this will have a negative performance effect if the database service requires a large return data set. As shown in FIG. 5A, without a separate READ-ONLY service, a READ-ONLY transaction 92 will invoke the primary server to preform processing 92 and return with data return. The gateway also directs all other non-primary servers to concurrently process the READ-ONLY transaction, represented by processing 296 in through processing 98. The gateway 1 waits for the return data before it starts a next transaction 295. With the feature of separate READ-ONLY service in the database gateway 1, as shown in FIG. 5B, the READ-ONLY database service thread 11aisends a STOP signal to all non-primary database servers, thus improving its timing significantly since there is no delay in receiving return information 97 and 99 from the duplicate processing 2 and 3, 96 and 98. Accordingly, READ-ONLY query 91 of FIG. 5B is significantly reduced due to the STOP signals sent in step 43 of FIG. 2B in comparison to the timing of an update query shown in FIG. 5A. Since update queries do not generally contain large return data sets, the overall system's performance to processing data queries is thus optimized.

[0048] The present invention is flexible by providing management means to setup and modify different tuning parameters of a database gateway remotely. FIG. 6 illustrates the top-level diagram of an enterprise fault tolerant database manager 110 that can setup and manage multiple database gateways. Each of the database gateways should be properly connected to a database server group as illustrated in FIG. 1. The enterprise manager 110 can perform its management functions remotely.

[0049] The enterprise manager includes three main functions:

[0050] a) A fault tolerant database service controller 111. This group functions include:

[0051] 1) setup fault tolerant database service: define data port, management port, define database server group, gateway primary/slave and primary/backup role.

[0052] 2) runtime functions: Start/stop/pause/resume.

[0053] b) A database Server Controller 112. This group functions include:

[0054] 1) define database server network address and port number.

[0055] 2) define database server monitor port, error message filtering and parameter.

[0056] c) A database Connection Controller 113. This group functions include:

[0057] 1) show connection status.

[0058] 2) force closing of a connection.

[0059] One embodiment of the present invention includes one database gateway connected to a client-side network and a server-side network that includes all functions for data encryption, access filtering and database server monitor communication. For applications with mixed READ-ONLY and update queries, the database gateway preferably hosts at least two database services, one for READ-ONLY and the other for update queries. Each of the database servers in the supporting server group preferably have the database server monitor installed and running. Each of the database servers preferably employ the first-come-first-serve scheduling policy.

[0060] An alternative embodiment is to merge the client-side network and the server-side network into the same network. This is suitable for low cost implementations but suffers from the potential network performance bottleneck.

[0061] Another alternative embodiment of the present invention, in the database servers, can function without the first-come-first-serve policy. The invention is still effective to all applications that do not require multiple users updating the same record at the same time. This is because that race conditions only occur when multiple users updating the same record at the same time.

[0062] A third alternative embodiment is implemented in a single type database service on the same database gateway. The system will still function but suffers performance loss if there are READ-ONLY queries with large return data sets.

[0063] A fourth alternative embodiment of the present invention is without the encryption function in the database gateway. This eliminates the encryption process overhead but exposes the raw data movements on the client-side or the server-side network. If any part of the data access network uses the Internet, it may present a potential security risk.

[0064] The present invention performs instant database transaction replication while using off-the-shelf database servers. This allows applications to setup cost-effective multiple database servers either locally or distributed globally. Any change in one database server will be instantly reflected in all database servers in the same group.

[0065] The present invention provides instant database service fault tolerance without expensive hardware-aware database server software changes. This eliminates the possibility of lost transactions using non-concurrent replication methods where no changes in data access programs nor modifications to the database servers is required. The database gateway can be built entirely by off-the-shelf hardware.

[0066] The present invention allows on-line repair of crashed database servers. Once a server crash is detected, the database gateway 1 automatically disallows data access to the crashed server. Database administrators can then service the non-working database server for online repair. On-line repair may consist of data reloading, device re-allocation and/or database server reinstallation. Even operating system reinstallation without affecting the on-going database service may be performed. Thus, the invention allows more flexible time for off-line repair of crashed database servers.

[0067] If a crash is hardware related, the crashed database server can be taken off-line for off-line repair. Application of the present invention gives the administrators more time and easy access for the repair since the database service is not interrupted while the off-line repair is in progress.

[0068] The present invention also provides more protection to critical data than direct database access. The database gateway's network address filtering function can deny data access from any number of predetermined hosts. This can further filter out undesirable data visitors from the users that are allowed access to the network. The database gateway encryption function allows data encryption on all or part of the data networks. It is easy to manage. Even though the invention uses multiple redundant database servers, management of these servers is identical to that of a single database server through the database gateway, except when there is a crash. Accordingly, one can define/change/remove tables, relations, users and devices through the database gateway as if there is only one database server. All functions will be automatically replicated to all database servers at the same time with high reliability and low-cost components. When a repaired database server is ready to be put back into service, the primary database server will be switched into READ-ONLY mode. Except database read transactions, there are no further database updates and write transactions to the primary database server from this point. The database of primary database server will be copied to all non-primary database servers including the repaired database server. When the database of all non-primary database servers and the database of the primary database server are synchronized, all database servers are switched back to normal operation mode.