Use this bulk materialization method when you cannot suspend updates to the primary database.
This method ensures replicated data consistency by retrieving the subscription data, activating the subscription, and suspending the DSI connection to the replicate database all in one transaction at the primary data server.
Use select with holdlock and the rs_marker stored procedure, as in this example:
begin transaction select from table with holdlock where search_conditions execute rs_marker 'activate subscription subid with suspension' commit transaction
subid is an integer that identifies the subscription. The subid for a subscription and can be found in the subid field of the rs_subscriptions system table in the RSSD. After the subscription is defined, you can find its subid by executing the following query in the RSSD of the primary or replicate Replication Server:
select subid from rs_subscriptions where subname = 'subscription' and dbid in (select dbid from rs_databases where dbname = 'replicate_database' and dsname = 'replicate_data_server')
Here are the steps to follow to simulate atomic materialization:
Verify that the entire replication system is working. Refer to Chapter 1, “Verifying and Monitoring Replication Server” in the Replication Server Administration Guide Volume 2 for details.
Execute the define subscription command at the replicate Replication Server.
Wait for the subscription to be defined at both the primary and replicate Replication Servers. Execute the check subscription command at both the primary and replicate Replication Servers to verify that the subscription status is DEFINED.
Execute a single transaction as provided in the previous sample transaction that includes select with holdlock and the rs_marker stored procedure. This action activates the subscription.
Wait for the subscription to become active at both the primary and replicate Replication Servers. Execute the check subscription command at the replicate Replication Server to verify that the subscription status is ACTIVE. When the subscription status is ACTIVE at the replicate Replication Server, the DSI connection to the replicate database will be suspended.
Begin loading the subscription data into the replicate database.
Resume the DSI connection to the replicate database using the resume connection command.
Execute the validate subscription command at the replicate Replication Server.
Wait for the subscription to become valid at both the primary and replicate Replication Server. Execute the check subscription command at the replicate Replication Server to verify that the subscription status is VALID.
Now the subscription is created and replication is active.
