Post reply

SQL Server 2012 AlwaysOn - ASYNC mode

  • March 27, 2014 at 5:01 am

    #289828

    Hi Guys,

    When in ASYNC mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

    In this case, how do we know how far the secondary database lag behind the primary?

    If one day my primary DB crash at 10am... how do the data in secondary database is as of what time so as to inform customers?

    thanks

  • March 27, 2014 at 6:25 am

    #1700647

    chewychewy (3/27/2014)

    Hi Guys,

    When in ASYNC mode, the primary replica commits transactions without waiting for acknowledgement that an asynchronous-commit secondary replica has hardened the log. Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

    In this case, how do we know how far the secondary database lag behind the primary?

    If one day my primary DB crash at 10am... how do the data in secondary database is as of what time so as to inform customers?

    thanks

    you can use the AlwaysOn dashboard, alternatively query the system catalogs.

    select t.[replica_server_name]

    , t.[endpoint_url], t.[availability_mode_desc]

    ,t.[failover_mode_desc]

    , s.synchronization_state_desc

    , s.log_send_queue_size

    , s.log_send_rate

    , s.redo_queue_size

    , s.redo_rate

    from [sys].[dm_hadr_database_replica_states] s

    inner join [sys].[availability_replicas] t on s.replica_id = t.replica_id

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply