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


Database mirroring failover with witness


Database mirroring failover with witness

Author
Message
Gary Minnock
Gary Minnock
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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
dmc-608719
dmc-608719
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2669 Visits: 787
What operating mode do you have mirroring setup in? It must be in high availability mode to have automatic failover.
george sibbald
george sibbald
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59100 Visits: 13706
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

---------------------------------------------------------------------
Gary Minnock
Gary Minnock
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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
Albert Matubis
Albert Matubis
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Gary Minnock
Gary Minnock
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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
Gary Minnock
Gary Minnock
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 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?
Albert Matubis
Albert Matubis
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
Gary Minnock
Gary Minnock
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 77
The mirroring is setup as described above. Do you know what the error means?

Thanks,
Gary
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