Dynamic Snapshots

Dynamic snapshots provide a performance advantage when applying the snapshot of a merge publication with dynamic filters. Performance is improved by using Microsoft® SQL Server™ 2000 bulk copy files to apply data to a specific Subscriber instead of a series of INSERT statements.

Generating a dynamic snapshot for a subscription also allows the flexibility of saving and transferring the snapshot on removable media (such as a CD-ROM) and applying the snapshot at the Subscriber from the media rather than applying the initial snapshot over a slow network connection.

How Dynamic Snapshots Work

When dynamic filters are used in merge publications, data is filtered from the publishing table based on the connection properties of the Merge Agent for the publication during the merge process. By default, dynamically filtered publications rely on INSERTs from the Publisher to apply data to the Subscriber as part of the initial snapshot. This can be a lengthy and resource-intensive process because the Merge Agent will have to determine row-by-row which data to include in the snapshot based upon the dynamic filter criteria.

Dynamic snapshots provide the performance advantage of using SQL bulk copy program (bcp) files to apply data to a specific Subscriber when applying the initial snapshot while using dynamic filters. When you create a dynamic snapshot, you pre-generate a snapshot that will be customized to a specified Subscriber. Because the data values are already copied and extracted, applying the snapshot will be just as fast as applying snapshots without dynamic filters. There is, however, additional time and space required when generating and storing the dynamic snapshot.

Although it takes longer to prepare a dynamic snapshot (you will need to generate two snapshots), the process of applying the snapshot at Subscribers is faster than applying a standard snapshot for a dynamically filtered merge publication. You will need to generate a standard snapshot first, before the dynamic snapshot is created by filtering the standard snapshot.

Dynamic snapshots can be implemented using SQL Server Enterprise Manager and the Create Publication and Create Dynamic Snapshot Job wizards, Transact-SQL system stored procedures and scripts, Microsoft ActiveX® controls or SQL-DMO.

Dynamic Snapshot Considerations

When planning for dynamically filtered merge publications and dynamic snapshots, consider:

• Dynamic snapshots can be used with all types of subscriptions. You can generate the dynamic snapshot using the Create Dynamic Snapshot Job Wizard and/or running the Snapshot Agent with the appropriate parameters. Applying a dynamic snapshot is done using the Merge Agent or Merge ActiveX Control and setting the DynamicSnapshotLocation properties.
  
  
• You can use the –DynamicSnapshotLocation command line parameter for the Merge Agent or the DynamicSnapshotLocation property in the Merge ActiveX Control to apply a pre-generated dynamic snapshot.
  
  
• Dynamic filters and dynamic snapshot are available only with merge replication.
  
  
• To generate a dynamic snapshot, the publication must be enabled for dynamic filters and a standard snapshot must be generated.
  
  
• Dynamic snapshot files will also be compressed if the standard snapshot is compressed. To compress a standard snapshot, and therefore the dynamic snapshot, open publication properties, and on the Snapshot Location tab, select Generate snapshots in the following location, specify a snapshot location in the text box, and then select Compress snapshot files in this location.
  
  
• The login specified as the value of the Publisher login must be in the Publication Access List (PAL) or be a member of the publication database sysadmin role or db_owner group. This login can be specified in the Create Dynamic Snapshot Job Wizard or by using the -DynamicFilterLogin parameter of the Snapshot Agent.
  
  
• Because SQL Server adds and drops temporary logins in the Snapshot Agent, the Publisher login of the Snapshot Agent must be a member of the securityadmin server role and be a member of the db_owner group on the publication database to be able to generate dynamic snapshots.
  
  
• Dynamic filter logins specified for dynamic snapshot generation must be members of the corresponding publication access list (PAL).
  
  
• SQL Server on the Publisher must be running under mixed security mode.
  
  
• Changing publication properties without regenerating a standard snapshot for a dynamically filtered publication will invalidate all subsequent dynamic snapshots that are generated.

For example, if you have a sales representative who receives customer management information based on a SalesPersonLogin, which is really the integrated login used at the Subscriber to connect to the Publisher. In this example, there are two users, DOMAIN\JohnSmith and DOMAIN\BobJohnson. The administrator of the Publication can specify the -DynamicFilterLogin property of the Snapshot Agent to be DOMAIN\JohnSmith and generate a dynamic snapshot for the user named John Smith. Similarly, they can specify the –DynamicFilterLogin property to be DOMAIN\BobJohnson and generate the snapshot for the user named Bob Johnson. However, the dynamic filter must be expressed using the SUSER_SNAME() function for this to occur.

If the dynamic filter used previously was SalesPersonLogin = SUSER_SNAME(), the dynamic filter must now be SalesPersonLogin = SUSER_SNAME() to use the dynamic snapshot functionality.

• Do not use parameters in the SUSER_SNAME() system function used with dynamic snapshots, such as 'SUSER_SNAME(SID)'.
  
  
• Functions that implicitly rely on SUSER_SNAME() or the current user, such as USER_NAME(), CURRENT_USER(), SYSTEM_USER(), USER_ID(), or SUSER_SID() will not work as expected and should not be used with dynamic snapshots (use SUSER_SNAME() or HOST_NAME() instead).
  
  
• You can use user-defined functions in a dynamic filter; however, if the user-defined filter evaluates to the same value for all Subscribers, it is a type of static filter, and there is no need to use dynamic snapshots because all Subscribers would receive the same snapshot of data.
  
  
• You can use the SUSER_SNAME() system function nested in a user-defined function in the filter criteria for a dynamic filter, and you can use a dynamic snapshot (for example, MyUDF(SUSER_SNAME()) where the MyUDF user-defined function evaluates the SUSER_SNAME() system function). The system function must be visible in the dynamic filter criteria. If the system function exists in the definition of the user-defined function, and you enter only the user-defined function in the dynamic filter, you will not be able to use a dynamic snapshot.

To create a dynamic snapshot

Enterprise Manager

SQL-DMO

To generate and apply a dynamic snapshot manually

1. Run the Snapshot Agent to generate the standard snapshot schema and other files. Use standard properties (for -Publisher, -PublisherDB, -Publication, and so on) when running the Snapshot Agent.
   
   
2. Run the Snapshot Agent to generate bulk copy (.bcp) files once for each Subscriber partition defined. Use the standard properties and the following properties:
   • -DynamicFilterHostName
     
     
   • -DynamicFilter Login
     
     
   • -DynamicSnapshotLocation
3. Run the Merge Agent for each subscription to apply the initial dynamic snapshot at the Subscribers. Use the standard properties and add the following properties:
   • -Hostname
     
     
   • -DynamicSnapshotLocation

See Also

DynamicSnapshotLocation Property

MergeDynamicSnapshotJob Object

SQLSnapshot Object