Database Mirroring WITNESS Question

Greeting all,

I have a HA auto failover SQL 2005 mirroring setup in my production environment, and I need to move the witness instance from Server A to Server B.

Is it as simple as changing the witness address location in the mirroring section on the principal server?

This is a production DB so I want to make sure that changing the witness doesn't take the database offline.

Any links, tips, personal experiences would be greatly appreciated

more ▼

asked Aug 17, 2011 at 01:00 PM in Default

avatar image

1.3k 75 104 147

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

According to this Database Mirroring Best Practices and Performance Considerations article mirroring continues to work even when the witness fails (or as in your scenario it is taken offline and is replaced by another witness server). The same would be true if you SET WITNESS OFF.

But I would test this in a non prod environment, alternatively get someone to hold your hand during the switch if you're not 100% certain...

Loss of the witness If the witness server fails, database mirroring continues functioning without interruption, except that automatic failover is not possible. Once the witness becomes operational, it automatically joins the database mirroring session.

more ▼

answered Aug 18, 2011 at 12:27 AM

avatar image

Magnus Ahlkvist
22k 20 41 42

Correct +1.. In Mirroring you need at least 2 server to descide if there is a failure... Witness is needed for the "quorum vote"

Aug 18, 2011 at 08:36 AM sp_lock

So I have set the witness off and currently we are operating without a witness so no automatic failover basically. However, this is an exact replica of the production environment except virtual and no transactions/data coming in, etc.

Aug 18, 2011 at 09:03 AM Slick84

Guys, I have created an ENDPOINT with the same name the Principal and Mirror in the database mirroring session and role = witness.

When I try to alter in principal to point to the new witness, it fails telling me the ALTER DB CMD could not be sent to the remote server instance. The DB mirroring configuration was not changed. Verify the server is connected and try again.

I can ping the server, I can connect to it using SSMS from principal. What more should I try? What am I missing here?

Aug 18, 2011 at 10:58 AM Slick84
(comments are locked)
10|1200 characters needed characters left

The problem in my situation was that I did not know that all, the witness, the principal and the mirror's SQL Service account at the Windows Services level should be running under the same domain account. All three need to be under the same domain. Once that is done, the Witness server can be easily changed.

Also, mirroring is obviously possible even in different domains and with different names. You have to have the logins defined in each SQL and have the CONNECT privelages granted properly before you do this.

I have marked this solution as a community wiki incase someone needs to add/modify some of what I said.

more ▼

answered Aug 23, 2011 at 11:52 AM

avatar image

1.3k 75 104 147

(comments are locked)
10|1200 characters needed characters left

Hi, Could you make me clean about resolve this trouble

more ▼

answered Jan 13 at 10:01 AM

avatar image


Hi. You might be better off asking a fresh question - this is five years old, and so might not be visible to many users who only look at the first page of most recent topics...

Jan 13 at 10:03 AM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 17, 2011 at 01:00 PM

Seen: 2984 times

Last Updated: Jan 13 at 10:03 AM

Copyright 2017 Redgate Software. Privacy Policy