2

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.

|improve this question
  • As a side note, Brent isn't fond of the 3rd party solutions either. Though he ignores the cost aspect in that case. :-) – Aaron Bertrand♦ Sep 10 '14 at 16:31
  • I noticed in one of his blog posts that Brent said that mirroring was being replaced with AlwaysOn AG. That's not really a replacement when you're talking about something that does completely different stuff and costs 100 times more. Brent does seem to know what he is talking about though. On a different note, the thing that kills me is that most product information on third party stuff is mostly advertising fluff without good examples of their products working. – Joe Sep 10 '14 at 16:38
  • Are you upgrading to SQL Server 2020 this week? Because that'll probably be the first version where mirroring won't be available in its present form. I can assure you you have several years to prepare for its replacement, and I can assure you that Microsoft does understand that they need to provide a solution for Standard Edition customers - in other words, the replacement won't force you to upgrade to Enterprise (unless, as I joked in my answer below, they just get rid of standard - in which case you have more important decisions you'll need to make at that time anyway). – Aaron Bertrand♦ Sep 10 '14 at 16:43
  • Lol, no... I am just trying to find the best answer to backups with the least amount of downtime. AG seems to pushed hard by people like Brent and it does look great. I just have no experience with the pros and cons of different SQL backup solutions and want whatever is the best when the time comes to switch to the backup server. – Joe Sep 10 '14 at 16:46
4

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.

|improve this answer
  • What about if the department wants the servers live for higher availability? Mirroring all seems like the target server has to be put into recovery mode right? How long does it take to go from the target server just being set to do backups then, to make it live and functional? – Joe Sep 10 '14 at 16:40
  • Maybe you need to consider different approaches for HA and DR then. If you want to use mirrored servers for things like reporting, consider log shipping in addition to mirroring. The department needs to understand that there's no free lunch - if they want ultimate HA, ultimate DR, ultimate simplicity, and low cost, something is going to have to give, sorry. As for how long it takes? Nobody can answer that question for you because it depends on too many factors. Like asking how long is a piece of string... – Aaron Bertrand♦ Sep 10 '14 at 17:41
  • For the most part, the department just wanted both to be live to minimize down time. Honestly, I'm pushing for testing the different options on free servers so we don't have to take random guesses on how long things are going to take or how long the string is. Unlike AG or mirroring, it seems like with log shipping the servers are going to have 2 different names. Is changing connection strings when one fails always a manual task using these methods? – Joe Sep 11 '14 at 21:03
  • @Joe well, yes, and if you plan to use the Log Shipping secondary as a fallback for read and write activity, you may need a little more planning around that. It can serve that purpose but I was proposing it as a secondary place to serve read-only queries, not as DR. – Aaron Bertrand♦ Sep 11 '14 at 21:11
1

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.

|improve this answer
  • Would you use native Log Shipping from SQL Server or a Third-Party option? Also, using this method how would you handle connection strings for a fail over since the two servers would have unique names for each? – Joe Sep 11 '14 at 20:54
  • @Joe Personally I would use native log shipping. It's not like there's anything magic, it's just a bunch of backup and restore commands with specific options. – Aaron Bertrand♦ Sep 11 '14 at 21:12
  • My apologies, as my last comment timed out before I was finished: Native Log Shipping, as it works very well and is a standard / universal technology with a large knowledge base. I would default to native Log Shipping unless you find you have a specific need that's only met by a custom or Third-Party solution. As for the connection strings, I would recommend creating a DNS alias that points at the Primary server and modify all applications to point at that. As part of your DR document / process, you change the DNS entry to point to the Secondary server after failing over the databases. – Micah Nikkel Sep 12 '14 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.