Friday, November 11, 2011

SQL 2008 R2 Mirroring. Add a Witness After the Fact

Just a quick note:
We ran into a situation with a database already running mirroring without witness/failover to which we wanted to add a witness.  To add a witness server after the fact, we ran the mirroring wizard which appeared to be successful.  Failover, however, didn’t work, and the log on the secondary was full of permissions errors.
If you run all SQL services as the same user across your primary/secondary/witness, you won’t see this error…I’m not suggesting that’s a good idea.  In our case all the SQL services run as dedicated domain accts per server, and running the mirroring wizard doesn’t add the witness server account to the secondary if mirroring without witness is already configured.

There are two ways to fix this:

  • The right way:  Figure out how to assign permissions to the mirroring endpoint for the witness server service account on the secondary (I gave up on this because cheating is so much easier, and it’s not something we have to repeat).
  • The Kind of Cheating Way I Fixed It:  Create a dummy DB on the primary and secondary (don’t worry about the real mirroring setup, you just need the permission wizard to run).  On the dummy DB, run the mirroring setup as you would normally for synchronous mirroring with witness.  Drop the dummy DB.  Re-run the mirroring setup to add a witness on the primary database.  Test failover.

No comments:

Post a Comment