1

Setup:

  • SQL Server 2005 w/SP4 64-bit Standard for all three servers
  • Windows Server 2008 R2 Enterprise w/SP1 64-bit for all three servers
  • 01 is designated as the Principal
  • 02 is designated as the Mirror
  • 03 is designated as the Witness

My issue is that there are Principal and Mirrored DBs on both servers and it is causing Maintenance Plans and SQL scripts to fail.

My questions:

  1. Why are the DBs failing from one to the other when 01 should have all Principal DBs?
  2. How do I stop this behavior
  3. What is the least invasive process of changing the Principal DBs on 02 back to 01?
  • 1
    What mode is configured for each database mirror? – user507 Jul 14 '14 at 20:42
  • @Shawn, new to the mirroring configuration and I'm trying to learn more about it. This was one of those things that got handed to me. I started reading the whitepaper on MS's site about DB mirroring in 2005, but I've not gotten that far yet. How to check for what you're asking? – Sean Perkins Jul 15 '14 at 12:57
  • 1
    Through SSMS, right-click a database then "task" and "Mirroring...". The properties window will open up and a grayed out portion "Operating Mode" should have a radio button beside one of three options: High Performance, High Safety W/ Failover, or High Safety W/out Failover. I would expect you should see High Safety with failover. – user507 Jul 15 '14 at 14:26
  • As well, under the same menu (Tasks) you can open the "Launch Database Mirroring Monitor" and view what is going on with the mirroring sessions. – user507 Jul 15 '14 at 14:31
  • 1
    It can fail back if the current principal has an issue but not until that time or you do it manually. – user507 Jul 15 '14 at 18:56
2

Can you please look at SQL Server errorlog and see if you could find some relevant information on both Principal and mirror. Generally if SQL Server CPU utilization goes high above 50 % there can be failover. This is specifically caused by heavy load on SQL Server this is documented in Below Microsoft Web resource See 'recommendation for Configuring partner Server' in link : http://msdn.microsoft.com/en-us/library/ms366349%28v=sql.100%29.aspx

This can be avoided by Alter partner time out

Alter database db_name set partner timeout 20

Edit: After User provided more details

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.

As per this message its clear that network connecting principal and mirror flipped I dont know for how much time but this caused the failover. What happens is Mirror server keeps sending pings to Principal whether it is alive or not and suppose Principal goes down and now mirror will send 10 pings to principal(By default) and if it does not gets any response it will initiate failover with help of Witness( its little more complex with witness ,it involves quorum)

If it was for moment you can increase partner timeout( not a good practice though) but you should Immediately speak to network team and ask them RCA for this glitch as this caused failover. My course of action would be to meet them personally ask them to run bunch of commands to check response time

| improve this answer | |
  • the following are the only messages I found in the error logs: Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://SERVERNAME:PORT_NUMBER'.********** The mirrored database "DB_NAME" is changing roles from "PRINCIPAL" to "MIRROR" due to Role Syncronization. – Sean Perkins Jul 15 '14 at 18:41
  • There are two messages there, but I can't format them in a comment :/ – Sean Perkins Jul 15 '14 at 18:42
  • 1
    I have updated my answer please check. Immediately take help of Netwrok team this is a network issue – Shanky Jul 15 '14 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.