SQL Mirroring, remote retrival

Worried about security / failsafe

[raist007]

We are going to be building a large-scale website with a massive MySQL backend, served up by PHP. We are concerned about 3 things: server death, a fire at the facility, and hacking.

Basically, we know that we can do load-balancing across multiple servers using RAID but is there a serious lagtime assocaited with grabbing a remote database at another server facility, for fail-safe operation if something happens to one server or another for any reason?

More specifically, we want to have the same dataset mirrored on at least 3 database sets on 3 different servers that are always updated and can talk to each other in the event one goes down, as an airplane engine can pick up the other one's extra load should the second fail.

[txbakers]

Database mirroring is VERY tricky, given the nature of databases.

We looked into a two-server database solution, but it was very complicated and didn't seem very reliable.

You might want to look into a RAID 5 configuration instead of multiple servers. 5 Redundant hard drives, two mirroring, two striping and one current active one. (or something like that - I can't remember the full configuration.)

[raist007]

Thanks, txbakers. We will definitely be going with at least some type of RAID solution, probably level 5 I assume.

I am still worried about the actual hardware on the server itself, for example, the mobo blows, then the hard drives being mirrored = doesn't matter because the server is dead. Also hackers, since the HD is on the same server, they are essentially linked, so same system, same OS.

So, I guess that mirroring databases is not something that is possible right now? I'm not worried at all about cost implications.

[carguy84]

Real time database mirroring will be impossible. Your best approach is to do scheduled backups to the offsite DB's. Depending on how long each back up takes will determine how often you can send off the back ups. You won't be doing a full backup and send for each backup, you'll just need to send over the transaction log of updates that have occured. You can do something like this every 15, 30, 60 minutes or however you want to set it up.

But if you're dealing with a lot of data, and a lot of changes to that data, your internet connection is going to be the bottle neck. Your best bet is to have a second hot DB server running in the same co-lo ready to go in case your primary fails. Run the backups to that server regularly and you will have at least a few 9's of uptime. Probably not 5-9's but who knows :)

Chip-

[aspdaddy]

If you are hosting this yourself it will cost a lot of money to build and manage the infastruture. A decent data centrre will give you an SLA on all those requirements at a fraction of the cost.