Product Documentation

Appearance

Pausing and Resuming Replication ​

You can use ALTER DATABASE ALTER REPLICA PAUSE and RESUME commands to stop and restart replication for a specific replica. A replica that has been started can be paused at any time and for any length of time. Pausing a replica stops replication for that specific replica only; replication for other replicas continues.

Important: When replication is paused, the system cancels the in-process replication cycle, if there is one, and rolls back the changes. When replication is resumed, replication restarts, and all of the changes since the last completed replication cycle are sent to the replica database. The initial replication cycle for a large database may take a very long time. Do not pause the replica during this cycle, or any subsequent cycle that you expect to run for a long time (given the flow of incremental writes).

The following formula provides a rough estimate of the amount of time required for a replication cycle to complete.

Total time (in seconds) = (compressed data size MB) / MIN(3200, Max Network Bandwidth) + (2 * number of tables)

This formula calculates the total time as the compressed data size (in megabytes) divided by the maximum network bandwidth. Two seconds of compile time per replicated table are added to the total, but this part of the formula varies a lot depending on whether the plan has been cached and the width of the tables. Wider tables generate plans that take longer to compile. Concurrency on the source and target systems may also affect the overall replication performance.

Note: The maximum network bandwidth is the maximum throughput of a given cluster in MB/s (megabytes per second, not Mb/s). The MIN() calculation is factored into the formula because bandwidth greater than 3.2 GB/s has not been observed on a 40 Gigabit link.

If a lot of write activity occurs on the source database, a paused replica database may fall far behind the source database. In turn, when you resume replication, it may take a long time for all of the pending updates to be replicated.

You can view the current state of a replica by querying the sys.replica view or by going to Monitor > Replication Processes in the SMC. For example:

PAUSE ​

The following command puts a replica in PAUSED state:

premdb=# alter database premdb alter replica premdb_replica1 pause;
PAUSE REPLICA

Pausing a replica may be useful in the following situations:

  • When you know that no updates are occurring on the source database, and you want to suspend replication cycles until they are necessary. Pausing replication will reduce resource consumption on the system.
  • When you want to modify the replica before resuming replication.
  • When you are going to perform a failover.

RESUME ​

The following command restarts replication for the specified replica. Replication for that replica returns to the RUNNING state, as shown in the results of the following sys.replica query:

premdb=# alter database premdb alter replica premdb_replica1 resume;
RESUME REPLICA
premdb=# SELECT * from sys.replica where name = 'premdb_replica1';
-[ RECORD 1 ]------------------+-------------------
replica_id                     | 16418
database_id                    | 16391
remote_server_id               | 16417
backup_chain_id                | premdb_replica1
name                           | premdb_replica1
last_replication_time          | [NULL]
status                         | RUNNING
frequency                      | 10
alias                          | premdb_replica1_db
...

The replica database must be left in HOT_STANDBY mode when replication is paused and your intention is to resume at some point. Replication cannot be resumed on a database that is not in HOT_STANDBY mode. If you have taken a replica database out of HOT_STANDBY mode and you want to resume replication on it, see Rolling Back a Replicated Database.

See Replication Failover and Failback.

Parent topic:Managing Replication