quorum Archives

October 27, 2014

What’s New in Windows Server vNext for SQL Server: Cloud Witness

Unless you’ve been living under a rock, you should have heard by now that Windows 10 was announced in late September and is due sometime in 2015. A new version of the Windows client always means another version of Windows Server. At the same time the Windows 10 Technical Preview was made available, so too was its Windows Server counterpart. If you have a MSDN subscription, it’s there. More info and where else to get it can be found here. The release will most likely not be called Windows Server 10, so we’ll have to speculate on what they will call it.

With a new version of Windows Server comes changes – usually for the better. I love what both Windows Server 2012 and 2012 R2 brought to the table for clustering especially with things like dynamic quorum and witness, last man standing, the tie breaker, and more. Until now, I’ve been under NDA not to talk about some topics related to vNext, but today I got the thumbs up on the one I care about the most which would impact things like AG deployments.

One problem with multi-site (or even hybrid) scenarios for AGs or FCIs is that if you need to use a witness resource, where do you put it? There’s a lot to say on that topic which I put into one of the two quorum chapters in the book, but the bottom line is that you basically have two options: have a witness at one of the sites (known as asymmetric witness) or stick it somewhere that all sites can see such as another data center or somewhere in the cloud if you have the right networking. Today’s version of putting a witness in the cloud is the traditional file share witness (FSW). In vNext, you have a new tool in the drawer: cloud witness. It is early days, so there is always a chance this feature may not make the final cut but I sure hope it stays!

This option can easily be seen in Figure 1 in the Configure Cluster Quorum Wizard. It was hiding in plain sight, but like I said, I couldn’t talk about it until today.

Cloud witness option in the quorum configuration wizard

Figure 1. Cloud witness option

This is not a FSW – let me be clear. On the next dialog shown in Figure 2, you have to enter your credentials for Azure storage. Yes, it’s Azure only – sorry Amazon or Google lovers. What this also means is that you need to have the cluster nodes connected to the Internet (not always possible), but this does NOT require the special hybrid networking you would need to normally bridge Azure to on premise deployments. That alone reduces many complexities intruduced with standard hybrid or disaster recovery solutions for SQL Server.

Figure 2. Entering the account information for the cloud witness

After the cloud witness is created, Figure 3 shows what you will see in Failover Cluster Manager (you can also see things in PowerShell, but this looks nicer in a blog post!). The cloud witness is a resource in the WSFC’s cluster group just like a disk or file share witness would be.

Figure 3. Cloud witness and how it looks in Failover Cluster manager

Finally, on the backend in Azure, it creates a GUID associated with the WSFC similar to that of a FSW. Figure 4 shows what this would appear like.

Figure 4. What the cloud witness looks like in Azure

Want to know more about quorum and witness? It’ll be in the book, but if you’ll be at PASS Summit next week, come see my session “Did You Vote Today? A DBA’s Guide to Cluster Quorum” (DBA-311) on Friday, November 7, at 8:15 AM in room 6E. It is a 75 minute session. “Did You Vote Today?” was also selected to be broadcast on PASStv, so even if you’re not at Summit, you can watch it live on the Web. No pressure, right? I may take questions live from Twitter – that has yet to be determined.

July 2, 2013

WSFC’s Dynamic Witness in Windows Server 2012 R2

As I noted in a previous blog post “Windows Server 2012 R2 for the DBA: 10 Reasons It Matters”, Windows Server 2012 R2 introduces a new quorum concept: dynamic witness. In fact, the whole notion of quorum has changed a bit in Windows Server 2012 R2 (I’ll save that for another time). In this post I want to focus on dynamic witness only.

For those who don’t know, a witness in context of quorum is that additional “voter”/tie breaker in cases where you have an even number of nodes to ensure that you always have an odd number of votes. Prior to Windows Server 2012 R2, if you lost a node in a Windows Server failover cluster (WSFC), you would manually have to readjust quorum. For example, if you have a three-node WSFC and lost one node, you would still be up and running, but you would be down to two nodes and to get back to odd, you’d have to add some sort of witness in manually. Managing quorum is one of the most important administrative tasks for deploying WSFCs, and one of the most common things I see most environments not do well.

Enter Windows Server 2012 R2. By default, you create a witness when the WSFC is formed no matter how many nodes you have. However, the WSFC will not use the witness unless it needs it. I’m going to show you an example to illustrate how all of this works. In this case, I have a three node WSFC named STYX with the node names of DENNIS, TOMMY, and JY. I have a single clustered instance (FCI) of SQL Server 2014 named GRANDILLUSION that is currently running on JY. Figures 1 and 2 show this configuration.

