แก้ไข

แชร์ผ่าน

sys.dm_geo_replication_link_status (Azure SQL Database and Azure SQL Managed Instance)

  • บทความ

Applies to: Azure SQL Database Azure SQL Managed Instance

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 master database. However, querying this view in the master database 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 time when the primary received the acknowledgment that the last log block has been hardened by the secondary, based on the primary database clock. Log blocks are sent to the geo-secondary continuously, without waiting for transactions to commit on the primary. 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

SUSPENDED
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 replication 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, the row for that database in the sys.dm_geo_replication_link_status view disappears.

Permissions

Requires the VIEW DATABASE STATE permission in the database.

Examples

This Transact-SQL query shows replication lags and last replication time of secondary databases.

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

Next steps

Learn more about related concepts in the following articles: