Replication ISSUE - how to prevent error MSSQL_REPL-2147199363. after it happens, recreate publish and configure all suscriptors is necessary.

Question

HI ALL, help please. currently we have our main database and many subscriptors, when some of the subscriptors are offline form some days (maintenance or politics company pourposes doesnt matter), and getting online again, we are facing some issues getting this error: MSSQL_REPL-2147199363 once it happens, database that was offline is not getting sync and new subscriptors are not allowed (we are adding more subscriptors as company requires), so, the workable solution is to recreate the publish and reconfigure subscriptors (15) , but this is not optimal solution, because rework. im adding logs and verbose logs.

MODULE NAME                   , UTC DateTime           ,  TID,  LINE, SEV, MESSAGE
-----------                   , ------------           ,  ---,  ----, ---, -------
CReplDebugLog                 , 2023/04/18 00:18:27.420, 75032,   620,  S1, Starting log file.
CReconcilerTask               , 2023/04/18 00:18:27.420, 75032,   456,  S1, INFO: =============== STARTING OF MERGE ===============
CReconcilerTask               , 2023/04/18 00:18:27.420, 75032,   534,  S2, INFO: CommandLine:  "c:\Program Files\Microsoft SQL Server\150\COM\replmerg.exe" -Publisher [VR-VRMKIOSK] -PublisherDB [Cafeteria_VNN] -Distributor [VR-VRMKIOSK] -Publication [Merge Cafeteria_VNN] -Subscriber [VRT-LEG\SQLEXPRESS2] -SubscriberDB [Checador-20230317]
CReconcilerTask               , 2023/04/18 00:18:27.420, 75032,   542,  S2, INFO:   -SubscriptionType 1 -SubscriberSecurityMode 1
Replprov.dll                  , 2023/04/18 00:18:27.628, 75032, 21144,  S2, INFO: Reading profile from Subscriber: ProfileName:
DatabaseReconciler            , 2023/04/18 00:18:27.894, 75032,  4778,  S2, :T:,10,0,0,0,0,0,0,,,
DatabaseReconciler            , 2023/04/18 00:18:27.894, 75032,  5874,  S2, :T:,20,0,Merge Cafeteria_VNN,VR-VRMKIOSK,VRT-LEG\SQLEXPRESS2,VR-VRMKIOSK,Cafeteria_VNN,Checador-20230317,websync-client,
DatabaseReconciler            , 2023/04/18 00:18:28.056, 75032, 25603,  S2, INFO: [WEBSYNC_PROTOCOL] Sending client ReconcilerPhase WebSyncReconcilerPhase_ReinitSchemaAndFiles
WinHttpClient                 , 2023/04/18 00:18:28.057, 75032,  1206,  S2, INFO: Exchange ID = 42AF9764-408D-4FB9-938B-FF4419A15DD1.
Replprov.dll                  , 2023/04/18 00:18:31.160, 75032, 21144,  S2, INFO: Reading profile from Subscriber: ProfileName:
DatabaseReconciler            , 2023/04/18 00:18:31.172, 75032, 25709,  S2, INFO: [WEBSYNC_PROTOCOL] Received server ReconcilerPhase WebSyncReconcilerPhase_ReinitSchemaAndFiles
DatabaseReconciler            , 2023/04/18 00:18:33.455, 75032,  4778,  S2, :T:,10,1384,0,0,0,0,0,,,
Replprov.dll                  , 2023/04/18 00:18:34.191, 75032,  3020,  S2, :T:,80,1384,1,sub,0,,,,,
Replprov.dll                  , 2023/04/18 00:18:34.191, 75032,  2494,  S2, :T:,70,1384,1,sub,65,,,,,
Replprov.dll                  , 2023/04/18 00:18:34.254, 75032, 21144,  S2, INFO: Reading profile from Subscriber: ProfileName:
DatabaseReconciler            , 2023/04/18 00:18:34.424, 75032,  4778,  S2, :T:,10,1384,0,65,0,0,65,,,
DatabaseReconciler            , 2023/04/18 00:18:34.425, 75032,  5874,  S2, :T:,20,1384,Merge Cafeteria_VNN,VR-VRMKIOSK,VRT-LEG\SQLEXPRESS2,VR-VRMKIOSK,Cafeteria_VNN,Checador-20230317,websync-client,
DatabaseReconciler            , 2023/04/18 00:18:34.492, 75032, 25603,  S2, INFO: [WEBSYNC_PROTOCOL] Sending client ReconcilerPhase WebSyncReconcilerPhase_RegularDownload
WinHttpClient                 , 2023/04/18 00:18:34.494, 75032,  1206,  S2, INFO: Exchange ID = 700998A2-9363-4986-9F44-EBBE69AFD976.
DatabaseReconciler            , 2023/04/18 00:18:36.196, 75032, 20462,  S2, :T:,110,1384,647,,,,,,,
DatabaseReconciler            , 2023/04/18 00:18:36.196, 75032, 20469,  S2, INFO: Session Highlights: ADD_INITIAL_PUBLICATION, SCHEMA_CHANGES, FAIL, WEBSYNC_CLIENT, PUB_INSERTS, 
DatabaseReconciler            , 2023/04/18 00:18:36.196, 75032, 25050,  S1, ERROR: Failed to upload supportability data to the publisher.
CReconcilerTask               , 2023/04/18 00:18:36.197, 75032,   692,  S1, INFO: =============== ENDING OF MERGE =================

how to avoid this kind of errors? any suggestion

Answer 1

Hi @Oscar Carrillo Razón,

This error usually occurs when a subscriber database has been offline for some time and has fallen out of sync with the publisher.

Here are some steps you can take to prevent or resolve this error:

Keep subscribers online: To avoid this error, try to keep all the subscribers online and connected to the publisher as much as possible. If a subscriber goes offline for an extended period of time, it may fall out of sync with the publisher and result in this error when it comes back online.

Check replication status: Monitor the replication status regularly to identify any issues with the subscribers. You can use the SQL Server Replication Monitor or run SQL Server Replication Agent profiles to check the status of replication.

In additon, you can set the "SubscriptionStreams" property to a value greater than 1 during the initial subscription setup. This allows the subscriber to catch up with the changes that occurred during its offline period without having to recreate the entire publication and subscription.

Please refer to this blog and MS document:

https://www.sqlservercentral.com/blogs/sql-replication-subscriptionstreams-setting

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/performance/configure-troubleshoot-subscriptionstreamsof-distribution-agent

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.