I'm looking for options to prevent downtime of SQL Anywhere server, in case of failure of machine server. Is there solution, that will allow us in real time, to switch to different server? I mean, real time databases replication between two machines.
I want two separate environments, so if machine A crash, then we can use machine B with the same state of data which were on machine A. The two machines A and B, will be both running at the same time, both need to have the same databases, real time mirror copies. Users on the desktop will have two shortcuts, one to our application that uses databases on machine A, second that uses databases on machine B. In case of machine A failure, our application will close, and users will be not able to use it anymore. Then they will use second shortcut. In meantime we will recover machine A, and when it's recovered it need to synchronize data to the same state which is actually on machine B.
Please, tell me what are available solutions for SQL Anywhere 9 and 12, databases size between 700MB to 6GB, 40 users connected at the same time.
asked 22 Nov '14, 11:17
The best that SQL Anywhere 9 has to offer is called "live backup" where a full backup copy of the database file sits on a separate backup machine, and dbbackup.exe runs 24x7 on the backup machine but connected to the running database on the primary machine, to continuously pull transaction log records from the primary machine over to the backup machine where they are appended to the log file sitting on the backup machine. When a failure occurs, dbbackup stops running and you fire up SQL Anywhere 9 on the backup machine, start the full backup copy and apply the backup transaction log. This is called "warm failover" because the startup process can take a while to apply the log. It works quite well, and you can even have two copies; e.g., one in a nearby co-location site and another in a completely different timezone.
SQL Anywhere 12 offers High Availability (see this article) which works like live backup except there is a secondary SQL Anywhere 12 server already running on the backup machine, and the log records are continuously applied to the secondary database so startup is much much faster... "hot failover".
The most important step in the implementation of High Availability is testing. If you are not willing to regularly test failover in the production environment then do not bother to implement it... it will not work when you need it, and the effort will be wasted.
Implementing failover moves SQL Anywhere from the "zero effort" category of database administration into the world of enterprise server setup and administration... in theory it's easy, in practice it's not.