The interesting thing as shown in Figure 1 is that there’s a new parameter for the WSFC: WitnessDynamicWeight. This will either be set to 0 or 1. If it’s 0, no witness is in play. With 1, a witness is being used. It’s really that simple.

Figure 1. All nodes up

Figure 2. GRANDILLUSION up and running

Let’s say we lose JY which is currently hosting GRANDILLUSION. JY fails over to TOMMY as shown in Figure 3.

Figure 3. GRANDILLUSION now running on TOMMY

Now this is where things get interesting. With Windows Server 2012 and earlier, at this point you would see warnings noting with the loss of a node, you would go down. If you look at Figure 4, note that JY is down as I stated, but WitnessDynamicWeight has a value of 1 since we have an even number of nodes, making the total number of voters 3. This means it is using the witness I configured during the initial setup of the WSFC. In other words, the WSFC is managing quorum for me.

Figure 4. WitnessDynamicWeight = 1

Now we lose TOMMY and GRANDILLUSION is running on DENNIS as shown in Figure 5. Wait, what? We’re down to one node – last man standing – and still running? You bet! This concept is not 100% new – last man standing was possible with Windows Server 2012 in many cases.

Figure 5. GRANDILLUSION now on DENNIS

Figure 6 shows that both JY and TOMMY are down, but DENNIS is left standing alone with the witness disk.

Figure 6. Down to one node, but still running

Can you override dynamic witness? Sure. Let’s assume you have the same three node WSFC and you want the old behavior. Just get rid of the witness. In the Cluster Cluster Quorum Wizard dialog shown in Figure 7, select the option Do not configure a quorum witness.

Figure 7. Removing the witness

Figure 8 shows the warning that Microsoft gives you when you have no witness in Windows Server 2012 R2.

Figure 8. Warning that there is no witness

Now let’s look at a similar scenario as above. We lost JY, and GRANDILLUSION is on TOMMY as shown in Figure 9. Note that WitnessDynamicWeight has a value of 0 and there is no quorum resource (nee witness) configured.

Figure 9. Look Ma! No witness

Finally, in Figure 10 we see that TOMMY is down for the count, and DENNIS owns GRANDILLUSION. The reason I show the value for WitnessDynamicWeight and no quorum is to show I wasn’t making this up; you’re literally down to last man standing again.

Figure 10. Down to last man standing

Like my quick glance at using CSVs with SQL Server 2014, this is a sneak peak at how Windows Server 2012 R2’s dynamic witness along with dynamic quorum introduced in Windows Server 2012 can increase the availability of your FCIs and AGs.

June 28, 2012

Maintaining Quorum During Windows Patching and Updating

I’ve been working with a customer to deploy their new Windows Server 2008 R2 SP1-based failover cluster that will have bot SQL Server 2008 R2 and SQL Server 2012 instances. One thing I always talk about in my classes as well as the presentations I focus on patching is this: you must maintain quorum during the patching and updating cycle. Quorum is one of the most important considerations in general for your clustered deployments (whether you’re implementing traditional failover clustering instances or availability groups), and it’s something that quite frankly, I find there’s a real lack of knowledge out there. It’s so important it’s most likely getting a whole chapter in my upcoming book Mission Critical SQL Server 2012.

I have talked to customers that have had SQL Server outages because their Windows guys when patching the underlying nodes for the Windows Server failover cluster (WSFC) rebooted too many at once, so there were not enough voters us, thus bringing the WSFC (and SQL) down when it wasn’t expected. This is NOT a scenario you want. While in theory it’s easy to control and keep track of when things reboot, this little gremlin in Figure 1 pops up after a little while reminding you it needs to be done:

Figure 1. Reboot needed warning

That’s all well and good, but the unskilled administrator who happens to see that message if they have to log onto the server may think nothing of it and just click Restart now. Yikes! The value of the reminder can be altered as shown in Figure 2, but you still need to worry about the reboot being done.

Figure 2. Changing the reminder value

There’s another dark underbelly I’ve seen these dialog boxes associated with: automatic updating of Windows via Windows Update. That is a much scarier situation because that means your servers could be updating 24×7 on their own, and some of those updates may even force reboots. Check your settings! I still see production servers at client sites that have automatic updating enabled. If they are, you need to have a serious discussion with your server admins as to why this is being done. Automatic updating is not in line with mission critical. Yes, it’s important to have a patch management strategy that keeps you up-to-date, but doing it smartly is the key. Not blindly.

April 13, 2012

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>

Categories