How to Find Time Difference Between Primary and Secondary Database in SQL Server Always On Architecture

dbtut

6 years ago

If you define the availability group asynchronous in the SQL Server Always ON architecture, the secondary database is slightly behind.

You can find details about Always On in my article “SQL Server Always ON Availability Group“.

The difference between the secondary database and the primary database is related to the application’s transaction intensity.

For example, if the application contains very intensive insert, update, and delete operations, it may take some time for the processes to be transferred to the secondary database.

Sometimes, even if you define the availability group as synchronous, the databases may appear synchronous, but the secondary may be behind for various reasons. For example, the slowness of the disk on the secondary server.

For the secondary database to be fully synchronized, the log records from the primary server must be written to the disk (log hardened operation).

Then the records written in the log files need to be written to the data files (redone process)

Commit process is also done with the redone process.

If the log hardened process occurs, there is no data loss. Because data is somehow transferred to the secondary. SQL Server determines the synchronization status according to the log hardened process.

So if the log hardened process is complete, and you are using the sync availability group, you see synchronized on the dashboard.

But in some cases, even if the log hardened process has been completed, the redone operation may be behind.(Disk slowness, etc.)

In this case, assume that you see the availability group synchronized.

If you try to perform a failover, you may have to wait for hours to complete the failover.

Because the “redone” process is incomplete. So the records written to the log file are not processed in the data file.

Therefore, the secondary database needs to be recovered to become a primary.

That’s why last redone time and last commit time has a serious importance for us before failover.

With the help of the following script, you can find the real difference between the Secondary database and the primary database. You must run this script on the secondary server.

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, DB_NAME(dr_state.database_id) as database_name, 
dr_state.last_hardened_time,dr_state.last_redone_time,dr_state.last_commit_time, datediff(s,last_commit_time, 
getdate()) as 'secondary sunucunun geriden geldiği süre SN' 
FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) 
JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id) 
JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id 
and dr_state.replica_id = ar_state.replica_id
where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1
order by last_commit_time asc

SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, DB_NAME(dr_state.database_id) as database_name,

dr_state.last_hardened_time,dr_state.last_redone_time,dr_state.last_commit_time, datediff(s,last_commit_time,

getdate()) as 'secondary sunucunun geriden geldiği süre SN'

FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id )

JOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id)

JOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id

and dr_state.replica_id = ar_state.replica_id

where ar_state.role_desc='SECONDARY' AND ar_state.is_local=1

order by last_commit_time asc