0

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

Improve this question

1 Answer 1

Reset to default
1

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+.

Improve this answer
1
  • Thanks for your response. I found that the login failure is still present on the primary regardless of the default database (the same happens if i set that to master) Its a pain because it means the log is cluttered with login failures that aren't "true" failures, making it hard to see if anything is indeed failing. Availability groups are on our roadmap which should simplify this in future due to the AG listener
    – SE1986
    Nov 16 at 23:46

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.