on December 17, 2015
SQL Server Availability Groups are growing up. SQL Server 2016 adds more features and improvements, and these include options to run SQL Server in different domains, or without a domain.
That brings Availability Groups closer to feature parity with Database Mirroring in SQL Server 2016.
The recent improvements caused me to think about the ways Availability Groups are better than Database Mirroring that aren’t listed as “big features”. (By “big features” I mean having more than one replica, having multiple databases fail over in a group, having multiple failover partners in SQL Server 2016, readable secondaries, having a basic load balancing concept in SQL Server 2016.) I think there’s a few.
1. The Listener is Way Better than Ye Olde Failover Partner Connection String
I’m a longtime fan of Database Mirroring. But troubleshooting connection failures with mirroring is a giant pain. Sometimes people find that their client is trying to connect to the mirror server periodically when there’s been no failover. And what happens if you need to fail over to the mirror, and then rebuild mirroring from scratch back to the original primary server?
There were ways to reduce the pain using DNS “friendly names” in Active Directory, but they weren’t perfect.
Availability Groups use a Listener resource in the Windows Failover Cluster instead. The client just needs to know the name of the listener, it doesn’t have to know the name of any failover partners.
There’s still complexity, particularly if you’re using multiple subnets. You need to make sure that your applications can use the right settings, and hopefully the right SQL Client providers – if you’re able to use .NET 4.6.1 or greater, it will automatically set important defaults for MultiSubnetFailover. Overall, I think it’s a big improvement.
2. Third Party Monitoring Tools are Better than Hacked Alerts
In theory, setting up alerts for Database Mirroring aren’t that hard. In practice, it’s kinda stinky. You need to find some scripts on Technet, wrestle with them for three hours, and you end up with a lot of WMI Alerts. Then you wander around in the Database Mirroring monitor for a while guessing at thresholds.
If you have more than one database in Database Mirroring, you end up with about 150 emails every time something hiccups. Or even thinks about hiccupping. It can be tricky to figure out what went wrong every time that happens.
With Availability Groups, it’s far more obvious that if you’re serious about monitoring, you should be buying a monitoring tool from a 3rd party provider. The more complex your topology, the more obvious it is. I actually think that’s a better story!
3. The AlwaysOn-Health XE Session is Better than… Nothing?
I really like the AlwaysOn-Health Extended Events session. Want to take a quick look and see when the last failover event was? Open it up! It has information on failovers and lots of critical events for AGs. You can gropu by event name and see the recent dates.
You could review state change with Database Mirroring, but you had to have the Database Mirroring Monitor tables or a trace event.
(Secret: I noticed that the AlwaysOn-Health XE Session also picks up failures for Database Mirroring, too. You could manually create your own session to do this for Mirroring, too.)
But Database Mirroring is Still a Valid Design for Some Environments!
I like a lot of things about Availability Groups. But I’m not saying they’re just “better than Database Mirroring.”
Version and licensing matters. If you’ve got SQL Server 2012 on Windows Server 2008R2, Availability Groups probably aren’t right for you. (We’ve had TONS of reliability and performance issues resolved with AGs in later versions of both clustering and SQL Server.)
Team size matters, too. If you don’t have a DBA or you have a single DBA, can you really gain expertise in Windows Failover Clustering as well as SQL Server Availability Groups? That takes a bunch of time, as does testing and managing upgrades, handling incidents, and deploying and tuning that custom monitoring.
For all its flaws, Database Mirroring is still easier to take care of in simpler scenarios for small teams. And for that reason, there’s still a place for it.