x

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

Slick84 gravatar image

Slick84
1.3k 75 102 142

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

2 answers: sort voted first

According to this [Database Mirroring Best Practices and Performance Considerations][1] 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.

[1]: http://msdn.microsoft.com/en-us/library/cc917681.aspx
more ▼

answered Aug 18, 2011 at 12:27 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

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

Slick84 gravatar image

Slick84
1.3k 75 102 142

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1951
x58

asked: Aug 17, 2011 at 01:00 PM

Seen: 2300 times

Last Updated: Aug 18, 2011 at 12:18 AM