Database mirroring is a configuration of either two or three database servers, running on separate computers, that co-operate to maintain copies of the database and transaction log files.
The primary server and mirror server each maintain a copy of the database files and transaction log files, while the third server, called the arbiter server, is used when it is necessary to determine which of the other two servers can take ownership of the database. The arbiter does not maintain a copy of the database. The configuration of three database servers (the primary, mirror, and arbiter servers) is called a mirroring system, and the primary and mirror servers together are called the operational servers or partners.
Clients connect to the primary server to access the database. Any changes that are made to the database are recorded in the transaction log on the primary server. When the changes are committed, the transaction log pages are sent to the mirror server where they are applied to a mirror copy of the database. The copy of the database on the mirror server cannot be accessed by clients while that server is acting as the mirror.
If the primary server becomes unavailable because of hardware or software failure, the mirror server negotiates with the arbiter to take ownership of the database and assume the role of primary server. For an ownership transfer, or role switch, to take place, the surviving operational server and the arbiter must agree that the mirror was in a current, synchronized state at the time the role switch is attempted. Any clients that were connected to the original primary server are disconnected, and any uncommitted transactions are lost. Clients must then reconnect to the database on the new primary server to continue accessing the database. When the original primary server becomes available again, it assumes the role of mirror server.
The database servers display status messages in the Server Messages window on startup to indicate which role the server is assuming and how far the startup process has progressed. A message appears if the database must be restarted because of the loss of one or more of the other servers in the mirroring system, or if its role changes from mirror to primary.
If an assertion failure occurs on a server that is part of a mirroring system, the server writes the error to the console and then exits. This notifies the other servers that it has failed so that they can take appropriate action.
There are no special hardware or software requirements for database mirroring, and the database servers can be running in separate geographical locations. Database servers that are participating in a database mirroring system can run both mirrored and non-mirrored databases. As well, the arbiter server can be the arbiter for multiple database mirroring systems.
Details about the state of each database in the database mirroring system are stored in a state information file. See State information files.
NoteDatabase mirroring is not a replacement for a backup and recovery plan. You should always implement a backup and recovery strategy for your database. See Database mirroring and backups, and Backup and Data Recovery. |
For information about upgrading SQL Anywhere or rebuilding a database involved in a database mirroring system, see Upgrading SQL Anywhere software and databases in a database mirroring system.
Before a server can assume the role of primary server, it must have a quorum, which means that at least one other server must agree that a server can own the database. If the mirror server becomes unavailable while the primary server and arbiter are connected, the primary server continues to provide access to the database. If the primary server loses quorum, it can no longer permit access to the database. At that point, it stops the mirrored database, attempts to restart it, and then waits to regain quorum before making the database available.
When you start a database mirroring system, the database servers go through a startup process to reach quorum and accept client connections. The following steps describe a typical sequence of events for this process:
The arbiter server waits for Server 1 and Server 2.
Server 1 looks for the arbiter server or Server 2.
Server 1 connects to the arbiter server.
Server 1 negotiates with the arbiter server to become the primary server.
The arbiter server and Server 1 agree that Server 1 can become the primary server.
Server 1 starts accepting connections.
Server 2 looks for Server1 and the arbiter.
Server 2 connects to the arbiter and to Server 1.
Server 2 requests quorum. It does not receive quorum because Server 1 is the primary, and so it stands by waiting for transactions from Server 1.
Server 1 sends transactions to Server 2.
The following restrictions apply when using database mirroring:
Network database server required Because mirroring involves network communication between the servers, you must use the network database server (dbsrv10); the personal server cannot be used.
LOAD TABLE statements not permitted LOAD TABLE statements that load data into permanent tables on a mirrored database are not permitted because the data files would not be available on the mirror server. An error is reported in this case. However, LOAD TABLE is permitted on temporary tables.
As an alternative to using LOAD TABLE on a permanent table, you can load the data into a temporary table and then use an INSERT ... SELECT statement.Clients cannot connect to the database on the mirror server If the mirror server must be stopped using the Stop Server utility (dbstop), a connection to the utility database must be used. To use the utility database, you must use the -su server option when starting the database server, or specify the utility database password in the util_db.ini file. See Using the utility database, and Stopping a database server in a mirroring system.
TCP/IP required Only TCP/IP connections are permitted between mirroring servers.
Failover and scheduled events If your database has scheduled events, and failover occurs, scheduled events run on the mirror server as long as failover completes before the scheduled start time for the event. Otherwise, the next scheduled occurrence of the event runs on the mirror server.
Transaction log restrictions You cannot truncate the transaction log when you are using database mirroring because this may result in lost transactions. You can rename the transaction log as often as necessary. If you want to remove old transaction logs, you can use a scheduled event to delete them once you are certain that they are no longer needed. For example, you could create an event that runs each day and deletes copies of the transaction log that are more than a week old. See Database mirroring and transaction log files.
Web servers cannot participate in a mirroring system You cannot use a SQL Anywhere database server as a web server if the database server is participating in a database mirroring system because when failover occurs, the IP address of the database server changes.
When you are using database mirroring, in almost all cases, applications should be able to run in the same manner as they do when connected to a non-mirrored database. However, there are a few considerations to take into account when developing applications that are used with database mirroring:
Create clients that can reconnect to the database (for example, when failover occurs the user may need to shut down the application and then restart it).
When running in asynchronous or asyncfullpage mode, you must determine what happens when failover occurs and transactions are not committed to the database.
Incomplete transactions must be rolled back when the mirror server takes ownership of the database, and the longer a transaction is, the longer it takes to roll the transaction back. The recovery speed for failover is affected by the number of clients and the length of their transactions that need to be rolled back. If recovery speed is a concern, you may want to design your application to use short transactions whenever possible.
Benefits of database mirroring
Understanding the role of the arbiter server
Choosing a database mirroring mode
Tutorial: Using database mirroring
Setting up database mirroring
Database mirroring and transaction log files
Database mirroring system events
Database mirroring and performance
Database mirroring and backups
Database mirroring scenarios