PRB: SEM Does Not Allow You to Indicate Manual Sync Complete (151403)

SYMPTOMS

Under Manage Replication\Subscriptions, SQL Enterprise Manager allows you to subscribe to a publication using manual synchronization. After manually synchronizing the tables, when you look up the Manage Replication\Subscriptions option to indicate completion of manual the synchronization, "Sync Completed" is not available (dimmed).

CAUSE

If you choose the Manual Sync option during subscription, it indicates that the tables will be manually synchronized. The Sync task still has to run to create the temporary files and insert a placeholder job in the distribution database. The distribution task still has to run, to apply this placeholder job at the subscriber and this places placeholder entries in the MSlast_job_info table at the subscriber. When these entries are available at the subscriber, the Sync Completed button is available. If the distribution task is changed to On Demand, has been disabled or runs into any errors, the placeholder entries will not be made at the subscriber and the "Sync Completed" button is disabled.

WORKAROUND

Here is the proper procedure for achieving manual synchronization:

Create the publication and specify the articles in the publication. Then, you can setup a subscription to this publication or to specific articles in this publication.
The Sync task is scheduled to run every 5 minutes by default. If this has been modified to run On Demand or is not Enabled, run the Sync task manually. When the Sync task runs, it creates the bcp data files (.tmp files) in the distributors working directory and inserts a placeholder job in the distribution database.
Run the distribution task to apply this job at the subscriber, thereby inserting the placeholder entries in the MSlast_job_info table at the subscriber.
Manually synchronize the tables using the .tmp files or using other means like the Transfer option in Enterprise Manager or by using database dumps.
Now, use the SQL Enterprise Manager to signal Sync Completed or use sp_replsync at the subscriber.

MORE INFORMATION

SQL Enterprise Manager initially disables the Sync Complete option and the articles initially show Not Sync'ed. After the Sync task runs, they change to Sync'ed. Note that the Sync'ed status just means that the Sync task has run and created the bcp data files. It does not mean that this file has already been applied at the subscriber. The Sync Completed button is still dimmed. When the distribution task runs, the Sync Completed button is available. You can manually synchronize the tables and then signal completion for further transactions to be applied. These transactions are queued at the distribution database to be applied at the subscriber, after the signaling of Sync Complete.

In essence, choosing the manual synchronize option does not allow you to signal the completion of manual synchronization until the Sync task and the Distribution task have run at least once, after subscribing.

REFERENCES

SQL Server Books Online; search on: "Manual synchronization"