We have a SQL anywhere 11 high availability cluster on site. Currently we are looking into disaster recovery, and we want to have a "30 seconds behind" database copy off site in two locations. The sole purpose of this is that "when our server room gets hit by a meteor" we will be able to move key personnel to our disaster recovery site and be back up and running with core functionality within a reasonable amount of time with minimal data loss. How do we achieve this?
Currently we are thinking about using either GlobalScape Continuous Data Protection or Microsoft Data Protection Manager to copy the database files to the disaster recovery locations. There is some fear that the database files might be in some sort of "in between" state and that we might not able to start it when using these solutions (which would obviously defeat the purpose). Alternately we are wondering what SQL Anywhere itself offers us in this area.
Is there anyone out there that is doing something like this, and how are they set up, and what are the advantages and disadvantages? Any input is appreciated!
If you can't move to Version 12, and you must locate the HA primary and secondary servers locally, then running dbbackup -l at a remote location may be your only option.
As I remember the joys of Version 9 dbbackup -l between Miami and Brussels, the big issues were this:
(1) shipping the giant full backup to get the setup established at the remote location,
(2) actually starting the database at the remote location after the dbbackup -l has run for a long time to accumulate a very large backup log.
Ideally, you should do step (1) frequently so the that the log doesn't grow so large as to make remote startup (recovery mode) take forever.
In this respect, HA is live backup on steroids... rather than accumulate the log to be applied after failure, the log is applied on a continuous basis... a big advance.
If you want to learn about dbbackup -l, just ask... it is rather funky, lots of gotchas.
Version 12 brings a feature called "read-only scale-out" so you could move your HA secondary to the remote location and establish a third "copy node" locally.
The reason third-party solutions may not work is that the copies they make of the database file and transaction log may not be in synch, and it may not be possible for SQL Anywhere engine to bring them into synch when it starts up and goes through recovery mode. If you check their documentation, you may find that they have special "agents" or other modules or options dedicated to handling specific DBMS software like SQL Server, but no agent for SQL Anywhere. The definition of "in synch" is complex, given the existence of things like dirty pages, checkpoint log, rollback logs and so on.
However, it would be easy to test: Just have them back up a very busy database and then see if you can start it at the remote location.
answered 07 Jan '11, 20:54
Database file copying most likely won't work. I'd consider live backup (dbbackup -l) instead.
answered 05 Jan '11, 20:50
I can't comment on HA as we have no production experience of using it. We do however have many installations with "live" back-up configurations which have proved very effective in genuine "disasters".
Unlike HA - you have to make the decision to start the replacement server, apply the logs and switch over - but batch files will do all the work. It does allow you to distinguish between a real emergency where a switch over is required and a brief outage of the main server.
answered 07 Jan '11, 18:39
Somebody has to say it, and it's worth saying loudly (hence the separate answer):
High availability is not the same as backup, and it does not replace backup. A straightforward, local, backup of the primary database should be implemented as a regular process, completely independent of the HA implementations.
The reason? You may want to restore to an earlier point, or go back and pluck data out of old backups, for technical or business or security or legal reasons that do NOT involve server failure or failover or availability.
Folks tend to concentrate on immediate failover reliability and performance when designing HA setups. That's as it should be... and a separate backup process that takes care of the "historical record" side of things lets you do that (concentrate on HA performance).
Note: A complete separation of regular backup and live backup processes is not possible; they are interrelated as discussed in the section "Live backups and regular backups" here. Like I said earlier, live backup is funky... and if memory serves, taking a full backup and truncating the transaction log necessitates sending the full backup to the remote live backup site because the remote live backup log file was truncated at the same instant as the primary log... and until that full backup gets there, the remote site is useless for failover.
Oh, and the hard part? Testing recovery. Not just testing HA failover but actually testing restore-from-backup. If you don't test recovery, it absolutely positively will not work when you need it.
Tip: Validate the backup. That makes validating the primary unnecessary, since if a backup is valid then by definition the primary was valid at the point the backup was taken. As well as being hard to do, validating the primary is completely pointless, and validating the backup is an absolute requirement... if you don't validate the backup then don't bother making one :)
answered 08 Jan '11, 10:38
In case I understand your current situation right:
If you're already using SQL Anywhere HA on site, wouldn't it be the easiest thing to extend this so that instead of using a local secondary server, use one at a geographically different location (and put the arbiter somewhere else, too)?
AFAIK, HA is made for such purposes.
And you would not have to mess around with copying database files and the like (which won't work in a few seconds given a typical x GB database size).
answered 06 Jan '11, 08:45
I would favor Brecks suggestion to use the "read-only scale-out" in addition to the HA scenario. But has anyone checked, if the resulting DB can be started and used independently of the scale-out if necessary?
answered 10 Jan '11, 08:38