SALES: 1-800-867-1380

Monitor a Continuous Copy Relationship

Updated: July 31, 2014

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

In This Topic

Monitor the Replication State of the New Active secondary

The Start-AzureSqlDatabaseCopy cmdlet runs asynchronously and returns immediately without waiting for completion. Because data seeding is an I/O intensive process, a seeding quota limits the number of concurrent seeding operations per physical node. When this quota is reached, new copy requests are placed in an internal request queue. Therefore, we recommend that you monitor the progress of creating the active 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 active secondary grid of the primary database, or on the replication role or status of the active secondary.

The following states are shown:

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

  • Seeding: The replication target is being seeded and is in a transactionally inconsistent state.

  • Active: 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.

Lagging Secondary Database

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 SLOW_SECONDARY_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.

System Views

Use the following views to monitor a continuous copy relationship:





Obtain information about the bandwidth used by each database in your Azure SQL Database server.

sys.bandwidth_usage (Azure SQL Database)

The sys.bandwidth_usage catalog view exposes information about bandwidth usage. The total cost of a continuous copy relationship includes the bandwidth required for continuous replication. You can use the sys.bandwidth_usage system view to estimate the total cost of network connectivity for your application. For Geo-Replication, bandwidth-usage data is visible in the logical master database on both sides of a given continuous copy relationship. Therefore, you need to interpret the ingress and egress direction indicators from the perspective of the logical server that you are querying.

Column names: time, database_name, direction, class, time_period, quantity

sys.bandwidth_usage returns a number of rows for each database per hour. For an Active Geo-Replication primary database, an additional row is returned for the Interlink class (class =Interlink).

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 relevant for Active Geo-Replication monitoring: state, state_desc

Obtain information about the active secondary databases on a given server.

sys.dm_database_copies (Azure SQL Database)

The sys.dm_database_copies dynamic management view 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 relevant for Geo-Replication monitoring: 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 offline secondary.

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.


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 active 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

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

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


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

SQL Database PowerShell Cmdlet

To monitor a continuous copy relationship, you can use the following cmdlet:





Check for the status of one or more continuous copy relationships in a given server context.


Use the Get-AzureSqlDatabaseCopy cmdlet to check the status of continuous copy relationships. This cmdlet is supported on both the primary and secondary servers. If the continuous copy relationship has been terminated, the Get-AzureSqlDatabaseCopy cmdlet returns an empty result set.

After calling Start-AzureSqlDatabaseCopy or Stop-AzureSqlDatabaseCopy, we recommend that you verify its status by calling Get-AzureSqlDatabaseCopy to verify success of the commands.

For more information about Get-AzureSqlDatabaseCopy, see Get-AzureSqlDatabaseCopy.

Replication State Values by View

To monitor the replication state for a new active 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:






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).


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.


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

In the Microsoft Azure Management Portal, this state is referred to as Active.


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


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 usually indicates that the bandwidth available is insufficient for the amount of transaction activity on the primary database.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2014 Microsoft