SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Mirroring In sql server 2005- Witness Full Quorum


Mirroring In sql server 2005- Witness Full Quorum

Author
Message
VaibhavChoubey@SQL
VaibhavChoubey@SQL
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 109
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.
Andy sql
Andy sql
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2990 Visits: 1323
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
free_mascot
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7645 Visits: 2250
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
VaibhavChoubey@SQL
SSC-Addicted
SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)SSC-Addicted (417 reputation)

Group: General Forum Members
Points: 417 Visits: 109
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
jay-h
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3839 Visits: 2381
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
Colleen Rossman
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 35
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
alastair_bravey
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 116
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
Colleen Rossman
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 35
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
cdaly-822071
SSC-Enthusiastic
SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)SSC-Enthusiastic (167 reputation)

Group: General Forum Members
Points: 167 Visits: 155
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
michael nee
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 70
Restarting the service on the Witness worked perfectly for me, thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search