disaster recovery procedures - using local backups, merging databases
Hi Andy,
In addition to having our database backed up daily offsite with RGP, we also have our server replicated every 15 mins on another server in a data centre.
I get that if my local server crashes I can re-install RGP on another workstation or server and restore the latest backup (either ours or RGP's). But, is it possible to somehow make use of this replicated data so that when we're back up and running we've only lost 15 minutes of data instead of a full day's worth?
Is this an extra service that we can pay for in the unlikely event that it were to happen?
Thanks,
Audrey
Comments
RGP now has a Replication feature you can enable via Data Entry->Manage->Maintenance->Database Replication
Backups are like security - there is a trade off between maximum redundancy and keeping things simple.
It is absolutely possible to have up-to-the-second onsite AND offsite backups of your MySQL database, but that comes at the cost of complexity, monitoring, and expense. Each gym needs to decide for themselves how much of the "redundancy" challenge they are willing to bite off.
When you evaluate backup strategies you need to work from the assumption that merging of databases is not going to happen. That is extremely complex and fraught with risk. Thus you should focus on complete or replicated backups.
IMPORTANT - You can eliminate 99% of the risk of a database failure with the following three items. Do these FIRST.
* Use RGP's offsite backup and respond to emails about missing backups!
* Have your server be connected to an uninterruptable battery power supply/surge protector. I've seen numerous instances where a power outage/spike causes database corruption.
* Your server should have RAIDed hard drives that are hot-swappable. Have a standby hard drive ready to go. Then a hard drive failure is a non-event
If you do the above steps, it is EXTREMELY unlikely you will have any downtime/corruption issues from a server failure.
I am not sure how you are doing your existing 15 minute copies... but beware if you are simply copying the database files directly they are likely to be corrupted and not easily usable and possibly worthless. Alternately, if you are doing a manual mysqldump you need to be aware that over time that will start temporarily locking your database and causing operational problems.
The next level beyond the steps above is to have a fully capable backup server located on-premises that uses MySQL's built in replication to replicate the database in real time. MySQL has great embedded support for replication, but it is not trivial to setup. With a replication sever standing by, if your primary server fails you just point your RGP workstations to your replication server and carry-on. That becomes your new master server and then you fix the old server and deploy it as new replication server.
MySQL replication is tricky to setup (but then reasonably simple to maintain), and if you are interested I can point you to someone who can help. Email me directly for that contact.
The next level beyond a standby replication server is doing the same to an off premises server. IMO that is probably overkill but can be setup exactly like a local replication server.
Now... having said the above about replication, some things to consider:
* Should you need to restore a daily RGP offsite backup, you are correct in that you may lose 24 hours of database changes.. BUT...
* Any waivers or online bookings will be redownloaded from our server into the new database, so you will NOT lose those
* So you will lose any local transaction and check-in history for the day. Yes, that is bad.... but how bad is it? Is it worth the complexity of maintaining replication. Only you can decide :) Missing 10 hours of transactions probably won't have a material impact on your operations within a few days......
Hope this helps explain things, and if you want the contact regarding replication... let me know.
Thanks, Andy. That's very helpful for me (and hopefully others too!)
Audrey
Please sign in to leave a comment.