Database Mirroring in SQL Server 2005

What�s the most important asset a company can hold? Data, and therefore one of the most important business requirements of companies is the availability of that data. Companies need to quickly gain access to their data during unexpected failure or planned downtime.

Having already experienced both log-shipping and replication techniques on other systems, Mirroring seemed like a good compromise between the two.

Introduced in SQL Server 2005, Database Mirroring gives DBAs another option for maintaining high availability, whilst minimizing performance impact and enhancing the security of their data. It�s a new feature with many benefits and in this article; I will hopefully outline these benefits and the functionality that Mirroring brings.

Of course, individual company requirements such as �acceptable� performance, �allowable� data loss, service-level agreements, particular server hardware, how your network performs and size of your database logs will be factors in deciding which type of mirroring you choose to implement. As with most decisions concerning IT, the cost of your desired solution is also a factor. (E.g. Clustering is an expensive high availability method when compared to Database Mirroring!).

Database mirroring maintains two copies of a single database that must reside on different instances of SQL Server. Typically, these instances will reside on computers in different locations, depending on your DR setup. One instance serves the database to clients (known as the principal server), while the other instance acts as a hot or warm standby server (known as the mirror server).

In a way, Mirroring provides a hybrid solution i.e. it can provide a copy of the database like Log Shipping whilst also providing rapid failover capabilities like Clustering does. A bit of both worlds!

So what exactly is Mirroring?

Mirroring works only with full recovery model. Database mirroring is available in both Enterprise and Standard editions of SQL Server.

Mirroring allows the user to create an exact copy of a database on a different server. And as mentioned earlier, the mirrored database must reside on different instance of SQL Server.

Database mirroring maintains a hot standby database (known as the mirror database) that can quickly assume client connections in the event of a principal database outage.
Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database.

Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring and applying a stream of database log records from the principal database to the other copy of the database (the mirror database).
It applies every database modification that is made on the principal database to the mirror database. This includes data changes as well as changes to the physical and logical structures in the database, such as database files, tables, and indexes. I will explain the various components of Mirroring in more detail later in this article.

As mentioned, the primary goal of database mirroring is to increase data availability and allow failover in case a server hosting the database becomes unavailable (e.g. as the result of hardware or network failure). In principle, it is similar to the log shipping, available in SQL Server 2000. As in log shipping, entries from a transaction log in one database are transferred and applied from its instance on a primary server (or the �principal� in Mirroring speak!) to its copy on a secondary server (the �mirror�), which, by the way, implies that a mirrored database must be in full recovery mode, so its transaction logs are not overwritten.

Maintaining synchronised copies of a database on two separate servers allows switching between them on an as needed basis, reversing roles of partners participating in the mirroring session, (the former principal becomes the mirror and vice versa). This functionality can be very useful when it comes to planned outages for software upgrades or hardware refreshes etc.

The mirroring process can be automated, which is extremely useful for �unplanned� outages or failures. This requires the presence of another instance of SQL Server, running on a separate server, this is known as a �witness�. This server monitors operations of mirroring partners, triggering failover in case of a lack of heartbeat response from the principal and ensuring that at any given time there is only one principal within each mirroring session. The decision is coordinated between the witness and at least one other operational server.

This protects against loss of connectivity between the principal and the mirror.As long as the synchronization between the two is maintained, the mirror can take over processing client requests without noticeable delay (no more than a few seconds) and without any data loss (once the principal comes back on line, it automatically assumes the role of the mirror and catches up with changes on the new principal).

Still with me so far?
Even though database mirroring involves three distinct servers, any one of them can serve multiple roles in distinct database mirroring sessions. For example, a single witness can monitor multiple mirrored sessions (performance impact of a single monitored session is negligible) or a particular server can function as principal and mirror for two distinct database mirroring sessions.

The solution does not place any special hardware demands on participating servers, beyond the ones expected for typical SQL Server 2005 installation (this eliminates distance limitations inherent to clustered, shared storage-based solutions), although it is recommended that all servers run the same version of the operating system.

Unlike in log shipping situation, a mirrored database is not available for access, since it is in "recovering" state.
It is not possible to create separate mirrors of the same database. In addition, it is still your responsibility to ensure that things such as SQL Server logins & SQL database jobs remain synchronised, since this activity is not built into the normal database mirroring mechanism. Copy the SQL Agent jobs, alerts, SSIS packages, support databases, linked server definitions, backup devices, maintenance plans, database mail profiles, etc. from the principal server to the mirror server.

The security of mirroring sessions can be controlled by configuring the listener ports used for communication between mirrored partners. This is set on a per server basis with the CREATE ENDPOINT T-SQL statement (this statement must be executed before mirroring session can be established).

Database Mirroring can be run in various modes. These can include using a �Witness� server to watch proceedings to see if a fail-over is required. You can also choose whether you want to wait until transactions have been applied at both Principal and Mirror sites before committing (High Safety Mode), or whether you want transactions to be applied asynchronously at the Mirror site (High Performance Mode). Again, I�ll go into more details on these different modes later in this article.

To help your client�s connect to the appropriate server, Database Mirroring also supports client connectivity options that allow your client applications to be redirected to the currently processing database in the event of a failover.

