Database Mirror is like RAID 1, it creats and maintains a redudence copy of a database in another SQL instance. The key point of using datbase mirroring is to improve high availability of production database. In terms of high availability, Database mirroring will keep exactly same data, and transaction log between Primary and Secondary. While either intentional offline like system upgrade or maintainance or unintentional like power outage, database mirroring will bring secondary up online as Primary without data loss.
Depends on configaration, you can set a primary, a secondary server and a optional witness server to comnuicate between session. You can configure database mirroring with synchronous or asynchronous mode. Actually, database mirroring was called continuous real-time log shipping. instead of manually kick off log shipping, database mirroring session is established at database starting up, then log manager will determine if there is new log at tail should be ready to log shipping.
As I mentioned above, the database mirroring is based on existing feature: log shipping, and extended it continuous real-time operation. So what is the trick? Let’s start it with each component.
- Log Manager: Each database has a log manager when it created, and logMgr will be initiated when database start up. When we configure Database Mirroring session, we enable DBMirroring flag at both primary and secondary database side’s log manager.
- LogShipping Manager: In the meanwhile, a log shipping manager will created for each log manager.
- Logshipping StateMachine: Log shipping manager’s behavior will be controlled by a threaded statemachine(lsStateMachine). Depends on role, lsmgr has a primary lsStateMachine or a backup lsStateMachine. Underneath, a lsConnectionStateMachine is owned globally and visible to both side. There are three connections in totally, but each connection could have multiple stream. When there is a communication beginning, log manager use an connection to established a new stream. Therefore databases in database mirroring are not guaranteed to share same connection.
To have more understanding about Database Mirroring and Log shipping, let’s start it with a long journey of a log.
When an series operations executed in a database, some log blocks are generated in log cache. When these log blocks are accepted to write and flush. During flush log blocks, log blocks are queue up to ship to the mirror. At this point, a event will be send to Primary database statemachine.
Log manager will append these blocks to a log