I inherited a very interesting environment and I'm tasked with finding and implementing High Availability/Disaster Recovery solution as soon as possible.
Simply speaking our environment looks like this:
Server 1 - production database(200GB)
Servers 2, 3, 4 - need daily restored copies of production database (have to be updated with production data once per day)
Server 5 - DR server (data loss should be as little as possible, no more than 5 minutes)
- Windows 2012 R2 on all servers
- SQL Server 2012 Enterprise
- We are happy to upgrade software and hardware (within reason) if that's necessary.
We need solution that:
would require as little maintenance work as possible (only 1 super busy DBA is available)
would allow us to continue producing full, differential and transactional backups(we need them for auditors)
Options I already considered:
I thought about it and I excluded following methods:
1) log shipping because we need to be able to show log backups to auditors and store them for years. Please correct me if I'm wrong but log shipping wouldn't let us create separate log backups.
2) Mirroring only works between 2 servers (principal and mirror) so it doesn't solve problem of additional servers that have to be refreshed daily. Maybe I can use it on only one server and something else on the other 3 servers.
3) Replication is mostly meant for single tables and functions so I'm afraid its not possible to replicate one database on a slow server to 4 other servers.
4) I never worked with other HA techniques so for example AlwaysOn or clusters is a one big unknown for me. I am happy to learn though as long as someone points me in the right direction.
I would be very grateful if you could suggest HA/DR solution that can be implemented in the described environment.