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

 

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *