Export (0) Print
Expand All

Planning for Snapshot Replication

SQL Server 2000

Snapshot replication requires planning in the following areas:

  • Transferring and storing snapshot files.

  • Scheduling snapshots.
Transferring and Storing Snapshot Files

You have the option of storing snapshot files in a location other than or in addition to the default location, which is often located on the Distributor. Alternate locations can be on another server, on a network drive, or on removable media (such as CD-ROM or removable disks). You can also save the snapshot files to a File Transfer Protocol (FTP) site for retrieval by the Subscriber at a later time.

Additionally, you can compress the snapshot files to improve network performance by writing data in the Microsoft® CAB file format. For more information, see Compressed Snapshot Files.

When planning to transfer and store snapshot files, estimate the disk space required at the snapshot file location and at the Subscriber that will receive the snapshot files.

The amount of space required for one snapshot can be affected by several factors including the size and number of articles published. You can create snapshot files in the default snapshot folder on the Distributor and in an alternate location. Compressing the snapshot files in the alternate location can reduce the overall space required.

When snapshot files are created in both the default folder and in an alternate location on the same drive, each file is created initially in the default folder and then copied to the alternate location. If you are using compressed snapshot files, the files are copied and compressed before they are placed in the alternate snapshot location. The total space required for all snapshot files in this situation is the size of the original snapshot files in the default location plus the size of the compressed snapshot files in the alternate location.

If the alternate storage location is on a different drive than the default location, the space required at the default location is the size of the snapshot files. The space required at the alternate location is the total size of the compressed snapshot files.

For more information, see Transferring Snapshots.

Scheduling Snapshots

Concurrent snapshot processing is provided for transactional replication, and an optimized merge snapshot generation is provided for merge replication. Concurrent snapshot processing is conceptually similar to how a database backup can be performed while updates on the database continue.

With concurrent snapshot processing and transactional replication, at the time the Snapshot Agent runs, it places temporary shared locks on the publication tables that are released quickly so that data modifications at the database can continue. The data modifications made at this time are included as part of the initial snapshot. The snapshot is applied at the Subscriber, and the Distribution Agent reconciles each captured transaction to see if it has already been delivered to the Subscriber. During this reconciliation, the tables on the Subscriber are also temporarily locked. 

To minimize the user from being temporarily unable to add to or update the table:

  • Choose the concurrent snapshot processing with transactional replication when possible. Shared locks on the Publisher are only held for seconds.

  • Identify times when the least amount of updates to data are needed and schedule the agent accordingly. Like a backup, the generation of the snapshot can be quite resource-intensive and that overhead will reduce the rest of the system performance during that time.

To plan the optimum schedule for running the Snapshot Agent, estimate the length of time it takes the Snapshot Agent to complete the snapshot. Because the snapshot is created using bcp, perform a test bulk copy of your data set and time how long it takes to complete. If your data set is very large, perform the bulk copy on a sample of the data set and extrapolate the lapse time to the entire data set.

Not applying a snapshot is another option if you are concerned about interrupting activity on your database. You can set up a Subscriber manually such as from a database dump. This is known as manually applying the initial snapshot.

See Also

Copying Data Between Different Collations

© 2014 Microsoft