I have a configuration where one database is log shipping to three different servers hosted in a disaster recovery site. These three disaster recovery servers are joined to the same AlwaysOn availability group (AG).
In the event of failover, we recover the database on the server acting as the primary replica of the AG and then add the database to the AG using the 'Join only' synchronization option. Since the databases on the secondary replicas are already in a non-recovered state, the operation succeeds and we end up with a database synchronized across the AG. This is 100% great.
New problem: Our monitoring software does not like it when databases are not in a readable state. So while we are in our primary site and are log shipping to our NORECOVERY secondaries at the disaster recovery site, our monitoring software opens high-priority tickets because it thinks the secondary databases are down (because it can't read them).
Making the secondaries readable by switching them from NORECOVERY to STANDBY solves this issue, but creates a new one. When we failover to the disaster recovery site and try to add the database to the AG (as outlined above), it fails because the databases on the secondary replicas need to be in NORECOVERY in order to successfully join the AG.
If we switch these databases from STANDBY to NORECOVERY before attempting to add the database to the AG, we receive a message saying the databases on the secondary replicas are not restored far enough in order to be joined to the AG and the join fails. If at this point, we take a transaction log backup of the database on the primary replica and apply it to the secondaries with NORECOVERY, we can re-initiate the join procedure successfully.
It would seem that changing the secondaries from STANDBY to NORECOVERY is causing the engine to determine the databases are no longer in sync but I can't for the life of me figure out why. Anyone have any ideas? The only thing I can think of is that the act of recovering the primary database itself was enough to bring them out of sync, but if this were true, shouldn't it also be the case when we simply leave the secondaries in NORECOVERY to begin with (like our original plan)?