0

I was running security updates on the sql server mirror setup and I encountered split brain scenario as mentioned here

Should I pause Mirroring and stop SQL Server services before running windows updates?

So to fix this issue I turned off database mirroring.

Now I am trying to find a way to restore database mirroring.

  1. Would simply turning on database mirroring solve the issue? , Would the transaction logs automatically update?

  2. Or Should I take a full back and transaction log and restore the database. I would prefer option 1 if that is possible , because in option I have to setup the logins again .

  3. what's the best practice to resolve a split brain issue?

Incase you are already in a situation where db's on principal and mirror are in recovery mode. (This could occur due to many reasons like certificate expired network failures) etc, what's the best way to resolve such a situation, do you need pause mirroring and fix the issue?

  • You have to turn OFF the witness as described in my answer. When you say I turned off database mirroring - what steps you exactly performed ? – Kin Shah Apr 21 '15 at 18:56
  • Thanks for the quick reply, I saw this article only after encountering this situation. Since the apps were getting affected I did ALTER DATABASE dbname SET PARTNER OFF. I had to do this as both dbs were in restoring mode – mgeorge Apr 21 '15 at 19:05
  • 1
    So maybe, next time, do a little more reading before running the first command you find in a search. :-) – Aaron Bertrand Apr 21 '15 at 19:32
  • made edits to the third point – mgeorge Apr 22 '15 at 16:28
3

ALTER DATABASE dbname SET PARTNER OFF will break mirroring. See this answer. You will have to reconfigure mirroring again. So option 2 is your only option at this moment.

The answer that I linked has an automated script that will help you.

Would simply turning on database mirroring solve the issue? , Would the transaction logs automatically update?

No, since you issued SET PARTNER OFF, mirroring is broken. Now you have to initialize using a full backup and latest transaction log backup from the primary server to the mirror server.

what's the best practice to resolve a split brain issue?

Next time, first turn off (remove) the witness and then do SET PARTNER FAILOVER as I have detailed in my answer.

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.