Enhancing Snapshot Replication Performance

SQL Replication


Enhancing Snapshot Replication Performance

You can enhance the performance of snapshot replication in your application and on your network by:

  • Using a quality disk subsystem.

  • Using a single snapshot folder per publication.

  • Using compressed snapshots.

  • Using native bcp.
Use a Quality Disk Subsystem

Because snapshot replication bulk copies a complete copy of the publication, it writes the entire publication to the snapshot folder. The faster the disk subsystem can read and write data to the disk(s), the faster the snapshot is completed.

Using a Single Snapshot Folder Per Publication

When specifying the publication properties related to snapshot location, you can choose to generate snapshot files to the default snapshot folder, an alternate snapshot folder, or both. Generating snapshot files in both locations requires additional processing when the Snapshot Agent runs. This takes more time than generating the snapshot files to a single location for the publication.

For more information, see Alternate Snapshot Locations.

Consider Using Compressed Snapshots

Compressing snapshot files in the alternate snapshot folder can reduce snapshot disk storage requirements and, in some cases, improve the performance of transferring snapshot files across the network when they are used for replication over the Internet. However, compressing the snapshot requires additional processing by the Snapshot Agent when generating the snapshot files and by the merge agent when applying the snapshot files. This may slow down snapshot generation and increase the time it takes to apply a snapshot in some cases. Consider these tradeoffs carefully when using compressed snapshots.

For more information, see Compressed Snapshot Files.

Consider Using Native bcp

When you are not using ODBC or OLE DB Subscriber or using transformable subscriptions and you have a large volume of data, consider using native bcp mode to apply snapshot files to Subscribers. Storing information in native format is useful when information must be copied from one instance of Microsoft® SQL Server™ to another.