May 31, 2019 at 1:06 pm
Hello,
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 secndary 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.
4.) back on primary added it to the 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. 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 even 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.
May 31, 2019 at 1:47 pm
Found the answer, but still don't understand WHY it works. if anyone can share any insights it would be appreciated.
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:
fully recovery the DB on node1. do NOTHING on node2 with the DB so I can use automatic seeding.
Go through automatic seeding, and see that the DB did NOT show up on node2
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
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply