Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Mirroring In sql server 2005- Witness Full Quorum Expand / Collapse
Author
Message
Posted Monday, April 21, 2008 10:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 30, 2012 11:15 PM
Points: 295, 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.
Post #488344
Posted Monday, April 28, 2008 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:36 AM
Points: 1,025, Visits: 1,096
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
Post #491241
Posted Monday, April 28, 2008 6:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #491321
Posted Monday, May 5, 2008 11:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 30, 2012 11:15 PM
Points: 295, 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.
Post #495400
Posted Tuesday, February 3, 2009 2:05 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 7:01 AM
Points: 740, Visits: 1,892
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 --
Post #649336
Posted Tuesday, March 24, 2009 1:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 2:43 PM
Points: 10, Visits: 31
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
Post #682768
Posted Wednesday, May 20, 2009 8:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:16 AM
Points: 1, 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
Post #720586
Posted Monday, June 8, 2009 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 2:43 PM
Points: 10, Visits: 31
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.
Post #730638
Posted Thursday, October 22, 2009 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 18, 2014 10:45 AM
Points: 153, Visits: 139
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
Post #807259
Posted Tuesday, January 12, 2010 4:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 6:22 AM
Points: 2, Visits: 70
Restarting the service on the Witness worked perfectly for me, thanks!
Post #845999
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse