4

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)?

  • Have you considered looking into whether you can set an exception for your monitoring software to ignore the "restoring..." Log shipping secondaries? Log shipping is a pretty standard DR strategy, so I'm surprised that your monitoring software doesn't have a better solution. In general, when you start finding work arounds to the problems with your work around, I like to back up start over. – AMtwo Aug 7 '17 at 21:21
  • What if you restore more transaction log backups after switching from STANDBY back to NORECOVERY? The error may just be that the databases have fallen too far behind while sitting in STANDBY. – David Browne - Microsoft Aug 7 '17 at 21:24
  • AMtwo - I wish I could take you to one of our meetings. We started out with a pretty basic script to complete the failover that has grown in size and complexity to support the extra steps (which now include a transaction log backup of the primary and restore to the secondaries). The response from our monitoring team is to disable monitoring, lol. – Mike W Aug 7 '17 at 21:36
  • David Browne - We're currently testing this in a non-live environment where we are the only users, so there shouldn't be any user data being written. Also, we're executing the steps via script that recovers the database on the primary replica and then immediately joins it to the AG. Restoring another tlog after switching to NORECOVERY works, but I'm hoping to understand why. We use the same script (essentially) when the databases remain in NORECOVERY but it only requires additional tlog backups when switching from STANDBY. – Mike W Aug 7 '17 at 21:42
  • Why are you using Log Shipping for this instead of async replicas in an AG? Why isn't the prod server in the AG with the 3 other DR servers? – Tara Kizer Aug 7 '17 at 23:27
1

When restoring WITH STANDBY SQL Server has to undo uncommitted transactions (the relevant data pages get written to the undo file that you specify). The NORECOVERY option does not care about the state of the log, or performing any potential rollbacks, that would happen if the database were recovered. Because the two leave the DB in different states you would have to reapply the final transaction log WITH NORECOVERY in order to be in a good state. There's an excellent write-up on this at https://askmesql.blogspot.com/2011/01/log-shipping-norecovery-vs-standby-mode.html

As for your monitoring software, I would recommend putting in a temporary exclusion for certain databases, or even instances while getting the AG setup so that you do not get those spurious alerts.

  • Thanks Nic! But if I'm switching the database back to NORECOVERY before adding it to the AG, shouldn't it return the database to its prior state after the redo file is applied? My assumption up until now has been that it should be as if it was never put into STANDBY to begin with. In my tests, changing the database from STANDBY to NORECOVERY allowed me to continue applying tlogs from the original database in the primary site, suggesting at least that the lsn chain had not been broken. – Mike W Aug 7 '17 at 22:33
  • Sorry, I should specify. When I am changing from STANDBY to NORECOVERY, I am doing so without actually restoring a new tlog. I'm just running the RESTORE statement without specifying a tlog file. – Mike W Aug 7 '17 at 22:35
  • Run the restore statement specifying the same log as the last one, see if that makes the difference. – Nic Aug 8 '17 at 13:32
  • Just got a chance to give it a try. I was able to re-restore the last log successfully with NORECOVERY, but I received the same error when trying to join the database to the AG: The remote copy of database "xxx" is not recovered far enough to enable database mirroring or to join it to the availability group. You need to apply missing log records to the remote database by restoring the current log backups from the principal/primary database. – Mike W Aug 9 '17 at 20:12
  • The only thing I can think is that you have an extra log backup occur between the state changes. I'll have a dig through my AGs, see if I can repro. – Nic Aug 9 '17 at 20:49
0

Based on your description of the problem, the actual problem is configuring your monitoring software, not the SQL Server replication solution you have in place. Either figure out how to change/fix the monitoring software or throw it away. Never change the functionality of HA/DR for databases to "serve" monitoring software, that by the way sounds ridiculously incompatible with SQL Server.

  • Thanks SQL_Hacker, while I agree that reconfiguring the monitoring software would be the optimal course, that decision is out of my hands. It is in the purview of a completely separate and independent team. Regardless, what I'm really interested is not a business solution but an explanation for the question I posted. Namely, why going from STANDBY to NORECOVERY seemingly breaks the lsn chain (or whatever is making SQL determine the secondaries are missing transactions). – Mike W Aug 9 '17 at 17:43
  • Ah...well, that sucks. I don't know how to answer that question. I think more information is needed...or I am just not understanding the question you're asking. – SQL_Hacker Aug 9 '17 at 18:58

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.