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.
SQL Server Database Mirroring
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
  1. Roles Of the Server
  2. Modes of Database Mirroring
  3. Loss of Servers
  4. Prerequisites
  5. Restrictions
  6. Endpoints
  7. Creating a mirror database for mirroring
  8. Database Mirroring

Roles of the Server in Database Mirroring

Database Mirroring Modes

Database Mirroring has three different Modes
Database Mirroring 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
  1. PRINCIPAL Server Lost

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

    PRINCIPAL Server Lost
  2. MIRROR Server Lost

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

    MIRROR Server Lost
  3. 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.

    WITNESS Server Lost
Continue to Part 2