1

I recently installed SQL Server 2016 SP1 Standard on two servers and 2016 SP1 Express as a Witness on a third server. Firewall is off and the servers can ping each other. I created an empty database on afserver1 (full recovery model), performed a full backup, and restored with norecovery on afserver2.

The first issue I'm seeing is that on afserver2 when I refresh the databases for the first time after restore, two identical database entries show up in the list. Refreshing again corrects it.

enter image description here

It also takes about 10 seconds to refresh the database list on the secondary even though there is only two db's on the server. The primary always refreshes the db list instantly. In the log file on the secondary after the refresh shows a number of these errors:

Login failed for user 'DOMAIN\USER'.
Reason: Failed to open the explicitly specified database 'MirrorTest'.
Error: 18456, Severity: 14, State: 38.

It seems odd that simply refreshing the database list would result in 6 access errors in the logs for a database in a restoring state, but maybe that's normal?

When I go through the wizard to set up the mirror, it succeeds immediately. However, when I start mirroring it takes a very long time to start (and this is an empty db with no transactions). It will finally start but after a short time will fail over to the secondary. In the logs after about a minute, I start seeing entries for both the secondary and witness server:

Database mirroring connection error 4
'An error occurred while receiving data:
'10054(An existing connection was forcibly closed by the remote host.)'.'
for 'TCP://AFSERVER3.domain.local:5022'.
Error: 1474, Severity: 16, State: 1.

enter image description here

It appears the database is failing back and forth between afserver1 and 2, with a number of messages in the logs about database mirroring being inactive and then active again. It feels like a communications problem but the servers are on the same subnet with the firewalls turned off.

Any ideas for what could be causing not only the slow refresh of the database list and the back and forth failover of the database?

I've verified that the account has CONNECT permission to the endpoint on each server. I'm using domain accounts for everything on these servers.

Improve this question
0

1 Answer 1

Reset to default
1

It looks like CU1 corrects the issue I was having with the spontaneous failovers. The mirror now seems stable and can fail manually back and forth correctly.

Refreshing the database list on the secondary is still slow, presumably because it is trying to log into the database but can't; log files contain "Login failed for user. Reason: Failed to open the explicitly specified database 'MirrorTest' when I try to refresh the list.

Improve this answer
2
  • thats because the default database setting for the login/user. Change it to master.
    – Kin Shah
    Commented Feb 7, 2017 at 18:45
  • Security is set by AD group, and the group I'm in already has the default set to master and is sysadmin. My individual AD user account is also there since I was the one to do the installation, and it is also set to master for default. Oddly enough, default language for my individual account was set to Arabic instead of English, but correcting that did not change anything.
    – Chris Mangrum
    Commented Feb 7, 2017 at 21:33

Your Answer

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

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