SALES: 1-800-867-1380

Monitor a Continuous Copy Relationship

Updated: September 10, 2014

Microsoft Azure SQL Database provides a set of views and several PowerShell cmdlets that you can query to monitor a continuous copy relationship.

The Start-AzureSqlDatabaseCopy cmdlet runs asynchronously and returns immediately without waiting for completion. We recommend that you monitor the progress of creating the secondary database by monitoring its replication state. To check for the status of one or more continuous copy relationships that are active on the server, use the Get-AzureSqlDatabaseCopy cmdlet. For more information, see Get-AzureSqlDatabaseCopy.

The replication state can also be viewed on the Microsoft Azure Management Portal. The state can either be viewed on the secondary grid of the primary database, or on the replication role or status of the secondary.

The following states are shown:

  • Pending: The creation of the secondary and the seeding process is yet to start.

  • Seeding: The secondary database is being seeded and is in a transactionally inconsistent state.

  • Catch-up: The secondary is finished catching up to the primary database and is in a transactionally consistent state.

The primary and secondary databases will remain connected in a continuous copy relationship until an explicit termination command is issued. For more information, see Terminate a Continuous Copy Relationship.

Sometimes the secondary is not able to commit the transactions at the same rate as the primary database and is lagging behind. This may be due to high transaction rate on the primary database or large transaction sizes. If the secondary is not able to keep up, the client will experience high wait times while writing data to the primary. If this lag continues it may result in throttling and affect the primary. These waits are visible in sys.dm_db_wait_stats as SE_REPL_SLOW_FORWARDER_THROTTLE waits. To prevent transaction failures or timeouts, you must slow down the rate of incoming transactions to allow the secondary to catch up with the primary.

Use the following views to monitor a continuous copy relationship:

 

Task

View

Description

Obtain information about the status of operations on a given database.

sys.dm_operation_status

The sys.dm_operation_status view shows the status for all database operations including the continuous copy status.

Column names: operation, state, state_desc, percent_complete, error_desc, start_time, last_modify_time

Obtain information about the secondary databases on a given server.

sys.dm_database_copies (Azure SQL Database)

The sys.dm_database_copies dynamic management view in the master database returns information about all copies of each database on the Azure SQL Database server. This view contains a row for each primary database and for each of its continuous copy relationships. This view also returns one row at the source and one at the target during the seeding process of a Database Copy operation. This view resides in the logical master database.

Column names: database_id, start_date, modify_date, percent_complete, partner_server, partner_database, replication_state, replication_state_desc, maximum_lag, is_continuous_copy, is_target_role, is_interlink_connected is secondary.

noteNote
For information about replication state values, see "Replication State Values by View" later in this section.

Obtain information about all the secondary databases of a given primary database.

sys.dm_continuous_copy_status

The sys.dm_continuous_copy_status dynamic management view returns a row for each Azure SQL Database that is currently engaged in a continuous-copy relationship. This includes both primary and secondary databases. If more than one continuous copy relationship exists for a given primary database, this table contains a row for each of the relationships.

The sys.dm_continuous_copy_status view is created in the resource database and is visible in all databases, including the logical master. However, querying this view in the logical master returns an empty set.

Column names: partner_server, partner_database, last_replication, replication_state, replication_state_desc, last_replication, is_rpo_limit_reached, is_interlink_connected

noteNote
For information about replication state values, see "Replication State Values by View" later in this section.

Obtain information about the database state of a database that is being copied.

sys.databases (Azure SQL Database)

The sys.databases catalog view returns information about the database states that a database goes through when it is being copied. This catalog view contains one row per database in an Azure SQL Database server.

Column names: state, state_desc

You can call the Get-AzureSqlDatabaseCopy cmdlet to access sys.dm_database_copies view. You can call the Get-AzureSqlDatabaseOperation cmdlet to access sys.dm_operation_status.

You can send the Get Database Copy request to access sys.dm_database_copies view. You can send the Get Database Operation request to access sys.dm_operation_status.

To monitor the replication state for a new secondary, use sys.dm_database_copies. For an established secondary, use sys.dm_continuous_copy_status. These dynamic management views support overlapping sets of replication states, as follows:

 

State

Description

View

PENDING

When creation of the secondary is scheduled but the necessary preparation steps are not yet completed or are temporarily blocked by the seeding quota, the replication state is set to PENDING (replication_state = 0).

SEEDING

When the secondary is created, the replication state is set to SEEDING and is in a transactionally inconsistent state (replication_state = 1). Until seeding completes, you cannot connect to the secondary.

CATCH_UP

Once the secondary reaches a transactionally consistent state, the replication state is changed to CATCH_UP (replication_state = 2).

TipTip
In the Microsoft Azure Management Portal, when this state is reached the Status column shows Offline or Online, depending on whether you use Standard Geo-Replication or Active Geo-Replication.

RE_SEEDING

If an unrecoverable replication failure occurs on an existing secondary, the database is automatically reseeded. The replication state is set to RE_SEEDING (replication_state = 3).

SUSPENDED

If a continuous copy relationship becomes inactive, its replication state is set to SUSPENDED. However, the continuous copy relationship remains intact. (replication_state = 4).

The SUSPENDED state indicates the generally unhealthy state of the replication link.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft