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.
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.
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.
Best Answer
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.