Windows 2016 data center edition and SQL 2016 AOG

Daniel,

"SQL Server requires the use of certificates for endpoint security, just as the database mirroring scenario requires certificates."

this means it needs SSL for the workgroup ?

so without this,should join domain...

Prerequisites are as they've been made by their creators, Microsoft. If you want to have cluster and further AGs without domain you have to comply with these prereqs.
Certificates are required for trust relationship. You just need to have certificates which are trusted on both boxes, it doesn't imply Domain.

Regards,
Daniel

" it doesn't imply Domain.
"

yes, but Certification should means SSL here, right?  so the question therefore is I need to create SSL for it as part of the reader comment also asking this.

Indeed, you have to have certificate which is trusted on both sides. This certificate is being created on each SQL Server and then exchanged, as in database mirroring between workgroups. You don't need AD for this.
I don't find certificate requirement for Cluster itself.

Regards,
Daniel

"I don't find certificate requirement for Cluster itself."

this is the problem, I don't see that link show us how to create SSL on that windows server, probably enable the CA first then generate one.. ..

any idea?

Still, no certificate requirement for Windows Cluster

To create a new cluster or to add nodes to the cluster, a local account needs to be provisioned on all nodes of the cluster (as well as the node from which the operation is invoked) with the following requirements:

*Create a local ‘User’ account on each node in the cluster
*The username and password of the account must be the same on all nodes
*The account is a member of the local ‘Administrators’ group on each node
*When using a non-builtin local administrator account to create the cluster, set the LocalAccountTokenFilterPolicy registry policy to 1, on all the nodes of the cluster. Builtin administrator accounts include the ‘Administrator’ account. 
	You can set the LocalAccountTokenFilterPolicy registry policy as follows:
	On each node of the cluster launch a Microsoft PowerShell shell as an administrator and type:
	new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
	
*The Failover Cluster needs to be created as an Active Directory-Detached Cluster without any associated computer objects. Therefore, the cluster needs to have a Cluster Network Name (also known as administrative access point) of type DNS.
*Primary DNS Suffix Requirements
*Each cluster node needs to have a primary DNS suffix.
	For Multi-domain Clusters: The DNS suffix for all the domains in the cluster, should be present on all cluster nodes…

Select allOpen in new window

but you have said that:


"Indeed, you have to have certificate which is trusted on both sides.

so for workgroup SQL nodes I need to :

1) Create a local ‘User’ account on each node in the cluster, The username and password of the account must be the same on all nodes

2) add the account as a member of the local ‘Administrators’ group on each node.

3) set the LocalAccountTokenFilterPolicy registry policy to 1, on all the nodes of the cluster by  launch a Microsoft PowerShell shell as an administrator and type:

      new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1


3 steps ?

but this command don't tell how can that nodes knows the account I created is for cluster configuration, right ?

"Therefore, the cluster needs to have a Cluster Network Name (also known as administrative access point) of type DNS.
*Primary DNS Suffix Requirements
*Each cluster node needs to have a primary DNS suffix.
      For Multi-domain Clusters: The DNS suffix for all the domains in the cluster, should be present on all cluster nodes…

"

this seems only for nodes joined the domain so it has the DNS suffix ?

once I done:

3) set the LocalAccountTokenFilterPolicy registry policy to 1, on all the nodes of the cluster by  launch a Microsoft PowerShell shell as an administrator and type:

      new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1

Select allOpen in new window

when running cluster vaildation wizard of local node I STILL see this:


any idea ?

now I tried to go ahead and ignore the error message above, I even create a DNS surfix myself. the cluster can  be create from one nodes and can be seen with that node's failover cluster manager.

but I can't connect to cluster from failover cluster manager of other nodes, it said "Cluster xxxx not found!"

any idea? or still need to rejoin domain anyway ?

but from other nodes verify cluster wizard, all 3 x nodes can be shown once I type in one of the nodes name and it will find out the rest.

