To acknowledge completion of a manual synchronizationBefore a subscription server can receive replicated transactions from an article or a publication, the publication and destination tables must contain the same schema and data. The process of accomplishing this is called initial synchronization. Synchronization ensures that the table schema and the table data in the publication and destination databases are made identical. Synchronization affects new subscribers only.
Replication allows updates to be applied to a subscriber only after the subscriber is synchronized (has a current snapshot of the table schema and data).
If you choose the automatic synchronization option for a subscription, synchronization will occur automatically, and you do not need to take any special action. However, if you choose the manual synchronization option for a subscription, before replication can begin for that subscription, you must manually synchronize the published tables on the publication server with the destination tables in the destination database on the subscription server.
Manual synchronization might be accomplished, for example, by loading files from a tape that has been mailed to a remote server location. Manual synchronization is particularly useful when the publication and subscription servers are connected by a slow, expensive, or low-quality network connection or when a table being published is very large.
SQL Server automatically creates synchronization files and places them in the publication server's distribution working directory. There are two files for each synchronization job for each published article: a schema script (an .SCH file) and a data file (a .TMP file).
You can determine the name and location of the schema synchronization file for an article by opening the Manage Article dialog box for that article, choosing the Scripts tab, and reading the entry in the Creation Script box. This box contains the full path to the schema script in the distribution working directory. For information about accessing the Manage Article dialog box, see Publishing, earlier in this chapter.
For example, suppose the server TERRIER is a combined publisher/distributor; has its distribution working directory set to C:\SQL60\REPLDATA; and has a publication database named sales, a publication named receipts, and a published table named charges. Then the following could be displayed for the article in the Creation Script box of the Manage Article dialog box:
\\TERRIER\C$\SQL60\REPLDATA\terrier_sales_receipts_charges.sch
To determine the name of the synchronization data file, you can view the task history for the sync task. This history gives the exact .TMP filename created. (Also, when a destination database is waiting on a manual synchronization, the full name and path of the .TMP file will be contained in the description column of the MSlast_job_info table in the destination database of the subscriber. The article and publication names are also contained in that table.) For information on viewing task history, see Chapter 16, Scheduling Tasks.
After you have created a table on the destination server with the schema from the creation script and have populated it with the data from the data file, you must inform SQL Server that the manual synchronization is complete.
Note that when a destination database is waiting on a manual synchronization, it cannot receive any updates to any of the other publications it is subscribed to (not just the publication that is waiting for a manual synchronization).
To acknowledge completion of a manual synchronization
The Manage Subscriptions dialog box appears.
A list of that database's publications appears.
A confirmation dialog box appears. If the distribution process is waiting for the manual synchronization of this article, the Sync Completed button is enabled. If not, it is dimmed.
SQL Server has been informed that the subscription server's destination database is synchronized with the publication or article(s). The replication processes are now allowed to send replication transactions (changes to data in the synchronized articles) to the destination database.
For more information about manual synchronization, see Chapter 13, Replication Concepts.