1

Using SQL Server 2017, CU3. I successfully create a 2 node cluster=NONE availability group. Both replicas are VMs local to the site. When attempting to add another replica that is off-site, I get sql error 19417, which basically says my replica add failed because the operation is not supported when the AG is running under a Windows Server Failover Clustering context. However, I am not using a cluster, that is the point of the cluster=none AG. I have had trouble before with CU1, and went backwards to the RTM version. That is my next step. Posting in case someone has seen something similar.

2

I finally found Creating a clusterless availability group using windows server 2016 and sql server vNext CTP 1.4 on Stack Overflow, which got me on the correct path.

I had been using the wizard to create the AG, when that failed I used the wizard to script out the t-sql and run step by step. What I failed to notice is that the wizard creates the replica on the primary, but never actually joins the replica on the secondary.

That is, this command executes on the primary:

ALTER AVAILABILITY GROUP [XXXXX]
ADD REPLICA ON N'YYYYY' WITH (other stuff)

but this command never runs on the new secondary:

ALTER AVAILABILITY GROUP [XXXXX] JOIN WITH (CLUSTER_TYPE = NONE);

And apparently the cluster_type = none is critical. At any rate, I am watching my clusterless AG successfully build now, so woohoo! And maybe worth mentioning that I am using SSMS 17.5. The wizard code seems to need an update to include the missing step.

As a follow up. I worked with MS through my company's support agreement. They verified there were a few scenarios that exhibited the wizard behavior described above. They are targeting SSMS 17.6 to incorporate the updates.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.