Modes of Database Mirroring

There are 3 operating modes in database mirroring. The exact mode is based on the setting of transaction safety and whether a witness server is part of the mirroring session.

What is Transaction Safety?

If transaction safety is set to FULL, it means the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for acknowledgement from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk. When safety is set OFF, the principal does not wait for acknowledgement from the mirror, and so the principal and mirror may not be fully synchronised (that is, the mirror may not quite keep up with the principal).Synchronous transfer guarantees that all transactions in the mirror database's transaction log will be synchronised with the principal database's transaction log, and so the transactions are considered safely transferred.

When safety is set to OFF, the communication between the principal and the mirror is asynchronous. The principal server will not wait for an acknowledgment from the mirror that the mirror has hardened a block of transaction records. The mirror will attempt to keep up with the principal, by recording transactions as quickly as possible, but some transactions may be lost if the principal suddenly fails and you force the mirror into service.

High Availability Operating Mode

The High Availability operating mode supports maximum database availability with automatic failover to the mirror database if the principal database fails. It requires that you set safety to FULL and define a �witness� server as part of the database mirroring session.
The High Availability mode is best used where you have fast and reliable communication paths between the servers and you require automatic failover for a single database. When safety is FULL, the principal server must wait briefly for responses from the mirror server, and therefore the performance of the principal server may be affected by the capability of the mirror server. Because a single database failure will cause an automatic failover, if you have multi-database applications you want to consider other operating modes

In the High Availability mode, database mirroring is self-monitoring. If the principal database suddenly becomes unavailable, or the principal's server is down, then the witness and the mirror will form a quorum of two and the mirror SQL Server will perform an automatic failover. At that point, the mirror server instance will change its role to become the new principal and recover the database. The mirror server can become available quickly because the mirror has been replaying the principal's transaction logs and its transaction log has been synchronised with the principal's.

Also, SQL Server 2005 can make a database available to users earlier in the recovery process. SQL Server database recovery consists of three phases: the analysis phase, the redo phase, and finally the undo phase. In SQL Server 2005, a newly recovered database can become available for use as soon as the redo phase is finished.
Therefore if a database mirroring failover occurs, the recovered new principal database can become available for use as soon as it finishes the redo phase. Because the mirror database has been replaying transaction log records all along, all the mirror serves has to do is finish the redo process, which normally can be accomplished in seconds.

High Protection Operating Mode

The High Protection operating mode also has transactional safety FULL, but has no witness server as part of the mirroring session. The principal database doesn�t need to form a quorum to serve the database. In this mode only a manual failover is possible, because there is no witness. An automatic failover is not possible, because if the principal server fails, the mirror server has no witness server with which to form a quorum.

Since there is no witness server defined, automatic failover cannot occur and a principal server which suddenly loses its quorum with the mirror does not take its database out of service.

High Performance Operating Mode

In the High Performance operating mode, transactional safety is OFF, and the transfer of log records is asynchronous. The principal server does not wait for an acknowledgement from the mirror that all transaction log records have been recorded on the mirror. The mirror does its best to keep up with the principal, but it is not guaranteed at any point in time that all the most recent transactions from the principal will have been hardened in the mirror's transaction log.

Since the safety is OFF, automatic failover is not possible, because of possible data loss; therefore, a witness server is not recommended to be configured for this scenario. If the witness is set, a quorum is required. If the witness is not set, then a quorum is not required. Manual failover is not enabled for the High Performance mode. The only type of failover allowed is forced service failover, which is also a manual operation.

The forced service failover causes an immediate recovery of the mirror database. It may involve potential data loss on the mirror when it is recovered, if some of the transaction log blocks from the principal have not yet been received by the mirror.
The High Performance mode is best used for transferring data over long distances (that is, for disaster recovery to a remote site), or for mirroring very active databases where some potential data loss is acceptable � but to be honest any data loss these days is usually frowned upon!

Mirroring Components

Database mirroring consists of the following components; many of them I�ve already touched upon, but let�s discuss each one in detail.

Principal Server
The Principal server is the source server which contains the database which is configured for mirroring. There can be only one principal database and it has to be in a separate SQL Server instance than the mirror database.

Mirror Server
The Mirror server is the receiving database in a mirror pair i.e it is the destination server which contains the mirrored database. There can be only one mirror server for each principal database. The mirror server needs to be on its own separate SQL Server instance preferably on separate physical server.

Witness Server
A Witness server is optional and it monitors the Mirrored Pair. It ensures that both principal and mirror are functioning properly. The Witness server is also a seperate SQL Server instance preferably on a seperate physical server than principal and mirror.

Quorum
A Quorum is the relationship between the Witness, Principal and the Mirror servers. A Full Quorum is when all 3 servers (Witness, Principal and the Mirror) can communicate with each other. Because the witness is present automatic failover occurs.

Endpoint
Endpoint is the method by which SQL Server communicates with applications. In the context of Database mirroring endpoint is the method by which the Principal server communicates with the Mirror server. The mirror listens on a port defined in the endpoint. Each database mirror pair listens on its own unique port.

Prerequisites for Database Mirroring

