ALTER DATABASE ALTER REPLICA ​

Start, pause, or resume replication for a specific replica. Also use this command to modify other attributes of the replica.

ALTER DATABASE local_database_name
ALTER REPLICA replica_name
START [ WITH CHAIN chain_name ] |
PAUSE |
RESUME |
FORCE |
PROMOTE [ WITH DATA LOSS ] |
WITH (FREQUENCY seconds)

Syntax ​

local_database_name

Specify the name of a database on the source system whose replica you want to start or modify.

replica_name

Specify the unique name of the replica you want to start or modify.

START

Start running replication for the specified replica and its target database, as defined with the ALTER DATABASE ADD REPLICA command. The replication interval will be the frequency that was set when the replica was created.

WITH CHAIN chain_name

Optionally, specify an existing backup chain name. By default, the replica will have a backup chain name that is the same as the replica name, and you do not need to specify it when you start replication. When replication has started, the specific backup chain in use cannot be dropped or used for future backup or restore operations. The replication process depends on that chain for incremental restore operations to the replica database.

If NEW chain is specified, the remote database is seeded with a new backup chain. A new backup chain is created in the source database, and a full backup is transmitted and restored via the replication service.

You can query the sys.replica view to get information about the backup chains and replicas that are in use.

Note: If you seeded the replica from a backup, you must start replication with the same backup chain that the backup used.

PAUSE

Pause replication for the specified replica and its target database.

RESUME

Resume replication for the specified replica and its target database.

FORCE

Start replication now for the specified replica and its target database. Replication must be in PAUSED state in order for this command to run.

PROMOTE [ WITH DATA LOSS ]

Reverse the roles of the primary and secondary systems, using a reverse replica. If you specify the WITH DATA LOSS option, the PROMOTE command will not try to force replication from the primary to the secondary before reversing the roles. Data written to the source database on the primary system may not have been replicated and may be lost when replication starts in the reverse direction.

See also Replication Failover and Failback.

Note: A ROLLBACK DATABASE TO SNAPSHOT command is run automatically as part of an ALTER DATABASE...ALTER REPLICA...PROMOTE command. You cannot run multiple PROMOTE operations (or explicit ROLLBACK DATABASE operations) in parallel. Because of exclusive locks on shared dependencies (required by the rollback), the PROMOTE operations will block each other and fail. For example:

alter database repldb1 alter replica repldb1_failover promote;
alter database repldb2 alter replica repldb2_failover promote;
Error:
ERROR: Failed to promote replica
DETAIL: Unable to promote replica “repldb1_failover” Reason: ... ROLLBACK DATABASE failed to lock shared relations
...
ERROR: Failed to promote replica
DETAIL: Unable to promote replica “repldb2_failover” Reason: ... ROLLBACK DATABASE failed to lock shared relations

WITH (FREQUENCY seconds)

Change the frequency for a replica that is not running (paused or not started). Specify the number of seconds for the interval.

Examples ​

Start replication with the default backup chain:

premdb=# alter database premdb 
alter replica premdb_replica start;
START REPLICA

Start replication with a designated new backup chain:

premdb=# alter database premdb
alter replica premdb_replica
start with chain premdb_replica_chain;
START REPLICA

Attempt to start replication when a backup chain is already in use:

premdb=# alter database premdb 
alter replica premdb_replica start;
ERROR:  Backup chain for replica "premdb_replica" already exists. Did you mean to RESUME this replica or START WITH CHAIN?
premdb=# alter database premdb alter replica premdb_replica resume;
RESUME REPLICA

Alter the frequency of a paused replica:

premdb=# alter database premdb alter replica premdb_replica1 with (frequency 30);
ALTER REPLICA

See also the examples for ALTER DATABASE ADD REPLICA.

Parent topic:SQL Commands