I was having a twitter interaction the other day. Since High Availability and Disaster Recovery topics are on my mind as I just wrapped up delivering an online class on SQL Server HA/DR last week and am preparing for my next free webinar series talking about HA/DR, that twitter thread piqued my interest.
The short story – someone was asking some questions about Basic Availability Groups. He got a couple of answers from a couple friends in the SQL Server community. Both were helpful comments. One was from the legendary Allan Hirt, who is the person I instantly think of when it comes to SQL Server HA/DR every time. Without fail. Along with his useful and helpful comments was one I’ve been seeing from him a lot lately – in short – “THERE IS NO BASIC!” now I see his point. The pre-created material I was teaching made this big deal about “Basic” and “Advanced” availability groups. Microsoft needed a way to distinguish the AGs we all know and loved from the implementation available in SQL Server Standard – which is, effectively, the exact same thing only with a few limitations. While I have a lot of sympathy for my friend Allan who loves naming conventions and getting things spot-on, the “Basic” term is well entrenched (just do a google search for “Basic Availability Groups” you’ll find a lot of links to Microsoft – and not just the marketing pages, but the docs). He’s right, in a sense, though. The terminology confuses folks. The other answer was from another friend who repeated something I hear all the time in the community. I’ve heard it in my classes, too. There is this sense that with a Basic Availability Group (Or, For Allan, an Availability Group in SQL Server Standard), you lose a lot of features. So I figured a quick post about a few of the facts and myths out there would be in order. I also include a few other pet peeves. While I don’t go quite as far as Allan (and I really should! Naming conventions MATTER!), I have seen quite a few people burned by some misunderstandings brought on by thinking of an AG as a “Cluster” and repeatedly calling it a “cluster”.
Anyway, I’ll try and dispell a few others. And if I’ve gotten something wrong! I’ll amend my post with a big old “Mike’s a fool!” apology note! And PS – in that exchange, I mentioned that if one is careful, one could theoretically have an AG in SQL Server Standard and just rely on one listener. This post is NOT about that – and I don’t want someone finding the twitter exchange and running hog wild with it. Maybe I’ll do a post with some screenshots and scripts about it, though. It’s ill-advised – if you need multiple DBs to failover together and don’t want to manage an independent connection string per – Enterprise Edition is right for you (or maybe failover cluster instances with a tool like SIOS DataKeeper or a windows storage replication approach). That said – it is possible, you just need to delay failover and get “creative” with scripts that look for a failover event of ANY AG and fail them all over. And it is kludgey because what if you have multiple failovers at once, etc.. So Stick with Enterprise Edition or FCIs 😉
2/19/2019 – UPDATE – MIKE’S A FOOL!!! – As I promised, I’d let you know if I updated. Like a big ‘ole fool I forgot to mention how you could do a readable secondary (if you license it to use it!) on a Basic AG. How? You can take a snapshot of the secondary database (totally supportable, you aren’t doing magic) and read off of the snapshot. There are some caveats around a quick hiccup in connections, and you have to actually create a new snapshot and redirect your users to it – and you should keep those old snapshots clean! But it 100% works – just like in mirroring. Thank you to my friend Argenis Fernandez on the SQL Server engineering team. HIs twitter background picture, btw, is one of my most cherished mugs also. Really cool speaker gift from SQL Saturday Orlando 9 years ago 🙂
SQL Server Availability Group Myths/Truths
Particularly about “Basic Availability Groups” (or Availability Groups in SQL Server Standard)
Your Availability Group has to be in (SYNCHRONOUS | ASYNCHRONOUS) commit mode
Not true! There is no difference in the commit modes available to an availability group whether it is on a SQL Server Enterprise Edition license or a SQL Server Standard Edition license. You can choose either. This myth probably got its start in Database Mirroring. With a mirror, this is true. In a SQL Server standard mirror – it had to be in “full safety mode” – or synchronous only. This meant you couldn’t run in asynchronous (“high performance”) mode. So go ahead and create your Availability Group in SQL Server Standard (as long as you are on SQL Server 2016 or higher!) and pick your own commit mode.
You Can not use Automatic Failover in an Availability Group in SQL Server Standard
Again, Not True. You can choose either automatic or manual failover in an Availability Group in SQL Server Standard or Enterprise edition. (Of course, you can only choose Automatic failover if you are in synchronous-commit mode, and that’s a good rule! We don’t want our AG failing over to our secondary all by itself if the DBs are not synchronized, do we??)
You Can Only Have Two Replicas in a “Basic” Availability Group!
Aye, I’m afraid this one is true. If you want to have a three replica AG with three separate SQL Server instances and do an HA + DR failover (Two replicas in one data center in synchronous-commit mode, one in another data center in asynchronous-commit mode, by example) you are designing a solution perfect for an Availability Group in SQL Server Enterprise Edition, but not one in SQL Server Standard Edition. Sorry. You have other options, though. You could log ship to your secondary data center. (Log shipping too often gets a sneer these days. It works really well based on your SLAs around your RPOs/RTOs – we’ll chat about that in the webinar series in a few weeks).
You Can Only Have one Database Per Availability Group in Standard Edition
TRUE! But the mistaken assumption at times draws a conclusion of, therefore, I can only have one DB protected by an AG per SQL Server instance. In other words, people hear something like “In a Basic Availability Group, only one Database can be protected” – this is not true. Theoretically, you could have 25 or 50 or whatever (I get nervous if you have more than 100 DBs in an AG, I get mad concerned if you have more than 150… The threading and the way it is handled is better in an AG than it was in mirroring – but it still isn’t a situation where you can protect hundreds of DBs and not expect issues) database protected by Availability Groups on those SQL instances. But notice, I pluralized groups. You just have to have a 1:1 mapping of AG to DB. And if you want to have a listener to connect to and not have to deal with changing connection strings at failover or doing something like my kludgey tweet that mentioned using one listener and scripts to automate the failover of the other DBs on the same instance, you have to also have a 1:1:1 mapping of DB:AG:Listener. So if you have 25 DBs to protect? That could be 25 listeners! That’s 25 IP addresses burned and having to be dealt with, etc. I’m not saying, here, you should have an Availability Group in SQL Server Standard with so many DBs, and I’d try and talk you out of it (depending on your goals – if your goal is just “I don’t want to lose data – so I have a really low RPO, but I’m okay with it being DB by DB, and I don’t care about so many listeners or I am fine just doing something manual with CNAMEs and bypass a listener altogether or do something like Mike was hinting at” – then I’d still potentially walk down the route of an AG in SQL Server Standard. (Since SQL Server 2014, we get 128GB of RAM… Since SP1 in SQL Server 2016, we get many EE features in SE, etc)
You Can’t Read off of or Backup From a Secondary in an Availability Group in SQL Server Standard
That’s right. I mean, give Microsoft some love for all we can do in Standard Edition these days! if you want to start offloading load from your primary to your secondary for read traffic, that’s an Enterprise License. Or it’s setting up replication or snapshot copies/etc – but it isn’t going to be an AG on Standard Edition. And the same holds true for backing up or doing CheckDBs on the secondaries (though… I have a whole big, long-winded, thought about doing DBCC checks on BOTH primary and secondary, and some frustration with technicalities of license agreements, but I’ll wait a few weeks to write that post 😉 )
Cluster/AG – What Difference Does it Make… You Know What I Mean!
No. I don’t. And you may not either! I’ve had situations quite a few times where I’ve gotten calls from people who hit the site confused because, “after my cluster failed over, none of my apps worked” or “none of our jobs were there anymore?!” The documentation is clear. The training is clear. An Availability Group is a database-level protection scheme. The user DBs are protected. The things you do to an instance (Add jobs, add logins, add linked servers (but why!!), etc) are NOT automagically copied over to the secondary replicas in an AG. YOU, Dear reader, YOU have to do something about that. And if you haven’t done anything about it yet, quick – go check out DBATools.IO. They have a module for synchronizing availability groups!
A SQL Server Failover Cluster Instance (FCI), by contrast, protects an entire INSTANCE. So all of those instance level settings, logins, users, etc. are protected! Because when an FCI fails over, the whole instance fails over.
So when folks say “Cluster” – a lot of people imagine “Failover Cluster Instance” of SQL Server. And when they imagine that – they imagine instance level protection. Both of these technologies utilize Windows Server Failover Clusters in various ways, and that adds a bit to the confusion. But here, precise language really matters!
You Can’t use [Insert Enterprise Edition Feature Here] In an Availability Group in SQL Server Standard
Of course! I’ve seen folks debate this one here or there. They are lost in the AG discussion and missed that we are on Standard Edition. No! You can’t use an Enterprise Edition feature in SQL Server Standard whether the DB lives in an AG or Not!
Want to chat/learn more about SQL Server HA/DR?
Again – After I’m done with the current free webinar series about upgrading SQL Server, I’m going to take a week off from the live chat with the team on that series, and then start up a new multi week series (not sure yet. Probably 4-6 week) talking about SQL Server HA/DR. Again intro level, basics, chats, and we’ll walk through things to consider before, during, and after. And we’ll spend some time talking about each of the methods I’ve used or helped implement. Both “All in SQL” options and some options using third parties/etc. Plus we’ll help you clarify what your RPOs and RTOs and SLAs really are and how to have the “budget transformation” chats. Stay tuned/subscribed here and once I get a tigter agenda, I’ll wire it up in Webinar Ninja and send out the free reg links from them.