0

I am using Jonathan Kehayias - Monitoring Availability Group Replica Synchronization


SELECT 
    ar.replica_server_name, 
    adc.database_name, 
    ag.name AS ag_name, 
    drs.is_local, 
    drs.is_primary_replica, 
    drs.synchronization_state_desc, 
    drs.is_commit_participant, 
    drs.synchronization_health_desc, 
    drs.recovery_lsn, 
    drs.truncation_lsn, 
    drs.last_sent_lsn, 
    drs.last_sent_time, 
    drs.last_received_lsn, 
    drs.last_received_time, 
    drs.last_hardened_lsn, 
    drs.last_hardened_time, 
    drs.last_redone_lsn, 
    drs.last_redone_time, 
    drs.log_send_queue_size, 
    drs.log_send_rate, 
    drs.redo_queue_size, 
    drs.redo_rate, 
    drs.filestream_send_rate, 
    drs.end_of_log_lsn, 
    drs.last_commit_lsn, 
    drs.last_commit_time
FROM sys.dm_hadr_database_replica_states AS drs
INNER JOIN sys.availability_databases_cluster AS adc 
    ON drs.group_id = adc.group_id AND 
    drs.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups AS ag
    ON ag.group_id = drs.group_id
INNER JOIN sys.availability_replicas AS ar 
    ON drs.group_id = ar.group_id AND 
    drs.replica_id = ar.replica_id
ORDER BY 
    ag.name, 
    ar.replica_server_name, 
    adc.database_name;

and getting this result

I have already backed up and restored the last differential and the last transaction log back (and disabled the jobs so that none more would be taken in the while)

but still my database is not synchronizing

availability group properties

enter image description here

what can I do to start synchronizing as it should? what is missing?

if nothing then I think I will remove the database from the AG and add it back by automatic seeding

So far I have restored all the backups up to the last log and this is the situation. it is a big database 1.8 TB.

1 Answer 1

0

Basically what I did:

1 - disabled all backup jobs in the primary

2 - on the primary server right click on the availability group name and removed the database from the availability group

enter image description here

3 - take a differential backup and a log backup

4 - on the secondary restore the diff and the log.

in my case here, the diff was not restored, sql complained but the log was restored as you can see on the picture below.

5 - after that - on the primary server - I added the database to the availability group with JOIN ONLY

and all worked fine.

Now the same query (from the question) gives me the following result:

run from a different server that-s why the background is different:

enter image description here


------------------------------------------------------------------------------------------------------------
-----------//Differential backup restore [my_database]
------------------------------------------------------------------------------------------------------------
 
-- @DB_backup_finish_date: 14 Feb 2024 11:43:21
-- @MediaSetIdStopAT....: 35635
 
RESTORE DATABASE [my_database]
 FROM DISK = '\\my_server\SQLBackups\my_ag_my_database001\my_database\DIFF\my_ag_my_database001_my_database_DIFF_20240214_114319.bak'
 WITH FILE = 1
,STATS=1,NORECOVERY 

 
 
------------------------------------------------------------------------------------------------------------
-----------//Log backup restore [my_database]
---- @BackupSetID 35635 
---- @MediaSetIdStopAT 35635 
------------------------------------------------------------------------------------------------------------
 
-- @MediaSetIdStopAT: 35635
-- @LogMediaSetID....: 35635
-- @backup_finish_date 14 Feb 2024 11:43:40
RESTORE Log [my_database]
FROM DISK = '\\my_server\SQLBackups\my_ag_my_database001\my_database\LOG\my_ag_my_database001_my_database_LOG_20240214_114340.trn'
WITH FILE = 1
,STATS=1,NORECOVERY

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.