%3CLINGO-SUB%20id%3D%22lingo-sub-1447305%22%20slang%3D%22en-US%22%3EREQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT%20and%20error%20988%20--SQL%202017%20AlwaysOn%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1447305%22%20slang%3D%22en-US%22%3E%3CP%3ERecently%20we%20got%20the%20issue%20from%20customer%2C%20they%20failed%20to%20add%20the%20database%20into%20the%20existing%20AG%20with%20error%20988%3A%3C%2FP%3E%0A%3CP%3EMsg%20988%2C%20Level%2014%2C%20State%201%2C%20Line%2017%3C%2FP%3E%0A%3CP%3EUnable%20to%20access%20database%20'ag_03'%20because%20it%20lacks%20a%20quorum%20of%20nodes%20for%20high%20availability.%20Try%20the%20operation%20again%20later.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.jpg%22%20style%3D%22width%3A%20547px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197137i13A4113246DA1C2A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221.jpg%22%20alt%3D%221.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.jpg%22%20style%3D%22width%3A%20550px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197138i02E19B4650CD995C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.jpg%22%20alt%3D%222.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENo%20backup%20and%20no%20restored%20happened%20on%20all%20nodes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBased%20on%20this%20message%20%2C%20usually%20we%20will%20check%20SQL%20Server%20errorlog%2C%20cluster%20log%20%2C%20run%20cluster%20validation%20to%20confirm%20the%20current%20cluster%20healthy.%26nbsp%3B%20But%20on%20this%20issue%2C%20the%20root%20cause%20is%20not%20caused%20by%20the%20cluster%20issue.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20the%20AG%20environment%EF%BC%9A%3C%2FP%3E%0A%3CP%3E3-node%20AG%2C%20with%201%20primary%2C%201%20synchronized%20secondary%2C%201%20aysnchoronized%20seconary%2C%20using%20SQL%20server%202017.%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECx%20already%20ran%20the%20cluster%20validation%20%2C%20no%20issue%20found.%26nbsp%3B%20The%20AG%20already%20works%204%20months%20no%20issue.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3ETroubleshooting%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20set%20below%20XEVENT%20on%20our%20lab%20and%20customer's%20environment%20to%20compare%20the%20difference.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECREATE%20EVENT%20SESSION%20%5Bag_state_change%5D%20ON%20SERVER%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.alwayson_ddl_executed(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.hadr_db_commit_mgr_harden(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.hadr_db_commit_mgr_set_policy(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.hadr_db_commit_mgr_update_harden(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.hadr_db_partner_set_policy(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%2C%3C%2FP%3E%0A%3CP%3EADD%20EVENT%20sqlserver.hadr_db_partner_set_sync_state(%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ACTION(sqlos.system_thread_id%2Csqlserver.session_id%2Csqlserver.sql_text))%3C%2FP%3E%0A%3CP%3EADD%20TARGET%20package0.event_file(SET%20filename%3DN'ag_state_change')%3C%2FP%3E%0A%3CP%3EWITH%20(MAX_MEMORY%3D4096%20KB%2CEVENT_RETENTION_MODE%3DALLOW_SINGLE_EVENT_LOSS%2CMAX_DISPATCH_LATENCY%3D30%20SECONDS%2CMAX_EVENT_SIZE%3D0%20KB%2CMEMORY_PARTITION_MODE%3DNONE%2CTRACK_CAUSALITY%3DOFF%2CSTARTUP_STATE%3DOFF)%3C%2FP%3E%0A%3CP%3EGO%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOn%20our%20lab%2C%20before%20joining%20the%20new%20database%20to%20secondary%2C%26nbsp%3B%20database%20policy%20is%20DoNothing%20during%20DDL%20statement%20execution%2C%20and%20harden%20status%20is%20NoCommitFailure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--Part%201%26nbsp%3B%3C%2FP%3E%0A%3CP%3EALTER%20AVAILABILITY%20GROUP%20%5BAG01%5D%3C%2FP%3E%0A%3CP%3EADD%20DATABASE%20AG_03%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBACKUP%20DATABASE%20AG_03%20TO%26nbsp%3B%20DISK%20%3D%20N'C%3A%5CProgram%20Files%5CMicrosoft%20SQL%20Server%5CMSSQL14.MSSQLSERVER%5CMSSQL%5CLog%5Cag_db03.bak'%20WITH%26nbsp%3B%20COPY_ONLY%2C%20FORMAT%2C%20INIT%2C%20SKIP%2C%20REWIND%2C%20NOUNLOAD%2C%20COMPRESSION%2C%26nbsp%3B%20STATS%20%3D%205%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197139iC9341C6F52A671C5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%223.jpg%22%20alt%3D%223.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20adding%20database%2C%20database%20harden%20policy%20changes%20to%20WaitForHarden%2C%20and%20the%20status%20is%20still%20NoCommitFailure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E--Part%202%3C%2FP%3E%0A%3CP%3EALTER%20DATABASE%20%5Bag_03%5D%20SET%20HADR%20AVAILABILITY%20GROUP%20%3D%20%5BAG01%5D%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%224.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197140i8E744AA9D036A2A3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%224.jpg%22%20alt%3D%224.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOn%20customer's%20environment%2C%20we%20could%20see%20the%20major%20different%20is%2C%20the%20%3CSTRONG%3Ehadr_db_commit_mgr_set_policy%3C%2FSTRONG%3E%20of%20below%20trace%20set%20to%20%3CSTRONG%3EWaitForHarden%3C%2FSTRONG%3E%20soon%20after%20add%20database%20into%20AG%2C%20and%20the%20harden%20status%20is%20%3CSTRONG%3EMinSyncCommitFailure%3C%2FSTRONG%3E.%20Thus%20this%20transaction%20is%20not%20committed%2C%20not%20to%20mention%20the%20following%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%225.jpg%22%20style%3D%22width%3A%20784px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F197141i7D9FBA2EE9A32078%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%225.jpg%22%20alt%3D%225.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20checking%2C%20we%20find%20there%20is%20a%20new%20feature%20called%20%3CSTRONG%3EREQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT%3C%2FSTRONG%3E%20%3CSPAN%3Eon%20SQL%20Server%202017.%20Here%20below%20is%20the%20definition%20of%20this%20feature%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%22Used%20to%20set%20a%20minimum%20number%20of%20synchronous%20secondary%20replicas%20required%20to%20commit%20before%20the%20primary%20commits%20a%20transaction.%20Guarantees%20that%20SQL%20Server%20transactions%20will%20wait%20until%20the%20transaction%20logs%20are%20updated%20on%20the%20minimum%20number%20of%20secondary%20replicas%E2%80%A6When%20replicas%20are%20in%20synchronous%20commit%20mode%2C%20writes%20on%20the%20primary%20replica%20wait%20until%20writes%20on%20the%20secondary%20synchronous%20replicas%20are%20committed%20to%20the%20replica%20database%20transaction%20log.%20%22%3C%2FP%3E%0A%3CP%3E(-ERR%3AREF-NOT-FOUND-%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Falter-availability-group-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Falter-availability-group-transact-sql%3Fview%3Dsql-server-ver15%3C%2FA%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20the%20reason%20why%20database%20policy%20changes%20to%20WaitForHarden%20after%20adding%20the%20new%20database%20on%20primary.%20Primary%20need%20to%20confirm%20this%20transaction%20won't%20commit%20until%20secondary's%20is%20hardened.%20%26nbsp%3BYet%20from%20secondary's%20view%2C%20it%20can%20only%20add%20the%20new%20database%20after%20the%20primary%20transaction%20committed.%20This%20feature%20conflicts%20with%20what%20we%20want%20to%20do.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20to%20solve%20the%20issue%20actually%20is%20easy%3A%3C%2FP%3E%0A%3CP%3EDisable%20this%20feature%2C%20or%20using%20Auto%20Seeding%20%2F%20Join%20Only%20mode%20to%20synchronize%20data%20with%20secondary.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy%20tech%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1447305%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22WeChat%20Image_20200106134830.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211983i19F412813ED9783F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22WeChat%20Image_20200106134830.jpg%22%20alt%3D%22WeChat%20Image_20200106134830.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1447305%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Esql%202017%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Recently we got the issue from customer, they failed to add the database into the existing AG with error 988:

Msg 988, Level 14, State 1, Line 17

Unable to access database 'ag_03' because it lacks a quorum of nodes for high availability. Try the operation again later.

1.jpg2.jpg

No backup and no restored happened on all nodes.

 

Based on this message , usually we will check SQL Server errorlog, cluster log , run cluster validation to confirm the current cluster healthy.  But on this issue, the root cause is not caused by the cluster issue.

 

Here is the AG environment:

3-node AG, with 1 primary, 1 synchronized secondary, 1 aysnchoronized seconary, using SQL server 2017. 

Cx already ran the cluster validation , no issue found.  The AG already works 4 months no issue.

 

 

Troubleshooting

 

We set below XEVENT on our lab and customer's environment to compare the difference.

 

CREATE EVENT SESSION [ag_state_change] ON SERVER

ADD EVENT sqlserver.alwayson_ddl_executed(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)),

