I'm getting an odd problem in trying to add a replica to an AG. This is part of a POC project to upgrade a 2-node AG running on Windows 2012 and SQL Server 2012 to a new 2-node AG running under Windows 2016 and SQL Server 2017. The plan is therefore to add the two new 2017 replicas, fail over to one of those, upgrade the cluster to Windows 2016 and remove the old 2012 replicas. All went fine until I tried to add the 2017 replicas. I didn't get any errors however the database status on both was Synchronizing / In Recovery (the new nodes were both async initially) and there was a Dashboard warning telling me this:
'This secondary replica is not joined to the availability group. For an availability replica to be successfully joined to the availability group, the join state must be Joined Standalone Instance (1) or Joined Failover Cluster (2).'
So I tried various things like removing and re-adding the replicas, re-starting the SQL Server service. I even tried failing over to one of the 2017 nodes (NODE3) just to see. Naturally the AG fell over and I had to force failover back with data loss. It just after that point that I noticed that one of the new nodes (the one I HADN'T failed over to) now seemed to be working with no warnings in the Dashboard. However the status of the database on this replica was still Synchronizing / In Recovery and attempting to fail over to that node results in the AG failing over again, so its apparent 'green' status seemed to be misleading.
I then noticed that the remaining node (NODE4) was now nice and green in the Dashboard so it looks like the failover to NODE3, oddly, got that 'working' again.
I then changed NODE3 to synchronous mode - 3 of the 4 can be synchronous - and failed over again. This time the failover worked, however NODE1 and NODE2 were left in a 'Not synchronising' state.
I'm assuming two things after all this that: (1) it's because a failover from a 2012 database to a 2017 one can't be failed back to the older version of the database without data loss; and (b) I can only failover to the synchronous 2017 node, not the asynchronous one, although I'm not entirely sure of the reason for that one. I was therefore looking for confirmation of these assumptions or for an alternative explanation.