Database Mirroring: Failed Logins on Primary After Failover

mirroringsql serversql-server-2016

We have a Database Mirroring setup between PROD and SECONDARY and we have failed over to the secondary replica (SECONDARY) . We are now getting a number of almost constant failed logins showing in the log of the server PROD .

enter image description here

The applications use the Failover Partner property of the connection string:

Data Source=PROD;Failover Partner=SECONDARY;Initial Catalog=myDataBase;

and we have confirmed that they are working as expected.

It makes sense that the explicitly specified database on PROD cannot be opened as it is in a restoring state due to being failed over but the app is connecting successfully to SECONDARY. Is this error message expected behaviour on a failed over database mirroring primary replica?

Below replicates the problem using a simple, ODBC based scenario

Set up a Mirorred database:

enter image description here

create a login on the primary

USE Mirroring
CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere', DEFAULT_DATABASE = 'Mirroring'
CREATE USER MirrorUser
ALTER ROLE db_datareader ADD MEMBER MirrorUser

and the secondary:

CREATE LOGIN MirrorUser WITH PASSWORD = 'MyPassHere',SID = <Sid from primary here>

Set up a DSN:
enter image description here

enter image description here

enter image description here

enter image description here

Set up a linked table in an Access database

enter image description here

enter image description here

enter image description here

enter image description here

Failover the database to the secondary:

ALTER DATABASE Mirroring SET PARTNER FAILOVER

Open the Access linked table and verify we can see the data after the failover:

enter image description here

Observe the error on .\PROD

enter image description here

Best Answer

In Database Mirroring, failover is detected by attempting to connect to the principal first, then if that fails, the driver attempts connection to the failover partner server. So, if you have an application that is configured without connection pooling, and is coded to not maintain connections between databases requests, the primary will see login attempts, but deny them if the login has its "default database" property set to one of the mirrored databases.

It might also be that you have a misconfigured connection string somewhere that doesn't have the failover partner parameter configured. The error message in the error log shows which client is experiencing login failures - that may help you track down where the problem is. You may also have multiple connection strings configured in your client app, and one of the strings is missing the failover partner parameter.

As an aside, Database Mirroring is marked as deprecated, and may be removed from a future version of SQL Server. The replacement, which works quite a bit better, is Availability Groups. I would consider looking at migrating to Availability Groups since it will significantly simplify migrations to new hardware or migration into the cloud via Distributed Availability Groups, which are supported on SQL Server 2016+.

Related Question