If we could take a picture of the HA solutions currently offered in SQL Server, database mirroring would be the short guy in the back, standing on his tippy toes trying to make sure everyone remembers he was there. Despite this, and the fact that it’s been marked as a deprecated feature, Microsoft still feels it’s necessary to focus on this feature in the 70-462 exam. If you aren’t a heavy user of database mirroring, it can be somewhat overwhelming to start studying from scratch – so lets clear it up to get you on the right track.
Mirroring Roles
In it’s simplest form, mirroring consists of two servers. One plays the role of the principal (active) role, while the other plays the role of the mirror (passive). These two servers communicate as partners, where every insert, update, and delete done on the principal is redone on the mirror.
High-Safety or High-Performance?
The transactions can be committed on the mirror either synchronously or asynchronously. To confuse you more, Microsoft created new terms for this in the context of database mirroring. If you set up synchronous mirroing it’s referred to as ‘high-safety’ mode, because the transaction isn’t considered complete on the client end until it’s been completed at both at the principal and mirror server. Asynchronous is referred to as ‘high-performance’ mode, because the client doesn’t have to wait until the transaction is commmited on the mirror server.
Automatic Failover…sort of.
Automatic failover is available in database mirroring, but only if you’re in high-safety mode, and there is also the requirement of a third server, known as the witness server. The job of the witness server is to make sure the principal is still active, and failover only occurs when the mirror and witness remain connected to each other, and when the principal has been lost. Two of three types of failover (automatic and manual) both require high-safety mode to be enabled. A third type, coined ‘forced service’, allow you to force a failover and allow possible data loss in the event the principal has been lost, and you’re in high-performance mode.
Even if you don’t regulary support database mirroing, you can see why it’s the odd one out when it comes to SQL Server HA solutions. It’s an older technology, and has been outpaced by advancements in failover clustering and the introduction of availability groups. That said, there’s a good chance you may still need to support database mirroing in your career if you aren’t already, which is probably the reason Microsoft finds it necessary to have exam questions on the subject.
