Backup strategies for zero data loss

Question

I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state.

I've gone through two options:

1) Mirroring (to be deprecated in future versions)

2) AlwaysOn Availability Groups

Neither looks like a perfect fit in my scenario because mirroring would be obsolete soon and Availability Groups are only available in Enterprise Edition of SQL server. We are only having BI edition.

Are there any good options apart from these? We are ready to spend money even for 3rd party tools, but can't afford Enterprise Edition at this moment.

Answer 1

There is always a balance. You can get close to zero data loss with the right restore strategy and none of this additional technology - which are more geared to HA than DR. I've stopped calling this a backup strategy, because the key takeaway from any such plan is that you need to test that you can restore your backups, not just that they're running successfully.

Since you can afford a day of downtime, you can take your time retrieving your full and log backups from wherever you store them and restoring them on a new server. With FULL recovery, and taking a log backup every 5 minutes, you should be able to guarantee that you will never lose more than 5 minutes of data (unless, of course, you backup to the local disk and the disk explodes - best practice is to get your backups as far away from the primary server as possible, as soon as possible). You can tighten that window up to three minutes, two minutes, one minute, etc., but backing up the log too frequently can cause a few ripple effects - most notably on your I/O subsystem, your SQL Server Agent history, and of course the complexity of restoring to point-in-time.

I also wouldn't be afraid to use mirroring in addition to a proper backup/restore methodology (but certainly not instead of), to get you as close to zero data loss as possible. Just because it's deprecated doesn't mean you have to worry about it being unavailable "soon." Deprecation announcements are typically three releases out... plenty of time in 6-9 years to come up with an alternative, even if you are staying on top of every new release, which most people don't, and even if this application is still in service, which it may not be. There are a lot of folks still running 2000 and 2005; do you think they're worried about deprecated or discontinued features in current versions?

Besides, Microsoft will most certainly have had to come up with an alternative for Standard / BI Edition customers by the time they actually remove mirroring from the product. I know they have been told loud and clear, from the moment they announced this deprecation, that they need to have a contingency plan for non-Enterprise customers. My guess, with absolutely no internal knowledge on the subject, is that at some point they will introduce some extremely limited form of Availability Groups to Standard Edition, much like they did with backup compression in 2008 R2, which was previously only available in Enterprise.

Answer 2

You can always use "backup and restore" - just set the agent to do a backup at regular intervals. Backup supports both full and differential backup so it won't fill up your hdd.

Another approach would be to archive your data - this can be done using the Merge statement. Of course this has a very low granularity so it will take more time to setup but you also have more control.

-- these can cause data loss of course

Here's another thought: you store the data on a server, or ideally on a SAN - there are mechanisms to assure redundancy, for example RAID. So if one drive goes down, the other should be fine. And if you combine this with clustering you should minimize down-time as well. Here is a nice article about it.

Answer 3

Besides mirroring/Always ON, it's essential to create regular Full, differential and transaction log backups. And make sure that you validate them, to avoid unpleasant surprises This chain of database backups will help you restore to a point in time. There is a solution even for the loss of the transactions made after the last tlog backup that provides minimal loss - it's a Tail-Log Backup

In case when database data files are damaged or even destroyed, you can backup all the transactions in the LDF file that are made after the last transaction log backup (till the crash)