1

I have 2 SQL Server 2012 machines. One is production and the other will be for backup. I was looking at using the AoHA but it requires

  1. SQL Server 2012 Enterprise for server OS
  2. an AD domain

I have neither Enterprise or a domain so that throws out the AoHA option.

I was investigating mirroring as that looks to be my next best option but all the documentation I'm reading on MSDN, etc says that this feature will be removed.

For my situation will mirroring work? I have about 50GB worth of DB's and would like to have them duplicated on a 2nd server in case the primary goes down or something.

Quick update - based on comments I looked into Log Shipping and that solved my issues. My DB's are now backing up on a secondary server via the Log Shipping method (I also offsite backup the DB's as well nightly). Thanks for the help.

  • Mirroring can do what you are looking for, but whether it will work well in your scenario is something we'd have a hard time answering. Do you really need HA or do you really need DR? Have you talked with your team and stakeholders about things like RPO, RTO, and what a minute or an hour or a day of downtime costs? – Aaron Bertrand Aug 24 '15 at 18:30
  • I'm only really looking for basic HA. In a sense if the primary DB server dies I can get the secondary one up with minimal downtime while primary is being rebuilt. As far as stakeholders are concerned there isn't any real SLA in place for downtime. I'm just trying to be proactive. – Valien Aug 24 '15 at 18:36
  • That's really basic DR (disaster recovery), not HA (which is more about zero disruption). If you can afford some downtime, log shipping is more flexible (IMHO) than mirroring, including different restore schedules for different databases, allows some level of read-only reporting (which you'd need Enterprise / snapshot management with mirroring), and has a longer future (mirroring has been deprecated). – Aaron Bertrand Aug 24 '15 at 19:05
  • Ah, good to know! I just read a little of log shipping so that might be what I'm looking for. I'll dig into it some more. Thanks! – Valien Aug 24 '15 at 19:50
2

Update: as Paul White mentions in a comment, it has been announced that SQL 2016 will support two-node alwayson groups via the limited "Basic AlwaysOn Group" feature. As Windows 2012 Standard edition includes clustering there will be no need for "enterprise" licensing to use a simple AlwaysOn pair. As SQL2016 is not yet released this does not immediately affect the asker of this question, things may change further between now and RTM, and Valien is already using SQL2012 currently, the answer is still correct as of now but there will soon be a more-or-less direct replacement for simple mirroring in SQL Standard Edition.

But before SQL 2016 is released...

Mirroring being dropped in future versions is an annoyance to many who can't use AO instead (mainly due to the enterprise edition requirements), but if you need to use it you'll find it present and supported as far as SQL2014 (in MS release terminology deprecated means "we intend to remove it at some point, so be prepared to switch sooner rather than later" not "stop using it right now or you are in an unsupported configuration"). As 2016 is not released yet the final feature set can not be confirmed but the length of 2014's support window gives you some time to play with before you have to switch to some other method so you could go with mirroring now but make sure management know that you need to put some time into researching future options.

Having said that implementing a fresh solution with a deprecated feature (what-ever definition of "deprecated") is not generally recommended.

If your intention is to prove disaster recovery then a good backup solution may be all you need: set your DB to full recovery, take full backups regularly and log backups even more regularly in between, make sure copies of these backups are kept off the main server and tested regularly.

The next step up would be to automate the testing of the backups: regularly restore on the second box the last full backup and the subsequent log backups. Or to achieve the same thing, implement log shipping. In either case have some automated solution scan the destination copy to make sure that it is running OK and has data you would expect (if your log backup is every hour then at any given time any change in the source DB made a little more than an hour ago should be present in the destination, if the application doesn't already have something you can easily check (such as a full audit trail with regular enough activity) then have a table with a single datetime column set a scheduled task to drop CURRENT_TIMESTAMP in there once per minute), have this drop you a mail/sms/other if it detects a problem (either database not available, or data in the secondary looks too old). This way you don't have to restore from scratch when something catastrophic happens: you should be at most one log restore away from having a relatively up-to-date database on the second box. This isn't as smooth as the quick switch-over possibility provided by mirroring or AO but it may be sufficient for your needs. Do consult with your users to make sure this is in fact sufficient - get them to specify "maximum downtime" and "maximum data loss in a DR situation" times, i.e. "service must be restored inside a two hours, we must not lose data from more than an hour before the outage" or some such (this can vary widely: some of the low volume systems we manage have a pleasantly lenient 24 hour loss window, if the business needs something in the few-minutes-or-less range then you probably need to convince them to stump up for relevant licensing so you can use AO (or just use mirroring for now but let them know the mid/long tern need to change due to the deprecation of the feature)).

Of course be aware that the backups are held off-site as well as on-site: while the most common need for DR is hardware failure within a single server your data could be rendered inaccessible by a number of things that would make a local second server unavailable too: complete power-out or network failure, fire, and so on. And make sure that your off-site backups are tested. It might feel inefficient to push the backups off-site then draw them back down for testing (instead of testing the original local copies) but this protects you from network transmission errors or otherwise undetected disk errors on the off-site store. If your second box is already off-site this dynamic changes somewhat.

0

When I checked the latest 2016 CTP release mirroring was still supported with the same depreciation disclaimer. I'm still standing up new instances with a mirror for manual failover DR. We won't be using AO because of the clustering requirement and also because it doesn't really fit our DR plans. I how MS realizes how AoAVG doesn't fit a lot of organizations current infrastructure.

0

In SQL 2016 AG is a standard feature, at the same level as Mirroring currently exists (meaning 1 DB per AG, in a 1-1 scale), and at least the Enterprise Edition supports AG setups with no Windows Clustering. Meaning crossdomain is possible using the AG technology, making it the direct replacement for Mirroring in future releases.

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.