September 9, 2008 at 3:49 am
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
September 9, 2008 at 2:37 pm
What operating mode do you have mirroring setup in? It must be in high availability mode to have automatic failover.
September 9, 2008 at 2:59 pm
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
---------------------------------------------------------------------
September 10, 2008 at 2:18 am
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
September 10, 2008 at 4:16 am
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
September 10, 2008 at 7:28 am
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
September 10, 2008 at 8:59 am
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?
September 10, 2008 at 10:04 am
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:
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
September 10, 2008 at 10:13 am
The mirroring is setup as described above. Do you know what the error means?
Thanks,
Gary
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply