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:
![PRINCIPAL Server Lost]()
-
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]()
-
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