How subscriber table updated during snapshot replication?

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 ?

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.

StackExchange Database Administrators Q#210022, answer score: 4