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.



  • I see this got two down votes, which is really annoying. I've put my vote in to bring the score back up. Questions like this are good because they describe a detailed upgrade process for a complicated system (AG) and provide others with insights into what an upgrade process might look like and the kinds of issues one might face. In this case, there are two clear questions at the end too - asking for confirmation if understanding of the process is correct. I see no reason for the down votes. – youcantryreachingme Oct 14 '19 at 21:51
  • Following that up, the official docs are fairly unhelpful (here docs.microsoft.com/en-us/sql/database-engine/…) – youcantryreachingme Oct 14 '19 at 22:04

OK, it looks like there wasn't actually a problem - behaviour was 'normal' when adding SQL Server 2017 nodes to an existing 2012 Availability Group and warnings about the new secondaries not being joined to the Availability Group were red herrings.

So what would appear to be going on is as follows:

  1. Having added the new 2017 replicas warnings suggested that they hadn't been joined to the Availability Group. However a quick examination of synchronisation showed that data was indeed syncing to the new nodes.

  2. After failing over to one of the new 2017 replicas the other one went nice and green whilst the other one remainined in an apparent warning state until a failover to the node got rid of the last of the warnings.

  3. Fail back to either of the old nodes was possible only if loss of data was a viable option since synchronisation would no longer be working between the newer nodes and the older, which makews sense. However the plan was to remove those nodes anyway unless something catastrophic had happened after failing over to the new nodes.

  • Glad you got it working. One thing in the question confused me - you said "This secondary replica [on the 2017 server] is not joined to the availability group". If that was true, then when you "even tried failing over to one of the 2017 nodes (NODE3) just to see" and "Naturally the AG fell over" I don't understand how the AG can fall over if you give it an invalid instruction to attempt to fail over to a node that isn't part of the AG. But as you said, it was in fact syncing and the message seems to be a red herring. I wonder why that message was given. – youcantryreachingme Oct 14 '19 at 21:53

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.