Freigeben über

How can you drop the witness of a mirroring session when the partners have been dismantled?

  • Artikel

A colleague of mine described the following situation:

Long time ago they setup a database mirroring environment with 3 servers (2 partners + a witness). Then they changed their mind and decided that clustering fit better their HADR necessities. So they completely rebuilt the 2 partners (reinstalled operating system, etc), while forgetting to properly remove the mirroring session before revamping the servers, or at least setting the witness for the database mirroring sessions to none (ALTER DATABASE foo SET WITNESS OFF). So now when they query sys.database_mirroring_witnesses from the witness, it still believes it is part of whatever mirroring sessions they created between those two partners.

So my question is: how can we tell this mirroring witness that he’s no longer part of a mirroring session if the partners are not available?

As we no longer have a valid mirroring session, we can’t issue an “alter database foo set witness off” from the principal.

System Table sys.syslogshippers still contains a row for the mirroring session.

So the question is how can I “reset” my instance to remove this orphaned mirroring witness?

I’ve tried to remove mirroring endpoint: no effect.

Until now, the only solution I’ve found is to:

  • Start witness SQL Server in single user mode.
  • Connect my instance with an admin connection.
  • Delete orphaned entries in sys.syslogshippers.
  • Restart SQL in multi user mode.

Is it the only solution?

The solution he proposed in those four steps is doable and valid, but it is too intrusive and requires bringing the service down twice, plus the time it will be inaccessible while we do this quick operation.

By looking into the source code where Database Mirroring is implemented, I discovered that the only way to manually drop the witness from the witness was by executing ALTER DATABASE foo SET WITNESS OFF while connected to the principal partner. And that wasn’t doable anymore because the two partners were gone since long.

Now, the only reason why the witness would automatically decide to drop its reference as a witness off that session, from the protected system table sys.syslogshippers, whose contents are publicly exposed via sys.database_mirroring_witnesses is if it could again establish a connection with the endpoint of either of the original partners, and would discover that the instance listening on that DBM endpoint doesn’t contain a database with such name or, if it does, it is not one being mirrored.

So, the cleaner solution to this situation is to recreate one of the endpoints through which either partner was listening on, and the instance where you have recreated the endpoint must have no database matching the name of the one being originally mirrored or, if a database with that name exists it must not be participating in a mirroring session. Meeting this conditions triggers the part of the code where the witness deletes the row referencing the obsolete session from the system table exposed by sys.database_mirroring_witnesses.

Comments

  • Anonymous
    April 14, 2014
    Hi,   Very good article. I got into this and thank you for posting this. Can you please explain how to delete Orphaned entries from sys.syslogshippers table? Daniel.