Sql-server – SQL Server : Restoring database mirroring once its turned off

mirroringsql-server-2012

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?

Best Answer

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.

Related Question