this is the error from cluster verify wizard:

 * All the servers are not joined to a domain. Ensure that the nodes have a consistent configuration. Multi-domain and Workgroup clusters (clusters with non-domain joined nodes) introduce higher risk of configuration drift. When deploying ensure that the same set of Windows patches are applied to all nodes in the cluster. Also, if group policies are rolled out to the cluster nodes, they should not be conflicting. Finally, ensure that the cluster node and network names are replicated to the DNS servers authoritative for the cluster nodes.
 * Node SWVD03DSQLPoC is reachable from Node SWVD02DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * Node SWVD04DSQLPoC is reachable from Node SWVD02DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * Node SWVD02DSQLPoC is reachable from Node SWVD03DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * Node SWVD04DSQLPoC is reachable from Node SWVD03DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * Node SWVD02DSQLPoC is reachable from Node SWVD04DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * Node SWVD03DSQLPoC is reachable from Node SWVD04DSQLPoC by only one pair of network interfaces. It is possible that this network path is a single point of failure for communication within the cluster. Please verify that this single path is highly available, or consider adding additional networks to the cluster.
 * The cluster is not configured with a quorum witness. As a best practice, configure a quorum witness to help achieve the highest availability of the cluster.
 * This resource is marked with a state of 'Failed' instead of 'Online'.  This failed state indicates that the resource had a problem either coming online or had a failure while it was online.  The event logs and cluster logs may have information that is helpful in identifying the cause of the failure.
 * The cluster validation test has detected that all nodes do not have the same software updates. We recommend that all nodes run the same version of the operating system, and install the same software updates. If you have verified that all nodes are consistent, you can ignore this warning.
 * Unable to determine whether the Windows Firewall on node SWVD02DSQLPoC is configured to allow cluster network communication.  A query to the Windows Firewall service on node SWVD02DSQLPoC failed.

The dependency service or group failed to start. (Exception from HRESULT: 0x8007042C)

 * Unable to determine whether the Windows Firewall on node SWVD03DSQLPoC is configured to allow cluster network communication.  A query to the Windows Firewall service on node SWVD03DSQLPoC failed.

The dependency service or group failed to start. (Exception from HRESULT: 0x8007042C)

 * Unable to determine whether the Windows Firewall on node SWVD04DSQLPoC is configured to allow cluster network communication.  A query to the Windows Firewall service on node SWVD04DSQLPoC failed.

The dependency service or group failed to start. (Exception from HRESULT: 0x8007042C)

Select allOpen in new window

hi all,

when I created a AOG group, I saw this:

Hi,

First of all you should perform installation of a cluster from that account created on both boxes which should belong to local administrators group. Since that account is equal (username and password) and is priviledged you'll be able to verify settings on both boxes.

Case with certificates still applies, but it is required after you have Windows Cluster and SQL Servers installed. Then, you create master key on each instance and then certificates which should be exchanged and installed on both SQL Servers (trusted). We'll get into this later.

"First of all you should perform installation of a cluster from that account created on both boxes which should belong to local administrators group. "

so this mean I have to use SQL configuration manager to change to that account I created for cluster to run SQL server ?

"Case with certificates still applies,"

enable windows server CA for it?

Nope, let's start from the beggining. Before you have WindowsCluster setup you should met prerequisites. One of them is to create user with same password on both boxes and add him to Administrators group on both machines.
After that you can start setting up your cluster but you need to log as that user just created.
Please don't enable any additional roles. We'll get back to SQL later, for now we need to make sure you have Windows Cluster setup correctly.

"Nope, let's start from the beggining. Before you have WindowsCluster setup you should met prerequisites. One of them is to create user with same password on both boxes and add him to Administrators group on both machines."

this one done.

"sure you have Windows Cluster setup correctly."
should be, but I log as local administrator to create that.

I'm unsure about your config now because you've reported some issues. However, if you want to go further please check what's your authentication mode on both SQL Servers.

SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly')   
WHEN 1 THEN 'Windows Authentication'   
WHEN 0 THEN 'Windows and SQL Server Authentication'   
END as [Authentication Mode];
GO 
--or
EXEC master.sys.xp_loginconfig 'login mode' ;
GO

Select allOpen in new window

Next check SQL Server errorlog for logon errors

EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login';

Select allOpen in new window

Or with SSMS by clicking Management->SQL Server Logs->Current (look for logon in column Source).

Windows and SQL Server Authentication

mixed mode.


"EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login';"

Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

Oh, they require unicode. Without any super scripts:

EXEC xp_ReadErrorLog 0, 1, N'Failed',N'Login';
GO
EXEC xp_ReadErrorLog 0, 1, N'18456';
GO

Select allOpen in new window

Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.  [CLIENT: fe80::9074:f5d5:67bb:a1b4%4]

