0

We have an always-on setup...we are doing some reprocessing of data and as part of that activity, we will be suspending the data movement between primary and secondary before making the changes on primary and if the changes looks ok, we will just resume the data movement but if changes does not looks ok, as a rollback plan, we will just failover SQL Server to secondary without resuming so that changes gets reverted....I am worried about rollback plan...is there any caveats or problem with this approach??? I know that log file with keep on growing on primary until data movement is resumed but is there anything else I need to be worried about? and also I should be suspending the data movement on primary right? thanks in advance

1
  • 2
    I've never tried to do this as a rollback technique. Any time you fail over to a replica that is out-of-sync, this will completely break data movement between replicas, and you will need to re-initialize the former primary from backup.
    – AMtwo
    Aug 27 '21 at 14:29
2

[...] we will be suspending the data movement between primary and secondary before making the changes on primary [...]

That's a terrible idea. Outside of just the log that you've mentioned, you're essentially setting the secondary with a log send queue out of the gate, blowing out the log pool cache, and purposely setting yourself up for a severely long reverting phase. You're better off doing any number of other things instead.

I am worried about rollback plan [...]

That makes two of us. You're better off testing the changes in a test environment or using transactions where they are meant to be used. If the rate of change is that large and encompassing, take a backup and restore from that, setup a log shipping secondary, something other than suspending data.

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.