I am trying to get database mirroring working properly in high safety mode with automatic failover. I have named instances of SQL Server 2014 installed on Windows Server 2012 R2 for my principal and mirror. I have another named instance of SQL Server Express 2014 installed on Windows Server 2016 as my witness. I have been having a great deal of trouble and it all seems to be related to strange behavior on the part of Windows Firewall.
During the initial setup of the mirror I had problems connecting to my secondary and witness in the Database Mirroring Security Wizard as well as getting error 1418 when trying to start the mirroring. In all cases I had to disable the Windows firewall temporarily on the various servers. Then I was able to complete the Wizard as well as start the mirror without any errors.
I already had ports 1433 and 5022 opened on all servers. I also tried opening ports 135, 137, 138, 139 and 1434 based on other information I dug up, but that did not help. The only thing that worked was turning the Windows firewall off on all three servers all the way through the process of configuring and starting the mirror. Then I was able to turn it back on and the mirror keeps working.
But now if I open the Database Mirroring Monitor on the principal and reboot the mirror server the mirror will not reconnect until I disable the Windows Firewall again momentarily. Once it reconnects I can turn the firewall back on and it will continue working until the next reboot.
If I disable the Windows Firewall before rebooting, when the server boots back up it will automatically reconnect then I can enable the firewall again and it will continue to work even after subsequent reboots UNTIL I failover. Then the principal becomes the mirror and vice versa and I get the same problem on the new mirror (former principal). I then have to take the same steps as above on the new mirror to get it to connect.
Now, even though I said that if I disabled the Windows firewall on the original mirror, rebooted and then re-enabled the firewall the mirror would be able to automatically reconnect after subsequent reboots, once I have failed over to the mirror, if I then fail back to the original principal, the ability to automatically reconnect after rebooting is lost and I will have to once again disable the firewall, reboot and re-enable.
Obviously this behavior is far from ideal. I need everything to just work and connect as they are supposed to automatically. Can anyone help me figure this out?
