Question :
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
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.
Any opinions or insight on what (when) to choose one over the other
would be welcome
My knowledge is limited when it comes to Hyper V’s replication functionality… So, rather than present you with detailed thoughts and opinions on which way might be better, I would steer you towards some solid testing of both options upfront.
I imagine you’re likely planning on doing some testing after you’ve decided which option you want to use (through research and asking questions here). However, you might want to reverse that and do some testing upfront to help narrow down which one you want to test further and eventually implement. This not only gives you some real confidence in the solution you decide to use but also gives you more backing for why you chose that option (proved to be better for our needs in testing vs. this option is supposed to work really well). This helps you get to the best possible solution in meeting the needs of your business.
One very important consideration is the level of awareness that Hyper V replication technology has about how SQL Server operates. The last thing you want is to fail over and realize that the databases are not in a consistent state, due to how the underlying replication works. Some testing/prototyping early in the process will help bring that to light.
Reading the article Hosting SQL Server in Hyper-V Replica Environment it indicates that Hyper-V replication can be used for SQL Server. However I don’t think that it necessarily should.
What level of RTO / RPO are you looking for? In other words, how long can you be down for, and how much can you afford to lose?
If the answers to those questions are measured in 10s of minutes, then I would look at using SQL Log shipping. If less than that then you need to start looking at the significantly more expensive HA type scenarios or Database Mirroring.
The drawback mentioned of having to have the entire database in one VHD file may easily introduce performance issues unless there’s some decent hardware or low throughput.