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>