all seems joining domain solve all problem..

Not exactly, you missed SQL Server certificates config part of AGs. Following is a guide.

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

Select allOpen in new window

Should be similar to:

--server1
USE yourDBname
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pUtY0uRP@$$w0rdHeRe';
GO
CREATE CERTIFICATE FirstServerCert WITH SUBJECT = 'FirstServer Certificate';
GO
BACKUP CERTIFICATE FirstServerCert TO FILE = 'C:\Temp\FirstServerCert.cer';
GO
CREATE LOGIN SecondServerLogin WITH PASSWORD = 'pUtY0uRP@$$w0rdHeRe';
GO
CREATE USER SecondServerUser FOR LOGIN SecondServerLogin;
GO
CREATE CERTIFICATE [SecondServerCert] AUTHORIZATION SecondServerUser FROM FILE = 'C:\Temp\SecondServerCert.cer'
GO
CREATE ENDPOINT AG_WORKGROUP = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SecondServerCert, ROLE = ALL );
GO
GRANT CONNECT ON ENDPOINT::AG_WORKGROUP TO 'SecondServerUser';
GO

--server2
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pUtY0uRP@$$w0rdHeRe';
GO
CREATE CERTIFICATE SecondServerCert WITH SUBJECT = 'SecondServer Certificate';
GO
BACKUP CERTIFICATE SecondServerCert TO FILE = 'C:\Temp\SecondServerCert.cer';
GO
CREATE LOGIN FirstServerLogin WITH PASSWORD = 'pUtY0uRP@$$w0rdHeRe';
GO
CREATE USER FirstServerUser FOR LOGIN FirstServerLogin;
GO
CREATE CERTIFICATE [FirstServerCert] AUTHORIZATION FirstServerUser FROM FILE = 'C:\Temp\FirstServerCert.cer'
GO
CREATE ENDPOINT AG_WORKGROUP = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE FirstServerCert, ROLE = ALL );
GO
GRANT CONNECT ON ENDPOINT::AG_WORKGROUP TO 'FirstServerUser';
GO

Select allOpen in new window

After you configure users and certificates you create AvailabilityGroups.

hi,

from that link:

"always required that the servers participating in a WSFC, also known as nodes, are joined to the same domain."

so if we don't create that certification, we need to join domain to make it simplier, right?

from that link:

"always required that the servers participating in a WSFC, also known as nodes, are joined to the same domain."

Yupp for Windows Server 2012 but your question's topic is Windows 2016 data center edition and SQL 2016 AOG.

You can join your servers into domain. I don't find this setup complex, however it may seem easier to create cluster on domain joined servers if you have all permissions and services under your control ;)

Better guide, by Klaus Aschenbrenner.

http://www.sqlpassion.at/archive/2016/01/11/how-to-create-a-sql-server-availability-group-without-an-active-directory-domain/

Select allOpen in new window

tks. let me check and might be back later. give you all score first.

tks all.

when trying the certification part from http://www.sqlpassion.at/archive/2016/01/11/how-to-create-a-sql-server-availability-group-without-an-active-directory-domain/

-- ====================================
-- Execute the following code on NODE1
-- ====================================
 
USE master
GO
 
-- Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO
 
