I need to establish an offsite copy of a number of production servers, including SQL server.
Hyper V replication has been chosen as a simple solution (all servers are virtual). This is to supplement offsite backup for disaster scenarios. We do not need instant failover, mirroring or any advanced setup, just a reasonably recent copy of the most critical data and systems.
Hyper V replication looks simple, set-and-forget (just make sure to put tempdbs on a non-replicated VHD, set flags to maintaing write order across VHDs). But I am still concerned of efficiency and robustness, and thinking that a simple log shipping setup might be better, easier on the bandwith (would not need to replicate anything but the logs)
Any opinions or insight on what (when) to choose one over the other would be welcome
Best Answer
I think the answer depends on the SQL skill level of the people who will ultimately be responsible (lose their job) if the data can't be recovered in a DR scenario.
Most of the time when DBAs are chit chatting you'll hear horror stories of vendor solutions that didn't work, or stopped working, or weren't monitored. And when the data is lost it's lost. I've seen all 3. It's all good to say some other team will be monitoring it with a fancy tool - until a few months later when they've learned to ignore the alarms, and simply don't have time to respond to your repeated emails that it's broken.
That's why DBAs like me are a bit anal and insist on ownership and responsibility wherever there's accountability. I keep backups in SQL because then I can verify they're working and monitor them as well. I keep it all as simple as possible because simple works.
On the other hand if someone hands you a server and says "this is a copy, confirm its a proper copy and working", there's not a whole lot you can do without SQL Data Compare and an outage window.
If a place doesn't have a DBA, or there are licensing restrictions or software installed on the database server, then you make do with what you can. And vendors and consultants will often play along as well because when the data is lost they've already been paid and moved on (again they're making do with what they've been told or provided).
But for me? For best practice and not even a difficult one at that? No vendors in my backups.
PS: The exception being underlying snapshots of the backup media like network drives, for long term or redundant storage, as long as it's clear whose responsible for validating that's working. However again it's a matter of buyer beware - when it runs out of snapshot space someone will inevitably delete all your old backups without consultation; I've seen it happen time and again. But in this case as long as it's clear X people are responsible for long term file storage there's not much you can do.