November 25, 2019 at 7:38 pm
I have a stand alone SQL 2008 R2 SQL Server that I'm migrating to a 3 node AG. At various times during the testing of the restore process to get the databases onto the new SQL Server 2017 servers that are on CU16 I run into a scenario where one or more databases will join the AG but they don't synchronize. Here is my process.
I drop all the databases from the AG. I then run a PowerShell script to create restore scripts for each database. I restored with recovery to primary and with no recovery on the two secondaries. Two databases failed to join and synchronize when using the Join Only option from the wizard. One is compat level 100 and there other is 90.
I then removed them from the AGs and tried again. This time I used automatic seeding. I’m now getting behavior where the databases aren’t actually present on the secondaries. They join to the AG but the databases aren’t present on the SQL instances and so they don’t synchronize. Same errors I saw in the SQL error log when I tired this with a different set of SQL Server 2017 AG nodes that will also be part of this process.
"Error 3906 Sev 16 state 2
Failed to update database 'MyDB' because the DB is read only.
Error 3313 Sev 21 State 3
During redo of a logged operation in databases 'MyDB' an error occurred in log record ID(SomeID Number).
Data movement suspended for the following reason 'suspend_from_redo. "
It then suggests I resume the data movement manually, but this also fails.
November 26, 2019 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 26, 2019 at 8:58 pm
When using the join only option - most likely the reason it isn't synchronizing is because a log backup was performed on the primary node prior to the restore on the secondary node. Once a log backup has been performed on the primary - you have to restore that log backup to the secondary to get both databases in the same state.
When I do this - I disable the transaction log backups on the primary until the restores have completed on both the primary and the secondary, that way both databases are at the same point in time once the restores have completed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply