Search code examples
sqlsql-serverfailoverdatabase-mirroring

How long does a sql mirror failover take to complete?

We are mirroring our database and I would like to know how long it would take to complete a failover from the time that I click "failover" in sql?

Solution

  • You need to understand .,how mirroring works before that..

    Mirroring transfers log blocks over wire.once the log is hardened on secondary,Primary will commit the log only then(synchronous mode).They also keep track of the last LSN ,both recieved in case of failovers/unexpected shutdowns..

    So when you failover,*minimum expected recovery time is the time that is need to REDO/UNDO the log on secondary*

    This is a high level overview,it also depends on so many things like your mirroring mode,automatic failover/manual failover..

    You can also use performance counters to check REDO queue size and log that needs to be sent to mirror yet..

    select * from sys.dm_os_performance_counters where object_name like ‘%mirror%’

    Which gives us..

    enter image description here

    here is good place to learn more....

    https://technet.microsoft.com/en-us/library/ms189590(v=sql.105).aspx