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 partner servers.

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

Note

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.

Quorum

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.

Restrictions

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 (dbsrv12); the personal database server cannot be used.
LOAD TABLE statement If you are using database mirroring and 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, the failover must complete before the events start; otherwise, the events are not executed until their next scheduled time. If the mirror server is changing to the primary server role but has not completed the transition, the scheduled event is not executed; it is executed at the next scheduled time. When an event is running and the primary server loses its connections to the mirror and arbiter servers, the event connection and all other connections are dropped. If an event is scheduled to run after the mirror assumes the primary server role, the event runs on the new primary 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.
Using SQL Anywhere web servers in a mirroring system If you are using SQL Anywhere as a web server and in a mirroring system, it is not possible to specify a URL for a web request in a way that guarantees that the request will be directed to the current primary server. If one of the server computers is named in the URL and that server is down, the request times out.

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

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

Discuss this page in DocCommentXchange.