When Database Mirroring came out in SQL Server 2005 Service Pack 1, we quickly dropped Log Shipping as our Disaster Recovery solution. Log Shipping is a good feature, but I can failover with Asynchronous Database Mirroring faster than I can with Log Shipping.
When Always On Availability Groups (AG) came out in SQL Server 2012, we were excited to get rid of Transactional Replication, Failover Clustering and Database Mirroring. It solved our reporting needs (your mileage may vary), our High Availability needs and our Disaster Recovery needs.
But what if you only have Disaster Recovery needs and want to use Availability Groups since Database Mirroring is deprecated? Make sure you have quorum setup properly!
Let’s look at the big difference between Asynchronous Database Mirroring and Asynchronous Availability Groups.
ASYNCHRONOUS DATABASE MIRRORING
For asynchronous Database Mirroring, all we need is two servers: the principal at the primary site and the secondary at the DR site. Setup async mirroring between the two, and you’re done. If the secondary server goes down, production still continues. The transaction log isn’t clearing when the log backups occur because the principal still needs to send those log records to the secondary. As long as you have enough disk space where the transaction log resides to support this until the secondary server comes back online, production still continues. Of course if you run out of disk space, users will start receiving errors. But that can take some time and often times is enough time to get the secondary back online.
ASYNCHRONOUS AVAILABILITY GROUPS
We still need two servers for our asynchronous AG: the primary replica at the primary site and the secondary replica at the DR site. Once the two servers are in the same Windows Server Failover Cluster (WSFC), setup the Availability Group with the two replicas, specifying asynchronous-commit for the availability mode. Now take the secondary replica down and see what happens. Did the primary replica also go down? We’ve heard from many people that their primary replica goes down when they do maintenance on their secondary replica, and they aren’t sure why. It’s because quorum and/or voting is not setup properly. Availability Groups use WSFC which requires quorum. You’ll need another resource at the primary site to achieve quorum. I usually use a file share witness. I did use a shared disk on one AG though. Once those are setup, modify the WSFC’s quorum to whichever you picked. For instance, “Node and File Share Majority” or Node and Disk Majority”. Now when the secondary replica goes down, production continues on the primary replica because the cluster still has a quorum (2 are up, 1 is down).
Both servers and the third resource all have quorum votes by default. In this simple example where we have just two servers, it would be okay for the secondary replica to have a vote. It may not be okay if you had a more complicated setup, like the one I setup for the system that needed HA, reporting and DR. I learned about quorum votes fast when a network interruption between the two sites took production down! That system had 5 replicas (1 primary, 4 secondary): 3 at the primary site, 2 at the DR site. So far so good. But we also had a 3rd server at the DR site in the cluster. This was using SQL Server 2012, so we were limited to 4 secondary replicas. That 3rd server at the DR site was a cold standby (we decided against Log Shipping to it), and it too had its quorum vote enabled. The cluster lost quorum when the network between the two sites went down as there were 6 votes and 3 went “down”.
If you are using Windows 2012 R2, you’ve got Dynamic Quorum! It is enabled by default. The votes can be dynamically adjusted by the cluster as needed. Dynamic Quorum would have come in handy for that system I mentioned, but this was before Windows 2012 R2 came out.
Brent says: something to think about – the book on database mirroring is 400 pages. The book on Availability Groups…still isn’t out yet. It’s not delayed because it’s easy, it’s delayed because it’s hard. It’s doable, it’s just…harder than mirroring.
19 Comments. Leave new
In your example of having 1 primary node @ primary site, 1 fileshare witness @ primary site and 1 secondary node @ secondary site…. I was wondering – if you lost the primary site you lose 2 out of 3 votes, in this scenario I thought the secondary server would also shut down as it couldn’t form a majority quorum as it had 1 vote against a possible 2 votes from the primary site (I think this prevented split-brain scenarios where you lost the network between 2 sites and it stopped the secondary trying to start up when it didn’t have a majority).
Am I wildly off the beaten track?
Yes the cluster would be down in that scenario. Both nodes would be reporting that the cluster had to be shutdown due to losing quorum. The server doesn’t shutdown, just the cluster.
So … just working through this in my head … no automatic failure to the secondary (DR?) infrastructure would take place.
I’ll have to look into this dynamic quorum that gives votes a “weight” – thank you 🙂
Automatic failover can’t happen when using asynchronous AG or mirroring. You have to either switch to sync when you want to do a manual planned failover or have data loss when you need to do an unplanned failover where the primary isn’t available.
Wouldn’t the same situation occur with synchronous AG? e.g. the commit mode asynch/synch doesn’t affect the quorum and the node majority – i.e. if you lost the production site and lost the majority of votes the cluster service would shut down on the secondary site, therefore automatic failover of the AG may occur but if you’re using a clustered listener then your listener wouldn’t come online because the cluster service shuts down because it cannot attain a majority to form a quorum?
Yes it can happen with a synchronous AG too. It all depends on quorum and votes, not on sync or failover modes. Automatic failover doesn’t occur if the cluster service is shutdown on each of the nodes. The listener would be down too.
I have a single Primary Replica and a single Secondary Replica (for DR only). Quroum Type is ‘Node Majority’. Voting is; Primary=1, Secondary=0. Is it OK to have a total vote count of 1?
Is there a problem with using Synchronous-commit availabilty mode with this scenario?
Synchronous-commit availability is giving you reduced RPO (less chance for data loss), at the expense of hurting your transactional throughput, especially if the secondary replica is in a different data center — the primary replica cannot complete any write transaction until it gets confirmation back from the secondary that the transaction has replayed successfully on the secondary. When you add a (bandwidth limited) WAN link into the mix, that can cause some noticeable performance impact on a high-utilization system.
In terms of vote count of 1 — any 2-node system that doesn’t have a file share witness isn’t going to automatically fail over to the secondary when the primary fails anyway, as the secondary has no way to know whether the primary has actually failed, or there’s just been a network disruption. So vote count of 1 is the best way to make sure the Primary doesn’t take itself offline if the Secondary is unreachable due to a network failure.
If you’re using synchronous-commit because you’re expecting the secondary to come online automatically (HA), then you need to modify your quorum to include a file share witness, preferably at a third site if primary & secondary are at different sites (so that a network failure between primary and secondary, or the primary site going completely offline, still results in secondary being able to talk to the FSW, so it will take over). There’s a lot of things you have to pay attention to in that setup, though — if you’re just aiming for DR (without automatic failover), your current setup is suitable, assuming the RPO vs transaction processing throughput tradeoff is acceptable to your business need.
In your comparison you give high merits to AG’s quorum/witness facility, however you seem to fail realise Mirroring has also offered this from the start!
And automatic failover is definitely provided for, with the inclusion of the ‘PARTNER=’ in the connection string.
We’ve been using this setup since 2005 and it has never let us down. Never a split brain, never both primary and secondary offline. 11 years. We are very happy with it’s robustness and simplicity and see no need (yet) for the complexities (and costs) of AG.
So true for synchronous, but it doesn’t have it for asynchronous, which is what I am covering in this post. 😉
Uhmm, maybe I missed the word ‘Asynchronous’ in your article 🙂
Haha! Yes indeed. This is purely about asynchronous.
Is the asynchronous mirroring mode in SQL Server 2016 SP1 still an Enterprise Edition feature? If that is the sad truth and if I’m, stuck on Standard Edition, then ASYNCHRONOUS AVAILABILITY GROUPS would be the only HA alternative to logshipping, right?
Joerg – mirroring hasn’t changed, but Async Availability Groups is available in Standard.
Thanks Brent – coming from 2008 R2 logshipping, a BAG could be an option (although I didn’t reach the score of 15 in your webcast’s quiz 😉 – It’s worth building a test envirnonment…
I am getting error 1418 trying to activate a Mirroring between Two servers SQL01>>SQL02.
The Principal SQL01 is running with localsystem. Same server is working ok replicating to SQL03 via logshipping. Shall this work if I use Network user to run Sql-Server in SQL01 ?
Luis – for unrelated questions, go ahead and hit a Q&A site like https://dba.stackexchange.com.
Hi, interesting article, thank you for publishing. I’m in a situation where I have an SQL Server 2008 R2 database (yes, I know Microsoft have withdrawn support but it is 3rd party controlled and they have advised they have no plans to upgrade the server). The lack of control is why (I think) I want to mirror it to a MYSQL server. This would give me the freedom to add my own data tables to the mirror enhancing the total data held. The SQL Server database is updated by automatic download from the 3rd party once or twice a day. I use the data only on a read only basis. So I’m wondering if I copy the database from SQL Server to MYSQL can I then set up mirroring that when the SQL Server database is updated the updates automatically get mirrored in the MYSQL database … is that what mirroring does?
David – no, it’s only for Microsoft SQL Server.