Introduction to Database Mirroring in SQL Server

Database mirroring was introduced with Microsoft SQL Server 2005 technology that can be used to design high-availability and high-performance solutions for database redundancy. It is designed to maintain a hot standby server with a transitionally consistent copy of the database. Mirroring is cost-effective, speedy, requires no special hardware, and ensures transactional consistency. This article will describe the different modes of database mirroring and how it is different from other technologies. Here will not get into the specifics of the SQL Server 2008 enhancements but will take a high level tour of SQL Server Mirroring concepts.

How it works in a Nutshell

In database mirroring, transaction log records are sent directly from the principal database to the mirror database. This helps to keep the mirror database up to date with the principal database, with no loss of committed data. 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. We will discuss these modes later. Fundamentally to summarize there are three jargons to understand – Principal database is the active live database that supports all the commands, Mirror is the hot standby and witness which allows for a quorum in case of automatic switchover.

In database mirroring, the transaction log records for a database are directly transferred from one server to another, thereby maintaining a hot standby server. As the principal server writes the database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance. The mirror server continuously applies the log records to its copy of the database. Mirroring is implemented on a per-database basis, and the scope of protection that it provides is restricted to a single-user database. Database mirroring works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. As with log shipping, in database mirroring you must ensure that all database dependencies exist on the standby server so that the system is able to operate correctly in the event of a failover to the mirror server.

Transactional Consistency

The transactional consistency provided by database mirroring depends upon the safety setting you apply to the mirroring configuration. If transaction safety is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens, its principal database logs records to disk and the principal server then sends them to the mirror server. The principal server waits for a response from the mirror server. The mirror server then responds when it has hardened these records to its log disk. This ensures transactional consistency. However, when transaction safety is set to OFF, the principal server does not wait for acknowledgment from the mirror server. As a result, the principal and mirror servers may not be fully synchronized and transactions may be lost in the event of failure of the principal server.

When transaction safety is set to FULL, the principal server waits for an acknowledgment from the mirror server. This process may generate additional latency and may increase your system’s response time. If you need to operate the mirror server in a synchronous transfer mode, you must consider the effects of the latency generated by this process on your system and ensure that it conforms to your service level agreement (SLA). If transactional safety is set to OFF, the principal server does not wait for an acknowledgment from the mirror server and no additional latency is generated. This process is also known as operating in a high-performance mode. When selecting the safety setting and operating mode for database mirroring, you must balance your requirement for transactional consistency with your performance requirement. Before we get into the specific modes, let us look at another technology that is very much mis-understood with DB Mirroring.

DB Mirroring Vs Log Shipping?

Log shipping and database mirroring are two different technologies that provide protection for individual databases. Both technologies rely on the restore and recovery capabilities of SQL Server databases, but implement it in different ways. Log shipping is based on scheduling frequent backups for the transaction log files and storing the backup files in the database of a secondary server. Both committed and rolled-back transactions are logged in the transaction log file on the primary server. This transaction data is then sent to the backup log file on the secondary server.

Database mirroring is based on TCP endpoints. In database mirroring, only committed transaction data is sent from the principal to the mirror server; rolled-back transaction data is not sent to the mirror server. In log shipping, both committed and rolled-back transaction data are backed up. Database mirroring cannot transfer bulk-logged data, and you can use only one mirror server. Log shipping, on the other hand, can transfer bulk-logged data, and you can use multiple secondary servers.

Unlike log shipping, database mirroring facilitates failover. If there is a witness server, failover occurs automatically; otherwise, failover needs to be performed manually. A failover takes less than 3 seconds, and the database downtime during a failover is less than 10 seconds. During a failover, the mirror server performs the role of the principal server. Failover preserves only committed transaction data.

DB Mirroring Modes

Database mirroring operates in three different modes: high-availability mode, high-protection mode, and high-performance mode.

In high-availability mode, you need all the three server roles: principal, mirror, and witness. In this mode, the transaction safety level is set to FULL. As a result, the data transfer mechanism between the principal and mirror servers is synchronous. That is, 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. If the principal server becomes unavailable, the witness server and the mirror server will form a quorum and perform automatic failover. In addition to automatic failover, you can manually perform the failover. Because the transaction safety level is set to FULL, you do not lose any committed transactions in the event of a failover.

In high-protection mode, you need only the principal server and the mirror server; you do not need a witness server. In this mode, the transaction safety level is set to FULL. As a result, the data transfer mechanism between the principal and mirror servers is synchronous. That is, 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. If the principal server becomes unavailable, you need to manually perform the failover because there is no witness server in this mode. Because the transaction safety level is set to FULL, you do not lose any committed transactions in the event of a failover.

In high-performance mode, as in high-protection mode, you need only the principal server and the mirror server; you do not need a witness server. In this mode, the transaction safety level is set to OFF. As a result, the data transfer mechanism between the principal and mirror servers is asynchronous. That is, 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. As a result, 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 becomes unavailable, you need to manually perform the failover because there is no witness server in this mode. Because the transaction safety level is set to OFF, you might lose some transactions in the event of a failover.

Conclusion

In this article we tooka simple tour of the basics that surround Database mirroring, in the subsequent articles I will write about the factors that potentially affect the use of DB Mirroring and some tips involving the enhancements of DBM in SQL Server 2008. There are aspects aorund compressed backups and automatic page recovery which makes use of DBM really worthwile. Do drop your comments if any.