-1

I think I was able to convince my cow-erkers that jamming all the databases on one server is a good idea. So, our setup will be something like this:

Production - SQL 2014 Enterprise Standby - SQL 2014 (with automatic failover from Production) (I think I can get this license free if I get SA on the Prod server, right?)

Then, I was going to have a separate server off site, but ideally I'd like that to be a clone of the production server (security, databases, the whole shot). I have a few questions about this:

1) Can I make this automatically failover if both prod and standby are down?
2) Can I have this use Asynchronous transactions and have Prod/Stand use Sync?

Thanks so much

Rob
  • 201
  • 1
  • 7
  • For the down vote, could you elaborate on what was unclear and I'll edit my question. Thanks. – Rob Jan 21 '16 at 16:15
  • 2
    You will set up each replica independently. Each replica will either be synchronous or ansynchronous - so the answer to question two is Yes. The answer to question one is No - you only have one active replica. You can however set up the preferred failover sequence. So if Prod went down, standby could be next and then the async replicas. You can also specify whether or not this is automatic or manual. You didn't ask, but I'll comment on licensing - you'll only need a license for the primary replica AS LONG AS YOU AREN'T USING A READABLE SECONDARY. – Steve Mangiameli Jan 21 '16 at 16:53
  • @Steve Yeah... after i asked, I read more on the licensing and I think I have that squared away. I was just wondering if there could be cascading automatic failing. It doesn't need to be, in fact, I'm not sure we're gonna be able to afford all of this, but I want to be prepared in case they give me a huge budget. Thanks. If you want to chuck your comment as an answer, I'll accept it. – Rob Jan 21 '16 at 16:59

1 Answers1

2

Yes, if you have software assurance you can still have a warm standby. (It used to be that you could have one without software assurance as long as you weren't querying it, but that changed with 2014.)

It sounds like what you're looking at is AlwaysOn Availabilty Groups, but do check the licensing (as Steve Mangiameli said in comments). (Particularly: If you are querying a "warm standby," you have to license it.) It looks like AlwaysOn will do almost everything you want (both synchronous and asynchronous replicas) with one exception: Asynchronous mode only supports manual failovers. (Other things that provide automatic failover include clustering and mirroring with a witness server, but it sounds like you want AlwaysOn because of the multiple replicas.)

Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59