SQL Server 2017 Availability group without Windows Server Failover Clustering

Question

SQL Server 2017 Availability group without Windows Server Failover Clustering

stuli3

SSC Veteran

Points: 202
More actions
December 7, 2017 at 5:53 am

#341218

Hi,
I would like create az availability group without Windows Server Failover Clustering.
I have a primary server and a secondary with SQL Server Enterprise 2017.
I have done availability group but when I trying join secondary server to the group it is drop an error: "An attempt to fail over or create an availability group failed. This operation is not supported when AlwaysOn Availability Groups is running under a remote Windows Server Failover Clustering (WSFC) cluster context. Under a remote cluster context, failing over or creating availability groups are not supported. (Microsoft SQL Server, Error: 19417)"
I cannot what I can do it. Please help me!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 1

An Availabilty Group requires WSFC. It does not require a Failover Clustered Instance (FCI), which is a SQL Server cluster.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server

NJ-DBA SSChampion Points: 13834 More actions · Answer 2

Do I really get to correct Steve Jones? Nah, I must be wrong.... SQL Server 2017 doesnt strictly require WSFC for AlwaysOn AGs. For example, on Linux there is no WSFC but AGs work fine using the "EXTERNAL" option in the wizard. I must confess that I've not set it up without a cluster, but I bet it would work. This guy is doing it: http://sqlha.com/2017/02/22/alwayson-availability-groups-no-underlying-cluster-sql-server-v-next/ here is another approach: http://www.sqlmastersconsulting.com.au/SQL-Server-Blog/sql-server-2017-read-scale-out-availability-group

MS documentation: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups

In any event, I dont think you are getting automatic failover without the cluster though.

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

NJ-DBA - Monday, December 11, 2017 7:01 AM
Do I really get to correct Steve Jones? Nah, I must be wrong.... SQL Server 2017 doesnt strictly require WSFC for AlwaysOn AGs. For example, on Linux there is no WSFC but AGs work fine using the "EXTERNAL" option in the wizard. I must confess that I've not set it up without a cluster, but I bet it would work. This guy is doing it: http://sqlha.com/2017/02/22/alwayson-availability-groups-no-underlying-cluster-sql-server-v-next/ here is another approach: http://www.sqlmastersconsulting.com.au/SQL-Server-Blog/sql-server-2017-read-scale-out-availability-group
MS documentation: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups
In any event, I dont think you are getting automatic failover without the cluster though.

You do, and that's cool. Didn't realize that was working. I thought there was still a WSFC involved.

I'll have to give that a try, though I know Allan has had issues with some of the newer AG stuff as it's been released. There must be something else with the OP that missed earlier in the steps. I'll confess I haven't tried this, but perhaps this is something I need to do in Jan when I'm back in the office.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

stuli3 - Thursday, December 7, 2017 5:53 AM
Hi,
I would like create az availability group without Windows Server Failover Clustering.
I have a primary server and a secondary with SQL Server Enterprise 2017.
I have done availability group but when I trying join secondary server to the group it is drop an error: "An attempt to fail over or create an availability group failed. This operation is not supported when AlwaysOn Availability Groups is running under a remote Windows Server Failover Clustering (WSFC) cluster context. Under a remote cluster context, failing over or creating availability groups are not supported. (Microsoft SQL Server, Error: 19417)"
I cannot what I can do it. Please help me!

Under sql server 2016 and Windows 2016 it is possible to deploy workgroup clusters or multi domain clusters as well as traditional single domain clusters.
These extra options put the cluster outside of Active Directory, DNS becomes the cluster registry. This is where we start to see the features appearing that sql server 2017 provides.

Under SQL server 2017 vnext it is possible to create a clusterless AG, but it's not without restrictions and pretty much only suitable for read only or test purposes at present.
This technology is heavily evolving and will likely change extensively through future releases as Microsoft "get it right"

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

NJ-DBA SSChampion Points: 13834 More actions · Answer 5

I've been working with SQL Server 2017 Distributed Availability Groups and (cross domain). I have 2 nodes in a cluster in domain A doing syncronous replication, and two nodes domain b doing async replication and then a Dist AG between the group in domain A and the group in domain B... it just works. I followed these steps:

Endpoint setups:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups

AlwaysOn config:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups

The biggest problem I have is that performance with syncronous replication is pretty bad for inserts and updates.

Admingod SSCertifiable Points: 5997 More actions · Answer 6

I tried configuring the AG without WSFC. However, when i tried to do a manual fail over and i see that there is a option grayed out. Does that mean it's a limitation and you can't do the fail over or i am missing something?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

A read-scale (or clusterless) Availability Group is for read scale out and not HA. There are very specific steps required to even use a read-scale AG for DR purposes:

Fail over the primary replica on a read-scale availability group

The books online topic for Read Scale Availability Groups without a cluster includes the following note:

This is not a high-availability setup. There is no infrastructure to monitor and coordinate failure detection and automatic failover. Without a cluster, SQL Server can't provide the low recovery time objective (RTO) that an automated high-availability solution provides. If you need high-availability capabilities, use a cluster manager (Windows Server Failover Cluster on Windows or Pacemaker on Linux).

Even doing DR to a clusterless node is more complex and leaves you open to a potential split brain scenario (more than one active primary replica) if you don't follow the steps precisely. The UI in SSMS doesn't allow you to do the manual failover on the read-scale AG setup, you need to use the DDL commands in the link above.