Always On Availability Groups with No Underlying Cluster in SQL Server v.Next
UPDATED 2/22/17 in the afternoon
With a lot of the focus seemingly going to the Linux version of SQL Server v.Next (including the inclusion of Always On Availability Groups [AGs] in the recently released CTP 1.3) , I don’t think that a lot of love is being showered on the Windows side. There are a few enhancements for SQL Server availability, and this is the first of some upcoming blog posts.
A quick history lesson: through SQL Server 2016, we have three main variants of AGs:
- “Regular” AGs (i.e. the ones deployed using an underlying Windows Server failover cluster [WSFC] requiring Active Directory [AD]; SQL Server 2012+)
- AGs that can be deployed without AD, but using a WSFC and certificates (SQL Server 2016+ with Windows Server 2016+)
- Distributed AGs (SQL Server 2016+)
SQL Server v.Next (download the bits here) adds another variant which is, to a degree, a side effect of how things can be deployed in Linux: AGs with no underlying cluster. In the case of a Windows Server-based install, this means that there could be no WSFC, and for Linux, currently no Pacemaker. Go ahead – let that sink in. Clusterless AGs, which is the dream for many folks (but as expected, there’s no free lunch which I will discuss later). I’ve known about this feature since November 2016, but for obvious reasons, couldn’t say anything. Now that it’s officially in CTP 1.3, I can talk about it publicly.
Shall we have a look?
I set up two standalone Windows Server 2016 servers (vNextN1, vNextN2). Neither is connected to a domain. Figure 1 shows the info for vNextN1 (for all pictures, click to make bigger).
Figure 1. Standalone server not domain joined
Using Configuration Manager, I enabled the AG feature. Prior to v.Next, you could not continue at this point since there was no WSFC; you would be blocked and get an error. However, in v.Next, you will get what is seen in Figure 2. It still indicates that there is no WSFC, but it happily allows you to enable the AG feature.
Figure 2. Enabling the AG feature in v.Next
After enabling and restarting the instance, you can clearly see in Figure 3 that the AG feature is enabled. We’re not in Kansas anymore, Toto.
Figure 3. AG feature is enabled, but no WSFC
Just to prove that there is no magic, if you look in Windows Server, the underlying feature needed for a WSFC is not enabled which means a WSFC cannot be configured. This is seen in Figure 4.
Figure 4. Failover clustering feature is not installed – no WSFC!
In SQL Server, configuring this is done via T-SQL and is similar to how it is done for AD-less AGs with Workgroup WSFCs in SQL Server 2016/Windows Server 2016. In other words, you’re using certificates. In addition to certificates, there is a new clause/option (CLUSTER_TYPE) that exists in the CREATE and ALTER AVAILABILITY GROUP T-SQL. Unlike the Linux example in documentation which shows how to use the CLUSTER_TYPE syntax, I altered the syntax I’ve been using for the AD-less AGs with certificates since it is basically the same and I did not use seeding (you can if you want); I manually restored the database AGDB1. I created an AG called AGNOCLUSTER. This can be seen in Figures 5 and 6.
Figure 5. vNextN1 as the primary replica of AGNOCLUSTER
Figure 6. vNextN2 as the secondary replica of AGNOCLUSTER
To support this new functionality, there are new columns in the DMV sys.availability_groups – cluster_type and cluster_type_desc. Both can be seen in Figure 7. You will also get an entry in sys.availability_groups_cluster with this new cluster_type (also a new column there).
Figure 7. New columns in sys.availability_groups
So what are the major restrictions and gotchas?
- This new AG variant is NOT considered a valid high availablity or disaster recovery configuration without an underlying cluster (WSFC for Windows Server or currently Pacemaker on Linux). It is meant more for read only scenarios, which means it’s more meant for Enterprise Edition than Standard Edition. I cannot stress enough this is NOT a real HA configuration.
- UPDATE – A major reason this is not a real HA configuration is that there is no way to guarantee zero data loss without you first pausing the primary and ensuring that the secondary replica is in sync (or replicas, as the case may be).
- Having said #1, you can do a manual failover from a primary to a secondary. This would be true even in the case of an underlying server failure, hence this not being really a true availability configuration: there’s no cluster and the mechanism (sp_server_diagnostics) to detect and handle the failure.
- Since there’s no underlying cluster, you can’t have a Listener. This should be painfully obvious. This also means that you will connect directly to any secondary replica for reading. This makes it possibly less interesting for read only scenarios, but again, did you expect you’d get everything?
- Since there is no Standard Edition version of the CTP, it is unknown if this will work with Standard Edition in SQL Server v.Next. I would assume it will, but we’ll see when v.Next is released.
- UPDATE – This also can most likely be used for migration scenarios (arguably it will be the #1 use), which I will talk about in a Windows/Linux cross platform blog post soon.
- UPDATE – This is not the replacement for database mirroring (DBM). That is/was putting AGs in Standard Edition in SQL Server 2016 even though it requires a WSFC. You get so much more that you really should stop using DBM. It’s been deprecated since SQL Server 2012 and they could pull it at any time (and I’m hoping it’s gone in v.Next).
Keep in mind this is how things are now, but I don’t see much, if anything, changing whenever RTM occurs.
I won’t really be talking about this configuration this weekend at SQL Saturday Boston or at SQL Saturday Chicago in March, but I will be talking about it a little bit at both the SQL Server User Group in London on March 29th and at the SQL Server User Group in Dublin on April 4. I will be covering this configuration in more detail as part of my Training Day at SQL Bits 16 – “Modern SQL Server Availability and Storage Solutions” (sign up for it today – seats going fast!). It will also be part of my upcoming new SQLHAU course Always On Availability Groups Boot Camp coming up in August, and incorporated into the 4-day Mission Critical SQL Server class (next scheduled delivery is in December).
Pingback: Clusterless Availability Groups – Curated SQL
Hello Allan,
Very nice article with demo. Looks like this is really cool feature.
I have one question about without domain account how both SQL Server connected to each other and also how you setup Always on between two different databases(on different instances).
Thanks in advance.
The feature is not Always On. It’s availablity groups. 🙂
If you do not use domain accounts, it’s certificates.
I’m trying to see if it’s possible to have a read scale out only replica on another instance that’s on the same physical box. Is this possible? Theoretically it should be, but I find no instructions for configuring this feature on Windows at all as yet.
No, not possible. Not sure why you thought it theoretically would be. AGs enforce that two replicas cannot be on the same server (physical or VM). AGs also prefer the DBs are in the same path, so again, not possible normally since two instances couldn’t put data files of the same name in he same folder. If they are in different, say, VMs on the same hypervisor host, not a problem.
Hi Allan,
Thanks for this article. can you please assist me on below points ?
(i) SQL server 2017 is called as SQL server VNext, correct ?
(ii) Can we create Always on without cluster in Windows or we can do this only in Linux ?
(ii) I seen one reply this is AG feature not Alwayson. Does it create the replica of db and copy the transactions from primary to secondary ? Can we use it for DR in place of Logshipping or Mirroring .
1. Yes.
2. There is no such feature as Always On. See http://sqlha.com/2015/12/16/dear-microsoft-i-love-you-but-youre-driving-me-batty/, which has links to other things.
The feature is availablility groups. And yes, you can do it in Windows as well but the reality is that this is not something you should do normally.
3. AGs have a source DB (primary replica) and one ore more secondary replicas. You can initialize manually or via the AG, and then it will automatically send over the txns like log shipping or DBM. You should consider attening one of my classes to learn more.
How is Availability Groups in SQL Standard edition a replacement for Mirroring?
This is what bring is down:
“Support for one availability database.”
Too bad if you have more than one DB – you now have the over head of having to create more than one AG for each DB you want to mirror. What fun.
Totally useless replacement until such time as you can do more than one DB.
In terms of limitations (1:1 from source to destination, one DB per AG), it’s the same as DBM but you get added things like the listener. If you wand more than one DB per AG, you need Enterprise Edition, and that support has been there since 2012. So if you’re stuck on Standard Edition, you at least have a modern replacement with the same high level limitations.
Thanks for sharing.
Just a small update regarding enabling the AG feature using Configuration Manager. If you use SQL 2017’s CM it’ll happily let you enable the feature on a 2014 instance – whereas if you use SQL 2014’s CM it will tell you the pre-reqs are not met. Since MS advises to always use the latest CM if you have multiple versions installed this could lead one to falsely believe a WFCS is not needed for 2014…
Hi Allan,
Just saw a question in dba.stackexchange “AG of 2 replicas on 2 nodes in WSFC : is it possible to add 3rd replica to AG and NOT add 3rd machine WSFC?”
https://dba.stackexchange.com/questions/220940/ag-of-2-replicas-on-2-nodes-in-wsfc-is-it-possible-to-add-3rd-replica-to-ag-an
My answer will be no because existing Availability Group is creating with having WSFC in place (and most likely listener, assuming here). Would love to know the for sure.
Taiob
The short answer is no, but it may be an it depends. Example: N1 has I1. N2 has I2 and I3. If you want AG1 to go from I1 to I2 and AG2 from I1 to I3, fine. If you want AG1 to go from I1 to I2 and I3, you need another node.
“… You get so much more that you really should stop using DBM. … ”
I’m still trying to figure out what “so much more” stands for with regards to the comparison of AG vs DBM for single database solutions.
The one I get is the advantage of the Listener ( WSFC needed ) which means you no longer have to provide “failover_partner” in the connection string.
First, DBM is deprecated. Supported & updated feature versus one whose development stopped years ago. 2016+ has multiple redo threads on a secondary replica. There is the aforementioned listener (HUGE and arguably the biggest difference). If you are using EE there is the ability to have readable replicas, perform backups, and run DBCC stuff on a secondary replica.