I am testing a snapshot replication process (SQLServer 2008 (yes, unfortunately...)).

Everything is fine but I can't find in MS documentation how are tables updated. I saw that the publication process drops tables, then recreates them, and then populates it with data.

OK, nice. But is there a kind of security (locks, transactions...) that prevents a query to be made during this synchronization (in particular just between the drop table and the create)?

Let's imagine a database A (publisher) having a table T replicated on another database B (subscriber). When the distribution agent is running and table T is updated on server B (drop table T + create table T + bcp-in data ?), during a few milliseconds the table won't be there. If an application tries to read table T, it will get an error.

When I try to launch a trace on server B when distribution agent is running, I can't see any transaction made for this step. And indeed I can make a select on a replicated table T on server B (and get an error).

Is it really how it is supposed to work ?

Are there settings somewhere to avoid such behaviour ?

  • Do you mean during the snapshot process and you are referring to the subscriber when you say Imagine an application trying to access a published table: the table won't be there, the application will get an error. Everybody will be upset. ? Commented Jun 19, 2018 at 12:09
  • @SqlWorldWide: I mean: during publication process (not during snapshot process), if an application tries to access a published table on subscriber database, it may have an error (if just between drop and create).
    – irimias
    Commented Jun 19, 2018 at 12:21
  • I am really not sure what you mean by publication process? Commented Jun 19, 2018 at 12:53
  • I believe you are referring to re-initialization instead of publication. Once you have established replication and it's taking and applying snapshots it will not drop the table every time. Only if something went wrong with replication and you decided to reinitialize would it drop the table.
    – MrTCS
    Commented Jun 19, 2018 at 13:32
  • @SqlWorldWide: sorry if I'm not clear (that's a new topic for me). I tried to rephrase the question. Hope it will be better.
    – irimias
    Commented Jun 19, 2018 at 13:58

1 Answer 1


Is it really how it is supposed to work ?

Yes this how it is supposed to work. You only run snapshot at the beginning of setting up transactional replication. Unless you are out of sync due to any error you do not need to run snapshot again. If you are running snapshot replication ONLY you can control when you want to resync all your articles and nothing should connect to subscriber while snapshot is being applied.

From books online:

Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots may also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot.

Snapshot replication has a lower continuous overhead on the Publisher than transactional replication, because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to utilize snapshot replication.

When is a subscription available; when can the subscription database be used?

A subscription is available after the snapshot has been applied to the subscription database. Even though the subscription database is accessible prior to this, the database should not be used until after the snapshot has been applied. Use Replication Monitor to check the status of snapshot generation and application.

Are there settings somewhere to avoid such behaviour ?

If you are sure that your schema and data is already in sync or you only want future changes to get replicated you can use following option to avoid snapshot. I used this many time during upgrade with downtime.

@sync_type=replication support only

Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions, if appropriate. Assumes that the Subscriber already has the schema and initial data for published tables. When configuring a peer-to-peer transactional replication topology, ensure that the data at all nodes in the topology is identical. For more information, see Peer-to-Peer Transactional Replication.

Not supported for subscriptions to non-SQL Server publications.

  • Thx for your answer. But your second link is broken.
    – irimias
    Commented Jun 27, 2018 at 9:03
  • Fixed the link. Sorry about that. Commented Jun 27, 2018 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.