AlwaysON issue v2

Hram Admin 40

Hello!

There are two newly installed SQL Server 2022 Standard servers installed from the same ISO(say SQL1 and SQL2), OS = WinServer 2022 Standard (for both servers, same ISO), both added to the domain with the single domain controller named DC1.

Here's the screenshot of the last step in configuring AlwaysON: CLUSTER9

Does this warning want to say SQL Server 2022 Standard does not support AlwaysON (Is HADR enabled = TRUE for SQL1 and SQL2) ???

Regards,
Michael

3 answers

Erland Sommarskog 115.3K Reputation points MVP

2024-12-06T15:51:12.2033333+00:00
Does this warning want to say SQL Server 2022 Standard does not support AlwaysON

It's a little tricky. You are trying to create a "normal" availability group, and that is indeed not supported on Standard Edition. On Standard Edition you can only create basic availability groups. That is, you need to say

CREATE AVAILABILITY GROUP grpname WITH (BASIC) FOR ...

Exactly how you would do that from the UI in SSMS, I don't know, since I've never used it. Then again, one likes to think that SSMS should know that you are on Standard and handle this for you, but apparently not.

Basic availability groups are limited to two nodes, and the secondary cannot be readable. There can only be one database in a basic AG.
Please sign in to rate this answer.
Hram Admin 40 Reputation points

2024-12-06T17:38:08.36+00:00

"You are trying to create a "normal" availability group, and that is indeed not supported on Standard Edition." - ??? I always right-click Availability Groups and select New AG wizard - it would create a basic AG on Standard edition - it just can't create not basic group, ~ as per by design...

I've been creating AGs on standard editions since SQL 2014 and haven't had any issues with them, for example: two AGs on standard edition in another domain (the same SQL 2022 and WinServer 2022):

Erland Sommarskog 115.3K Reputation points MVP

2024-12-06T21:57:05.89+00:00

I'm not sure what the wizard is doing. In any case, there is no difference between SQL 2022 and SQL 2016/17/19 with regards to basic AGs. (You also mention SQL 2014, but basic AGs were introduced in SQL 2016.)

Possibly, SSMS is confused. Possibly, you made some choices that confused SSMS. I would suggest that you try again.

And, oh, you did apply the most recent Cumulative Update before you started creating the AG, didn't you?

Hram Admin 40 Reputation points

2024-12-06T22:10:41.5466667+00:00

"And, oh, you did apply the most recent Cumulative Update before you started creating the AG, didn't you?" - no, but I haven't applied it in the other domain either and it worked perfect... Will keep trying - thank you for your help!

Erland Sommarskog 115.3K Reputation points MVP

2024-12-18T22:05:13.7333333+00:00

Looking closer at the error message, I see that it is trying to do ALTER AVAILABILITY JOIN, and the gist of the error message is that this command is not available in Standard Edition, which makes sense, since a Basic AG is always two nodes.

I think that you at some point failed to specify that you wanted a Basic AG. I can't say exactly where, because I don't have an environment to try on.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
ZoeHui-MSFT 39,496 Reputation points

2024-12-09T02:53:06.63+00:00

Hi @Hram Admin，

Enabling Always On availability groups is a prerequisite for a server instance to use availability groups. Before you can create and configure any availability group, the Always On availability groups feature must have been enabled on each instance of SQL Server that will host an availability replica for one or more availability groups.

Enable or disable Always On availability group feature

To create a basic availability group, use the CREATE AVAILABILITY GROUP Transact-SQL command and specify the WITH BASIC option (the default is ADVANCED). You can also create the basic availability group using the UI in SQL Server Management Studio starting with version 17.8. For more information, see CREATE AVAILABILITY GROUP.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.
Please sign in to rate this answer.
Hram Admin 40 Reputation points

2024-12-09T09:31:07.59+00:00

Hi Zoe Hui,

"Enabling Always On availability groups is a prerequisite for a server instance" - yes, of course, I've mentioned it here: (Is HADR enabled = TRUE for SQL1 and SQL2). SQL Server Management Studio = 20.0.2...

ZoeHui-MSFT 39,496 Reputation points

2024-12-10T06:17:58.3033333+00:00

Hi @Hram Admin，

You can also create the basic availability group using the UI in SQL Server Management Studio starting with version 17.8.

Have you check the SSMS version?

Hram Admin 40 Reputation points

2024-12-18T08:35:42.18+00:00

"Have you check the SSMS version?" - yes, I have: 20.2.30.0 :(

Hram Admin 40 Reputation points

2024-12-18T08:36:08.96+00:00
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Zhou, Yun (CMOC) 0 Reputation points

2024-12-18T09:05:42.4533333+00:00

First, your standard edition does not support the sql always on creation requirement. Secondly, please clarify your creation steps. Have you created a windows cluster first and added both server nodes to it? After ensuring that there is no creation error for the windows cluster, please find the sql configuration manager, select the instance (usually the default MSSQL) and enable the always on function. Please do not configure a shared disk under the windows cluster.

Bert
Please sign in to rate this answer.
Zhou, Yun (CMOC) 0 Reputation points

2024-12-19T01:26:26.4966667+00:00

Please help confirm the next step of the screenshot. I think your problem is that you did not add the SQL administrator group to the local administrator group. Another point needs to be confirmed, whether SQL AG has full control over the two node machines.

Hram Admin 40 Reputation points

2024-12-26T09:13:09.1266667+00:00

Hello Zhou Yun,

Please excuse me for the delay...

"Please help confirm the next step of the screenshot. I think your problem is that you did not add the SQL administrator group to the local administrator group. Another point needs to be confirmed, whether SQL AG has full control over the two node machines." - I can't create the listener at that point so I choose "Not to create listener..." and proceed without it.

"Please help confirm the next step of the screenshot. I think your problem is that you did not add the SQL administrator group to the local administrator group" - which group are you talking about? There's no such group as "SQL Administrators"...but the account I'm using for AG creation has sysadmin role on both SQL servers and is the member of local Administrators group on all servers.

"Another point needs to be confirmed, whether SQL AG has full control over the two node machines." - how can I do it?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

AlwaysON issue v2

3 answers

Your answer