Mirroring In sql server 2005- Witness Full Quorum

Question

Post reply

Mirroring In sql server 2005- Witness Full Quorum

VaibhavChoubey@SQL

Ten Centuries

Points: 1033
More actions
April 21, 2008 at 10:05 pm

#120973

Hi All,
I have established a Synchronous mirroring with witness ( to achieve automatic failover), I am getting all the configuration perfectly set except the value of "mirroring_witness_state" which is showing Connected on Principal and Disconnected on Mirror.
I believe these values should be Connected on both servers, but i am clueless why not.
Due to this when i try to test automatic failover my Mirror DB shows me "Disconnected ,In recovery". because it doesn't have anyone to make quorum with???? Any help highly appreciated.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Andy sql SSCrazy Eights Points: 9481 More actions · Answer 1

Hi there,

If you run:

select * from sys.database_mirroring

on the Principal and the Mirror, check the following values. For a normal, synchronised mirror with Witness, the values should be:

-- mirroring_state = 4 (synchronized)

-- mirroring_role = 1 (principal) or 2 (mirror)

-- mirroring_witness_state = 1 (connected)

That should be enough for you to work out where your problem lies.

Andy

free_mascot One Orange Chip Points: 27169 More actions · Answer 2

Use Database Mirroring Monitor tool to narrow down your problem. Refer the following link for further reference.

http://msdn2.microsoft.com/en-us/library/ms365809.aspx

http://msdn2.microsoft.com/en-us/library/ms365781.aspx

Also best practice:

http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

Good Luck!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

VaibhavChoubey@SQL Ten Centuries Points: 1033 More actions · Answer 3

Hi Everyone thankyou very much for your time.

I finally found out the problem, somehow my mirror server name was missing USERDNSDOMAIN and hence a connection was not getting established between Mirror and Witness. I explicitly added environmental variable %USERDNSDOMAIN% to server name and things worked.

jay-h SSCoach Points: 18816 More actions · Answer 4

I've got a similar problem with a key difference.

I currently have TWO databases mirrored, same primary, same mirror same witness.

One shows fully connected and synchronized in mirroring monitor

The other shows the witness connected to the primary but disconnected from the secondary. both databases synchronized. If I use the

select * from sys.database_mirroring

I get similar results. running it from the primary shows the witness connected for both databases. Running it from the mirror show connected for one, disconnected for the other.

...

-- FORTRAN manual for Xerox Computers --

Colleen Rossman Mr or Mrs. 500 Points: 523 More actions · Answer 5

Hi - I haven't checked whether I need to add the environment variable %USERDNSDOMAIN% to my witness server or not, but wanted to let you know your post was by far the most valuable in all I've seen on mirroring issues.

I've been very frustrated with mirroring in general. When everything stays connected it works flawlessly but rarely do things remain connected and so far THREE support calls to microsoft has returned ZERO on our investment. (each call resulted in links to articles that are available for free to the general public with 'case closed' statuses).

Unfortunately my issue is only happening in production so I can't easily makes changes to my environment.

Colleen

alastair_bravey Grasshopper Points: 13 More actions · Answer 6

I had this problem recently - I haven't tried the %USERDNSDOMAIN% solution however what worked for me was bouncing the server with the mirror&witness on. The mirror/witness connection just sparked into life after that and everything worked ok

Colleen Rossman Mr or Mrs. 500 Points: 523 More actions · Answer 7

Found a valuable tip that I don't see posted anywhere: If you're having trouble establishing connection to the mirror, undoubtedly the error messages will lead you astray. Instead, setup log shipping using the GUI first. Then establish the mirroring session. Then remove log shipping. It works every time.

cdaly-822071 SSC Veteran Points: 211 More actions · Answer 8

I recently had the same thing happen. Although, we have multiple db's on 1 box. The connection status on all other mirrors were fine. 1 db specifically was presenting connection problems from the mirror to the witness. I restarted the service on the witness instance and this resolved the witness connection problem. Hope this helps.

Thanks,

cdaly

michael nee SSC Enthusiast Points: 144 More actions · Answer 9

Restarting the service on the Witness worked perfectly for me, thanks!

tapan.1010 Valued Member Points: 58 More actions · Answer 10

I had similar problem, where one instance shows "Connected" and other instance show "Disconnected" in Database Mirroring Monitor. I have resolved the problem by "Remove Mirroring" and then configuring the same setup again. Hope this may help somebody.

tommi_j Old Hand Points: 371 More actions · Answer 11

Hi Everyone,

Old thread but I have got a similar problem. Edit: Sorry for posting in SQL Server 2005. I will post in correct forum.

I have 2 databases mirrored. They both uses same Primary, same Mirror and same Witness. All servers are SQL Server 2008. The first DB shows fully connected and synchronized in Database Mirroring Monitor but the second DB shows disconnected between Mirror and Witness. A restart of the Witness does not help. A manual failover back and forth of the second DB does not help either.

When I checked sys.database_mirroring_witness in Witness server, I discovered that regarding the second DB, the Mirror server name (TCP://srv.mycompany.com:5022) is missing from mirror_server_name column. Is there a way to fix this?

I guess removing the mirroring and then configuring the same setup again could work but this is a critical DB and not easily done. Is there another way to fix this issue without removing and re-creating mirroring?