What Current Continuous SQL Server Backup Solutions Are there?

Posted on

Question :

With Mirroring being deprecated in SQL Server 2012 and up along with AlwaysON Availability Groups costing $7k per core, I am wonder what options are there for constant reliable backups with a somewhat reasonable ($2k per server or less) price tag?

I understand that not every solution is going to do everything that Availability Groups. I am just looking for what solutions there are available that will duplicate changes to another live server as each change happens. Also, the less intensive the process the better.

Some other sources site Double-Take as a good solution. That question refers to file system backups rather than SQL but Double-Take does boast that it will work with SQL. I’m not sure of how reliable that is because Double-Take just sounds like it is file replication and I’m not sure if that is the best choice.

Also, I have looked at solutions like ApexSQL and Toad for SQL and I am not sure which solutions of these would do what I am looking for.

Any solutions with a clear answer as to whether the solution will post SQL changes to a backup server as they occur would be nice. Any other expertise on the surrounding matter would also be much appreciated.

Answer :

Well, you can keep using mirroring. “Deprecated” does not mean “doesn’t work any more” – it just means that, at some point in the future, it won’t be supported when you move to some version > 2012 (probably at least 3 versions later, maybe more given the backlash this announcement has caused).

By then, I am sure they will have a replacement for mirroring for Standard Edition, or will have simply deprecated Standard Edition, too. At that time, not today, you can make a decision to (a) use the replacement (or upgrade to Enterprise), (b) stay on the version you’re on, or (c) switch to a different RDBMS.

Deprecation isn’t exactly the jump-the-ship right now or sink ultimatum a lot of people infer. For example, they deprecated “statements that don’t end with semi-colons” back in 2008. Are you adding semi-colons to every statement you write, today, on current versions of SQL Server? Nope, I really doubt it. Do you need to abandon mirroring this instant, or even in the next three years? Nope, not even close.

While not real-time, Log Shipping can be performed at pretty frequent intervals. You can configure how often your Primary performs log backups and how far behind your Secondary should be (0 minutes, 30 minutes, 2 hours…). Brent Ozar makes the case you could even execute Log Shipping every minute if you wanted. It’s certainly not for everyone, but helps make the point that it can probably be more frequent than many of us configure it to be. In the end, you really have a fair amount of control over the level of potential data loss.

To help satisfy any questions about how long it might take to fail over and back, your best bet is to actually perform a DR test. This proves to you and the department that you know how to fail over and helps you set realistic expectations with them for the time it will actually take to do so.

Leave a Reply

Your email address will not be published. Required fields are marked *