SQL 2016 Always on with Basic AG

We currently have 2 sites PROD and DR with separate subnets across VPN and geographical region.
Prod has a Win failover cluster P1 with 2 nodes P1A, P1B with SQL installed as  a Role on Shared iSCSI storage
DR has a standalone SQL Server D1

We use merge replication to replicate 5 databases  between P1 cluster and D1 standalone.
The latest version of sitecore that we are using does not support this replication method because it creates data conflicts between the databases.

We have to switch to always on Model to make this work.

Always on Can work between 2 standalone instances or a failover cluster on one side and standalone on the other.
We plan on having a 2 node cluster in PROD just like now and standalone in DR.

I am looking for a step by step setup for this specific option.
Can anyone provide complete example with Failover setup as well as SQL AG setup?

Q1- SQL Standard offers basic AG which has a 2 node max.
In my case its a 1 failover pair and 1 standalone, that's total of 3 servers.
Does this count as a 2 node or a 3 node setup?

Q2- In WSFC would I see Role1 SQL cluster made up of Node P1A and P1B with Shared storage
and Role2 which is a AG made up on Node P1 cluster and D1

Q3 - How are SQL listeners configured in this case. Is it going to be 2 listeners in the AG, one for clustered SQL and one for standalone, or are they 3, one for each node?

Thanks
baysysadminAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Q1: A server is a node so that's 3 nodes Cluster.
Q2: What's the question?
Q3: Minimum required is 1 listener per AG. Listeners aren't per node nor per SQL Server instance but only per AG. Nevertheless you can have more than 1 listener if you want to.
baysysadminAuthor Commented:
Thanks for looking at the questions, but do you have the step by step for this type of setup?
Including failover cluster parts and the always on

As for Q2, i was asking how the setup looks when looking at the Roles section of WSFC, I cant seem to find a screen shot that matches my scenario.
Thanks

Does the order matter when configuring components?
If I have a standalone server with SQL installed, can I add failover role, and setup cluster after the fact and then configure always ON?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't have all steps on the top of my head and fortunately I don't need to setup AlwaysOn every day. When I have doubts I usually go to this MSDN article to check what I'm missing. If you want to see other articles, the best thing to do is to google by "sql server alwayson setup" or something similar.

Does the order matter when configuring components?
Yes it matter. First thing to do is to setup the Windows Cluster and only after that, install the SQL Server. But check the article above and if you have more questions, just return to ask about a specific step.

Good luck with your AlwaysOn setup.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

baysysadminAuthor Commented:
I founds bits of guides, but none match my config so its very confusing. But like you said, the order in which things are done is important.
Im having a hard time matching the right win cluster setup with right SQL cluster setup.

When I install the SQL server after doing the WSFC what setup option do I choose on the 3rd server?

Assuming the 1st 2 nodes will be clustered as one P1 cluster, P1a, P1b nodes, but the remote DR will be a standalone D1 node

So in the setup wizard I would choose ??
New SQL Failover cluster for the P1 nodes?
And New SQL Standalone install on the D1 node?

Then when I turn on Always ON, and setup the AG, the AG cluster will be made up of a P1 (2node) cluster and D1 (standalone)

So essentially it will be a SQL Always on cluster on top of Windows cluster.
baysysadminAuthor Commented:
I found this guide which seems to be the best so far
https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/

And it in it says this can be done
after-the-fact to an already-in-production SQL Server

So it sounds I can have standalone sql already installed. Then simple add the WSFC roles and setup a cluster, and then add the already installed sql to the cluster services.
Vitor MontalvãoMSSQL Senior EngineerCommented:
The SQL Server instances can and it's even recommended to be stand-alone instances but the servers need to be part of a Windows Cluster. The AG needs the cluster quorum so the 3rd server needs to be part of the existing Windows Cluster.
baysysadminAuthor Commented:
I just created my first AG, but it failed on 2 steps.
I didnt have any issues while adding them to Win cluster.

1. Microsoft SQL Server, Error: 19471 Creating AG Listener
So i followed this guide to create the name manually. But this didnt work for me, my symptom was a bit different, it said DNS OK, but Kerberos status: constraint violation occured
https://blogs.msdn.microsoft.com/alwaysonpro/2013/10/30/create-availability-group-listener-fails-with-message-19471-the-wsfc-cluster-could-not-bring-the-network-name-resource-online/
2. Failed to Join to secondary replica

