April 25, 2023 at 8:16 pm
I want tp create a scenario when I perform failover from Node1 to Node2, I want the database to get stuck in Reverting/In Recovery state on Node1 for longer time so that I can control it until I am done with testing. I know this can be done as index maintenance or big chunks of DML operations and so on. Any other options?
April 25, 2023 at 9:54 pm
I want tp create a scenario when I perform failover from Node1 to Node2, I want the database to get stuck in Reverting/In Recovery state on Node1 for longer time so that I can control it until I am done with testing. I know this can be done as index maintenance or big chunks of DML operations and so on. Any other options?
How about suspend data movement on Node1, Will that work?
=======================================================================
April 26, 2023 at 6:48 pm
Thanks. It's close but wish something closer that can reproduce in the error logs with "recover of database is completed x%"
April 27, 2023 at 3:40 pm
As I understand it, you want to fail over to a secondary, but you want to prevent the transactions from syncing back to the former primary(now secondary) while work is performed on the now primary?
I am very curious what you are trying to solve or prevent by doing this.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 27, 2023 at 9:14 pm
Would like to test fastest way of recovery rather than waiting for the SQL Server to recover on it's own.
April 27, 2023 at 9:22 pm
I’m even more confused.
Are you attempting to do a DR test?
Are these physical or virtual machines? If they are virtual, fail it over, shut off the node 1 server, and do your test.
If you do not do anything to the AG, then when node 1 is powered on the syncing should pick up.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 28, 2023 at 9:24 pm
Would like to test fastest way of recovery rather than waiting for the SQL Server to recover on it's own.
There really isn't any method to recover that is going to be faster than waiting for SQL Server to recover on its own, if the database is already recovering. If the database is hung and is not recovering - then the fastest method is to remove the database and re-add it using whichever method works best (automatic seeding, manual backup/restore and then join, etc.).
If you failover when there is a lot of activity on the system - then it is likely the send or redo queues will get backed up. There is nothing you can do about that except ensure the secondary is up and available and the databases are synchronized or synchronizing. If you are performing some type of maintenance (patching - for example), then you can expect there to be a lag as the send queue will get backed up - and then the redo queue will back up once the database is available again.
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
May 1, 2023 at 3:09 pm
When I did the failover to secondary. I had database was recovering on former primary, but it was very slow taking longer. If that is the case then you would leave it to recover on its own or rather than remove the database and re add would be faster. However, while recovering if I remove the database would that cause any additional issues?
May 1, 2023 at 3:32 pm
When I did the failover to secondary. I had database was recovering on former primary, but it was very slow taking longer. If that is the case then you would leave it to recover on its own or rather than remove the database and re add would be faster. However, while recovering if I remove the database would that cause any additional issues?
Assuming that the new primary is fine, and has failed over properly, removing it while it is in recovery should not cause any issues.
Has this recovered? How long did it take?
I think the first thing you need to do is determine why the recovery took so long, or why it failed. Correcting that will save you some aggravation when you need to fail over again.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 1, 2023 at 5:09 pm
When I did the failover to secondary. I had database was recovering on former primary, but it was very slow taking longer. If that is the case then you would leave it to recover on its own or rather than remove the database and re add would be faster. However, while recovering if I remove the database would that cause any additional issues?
Yes - if you remove the database then a backup/restore operation will need to be performed. Regardless of how that is implemented (automatic seeding or manual) that will take longer than a normal recovery generally takes.
If the database is taking too long to recover - you need to figure out why that is happening instead of working around the issue by dropping the database and re-adding it to the group.
If this is a one-time issue then removing/adding the database could be the better option.
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 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply