Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - Database Administration » Maintaining Your Database » SQL Anywhere high availability


Introduction to database mirroring

Separately licensed component required

Database mirroring requires a separate license. See Separately licensed components.

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.

A database mirroring system, showing the primary, arbiter, and mirror servers.

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 can only be accessed in read-only mode while that server is acting as the mirror server. See Configuring read-only access to a database running on the mirror server.

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 database 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 database server message log 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.


Database 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:

  1. The arbiter server waits for Server 1 and Server 2.

  2. Server 1 looks for the arbiter server or Server 2.

  3. Server 1 connects to the arbiter server.

  4. Server 1 negotiates with the arbiter server to become the primary server.

  5. The arbiter server and Server 1 agree that Server 1 can become the primary server.

  6. Server 1 starts accepting connections.

  7. Server 2 looks for Server1 and the arbiter.

  8. Server 2 connects to the arbiter and to Server 1.

  9. 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.

  10. 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 database servers, you must use the network database server (dbsrv11); the personal database server cannot be used.

  • LOAD TABLE statement   If you execute a LOAD TABLE statement on a base table, you must specify either WITH ROW LOGGING or WITH CONTENT LOGGING as the logging level for the statement. These clauses allow the loaded data to be recorded in the transaction log so that it can be loaded into the mirroring database as well. If these clauses are not specified, an error is reported. See LOAD TABLE statement and Import data with the LOAD TABLE statement.

  • 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.

Considerations when developing applications

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.

Upgrading SQL Anywhere

For information about upgrading SQL Anywhere for a database mirroring system, including applying EBFs, see Upgrading SQL Anywhere software and databases in a database mirroring system.

Benefits of database mirroring
Understanding the role of the arbiter server
Choosing a database mirroring mode