There are a few prerequisites to consider before configuring mirroring:- Firstly, make sure the principal server and mirror server, are running the same edition of SQL Server 2005. And if you are using a witness server, make sure that SQL Server 2005 is installed on its system.

The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.It goes without saying; you�ll need to ensure that the mirror server has enough disk space for the mirror database.
You cannot have different database names either; the mirror & principal must be identical.The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.

Prior to configuring mirroring ensure that at least 1 transaction log is restored in addition to full backup with NORECOVERY mode.Advantages of Database Mirroring

Database mirroring doesn't share the distance limitations of Clustering. And, unlike Clustering, which runs at the system server level, database mirroring is implemented at the database level. This feature works by sending transaction-log records between the principal and mirror servers. The principal server is the source of the database changes.

For a start, data protection is vastly increased as Mirroring provides minimal data loss. In the event of a disaster, in high-safety mode with automatic failover, failover quickly brings the standby copy of the database online (with no data loss).

Standby comes into good effect during service packs installation or any patch applied on Principal server which requires downtime.This will please your end-users and boss � as actual production downtime can be minimised.
Overall I find Database Mirroring architecture to be more robust and efficient than Log Shipping.

It has automatic server failover and client failover mechanism. Configuration is simpler than log shipping and replication.

Database mirroring supports full-text catalogs.

It does not require special hardware (such as shared storage, heart-beat connection) and clusterware, thus potentially has lower infrastructure cost

Preparing the Mirror for Failover

In the event of a failover, you want the mirror database to take up the full workload of the principal database. Therefore, it makes sense to use identical servers (in terms of CPU, memory, storage, and network capacity) as principal and mirror partners. Best practices recommendations are to use:

Identical partner servers (in terms of CPU, memory, storage, network capacity).
The same service pack and patch levels for the operating system and SQL Server on both partners. When performing rolling upgrades, the service pack and patch levels can be temporarily different on the principal and the mirror. However, for steady state operations, they should be identical.
The same edition of SQL Server on both partners with an identical directory structure for the install and database files on both partners. The same SQL Server configuration (trace flags, startup options, memory settings, etc.) for the principal and mirror instances.

If the principal and mirror servers are not identical in terms of CPU, memory, storage, and network capacity, they say you might experience undesirable performance when you fail over � all depends on the discrepancies I suppose.

Using the same edition of SQL Server, and the same version and service pack level of SQL Server and the operating system on both the partners minimizes the risks of encountering SQL Server or operating system issues. Ensuring that both servers have the same directory structure and SQL Server configuration eliminates the need for changes to these during or after the failover to the mirror partner.

Note however, each database in an instance of SQL Server is mirrored independently. The entire instance is not mirrored. For full instance failover capabilities (not database level), consider failover clustering. (see http://www.gre-sqlserver-solutions.com/clustering.html)

Also, only user databases can be mirrored. You cannot mirror the system databases. Therefore, you need to perform some administrative tasks to prepare the mirror server to take over the role of principal in the event of a failover.

You need to make sure that applications can connect and execute all necessary actions. For example, if you add a disk volume on the principal partner on which you plan to put data or log files for the database that is being mirrored, add a disk volume with the same drive letter on the mirror server as well. Otherwise, when you add a data file on the new drive on the principal, database mirroring tries to add the same file in the mirror. This will fail because the new drive doesn�t exist on the mirror server.

Have a process in place so that when you make any changes (such as changes to hardware, software, SQL Server settings, or any database support objects) to the principal, you repeat or transfer the changes on the mirror server.

And of course it�s always recommended that you test & document server failover and make sure that all objects, logins, permissions, etc. work on the mirror the same as on the principal.

Mirroring Multiple Databases

If there are multiple databases in an instance, you need to mirror each database individually. Although it is possible to choose a different mirror server for each database, it is a best practice recommendation that you choose one mirror server for all the databases that belong to one application. If the databases in an instance belong to different applications, you can choose different mirror servers for them, but doing so adds complexity to the configuration.

Viewing Mirroring Information

As with all of its functionality these days, SQL Server 2005 provides catalog views and dynamic management views (DMVs) to view mirroring information. It also provides System Monitor counters to view database mirroring performance information. E.g. sys.database_mirroring & sys.database_mirroring_witnesses.(SQL Books online can provide detailed information on these)

Monitoring database mirroring performance

There are various performance counters and statuses associated with Database Mirroring, and various ways of being notified if any of these indicate a problem. For example, you can set up an email or net-send notification to certain people if the oldest unsent transaction is older than a certain age, or if the connection between Principal and Mirror is broken.(Again, SQL Books Online can provide detailed information on these)

To conclude

Hopefully I�ve provided you with enough insight into how Database Mirroring works in SQL Server 2005 for you to decide whether it would be something you would consider implementing to alleviate any High Availability issues you may have. There is a lot of useful documentation at www.microsoft.com and within SQL Server Books Online, which will delve into more detail about the principals and components involved.Database Mirroring is just one of many options available, for me it proved to be a very good solution, it�s up to you � Happy Mirroring��.

Database Mirroring in SQL Server 2005 - An Introduction