Current Configuration: I have two SQL Servers, which are connected by the failover mechanism, meaning one is primary and one is secondary.
I restarted both services on both servers sequentially.
On the secondary server, all the databases are in the "Synchronizing" state.
AG replication mode is synchronous. It has been 12hr it's still showing "Synchronizing" state.
What I did so far to resolve
Drop the database from the secondary server and remove the database from AG group on the primary server and add it back.
Any insight on this, I am using SQL Server 2017.
Best Answer
Firstly, if you could drop the database from any one of replica, it means the database is not yet part of any of availability groups, before dropping the database it must be removed from availability group first.
Assuming that your current scenario has 2 nodes in 1 cluster, I would recommend first to add witness (preferred File-share from different server other than 2 nodes) in WSFC to ensure the quorum healthy, so that while you restarting 1 server there would be 2 votes available (1 Node, 1 File-share witness), and the WSFC could smoothly fail-over the roles (Availability groups) from one to another node.
For the synchronizing issue, please do the following
Make sure the network connection is stable between 2 nodes especially with endpoint port (Default: 5022) which you can identity from any availability group properties
right click on AG Name -> Properties -> look at availability Replicas section (bottom) example: TCP://<IP ADDRESS>:5022
. Ensure the failover mode is AutomaticTry Suspend data movement and Resume, you can do this for any one of the database that is already part of availability group
If step 2 completed without any error, verify the hardening of LOG by REDO worker is normal which you can identity with following query, result can describe with the delay between LAST SENT (Primary) - LAST RECEIVED (Secondary)- LAST HARDENED (Secondary) which is not available in AG Dashboard