I'm having a bad issue with Always ON on a SQL Server 2017 CU3 running on Windows Server 2016 with last cumulative updates (March 2018). Availability Mode is set to Synchronous commit and when I reboot the secondary node some DBs (randomly) get in a "Not Synchronized" state and they can't go back to "Synchronized". Everything looks OK, I have another SQL installation with same patching level and Os and it's working fine. I don't have specific errors on the event viewer. Antivirus is disabled, the nodes are on the same network and they look ok. I tried to raise the session timeout to 20s but this didn't solve the issue. Do you have any idea or suggestion?
I had the same issue on SQL Server 2017 CU 11 (latest as of today) with Windows Server 2016. After rebooting the secondary replica, some of the databases in the AG show not synchronizing. I used
ALTER database SET HARD RESUME, but it didn't work. The work around is to restart the SQL Server Services on the secondary replica. Then all databases show synchronized again.
Look at your Extended Events at AlwaysOn_health. Maybe you can get more specific information there. I've never seen that randomly fail, so it's not a bad idea to open a case with msft.
May I know how do you resolve the issue when seeing the DBs in the "Not Synchronizing" status? And you were saying only some random DBs in that state, while some other DBs are in a healthy state on the same instance?
I could only think of some possible causes:
- The DBs that went into "Not Synchronizing" state is a heavy-load transactional database where when the secondary node rebooted, the redo logs couldn't catch up with the primary node. How about trying "Asynchronous" mode and reboot your secondary to see if the issue still present?
- There's might also be some issues with the DNS name thingy. Did you check out the cluster event logs? This would probably need a Windows guy to help resolving it.