Improving Performance While Generating and Applying Snapshots

Depending on the amount of data in your publication and your network connection and resources, applying the initial snapshot to Subscribers can be time- and resource-consuming. Concurrent snapshot processing for transactional replication, dynamic snapshots (merge replication) and the –UseInprocLoader property have been added to Microsoft® SQL Server™ 2000 to improve performance while generating the initial snapshot and applying it at Subscribers.

Concurrent Snapshot processing for Transactional Replication

Typically, with snapshot generation, SQL Server places shared locks on all tables published as part of replication for the duration of snapshot generation. This can prevent updates from being made on the publishing tables. Concurrent snapshot processing, available only with transactional replication, does not hold the share locks in place during the entire snapshot generation, thus allowing users to continue working uninterrupted while SQL Server 2000 creates initial snapshot files.

When you create a new publication using transactional replication and indicate that all Subscribers will be instances of SQL Server 7.0 or SQL Server 2000, concurrent snapshot processing is enabled automatically.

For more information, see How Transactional Replication Works.

Snapshot Processing for Merge Replication

For merge replication, the process is similar to concurrent snapshot processing for transactional replication because locks are in place only for the duration of the copy of the merge contents table. The tables are not locked when the snapshot is being bulk copied and updates at the publication database are not prevented for the duration of the entire snapshot.

Dynamic Snapshots

Dynamic snapshots provide a performance advantage when applying the snapshot of a merge publication with dynamic filters. By using SQL Server 2000 bulk copy programming files to apply data to a specific Subscriber instead of a series of INSERT statements, you will improve the performance of applying the initial snapshot for dynamically filtered merge publications.

For more information, see Dynamic Snapshots.

Add a ROWGUIDCOL to Merge Publications

By planning ahead and creating a column that can be used to help track changes during merge replication, you will avoid the sometimes significant time (and disk and log) decrease in performance that could occur from waiting for the Snapshot Agent to alter the tables for you.

Merge replication requires that each published table have a ROWGUID column. If a ROWGUID column does not exist in the table before the Snapshot Agent creates the initial snapshot files, the agent must first add and populate the ROWGUID column. To gain a performance advantage when generating and applying snapshots during merge replication, create the ROWGUID column on each table published during merge replication. When creating the column, specify:

• The column title as ROWGUID.
  
  
• The data type as UNIQUEIDENTIFIER.
  
  
• The default as NEWID().
  
  
• The ROWGUIDCOL property.
  
  
• An index on the column.

The ROWGUID column is used frequently for relating to merge tracking data during tracking and synchronization of changes made at the Publisher and at Subscribers.

-UseInProcLoader

The –UseInprocLoader agent property improves performance of the initial snapshot for snapshot replication, transactional replication, and merge replication. 

When you apply this property to either the Distribution Agent (for snapshot replication or transactional replication) or the Merge Agent (for merge replication), the agent will use the in-process BULK INSERT command when applying snapshot files to the Subscriber.

The –UseInprocLoader property cannot be used with character mode bcp, and it cannot be used by OLE DB or ODBC Subscribers.

Important  When using the –UseInprocLoader property, the SQL Server 2000 account under which the Subscriber is running must have read permissions on the directory where the snapshot .bcp data files are located. When the –UseInprocLoader property is not used, the agent (for heterogeneous Subscribers) or the ODBC driver loaded by the agent (for SQL Server 2000 Subscribers) reads from the files, so the security context of the Subscriber SQL Server 2000 account is not used.

To set the –UseInprocLoader property