May 10, 2016 at 12:06 pm
In the event of a database failover, I have a problem with the event log filling up with "information" events that get in the way of viewing more critical events to investigate possible causes of the failover.
I've been under the impression that "this is just the way it is", but I'm wondering if perhaps I have a poor configuration (or am doing something wrong), and someone may be able to point me in a better direction.
Here is the phenomenon:
1. I have a SQL Server 2008 server (EE) mirroring in high availability, along with a separate witness server.
2. When there is a problem with the primary server, the witness successfully instructs the mirror to take over.
3. The applications (mostly .NET websites) that try to connect to the primary get redirected to the other server through the "Failover Partner" option in the connection string.
4. The primary (now acting as a mirror), adds an informational item to the event log for each attempted connection:
"Login failed for user '[USER]'. Reason: Failed to open the explicitly specified database. [CLIENT: xx.xx.xx.xx]"
5. The event log fills up RAPIDLY with these informational events, as there are a number of client applications sharing the same server.
6. If I don't investigate quickly enough, any useful error events (to identify what may have cased the failover) are pushed off the bottom of the stack.
So.
To be clear, the whole process of primary failing over to mirror is working fine. The applications are being notified of the failover sate, and are quickly connecting to the backup server. The high availability setup itself seems to be working as expected.
The ONLY problem is that the event log is being glutted with these (imo worthless) messages that happen because the client applications are using a connection string that tells them server X is the primary (try it first) and server Y is the failover partner (try it second).
So my questions are:
1. Is it expected behavior for a database (acting as a mirror) to log an event every time someone tries to access it?
2. Is there any way to disable logging these notifications (while logging other, more valuable events)?
3. Is the application connection string of "Data Source=[PRIMARY];Failover Partner=[MIRROR];Initial Catalog=[DB];Integrated Security=SSPI;" the best approach for a high-availability mirror?
4. Is there a way to persist Critical / Warning / Error events in the Event Viewer so they don't get pushed off the bottom of the stack by Information events?
Thanks.
May 11, 2016 at 3:31 pm
With regard to the login messages you are not wanting to see in the Sql Server Error log - while in SSMS, rt click on the sql instance>Properties>Security and change the Login auditing to "none". You may have to restart the instance for it to take effect.
May 12, 2016 at 5:59 pm
Thanks very much!
That's exactly what I needed.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply