SQL Server Database Mirroring: Part 1

In SQL Server Log Shipping and Mirroring can work together to provide solutions for high availability and disaster recovery. We can convert an existing log shipping configuration to a database mirroring configuration and also easily switch which pair of instances is using database mirroring and which instances are configured with log shipping.

SQL Server Database Mirroring

To move the database transactions from one SQL Server database (Principal database) to another SQL Server database (Mirror database) on a different instance the database mirroring is used. If the principal server fails, the mirror server automatically becomes the new principal server and recovers the principal database using a witness server under high-availability mode. It is a mixture of replication and log shipping. Mirroring works only with the full recovery model.

Logical Design Diagram of Database Mirroring

Difference between the Database Mirroring and Log Shipping

MIRRORING	LOG SHIPPING
1. It is limited to only two Servers	1.In this we can log ship to multiple Servers
2. Mirroring with a Witness Server allows for High Availability and automatic fail over.	2. Log shipping is only as current as how often the job runs. If we ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
3. We can configure our DSN string to have both mirrored servers in it so that when they switch we notice nothing.	3. We can leave the database in read only mode while it is being updated. Good for reporting servers.
4. While mirrored, our Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode.	4. Good for disaster recovery
5. Mirroring with SQL Server 2005 standard edition is not good for load balancing

Things that are explained in this article

Roles Of the Server
Modes of Database Mirroring
Loss of Servers
Prerequisites
Restrictions
Endpoints
Creating a mirror database for mirroring
Database Mirroring

Roles of the Server in Database Mirroring

Principal server

The principal server hosts the active copy of the database (referred to as the principal database) and services client requests. The principal server forwards all transactions to the mirror server before it applies them in the principal database.
Mirror server

The mirror server hosts a copy of the principal database (referred to as the mirror database) and applies the transactions forwarded by the principal database to keep the mirror database synchronized with the principal database.
Witness server

The witness server is an optional component of a database mirroring solution. When present, a witness server monitors the principal and mirror servers to ensure continued connectivity and participation in the mirror session (referred to as quorum). If either server loses quorum, the witness server assigns the principal server role, causing automatic failover from the principal server to the mirror server if necessary. A witness server is required for automatic failover; however, one witness server can support several mirror sessions because it is not an intensive job.

Database Mirroring Modes

Database Mirroring has three different Modes

High-Availability Mode

In this Mode we need all the three servers, since the transaction safety level is set to FULL that results in the "Database transfer mechanism between the principal and mirror server is synchronous" which means that the principal server waits for an acknowledgement from the mirror server that the transaction log record has been recorded on the mirror server. Then, the client application gets confirmation that the transaction is committed. But if the principal server becomes unavailable then the witness server and the mirror server will form a quorum and perform automatic failover.

High-Protection Mode

This mode is nearly the same as High-Availability mode, but the difference is that there is a need for two servers only (Principal server and Mirror server). The transaction safety level in this mode is also set to FULL that results in the same High- Availability Mode "Database transfer mechanism between the principal and mirror server is synchronous". Another difference is if the principal server becomes unavailable in this mode then we need to manually perform the failover because there is no witness server in this mode. Because the transaction safety level is set to FULL, we do not lose any committed transactions in the event of a failover.

High-Performance Mode

The same as High-Protection Mode, there is also a need of two servers (principal and mirror server). In this Mode the transaction safety level is set to OFF that results in "Data transfer mechanism between the principal and mirror servers is asynchronous", which means that the principal server does not wait for an acknowledgement from the mirror server that all transaction log records have been recorded on the mirror server and the client application gets confirmation that a transaction is committed as soon as the principal server has written the transaction to the log. If the Principal server in this mode becomes unavailable then we must manually perform the failover since there is no witness server in this mode. Because the transaction safety level is set to OFF, we might lose some transactions in the event of a failover.

Operating Mode	Transaction safety	Transfer mechanism	Quorum required	Witness server	Failover Type
High Availability	FULL	Synchronous	Y	Y	Automatic or Manual
High Protection	FULL	Synchronous	Y	Y	Manual only
High Performance	OFF	Asynchronous	N	N/A	Forced only

Loss of Servers

PRINCIPAL Server Lost

The following scenario considers what happens when the principal server is lost in a High Availability scenario:
MIRROR Server Lost

If the mirror server is lost first, the principal server is considered exposed because it cannot send data to the mirror.
WITNESS Server Lost

When the witness server fails, mirroring continues but no automatic failover is possible. Loss of just one more server will mean there is no quorum, and the principal database will no longer be able to serve the database.

Continue to Part 2

6 Comments

Join the conversation! Your thoughts help the community grow.

Hadshana Kamalanathan8y ago
Nice article thank you for sharing...
sayed basha10y ago
Good article, Could you please post AlwaysOn concept also
Gowtham Rajamanickam10y ago
Very good
Nipun Tomar12y ago
Thanks to all.
Jean Paul12y ago
Super Article! :)
Veena Sarda13y ago
Explained very well. A comment of on High Performance Mode Failover type - It is Manual. and for Hgh Protection - some reconfiguration needs to be done to bring it online.