Allan’s AlwaysOn Availability Groups FAQ
I’ve been working with the new AlwaysOn Availability Groups (AGs) feature for quite a long time now. As of late, I’m doing more and more SQL Server 2012 talks as well as consulting work and I figured I’d take the time to do a quick “trim the fat” blog post and answer a lot of the most popular questions I get and hear regarding AGs.
Disclaimer: Many of the answers could (and should) be blog posts or chapters in a book of course. I’m not trying to cover every “what if” and rat hole scenario here nor do a very deep dive into all of the minutiae of everything. For that, I may follow up some in blog posts over time, but all will be covered in depth in my upcoming book Mission Critical SQL Server 2012 which is tentatively due later this year (or early 2013) and will cover SQL Server 2012 on both Windows Server 2008 flavors (with a focus on Windows Server 2008 R2 with SP1) as well as Windows 8 Server. Since no RTM date has been announced yet for Windows 8, I want to ensure that I get the latest information in there before giving you guys a more definitive publication date.
And now without further ado, my AG FAQ. If I update this, I’ll note the date next to any updates over time.
Q: Is it true that AGs require a Windows Server failover cluster (WSFC)?
A: Yes.
Q: Why do I need a WSFC when DBM did not need it?
A: A WSFC is basically used for two things: quorum and the Listener. With database mirroring (DBM), the SQL developers essentially coded their own quorum mechanism for DBM in the Witness instance. With AGs, they are using a proven one built into the underlying OS. More on the Listener in a few questions.
Q: What edition of Windows do I need to deploy a WSFC? (added 4/16/12)
A: A WSFC requires that you’re using Enterprise Edition of Windows. However, pricing for EE of Windows is vastly different (at least through Windows Server 2008 R2; nothing has been announced for Windows 8 Server). Clearly cost matters, but you’re going to pay more for SQL than you will for Windows.
Q: What edition of SQL Server do I need to deploy an AG? (added 4/16/12)
A: You need Enterprise Edition of SQL Server 2012 to deploy an AG. Unlike DBM, there is no limited form of AGs in Standard.
Q: Do I need Active Directory?
A: Yes. A WSFC requires AD for various reasons, including the fact some cluster objects get a corresponding entry in AD. A WSFC also uses DNS, but your infrastructure already probably has DNS servers. Not everyone is currently using AD, especially if they are mostly a non-MS shop that happens to use SQL Server.
Q: Can I put AD on one of my cluster nodes if I don’t have AD elsewhere?
A: No. A node in a cluster cannot be a domain controller. SQL flags this during Setup, and it would be unsupported even if possible. See KB327518.
Q: Do all of my WSFC nodes need to be part of the same domain?
A: Yes. This will be a change for those who are looking to upgrade existing log shipping or DBM deployments where Instance A and Instance B are not in the same domain.
Q: Doesn’t having a WSFC mean I need shared storage?
A: No. This is where most people get confused when it comes to AGs. While the server participating in an AG is a node of the WSFC, it can just have a standalone installation of SQL Server. FCIs are not required for an AG. FCIs do have a shared storage requirement (even if just using SMB shares with SQL Server 2012). You can combine FCI + AG. That would require some sort of shared storage for obvious reasons for the FCI(s) involved.
Q: Does the WSFC have to be on physical hardware?
A: No. You can do this all with VMs with a supported hypervisor. The WSFC has to meet the supportability requirements and you must abide by the SQL Server virtualization support policy as outlined in KB956893.
Q: What is the Listener?
A: The Listener (sometimes referred to as the Virtual Network Name [VNN]) is similar to the network name and IP address(es) created when you install a FCI. Instead of connecting to the nodes, you would connect to the name or IP of the Listener which abstracts where the instance is. This means that applications and end users need only worry about one point of entry.
Q: Is there one Listener per WSFC?
A: No. A Listener is dedicated to a single AG. Different AGs do not and cannot share a Listener. A single WSFC can have multiple Listeners.
Q: Are AGs supported with a SQL Server 2012 deployment using Windows Server Core?
A: Yes. It doesn’t matter what flavor of the OS you use. However, with Windows Server 2008 R2 SP1, you would need to make a choice of all Server Core or full UI; you can’t mix modes and have a fully supported WSFC. Windows 8 Server introduces the concept of being able to switch between Server Core, MinShell, and full UI. It remains to be seen how SQL Server will support that.
Q: Is planning for and deploying an AG architecture easy?
A: It depends. Some are, others are not, and yet others may be deceptively simple. SQLHA can help you with this!
Q: Is quorum important? Isn’t that the Windows admin’s job?
A: When it comes to AGs, quorum is very important. DBAs need to get their heads out of the proverbial sand and learn more Windows stuff, especially WSFC if they want to deploy AGs properly(or FCIs for that matter). The bottom line with quorum is this: if you have enough voters up, you’re up. If not, everything is down (no matter how many instances of SQL Server may be running in that configuration – FCI or not). Quorum is not a straightforward discussion and one I will be addressing fully in most likely its own dedicated chapter in my upcoming Mission Critical SQL Server 2012 book. Things change even more (for the better IMO) with Windows 8 Server, which will be part of the book.
Q: Is it true I can actually use the secondaries/replicas for other purposes such as read-only queries and backups?
A: Yes. Depending on how you configure things, all of the replicas in an AG can be used for read-only purposes without any magic tricks or difficulty (such as database snapshots with DBM). The data would be near real time, meaning as soon as it’s there, it’ll be available. Full and t-log (not differential) backups can be made from a replica if the COPY_ONLY option is used.
Q: So what’s the trick to readable replicas?
A: Temporary stats are used, which means tempdb usage. Also, to minimize blocking for the writeable transactions, the queries against the readable replica use snapshot isolation no matter what and ignores any lock hints. That’s really the secret sauce under the covers, but it also means that you can’t have underpowered hardware and disk subsystems if you’re going to be doing read only queries on a replica.
Q: I had a bad DBM experience with regards to performance of synchronous mirroring. Will AGs fix that?
A: Probably not. AGs build on what DBM was (essentially), so if you’ve got poor I/O and network throughput, you will most likely see many of the same problems with AGs. There is no free lunch. If you want to configure synchronous (DBMs or AGs), you need the right underlying architecture to support it.
Q: Can I combine failover clustering instances (FCIs) with an availability group (AG) when using synchronous AGs?
A: Yes, but you cannot have automatic failover in this combination. This is a big improvement over FCI + database mirroring (DBM) where you had to futz with things like timeout values and even then it didn’t always play well. There are other concepts that may come into play with combining FCIs and AGs (such as asymmetric storage or quorum if we get into advanced configurations).
Q: Can an AG have multiple databases in it?
A: Yes. A single AG can encompass multiple databases (think of it like a folder, if you will).
Q: Can databases in an AG span multiple instances?
A: No. All databases in a single AG must be in the same instance. So if you have and AppDB_A in Instance_1 and AppDB_B in Instance_2, they cannot be part of the same AG.
Q: Are distributed transactions supported for a DB participating in an AG?
A: No. This is the same as DBM and log shipping.
Q: If there are multiple DBs in an AG, are they all kept in lockstep?
A: No. While they will fail over as a unit, the process for replication/mirroring the data in each DB is handled separately. You’ll need to sort out where they are from a data standpoint.
Q: Can a DB participate in multiple AGs?
A: No. A DB can be only a member of one AG.
Q: Do I need physical hardware to implement AGs?
A: No. A WSFC can be comprised of all VMs. As long as your WSFC is supported (see the aforementioned KB327518 as well as KB943984 for the official support stance on what constitutes a supported cluster from both a SQL and Windows perspective), you’re fine.
Q: Can I upgrade an existing DBM or log shipping implementation to an AG?
A: Yes. You can create the WSFC after SQL Server is already installed (assuming you meet all the other prerequisites). So even if upgrading from SQL Server 2005/2008 to 2012, you will have a migration path. That said, you still may have some things to do to ensure the underlying configuration meets the qualifications for a supported WSFC since the configuration may have never been thought to have been converted to a cluster node.
Q: Do I still need to worry about things like logins and SQL Server Agent jobs?
A: Yes. An AG is database-level protection (like DBM or log shipping), so anything residing outside the DB must be part of your planning. If you use contained databases (new to SQL Server 2012), it could potentially account for logins.
Q: How will AGs change my licensing for SQL Server?
A: I have no idea. I’m not a licensing expert nor do I play one on TV. Talk to MS or whoever is responsible for your SQL Server licenses to determine what your licensing will be based on your configuration.
Q: Is HADRON the same as an AG?
A: HADRON was an early name for availability groups. You may also see HADR as well (and is what is used for many of the DMVs).
Q: Is AlwaysOn the same as an AG?
A: No. AlwaysOn (no space, thank you very much) is a designation that covers both the AG and FCI availability features in SQL Server 2012. It is incorrect to refer to AlwaysOn as an AG. At one point during SQL Server 2012’s development AlwaysOn was used only for the AG feature, but that is no longer the case. I have a feeling this will be the new active/passive and active/active.
Q: Do AGs replace FCIs?
A: No. See my earlier blog post on this topic.
Q: So what about DBM? Is it going away?
A: Yes. DBM has been deprecated in SQL Server 2012, but don’t push the panic button. See my earlier blog post on this topic.
<shameless plug>
SQLHA can help you with your planning and deployments of AGs and we’re doing that already for customers (and have been since helping a customer in the TAP program since early last year). We’ve already got the real world expertise to jump start your SQL Server 2012 deployments. Check out our brand new Availability Group service offerings, and feel free to contact us even if you want something other than one of our offerings.
</shameless plug>
Good article. Like the FAQ coverage style. Cleared up a quite a few of my questions. However, is your CAPS LOCK key broken?
No, the post is in upper and lower case. Apparently some browsers can’t parse right.
You had answered the question above “If there are multiple DBs in an AG, are they all kept in lockstep?” with a “No”. Are there any configurations of High Availability in SQL Server 2008 or 2012 that will support keeping transactions in lockstep?
I am supporting a banking client that uses a third-party application which uses SQL Server 2008 (or 2012) for the backend. Certain types of transactions, such as ATM transactions, must be written on two separate databases, one with the ATM transaction, and another for updating balances which goes to the “core” database.
DBM, AGs, Log Shipping – none of ’em support distributed transactions. You’d have to bake things into your app to make that happen.
Excelent article Allan! Congratulations!
I’m new on this scenary and I have some questions: what can happen if I lose the AG listner? I fell in the 00.0001% of unavailable database possibility? Can I have a LBN configurated for different AG listeners?
Thanks in advance!
What do you mean by LBN? Load balancer? An AG has a single Listener (while you can configure more, it’s NOT recommended). Each AG will have its own Listener; it’s not shared. Something like a load balancer fronting nothing isn’t going to buy you anything.
Doesn’t a single listener create a single point of failure? Per Steben’s question, what happens if the Listener goes down and how would you mitigate that risk?
Thanks!
How many read only replicas could I have. I know I’m limited to 1 primary and 1 secondary, but is there a limit from SQL, or is it the windows cluster limit of 16 (less the primary and secondary)?
Answered my own question:
Two automatic failover servers, a primary and a secondary.
Three synchronous replicas (a primary and two secondaries). The rest of the replicas (up to four
replicas in total) will be asynchronous.
Not 100% correct. Your number exceeds five. It’s a total of 5, of which you can have the right combo (primary + secondaries be they sync/async/automatic).
My antivirus (Sophos) claims this page has a virus:
Malicious Content Blocked
The requested location contains malicious content, identified as Troj/JSRedir-KB and was blocked from downloading.
Thanks for the note. However, we’ve run AV and such against it and no viruses. We may have had a problem but some companies once they detect it put a site on a list and don’t update it. We’re all clean.
Pingback: Something for the Weekend - SQL Server Links 20/04/12 • John Sansom
Nice FAQ.
Thanks.
Can single SQL 2012 server be both a primary and secondary replica for different AGs i.e. I have two SQL servers SQL1 and SQL2.
SQL1 has 1 primary database which shall configured in AG for secondary replica on SQL2.
Similarly, SQL2 has 1 primary database which shall configured in AG for secondary replica on SQL1
Is this possible ? Hope we need to create AG for combination of SQL1+SQL2 and another AG for combination of SQL2+SQL1.
Kindly let know.
Yes, a single instance can have multiple AGs, where some DBs are primary and some DBs are secondary. A single DB can’t be in more than one AG.
Hi Allen, Thanks for this nice QA.
I’m planning to setup HADR environment, where the DR is in a different subnet. So, configuring the LISTENER/S seemed to be a bit tricky. Please advise me some best practices. Should I create two LISTENERS within the same AG?
Thanks!
No, a single listener would get an IP address in each subnet.
Excellent FAQ.
Through some research I found that you can’t have multiple SQL instances on the same server in a single AG, but you can have multiple AGs in the same SQL instance.
When would you recommend someone create a separate/additional SQL instance on a SQL server to host an AG versus putting the AG in an existing SQL instance with one or more AGS?
My team is trying to plan our our new SQL 2012 AG, and want to make sure we transition our current SQL 2008 R2 2 node cluster with 4 SQL instances over to the best possible model.
That’s always been the case – a single AG’s DBs have to be in the same instance; you can’t cross instances to create a single AG’s primary DBs. I always teach that when I do classes and such.
At the end of the day it’s about knowing your requirements for security and things like separation, but it’s also a resource issue. You need to test under load to ensure that whether you have one or 100 AGs, you don’t bring your system to its knees. There’s no clear cut “you must always do it NOW” answer.
If you want help in your strategy, feel free to reach out since that’s the type of thing we do consulting on.
Pingback: Availability Groups - My Bookmarks - SQL Server and related stuff - Site Home - MSDN Blogs
Is it required two network cards and multipathing?
You need two distinct network paths for multiple IP addresses.
Can I configure two instances, each with an AG, using the same Windows Failover Cluster?
Yes.
Can I configure two instances, each with an AG, using the same Windows Failover Cluster?
How I will setup two instances on single windows cluster each with own AG and Listener.
Yes. The instances would be on different WSFC nodes, and each would be a primary replica. The other instance would be the secondary to the primary. Ex: B would be A’s secondary replica for AG1, and A would be B’s secondary replica for AG1.
We have an AlwaysOn HA/DR Hybrid mirroring
We are having issues with “Server Agent jobs”, please elaborate on agent jobs in this question posted. My scheduled job somehow runs on the ACTIVE and INACTIVE box
Q: Do I still need to worry about things like logins and SQL Server Agent jobs?
Can you have Quorum and Listener in a AlwaysOn HA/DR Hybrid mirroring configuration ?
Thank You
George
There is no such thing as AlwaysOn HA/DR Hybrid mirrorring. That’s all the bad terminology in one sentence! I assume you have an AG that spans multiple sites (or into the cloud). I don’t see a question to answer here, so please elaborate on what you want to know.
Quorum is a mechanism for the WSFC. I suggest you watch my video from PASS Summit a few years ago to understand it. https://www.youtube.com/watch?v=o9s-Z70mizQ&list=PLl6tLuMdZdsb9-6HDwGIGV0PO88kBosLJ&index=6 It has nothing to do with the Listener. Any AG can have a Listener.
Can I combine failover clustering instances (FCIs) with an availability group (AG) when using synchronous AGs?
A: Yes, but you cannot have automatic failover in this combination
Q: What if we want to offload reporting onto an AG but keep automatic failover for our active/active FCI?
Using an AG for readable purposes to extend an FCI is fine. And remember, active/active is improper terminology. http://sqlha.com/2012/01/09/once-more-with-feeling-stop-using-activepassive-and-activeactive/
Will this work and work well? Create an “active-active” WSFC with SQL Server 2016 (enterprise edition) AlwaysOn — running primary on both nodes — separate (multiple) AGs — separate (multiple) listeners … Why? to make better use of resources. I understand that you need to size them such that everything “could” run primary on one server if necessary. Your thoughts?
First, let’s unpack the terminology. There is no feature called AlwaysOn; it is AGs. And there is no such thing as active active here 🙂 But how you described AGs i.e. N1 with I1 and N2 with I2, AG1 with I1 ad primary and I2 as secondary, and AG2 with I2 as primary and I1 as secondary – both with listeners – is how it works if you want to configure that.
Hi, Can we configure FCI with multiple AGs? Say, I have 3 user databases in an instance and I want to create AG for each databases, is it possible? Thank you.
Yes.