-- Create a new certificate
CREATE CERTIFICATE SQLBAG_Certificate_Node1_Private
WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 1',
START_DATE = '20160101'
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private
TO FILE = 'c:\SQLBAG_Certificate_Node1_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLBAG_Endpoint
STATE = STARTED
AS TCP
(
	LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
	AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node1_Private,
	ROLE = ALL, 
	ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Select allOpen in new window

it said:

Msg 15578, Level 16, State 1, Line 9
There is already a master key in the database. Please drop it before performing this statement.
Msg 15232, Level 16, State 1, Line 13
A certificate with name 'SQLBAG_Certificate_Node1_Private' already exists or this certificate already has been added to the database.
Msg 15240, Level 16, State 2, Line 19
Cannot write into file 'c:\SQLBAG_Certificate_Node1_Public.cert'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.
Msg 28055, Level 15, State 5, Line 24
The certificate 'SQLBAG_Certificate_Node1_Private' is not valid for endpoint authentication. The certificate must have a private key encrypted with the database master key and current UTC date has to be between the certificate start date and the certificate expiration date.

Select allOpen in new window

I am not sure why and today I already follow what is said in the link to create entry in the host name and the cluster verification report is the same, can't find other node but can ping it. I do the same thing as the writer, disable all firewall.

all ping working well using name this time instead of IP only. what is also good is from failover cluster manager, the cluster can be connect quite good using name instead of IP from all nodes.

and also from the SSMS console of node 1 , 2 and 3,  I don't think the AOG is working well:

Hi,

Remove START_DATE = '20160101' attribute from certificate, backup certificate to the path your SQL Server service account has access. Create Temp folder on C:, grant NT SERVICE\MSSLQSERVER account to it with FullControl, backup certificate. At last, create endpoint.
I'd sugget to remove current configuration of AlwaysOn AG's and recreate it from the scratch.

"I'd sugget to remove current configuration of AlwaysOn AG's and recreate it from the scratch."

what is it for ? is the icon in my screenshot looks normal ? this is a very important steps here as this is one of the checkpoint.


"START_DATE = '20160101'"

can just remark ?

after this it show:

Msg 15578, Level 16, State 1, Line 9
There is already a master key in the database. Please drop it before performing this statement.
Msg 15232, Level 16, State 1, Line 13
A certificate with name 'SQLBAG_Certificate_Node1_Private' already exists or this certificate already has been added to the database.
Msg 28055, Level 15, State 5, Line 24
The certificate 'SQLBAG_Certificate_Node1_Private' is not valid for endpoint authentication. The certificate must have a private key encrypted with the database master key and current UTC date has to be between the certificate start date and the certificate expiration date.

Select allOpen in new window

how to remove the certification from SSMS UI ?

DROP CERTIFICATE SQLBAG_Certificate_Node1_Private;
GO

-- Create a new certificate
CREATE CERTIFICATE SQLBAG_Certificate_Node1_Private
WITH SUBJECT = 'SQLBAG_Certificate_Private - Node 1';
GO
 
-- Backup the public key of the certificate to the filesystem
BACKUP CERTIFICATE SQLBAG_Certificate_Node1_Private
TO FILE = 'c:\Temp\SQLBAG_Certificate_Node1_Public.cert'
GO
 
-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLBAG_Endpoint
STATE = STARTED
AS TCP
(
	LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
	AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node1_Private,
	ROLE = ALL, 
	ENCRYPTION = REQUIRED ALGORITHM AES
)

Select allOpen in new window

"DROP CERTIFICATE SQLBAG_Certificate_Node1_Private;
GO
"
this means dropping certification can't do via UI.

when I run this part:

-- Create an endpoint for the Availability Group
CREATE ENDPOINT SQLBAG_Endpoint
STATE = STARTED
AS TCP
(
	LISTENER_PORT = 5022
)
FOR DATABASE_MIRRORING
(
	AUTHENTICATION = CERTIFICATE SQLBAG_Certificate_Node1_Private,
	ROLE = ALL, 
	ENCRYPTION = REQUIRED ALGORITHM AES
)

Select allOpen in new window

it said :

Msg 7874, Level 16, State 1, Line 15
An endpoint already exists with the bindings specified.  Only one endpoint supported for a specific binding.  Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
Msg 7807, Level 16, State 1, Line 15
An error ('0x800700b7') occurred while attempting to register the endpoint 'SQLBAG_Endpoint'.

I have one endpoint already:


what this supposed to mean ? can have more than one endpoint for AOG and I have to drop the existing one ?

I drop the exist endpoint and recreate using the example, no error any more.

so I go to this part:

-- ====================================
-- Execute the following code on NODE1
-- ====================================

-- Create login for the other node
CREATE LOGIN Node2Login WITH PASSWORD = 'passw0rd1!'
GO

-- Create user for the login
CREATE USER Node2User FOR LOGIN Node2Login
GO

-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_Node2_Public
AUTHORIZATION Node2User
FROM FILE = 'c:\temp\SQLBAG_Certificate_Node2_Public.cert'
GO

-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node2Login
GO

-- ====================================
-- Execute the following code on NODE2
-- ====================================

-- Create login for the other node
CREATE LOGIN Node1Login WITH PASSWORD = 'passw0rd1!'
GO

-- Create user for the login
CREATE USER Node1User FOR LOGIN Node1Login
GO

-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_Node1_Public
AUTHORIZATION Node1User
FROM FILE = 'c:\temp\SQLBAG_Certificate_Node1_Public.cert'
GO

-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node1Login
GO

Select allOpen in new window

ok problems comes, I have 3x nodes, am I going to import 2 x cert. to another nodes? like certification from node2 and nodes 3 to node 1?

just by adding one more line and create one more user to connect to node 3? and I do it on both node 1, 2 and 3:

node 1:

-- ====================================
-- Execute the following code on NODE1
-- ====================================
 
-- Create login for the other node
CREATE LOGIN Node2Login WITH PASSWORD = 'P@ssw0rd'
GO
 
-- Create user for the login
CREATE USER Node2User FOR LOGIN Node2Login
GO
 

				 -- Create login for the other node
				CREATE LOGIN Node3Login WITH PASSWORD = 'P@ssw0rd'
				GO
 
				-- Create user for the login
				CREATE USER Node3User FOR LOGIN Node3Login
				GO


-- Import the public key portion of the certificate from the other node
CREATE CERTIFICATE SQLBAG_Certificate_Node2_Public
AUTHORIZATION Node2User
FROM FILE = 'E:\SQLtemp\SQLBAG_Certificate_Node2_Public.cert'
GO
 
-- Grant the CONNECT permission to the login
GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node2Login
GO
 

		 CREATE CERTIFICATE SQLBAG_Certificate_Node3_Public
		AUTHORIZATION Node2User
		FROM FILE = 'E:\SQLtemp\SQLBAG_Certificate_Node3_Public.cert'
		GO
 
		-- Grant the CONNECT permission to the login
		GRANT CONNECT ON ENDPOINT::SQLBAG_Endpoint TO Node3Login
		GO

Select allOpen in new window

and I do it for both node 2 and 3 ! and finally the AOG icon seems changed without recreating AOG:

node 2:


anything else still missing? any more way to verify it ? e.g. other than using SSMS icon like what I shown above?

Correct, you need to exchange certificates and create logins between nodes, so each node can successfully connect to others.

Regards,
Daniel

also  a lot of yellow triangle in the properites page:


it seems saying async mode can make data lost but it seems it is hard to change it back to sync mode, am I right?


so anything  you usually used to test if AOG function well ?

I didn't configure my listener at all, but AOG can be create, when what situtation MUST we create listener? or the yellow triangle in the exmaple above is just because I don't have listener configure ?

Hi,

Listener is just a virtual name always pointing to primary replica so your application will always connect to writeable database(s).

For reference:

http://bidn.com/Blogs/what-is-the-alwayson-listener
https://www.mssqltips.com/sqlservertip/4597/configure-sql-server-alwayson-availability-group-on-a-multisubnet-cluster/

Select allOpen in new window

yeah, so is that mean we don't absolutely need this but we need to manual recofigure the connection in application ourselves?

That's correct, it is because without listener you don't have one common name for you AG, you always point to either replica.

Regards,
Daniel

" it is because without listener you don't have one common name for you AG, you always point to either replica.."


need to configure MANUALLY in net. php web tier to shows which one is primary and secondary or read only you mean ? and listener just manage this for us automatically like read only access to second replica ?


my concern is, if I need to consider listener I need an addition IP address.

Yes, you need additional IP Address and Name. This IP is considered virtual because you don't attach it to any adapter by your own.
When configured AG listener provides single point of access to your AG. You need to put it's name/address into the connection string.

server=AlwaysOnAG;Database=WindeWorldImporters;trusted_connection=yes)

Select allOpen in new window

I am planning to recreating the AOG again as the Dashboard shown yellow icon, please suggest what is that mean if you have any idea.

I just select data from both secondary replica, data from primary are there. so replication is good. so what is the yellow icon for ?

Hi,

The fastest method is to click the link Warning(1) and review message.

Regards,
Daniel

This is one of them:

At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.

Select allOpen in new window

the change of sync of data replication will works from async to sync ? it seems a yes but AOG wizard give primary Async and secondary to sync, and it gives this !  I changed primary to sync.

You need to restore full database backup plus at least one t-log backup on the replica(s) to initiate log replying. SQL Server errorlog is good place to start looking. Therefore review logs on primary and replicas to search for errors.

as I said, It seems once i change the primary from Async to Sync, warning gone ! so I think it is just a warning but not an error. :):)

I tried the read only operation on second replica, anything else you test on AOG to make sure it is working well ?

failover and .. ?