March 23, 2015 at 6:05 pm
Dear Sirs,
I have a mirroring environment in SQL Server 2008 R2. On the principal server are four mirrored databases to a mirror server. This environment is monitored by a witness server. The principal server is located in a region and the mirror server and the witness server in another region. This was to actually set up a contingency environment.
But there is a problem ! In this environment are occurring "failover" of the databases of the principal server to the mirror for no apparent reason. The main server did not suffer "boot" and also the SQL services were not stopped.
Why are these "failover" are happening? Because the servers are in different regions, can be a problem with network latency, causing communication failure of witness server with the principal server and then performs automatic "failover"?
In time : I observed that these automatic and unexpected "failover" have occurred in all four databases of the principal server but have also occurred in only a few databases. This is curious, because if the SQL service that had stopped on the principal server would cause the "failover" of all databases, not just some databases
When these unexpected occured "failover" I immediately checked the Event Viewer and the SQL log on the principal server and noticed the following errors:
- In the Event Viewer:
"SQL Server failed with error code 0xC0000000 to spawn a thread to process a new login or connection Check the SQL Server error log and the Windows event logs for information about possible related problems [CLIENT: 10.29.175.39]."
- In the SQL Server Log:
"Error: 18456, Severity: 14, State: 38."
"Login failed for user 'cm_aplicacao' Reason:. Failed to open the Explicitly specified database [CLIENT: 10.29.175.40]."
Note: "cm_aplicacao" is the application user that connects to the databases.
From these errors, does anyone have any idea of the cause of this problem and what is the solution?
Thank you in advance.
Kind Regards,
José Luiz
PS: I'm sorry because I doesn't Know how to write English very well
March 26, 2015 at 6:45 am
this is exactly what I would expect when you put the witness on your mirror site!
Either put the witness on a totally separate site to the mirror and the principal (recommended)
or put it on the same site as the principal.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply