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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10568 Visits: 13687
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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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 Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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 Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 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 Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 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