0

I am running into a strange situation that I have not been able to resolve.  I have a 2 node Basic AG running sql server 2016 standard.  I am trying to add the reportserver DB to an AG, but it keeps failing on joining the DB to the secondary node.

I have done the below:

  1. fully recovered the DB on node1 (primary), made sure its in Full recovery mode
  2. took a fresh backup, AND log backup immediately after it
  3. restored both the full and log backup to Node2 (secondary) leaving it in restoring state.

Back on primary I created an AG via the GUI, but when I get to the end it sits on joining the DB to the AG in node2. I get a few different errors, which essentially mean the same thing:

 "the remote copy of the db "reportserver" has not been rolled forward to a point in time that is encompassed in the local copy"

"the mirror db "reportserver" has insufficient transaction log data to preserve the log backup chain."

I know these mean that should be additional log backups to restore, but I had just prior to this taken the full AND log backup and restored both of them to the secondary, so there should be no other backups to restore...  The only thing I can think of is that something is still writing to the log on the primary, but as far as I am aware, even if that is the case, joining the DB to the AG should still work as the currently un-backed up portion of the log should be streamed to the secondary replica during the sync process.  I skipped the listener configuration for the time being. I also stopped the SSRS service on both nodes prior to doing this.

Has anyone encountered this before and had success fixing it?  Is it because it is the reportserverDB, and it can't be joined to the same AG as reportservertempDB?

I have tried automatic seeding, and join only mode, and the strange thing is that automatic seeding gives me NO errors during the AG setup, but the DB does not ever show up on Node2. When i do join only, that is when I get the errors above.  I'm pretty much baffled by this.  I have 6 other DB's on these nodes that are each in their own basic AG, and I have not run into any issues with them.

0

Found the answer, but still don't understand WHY it works. if anyone can share any insights it would be appreciated.

Issue with adding a database to an availability group

So, using the answer from the post above did NOT work when doing the join only mode. I was able to get it to work by doing the below:

  1. fully recovery the DB on node1. do NOTHING on node2 with the DB so I can use automatic seeding.
  2. Go through automatic seeding, and see that the DB did NOT show up on node2
  3. Run the below queries on node2. they failed for me for error 41106

    ALTER AVAILABILITY GROUP [agname] JOIN; GO ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE; GO

  4. Remove the DB from the AG on Node1, then add it back in with the GUI.

After this the DB showed up on node2 and is in the AG properly. I would love to understand WHY this works if anyone has info.

  • I have seen this on 2016 also when adding a DB to an AG that has three nodes, it would work for one secondary but not the other. I went through the wizard to the end and then got the script, and then ran each part of the script separately and it worked. There appears to be a timing issue when the commands are run too fast. – Tony Hinkle Jun 1 at 2:25
  • Sounds like you had an issue with automatic seeding and should troubleshoot it from there. – Sean Gallardy Jun 1 at 14:15

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.