I have 2 Sql Server 2008R2, enterprise edition servers setup in an asynchronous (High Peformance) mirror setup. There is no witness.

Intermittently I find that a client will attempt to connect to the secondary instead of the primary. This is despite the fact that the connection string used has no reference to a failover partner.

My research led me to this article: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/database-mirroring-in-sql-server, which states

"When a SqlConnection is successfully opened, the failover partner name is returned by the server and supersedes any values supplied in the connection string."

I believe this is the reason I'm getting spurious connections to the secondary mirror server.

My question is this: I'd like to prevent the primary from reporting the secondary as a failover to its incoming connections. Is there any setting that would allow me to do so?


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.