One guide said to add the cluster name permissions to the OU where the computer name is, so that it can create computer objects.
But that didnt help.
Event logs dont show any errors.
I generated a cluster log but could not find any obvious errors in it, just these generic ones.

INFO  [RES] Network Name: Agent: New request published under NN:fe939f3b-ec8f-44f5-8bad-9fb39d0d1f8d:Configuration: Netname/Error
ERR   [RCM] [GIM] ResType Virtual Machine has no resources, not collecting local utilization info
ERR   mscs::TopologyPersister::TryGetNetworkPrivateProperties: (2)' because of 'OpenSubKey failed.'
ERR   [RHS] RhsCall::Perform_NativeEH: (21)' because of 'Startup routine for ResType MSMQTriggers returned 21.'
ERR   mscs::ConfigureHyperVRootReserveMemoryForCache: (2)' because of 'OpenSubKey( name, access, key, NOTHROW() )'(SOFTWARE\Microsoft\Windows NT\CurrentVersion\Virtualization)


I am doing this in my lab environment.
I should point out that my lab is made of of SQL 2016 Standard and SQL 2016 Ent.
Would having SQL version mismatch cause issues here?

Any suggestions on how to troubleshoot this?
Vitor MontalvãoMSSQL Senior EngineerCommented:
I never tried to create an AG between different SQL Server Editions. In fact, I just have created AG on Enterprise Editions.
SQL Server 2016 Standard Edition has a Basic AG feature but to be honest, because of the limitations, I never used it. Can't you have only Enterprise Edition instances? Or Developer Editions if you are working in a non-productive environment.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Microsoft SQL Server, Error: 19471 Creating AG Listener
This means that the Cluster Service account doesn't have permissions to create objects in the Active Directory.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baysysadminAuthor Commented:
I got the network name working, yes it was premissions.
As for editions , thats what I have available in my lab, if I have to ill install another Standard or Ent version.

It still fails on the join to secondary part because of versions.
The weird thing is, I want to run the Basic lower mode, which is supported by both standard and Developer. Unless im wrong, maybe Basic wont run in developer?
This error would make more sense if I wanted to run FULL AG on Standard version, which is obviously not supported.

The specified command is invalid because the Always On Availability Groups join availability group (basic) feature is not supported by this edition of SQL Server. For information about features supported by the editions of SQL Server, see SQL Server Books Online.
Failed to join local availability replica to availability group 'AG-Core_TEST'.  The operation encountered SQL Server error 41199 and has been rolled back
The operation encountered SQL Server error 41199 and has been rolled back
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like the Basic AG it's really for Standard Edition. It makes sense because if you can go to a Full AG solution, then the Basic one should be not available.
baysysadminAuthor Commented:
I replaced my Dev with Standard, and I was able to create AG. I guess it didnt like mismatch versions.

Can you confirm that I CANT add a 2 node cluster together with 1 standalone using SQL 2016 Standard Basic AG?

I noticed when creating AG, when you add a 2nd SQL server hostname, the ADD gets grayed to.
The reason why i think maybe you can have a cluster + standalone, is because you add the cluster shared name, and not the individual nodes.
So in the end there are 2 hostnames but total of 3 nodes. And the limit says 2 replicas, NOT 2 nodes.

I dont have a cluster setup right now to test this, but it would be nice if someone could confirm YES or NO, if this is possible.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you confirm that I CANT add a 2 node cluster together with 1 standalone using SQL 2016 Standard Basic AG?
You can't work more than 2 Replicas (1 Primary and 1 Secondary). That's the limitation with the Basic AG.
baysysadminAuthor Commented:
Correct, BUT my Primary is a cluster (2nodes) and secondary is standalone 1 node.
Logically that should work i think.
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can't have a standalone server to be part of an AG. I've already mentioned that. All servers need to be nodes of a Windows Cluster.
baysysadminAuthor Commented:
By standalone I meant, standalone SQL instance, with win cluster membership.
And by sql cluster I meant 2 node sql cluster instance running on win cluster membership.
I guess its important to differentiate SQL clusters from Win clusters, when talking about this since they run on top of each other.

So thats 2 total sql instances running on WIN cluster.

And yes it is supported I just found my answer in #5 here.
The draw back is, you cant have auto failover in that scenario.

https://blogs.technet.microsoft.com/msftpietervanhove/2017/03/14/top-5-questions-about-basic-availability-groups/
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.