How Automatic Failover Works in DB Mirroring?

If the principal server is still running, it changes the state of the principal database to DISCONNECTED and disconnects all clients from the principal database.

The witness and mirror servers register that the principal server is unavailable.

If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database.

The former mirror database moves online as the new principal database, and recovery cleans up all uncommitted transactions by rolling them back as quickly as possible.

When the former principal server rejoins the session, it recognizes that its failover partner now owns the principal role. The former principal server takes on the role of mirror, making its database the mirror database. The new mirror server synchronizes the new mirror database with the principal database as quickly as possible.

What is Automatic Page Repairing?

After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) attempts to automatically recover the page. The partner/replica that cannot read the page requests a fresh copy of the page from its partner. If this request succeeds, the unreadable page is replaced by the readable copy, and this usually resolves the error, this whole process is called Automatic Page Repair.

What are the page types which are not repairable using Automatic Page repair option?

File header page (page ID 0).

Page 9 (the database boot page).

Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages.