When you create a subscription for a table replication definition, rows that fit the subscription are copied from the primary to the replicate table in a process called materialization. After materialization is complete, Replication Server distributes row changes in the primary database through normal replication.
If a subscription involves many rows, materialization can hold locks for a long time and overload the network. Replication Server queues may also fill with data. To avoid these problems, Replication Server provides four different ways to materialize a subscription.
You can use any method for subscriptions for table replication definitions or for publications. Use nonmaterialization or bulk materialization for subscriptions for function replication definitions.
Atomic materialization is the default method for table replication definitions. Replication Server selects rows at the primary table, using a holdlock, and copies them over the network. The primary table is locked during materialization and data is consistent between the primary and replicate tables.
In nonatomic materialization, Replication Server selects rows at the primary table, without using a holdlock, and copies them over the network. Because the primary table is not locked, the replicate may go through visible steps that did not exist at the primary while nonatomic materialization is in progress.
In nonmaterialization, the primary and replicate data is already in sync. You do not need to copy data over the network or load it from media. No updates can be in process while such a subscription is created.
In bulk materialization, data is manually unloaded and loaded from media. This is the most efficient way to materialize subscriptions that involve a large amount of data.
For more information about subscription materialization methods, see the Replication Server Administration Guide Volume 1.
Use these commands to create a subscription and initialize data at the replicate database:
create subscription – creates and materializes a subscription using atomic materialization.
create subscription ... without holdlock – creates and materializes a subscription using nonatomic materialization.
If you use nonatomic materialization, which selects primary data without a holdlock, you must also use:
set autocorrection – prevents failures caused by missing or duplicate rows in a replicate table. When primary data is selected without a holdlock, it might be updated before materialization is complete and before normal transaction replication begins.
Use this command to create a subscription when data is already in sync at the replicate database:
create subscription ... without materialization – creates a subscription without materializing data at the replicate database.
Bulk materialization is used to manually coordinate subscription status and to transfer data for function replication definitions.
Use these commands for bulk materialization:
define subscription – adds a subscription to the system tables at the primary and replicate Replication Server.
activate subscription – starts the distribution of updates from the primary database to the replicate database and sets the subscription status to ACTIVE.
After you use this command and verify status, manually load initial data from media into the replicate database. Use the with suspension option to prevent data from being applied to the replicate database until the load from media is complete.
validate subscription – completes bulk materialization and changes the subscription status to VALID. Replication Server is notified that materialization is complete.
