Monitor Active Geo Replication
If you want to know Active Geo Replication from scratch you can read its introduction, the step by step how to configure Active Geo Replication, steps to fail-over & most important Active Geo Replication process deepdive. Once you know the concept you are good to implement it in your own environment. But implementation is just one side of the coin, the other side is also indispensable which is having good knowledge about the ways to monitor Active Geo replication. In this blog post we’ll see different ways to monitor active Geo replication health and configuration using scripts.
Monitor Active Geo Replication
- Sys.dm_geo_replication_links DMV
Dynamic Management View Sys.dm_geo_replication_links resides in Master database & returns information about all existing replication links for each database on Azure SQL Database logical server. A few columns to pay attention are partner_server, partner_database, database_id, replication_state, replication_state_desc, role, role_desc, secondary_allow_connections & secondary_allow_connections_desc and all the time mentioned is UTC time at a regional SQL Database datacenter
replication_state & replication_state_desc
0 = Pending. Creation of the active secondary database is scheduled but the necessary preparation steps are not yet completed.
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.
secondary_allow_connections & secondary_allow_connections_desc
0 = No. The secondary database is not accessible until failover.
1 = All. The secondary database is accessible to any client connection.
2 = ReadOnly. The secondary database is accessible only to client connections with ApplicationIntent=ReadOnly.
SELECT database_id,
start_date, modify_date, partner_server, partner_database,
replication_state_desc,
role, secondary_allow_connections_desc FROM [sys].geo_replication_links;
2. Sys.dm_replication_link_status DMV
Dynamic Management View sys.dm_replication_link_status shows a row for each replication link between primary & secondary databases in an Active Geo Replication partnership. This view gets created in all databases including the logical master database but in order to get the values you must execute it in the scope of a user database which is taking an active part in Active Geo Replication, running this in master database will return empty set. (Zero rows) Here are a few columns to pay close attention: partner_server, partner_database, last_replication, replication_lag_sec, replication_state, role, role_desc.
last_replication
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
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:
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 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 interlink is insufficient for the level of transaction activity on the primary database. However, the continuous-copy relationship is still intact.
SELECT link_guid, partner_server, last_replication,
replication_lag_sec FROM
sys.dm_geo_replication_link_status
3. Sys.dm_operation_status DMV
Dynamic Management View sys.dm_operation_status shows the status of all database operations including the status of the replication links. This DMV exists in Master database & helps you to track the status of the following operations performed on a SQL Database:
- Create database
- Copy database. Database Copy creates a record in this view on both the source and target servers.
- Alter database
- Change the performance level of a service tier
- Change the service tier of a database, such as changing from Basic to Standard.
- Setting up a Geo-Replication relationship
- Terminating a Geo-Replication relationship
- Restore database
- Delete database
These are the few important columns in the result set: major_resource_id, operation, state, state_desc, percent_complete, & error related columns like (error_code, Error_desc, error_severity, Error_state)
major_resource_id: Name of the SQL Database on which the operation is performed.
State:
0 = Pending, operation is waiting for resource or quota availability.
1 = in progress, operation has started and is in progress.
2 = Completed, operation completed successfully.
3 = Failed, operation failed. See the error_desc column for details.
4 = Cancelled, operation stopped at the request of the user.
SELECT major_resource_id,operation,state_desc,percent_complete
FROM sys.dm_operation_status where major_resource_id =
‘DDM_Sarab’ ORDER BY start_time DESC
Hope you got answer to your questions! Happy Learning!
feel free to leave a comment. 🙂
Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp
Follow @Sarab_SQLGeek