This week, we'll take a look at part 3 of our series on application high availability and disaster recovery (HA/DR) for SQL Server: database mirroring.
Database mirroring provides high availability and protection for a single user database. Database mirroring provides database availability by mirroring the transaction streams from the principal server to a mirror server.
Part 1: Application High Availability and Disaster Recovery for SQL Server
Part 2: Poor Man's Disaster Recovery: Use Log Shipping for SQL Server (at Your Own Risk)
Database mirroring supports two operating modes:
-
Synchronous – a transaction must be committed on both principal and mirror server, and/or witness server before considered as completed. This is a real-time protection for a database.
- With automatic failover – requires a witness partner.
- Without automatic failover – no witness partner.
- Asynchronous – a transaction commits without waiting for the mirror partner to write the log to disk.
To configure database mirroring, you can use the following references:
There are many advantages and disadvantages with using database mirroring compared to other HA/DR solutions. Let’s take a closer look at some considerations:
- SQL protection is at the database level only. This is good if you only have a few databases you want to protect in case of a disaster. If you have many databases and/or you want to provide protection at the instance level, database mirroring is not your friend here.
- Data loss can occur. Under asynchronous mode, data does not have to be committed on the mirror partner before the transaction is considered completed. Thus, a failover before the actual commit on the mirror partner would result in data loss. Even under synchronous mode, you would still incur data loss due to latency in committing on the mirror partner. As designed, a transaction must commit on both the principal and mirror server to be considered completed. If there is a high latency, and before the transaction is actually committed on the mirror server and you failover, the transaction itself is never committed/accepted and must be re-entered.
- L-a-t-e-n-c-y is the killer. Did I mention latency yet? Although it is great to have the ability to do synchronous commit, with slow WAN or high latency, your normal OLTP will slow down considerably because each transaction must be fully committed on all partners.
- Mirror database is offline. The database is not accessible on the mirror server for normal user access. If you need to use the mirror database for off-site reporting, you will have to create a database snapshot of the mirror database to gain access to the data. A database snapshot is a point-in-time view of the database. So if you need more current data, you will have to create another snapshot.
- SQL logins and security must be manually configured on the secondary server. This is the only way to allow users to reconnect and get access to the database after a failover. The principal and mirror servers are unique servers with their own instances and databases. To keep the logins in sync, you can use, How to transfer logins and passwords between instances of SQL Server to transfer the logins from the primary server to the secondary server.
- Normal maintenance plans must be configured manually on the mirror server. Each server plays a different role in this data-mirroring configuration, so the active maintenance plan configured on the principal server must be duplicated on the mirror server. However, the maintenance plan should only be active if the database is actually active on the server.
- External database dependencies or chaining must be manually configured on the secondary server to achieve “normal” operation after failover. If the database requires a third-party agent solution, the agent solution must also be installed and configured on the secondary server. Licensing fee for the third-party agent could be a challenge here.
- The principal and mirror server must run the same version of SQL Server. To establish a mirroring session, the partner servers must be running the same version. Service packs or cumulative updates need to be applied to both servers, which means there would be downtime, and incompatibility could exist for other databases/applications on the same servers.
- Simple and bulk-logged recovery model are not supported. Every transaction must be fully logged and mirrored to achieve consistency across partners. Thus, you’d want to make sure you have plenty of disk space for your transaction log file(s) on each partner server.
- Servers can be locally or geographically dispersed, but.... Latency can be impactful if the connection between the principal server and the mirror server is over a slow WAN, especially if you have large transaction logs to mirror to the mirror server.
- Servers can be any class of hardware. Any commodity servers can be used for the partner servers and witness server. They do not have to be the same class or version.
- No witness no automatic failover. The witness server is the referee in the mirroring topology. It is the decider as to which partner server should have the principal role. If both partner servers can communicate/form a quorum with the witness server, then the principal role remains at the specified partner. If only one partner server can communicate/form a quorum with the witness server, then that server will be assigned the principal role. If neither partner servers can communicate with the witness server, then the principal role remains at the current principal server unless you manually switch roles.
- Distributed transaction is not supported. A database that is mirrored cannot be part of a distributed transaction – i.e., a transaction that spans across more than one database. Even if you create multiple database-mirroring sessions to attempt to group the databases together, you cannot have a distributed transaction across these databases.
Database mirroring is truly a step up from log shipping. With synchronous mirroring and automatic failover, it’s extremely simple to have a critical database protected for high availability and disaster recovery. But be aware of the cost of latency. High-performance OLTP will be affected. Also, if you need protection at the instance or server level, database mirroring is not the answer and you will have to check out other HA/DR solutions.
I hope you find the database mirroring information above useful and will join me again in the next installment of the Application High Availability and Disaster Recovery (HA/DR) for SQL Server series, where I will discuss another HA/DR solution.
Do you have a specific question about HA/DR solutions? If so, please let me know and I'll try to provide insight and solutions. Cheers!
