SQL Server Snapshot Replication – How Subscriber Table is Updated

replicationsql server

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 ?

Best Answer

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.

Related Question