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

Database mirroring failover with witness Expand / Collapse
Author
Message
Posted Tuesday, September 9, 2008 3:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:00 AM
Points: 14, Visits: 77
Hi, I'm new to sql 2005. I have managed to setup mirroring.
I have 3 sql 2005 servers.

Server 1 -> Primary:
Server 2 -> Secondary:
Server 3 -> Witness

It all setup ok and I can press the failover button and it fails over to Server 2 with no problem.

However if I restart Server 1, then I would expect it to failover to Server 2. This does not happen. It does not failover. I cannot find any relevant infomation from the event logs.

Why does this happen? How can I fix it?

Also I noticed that when Server 1 restarts the SQL services do not automatically restart as they should.

any help appreciated, thanks in advance.

Gary
Post #565997
Posted Tuesday, September 9, 2008 2:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:22 AM
Points: 375, Visits: 765
What operating mode do you have mirroring setup in? It must be in high availability mode to have automatic failover.
Post #566483
Posted Tuesday, September 9, 2008 2:59 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 5,976, Visits: 12,887
Gary Minnock (9/9/2008)


Also I noticed that when Server 1 restarts the SQL services do not automatically restart as they should.


Gary


go into sql server configuration manager, right click the service and check it is configured to start automatically on server restart. You could also do this via the services applet and check service set to automatic


---------------------------------------------------------------------

Post #566495
Posted Wednesday, September 10, 2008 2:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:00 AM
Points: 14, Visits: 77
1. Yes I have mirroring setup in high availability mode.
2. I already have the sql services setup to automatically restart, but this does not happen this is the problem.


Any more suggestions?
Regards,
Gary
Post #566662
Posted Wednesday, September 10, 2008 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 2:05 PM
Points: 39, Visits: 65
Database Mirroring in SQL Server 2005 works this way.

The Primary, witness and Secondary forms a quorom of three after a successful setup. A fail over will occur if Witness and Secondary agrees that Primary is dead, so they will form a quorom of two and automatic fail over will initiate within 3 seconds. Your secondary/mirror will act as the Primary/Principal. Now, once you restart Server 1 which as the original principal server before the failover it will find out that the mirror server (server 2) is the acting Principal server, so Server 1 will take the role of Mirror server this time. In this case you need to manually failover to server 1 by executing this statement on Server 2
"ALTER DATABASE database_name SET PARTNER FAILOVER" .

This TSQL statement will revert the Principal role to Server 1...


Albert B. Matubis
Database Administrator: Trend Micro PH
MCP. MCAD. MCDBA. MCT
MCTS: SQL Server 2005
MCTS: Business Intelligence
MCITP: Database Administrator
MCITP: Business Intelligence Applications Developer
Post #566722
Posted Wednesday, September 10, 2008 7:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:00 AM
Points: 14, Visits: 77
Yes thats all fine Albert. That information is on books online, but it does not solve my problem:

which is that when Server 1 goes down it should failover to Server 2 - which it does not.

So why is it not failling over to Server 2?

Thanks,
Gary
Post #566871
Posted Wednesday, September 10, 2008 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:00 AM
Points: 14, Visits: 77
I alos got this error out from the Event logs when I turned on Kerberos error logging:

The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/xxx.xxxxxx.xxxxxxxxxx. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named machine accounts in the target realm (xxxx.xxxxxxxxxx), and the client realm. Please contact your system administrator.

event id=4

any ideas?
Post #566999
Posted Wednesday, September 10, 2008 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 15, 2011 2:05 PM
Points: 39, Visits: 65
Can u send me a snapshot of your database mirror setup and any error msg if any... I'll try what I can do to help... Thanks

here is my email:
matubisalbert@gmail.com


Albert B. Matubis
Database Administrator: Trend Micro PH
MCP. MCAD. MCDBA. MCT
MCTS: SQL Server 2005
MCTS: Business Intelligence
MCITP: Database Administrator
MCITP: Business Intelligence Applications Developer
Post #567078
Posted Wednesday, September 10, 2008 10:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 31, 2013 6:00 AM
Points: 14, Visits: 77
The mirroring is setup as described above. Do you know what the error means?

Thanks,
Gary
Post #567086
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse