Home > Articles > Data > SQL Server

  • Print
  • + Share This
From the author of Dynamic Snapshots

Dynamic Snapshots

Merge replication allows you to filter the data you send to a subscriber. You might do this for security reasons, so that each subscriber gets only a minimal data set. But this feature is used more often to limit the snapshot size, and not only for bandwidth reasons. For example, publication databases often run to many gigabytes, whereas the subscribers may run SQL Server Express, which has a 4GB data file limit.

Dynamic snapshots allow you filter the publication data so that each subscriber receives only the bare minimum of data needed to do his or her job. You can filter on Host_name (which can be overridden by the HostName parameter), or SUSER_Name (which can be overridden by the PublisherLogin parameter).

Dynamic snapshots offer several choices for snapshot generation. First I'll highlight what not to do, as that makes it much easier to explain the other options.

When you've created your publication and generated the first snapshot, you'll notice an option to define a partition automatically (see Figure 2, in the lower-right corner of the window). If selected, this feature generates a snapshot whenever a new subscriber tries to synchronize. Use this option when you don't know in advance which subscribers will want to synchronize with your data.

Figure 2 Select the checkbox at the bottom to define a partition and generate a snapshot automatically.

With this option, a dynamic snapshot will be generated when the subscriber connects for the first time. Use this feature with caution: With clients in Oceania, I've seen snapshot deployments from the U.S. East Coast with 1GB snapshots take more than 15 hours. (As luck would have it, there was a network hiccup in the middle, and I had to start all over.) There's no option for you to compress this data to minimize the size of the snapshot; therefore, I recommend that you skip this option. Instead, create your pull subscription as usual, keeping in mind that you're under no real pressure to send the snapshot to your subscribers immediately.

If you select the option for automatic snapshot generation, a snapshot is generated synchronously with subscriber creation. Without this option enabled, you have a lot more control. You can create your subscription on the publisher and subscriber, and then generate the snapshot on the publisher, compress it (manually or using your favorite programming language), place it on your FTP site, have an agent on your subscriber download the compressed snapshot, expand it, and then use the dynamicsnapshotlocation parameter of the merge replication binary to point to the location of the decompressed files.

  • + Share This
  • 🔖 Save To Your Account