ADD EVENT sqlserver.hadr_db_commit_mgr_harden(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)),

ADD EVENT sqlserver.hadr_db_commit_mgr_set_policy(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)),

ADD EVENT sqlserver.hadr_db_commit_mgr_update_harden(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)),

ADD EVENT sqlserver.hadr_db_partner_set_policy(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text)),

ADD EVENT sqlserver.hadr_db_partner_set_sync_state(

    ACTION(sqlos.system_thread_id,sqlserver.session_id,sqlserver.sql_text))

ADD TARGET package0.event_file(SET filename=N'ag_state_change')

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

 

On our lab, before joining the new database to secondary,  database policy is DoNothing during DDL statement execution, and harden status is NoCommitFailure.

 

--Part 1 

ALTER AVAILABILITY GROUP [AG01]

ADD DATABASE AG_03;

 

BACKUP DATABASE AG_03 TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ag_db03.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

3.jpg

 

After adding database, database harden policy changes to WaitForHarden, and the status is still NoCommitFailure.

 

--Part 2

ALTER DATABASE [ag_03] SET HADR AVAILABILITY GROUP = [AG01];

4.jpg

On customer's environment, we could see the major different is, the hadr_db_commit_mgr_set_policy of below trace set to WaitForHarden soon after add database into AG, and the harden status is MinSyncCommitFailure. Thus this transaction is not committed, not to mention the following queries.

 

5.jpg

 

After checking, we find there is a new feature called REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT on SQL Server 2017. Here below is the definition of this feature:

 

"Used to set a minimum number of synchronous secondary replicas required to commit before the primary commits a transaction. Guarantees that SQL Server transactions will wait until the transaction logs are updated on the minimum number of secondary replicas…When replicas are in synchronous commit mode, writes on the primary replica wait until writes on the secondary synchronous replicas are committed to the replica database transaction log. "

(https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-availability-group-transact-sql?view=sql...)

 

That's the reason why database policy changes to WaitForHarden after adding the new database on primary. Primary need to confirm this transaction won't commit until secondary's is hardened.  Yet from secondary's view, it can only add the new database after the primary transaction committed. This feature conflicts with what we want to do.

 

How to solve the issue actually is easy:

Disable this feature, or using Auto Seeding / Join Only mode to synchronize data with secondary.

 

Enjoy tech :)