<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server Newbies
»
Database mirroring failover with witness
Database mirroring failover with witness
Rate Topic
Display Mode
Topic Options
Author
Message
Gary Minnock
Gary Minnock
Posted Tuesday, September 09, 2008 3:49 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, April 15, 2013 4:27 AM
Points: 14,
Visits: 76
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
dmc-608719
dmc-608719
Posted Tuesday, September 09, 2008 2:37 PM
Old Hand
Group: General Forum Members
Last Login: Monday, April 23, 2012 7:35 AM
Points: 373,
Visits: 761
What operating mode do you have mirroring setup in? It must be in high availability mode to have automatic failover.
Post #566483
george sibbald
george sibbald
Posted Tuesday, September 09, 2008 2:59 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 1:35 PM
Points: 5,269,
Visits: 11,208
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
Gary Minnock
Gary Minnock
Posted Wednesday, September 10, 2008 2:18 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, April 15, 2013 4:27 AM
Points: 14,
Visits: 76
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
Albert Matubis
Albert Matubis
Posted Wednesday, September 10, 2008 4:16 AM
SSC 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
Gary Minnock
Gary Minnock
Posted Wednesday, September 10, 2008 7:27 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, April 15, 2013 4:27 AM
Points: 14,
Visits: 76
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
Gary Minnock
Gary Minnock
Posted Wednesday, September 10, 2008 8:59 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, April 15, 2013 4:27 AM
Points: 14,
Visits: 76
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
Albert Matubis
Albert Matubis
Posted Wednesday, September 10, 2008 10:04 AM
SSC 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
Gary Minnock
Gary Minnock
Posted Wednesday, September 10, 2008 10:13 AM
Grasshopper
Group: General Forum Members
Last Login: Monday, April 15, 2013 4:27 AM
Points: 14,
Visits: 76
The mirroring is setup as described above. Do you know what the error means?
Thanks,
Gary
Post #567086
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
