sys.dm_geo_replication_link_status (Azure SQL Database)

APPLIES TO: noSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Contains a row for each replication link between primary and secondary databases in a geo-replication partnership. This includes both primary and secondary databases. If more than one continuous replication link exists for a given primary database, this table contains a row for each of the relationships. The view is created in all databases, including the logical master. However, querying this view in the logical master returns an empty set.

Column name Data type Description
link_guid uniqueidentifier Unique ID of the replication link.
partner_server sysname Name of the SQL Database server containing the linked database.
partner_database sysname Name of the linked database on the linked SQL Database server.
last_replication datetimeoffset The timestamp of the last transaction's acknowledgement by the secondary based on the primary database clock. This value is available on the primary database only.
replication_lag_sec int Time difference in seconds between the last_replication value and the timestamp of that transaction's commit on the primary based on the primary database clock. This value is available on the primary database only.
replication_state tinyint The state of geo-replication for this database, one of:.

1 = Seeding. The geo-replication target is being seeded but the two databases are not yet synchronized. Until seeding completes, you cannot connect to the secondary database. Removing secondary database from the primary will cancel the seeding operation.

2 = Catch-up. The secondary database is in a transactionally consistent state and is being constantly synchronized with the primary database.

4 = Suspended. This is not an active continuous-copy relationship. This state usually indicates that the bandwidth available for the interlink is insufficient for the level of transaction activity on the primary database. However, the continuous-copy relationship is still intact.
replication_state_desc nvarchar(256) PENDING

SEEDING

CATCH_UP
role tinyint Geo-replication role, one of:

0 = Primary. The database_id refers to the primary database in the geo-replication partnership.

1 = Secondary. The database_id refers to the primary database in the geo-replication partnership.
role_desc nvarchar(256) PRIMARY

SECONDARY
secondary_allow_connections tinyint The secondary type, one of:

0 = No direct connections are allowed to the secondary database and the database is not available for read access.

2 = All connections are allowed to the database in the secondary repl;ication for read-only access.
secondary_allow_connections_desc nvarchar(256) No

All
last_commit datetimeoffset The time of last transaction committed to the database. If retrieved on the primary database, it indicates the last commit time on the primary database. If retrieved on the secondary database, it indicates the last commit time on the secondary database. If retrieved on the secondary database when the primary of the replication link is down, it indicates until what point the secondary has caught up.

Note

If the replication relationship is terminated by removing the secondary database (section 4.2), the row for that database in the sys.dm_geo_replication_link_status view disappears.

Permissions

Any account with view_database_state permission can query sys.dm_geo_replication_link_status.

Example

Show replication lags and last replication time of my secondary databases.

SELECT   
     link_guid  
   , partner_server  
   , last_replication  
   , replication_lag_sec   
FROM sys.dm_geo_replication_link_status;  

See Also

ALTER DATABASE (Azure SQL Database)
sys.geo_replication_links (Azure SQL Database)
sys.dm_operation_status (Azure SQL Database)