SqlServer db mirror failover with EF6 fail Principle to Mirror only

Question

Sql server 2014, Entity framework 6.1.2, .net framework 4.5, IIS 8, DB mirror in high safety mode with no witness.

I have the following connection string

Data Source=A;Failover Partner=B;Initial Catalog=DB;Integrated Security=False;User Id=sa;Password=Password;MultipleActiveResultSets=true;Connect Timeout=10

Starting with: A = Principle, B = Mirror
And when I do a manual failover from A to B (Principle to Mirror), it fails with the Unable to open connection - Erro 40.

Starting with: A = Mirror, B = Principle
Manual failover works.
Now: A = Principle, B = Mirror
I then without restarting the site, failover back again, Error 40.
Restart IIS, works fine again.

Here's the code to test the connection:

while (!_exitFlag)
{
    using (var context = new EntityContext())
    {
        var result = context.Foos.Count();
    }
}

Any ideas?

score 2 · Accepted Answer · edited Apr 13 '17 at 12:42

So as it turns out, the client is joining from a different lan so the failover partner registered on the sql server is not resolvable on the client.

SELECT DB_NAME(database_id) AS 'DatabaseName', mirroring_partner_instance
FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

Will give you the "name" of the failover partner when a .net application connects to the principle. This name needs to be "pingable" on the client the .net application is sitting on.

What happens is when ado.net connects to the server specified in the "Data Soruce" value, it retrieves the failover partner from the database and DISREGARDS THE ONE IN CONNECTION STRING. (Great ****ing job Microsoft.)

I have not looked into why the reverse works, but apparently some where alone the line, the "Data Source" server in the connection string is checked again.

I got the idea from this article: http://blogs.msdn.com/b/spike/archive/2010/12/15/running-a-database-mirror-setup-with-the-sqlbrowser-service-off-may-produce-unexpected-results.aspx (Basically this problem is more prominently discussed when you search for DB mirror on custom port.)

Edit- I have followed it up with another question now I'm facing to solve the problem: https://dba.stackexchange.com/questions/87607/set-up-sql-server-db-mirror-specifying-ip-address-for-mirroring-partner-instance

SqlServer db mirror failover with EF6 fail Principle to Mirror only

1 Answers1