Mirroring

An alternative or complement (depending on the depth of your faith) to replication and log shipping for standby servers is database mirroring, which is new to SQL Server. It is available to SQL Server Standard and Enterprise Editions and works for databases configured for the full-recovery model (see Chapters 4, 6, and 7).

The model is straightforward. You simply mirror a database to another server, which acts as a “warm” standby. If the primary server fails, you can redirect your clients to the warm standby, where an identical copy of the database they just lost their connection to is available. The feature is a simple alternative to replication (which, as you learned earlier, can alter a database in ways that will not work for you), as well as to both fail-over clustering (especially over widely dispersed geographical locations) and log shipping. Database mirroring is also faster than replication or log shipping, which requires more effort to bring a fail-over online, and even more effort to fail back. In a mirror there is very little latency between the committal of a transaction on the source server and the target mirror. Thus a client can be quickly restored by connecting to the standby or fail-over server.

Database mirroring is not a new concept. In the example we will discuss here, one server is based in Florida and the mirror partner is based in California. Incidentally, the mirror functionality that now ships in SQL Server 2005 was added with the release of Service Pack 1 (SP1). As promised, we will also look at a third-party solution, Double-Take from Double-Take Software, Inc. However, the built-in option in SQL Server is free.

The mirror can be set up either synchronized or unsynchronized. When the mirror is synchronized, the solution supports rapid fail-over, which ensures that the last transaction to be committed or rolled back is synchronized with the target server. In other words, you will not experience data loss from committed transactions that get lost in the latency during a fail-over. If you do not synchronize, there is a possibility of data loss, which is proportional to the distance and network conditions between the source and target servers.

All your database servers are able to participate in multiple, concurrent database mirroring sessions, albeit only once per mirrored database. You can even configure a server to act exclusively as a partner or as a witness in all of its database mirroring sessions. A server instance can also act as a partner in some sessions and as a witness in other sessions. In this regard, mirror architecture is pretty flexible.

How SQL Server Mirroring Works

Two servers, the principal server and the mirror server, are set up as partners that share a mirroring session, each with its own role. The source server is known as the principal role, and the destination (stand-by) server is known as the mirror role.

Another way to look the roles is using the active-passive paradigm; in the mirror scenario we can have (at any given time) an active mirror and a passive mirror. This may be an easier concept to understand than referring to a principal mirror and a mirror role. Thus in a production environment, the principal or active mirror is the production database.

The actual mirror process is very simple. Unlike replication, which works at a logical level, replicating rows in each table, database mirroring actually sends every transaction from the active mirror to the passive mirror. The transactions are sent as log records, which the passive mirror simply applies to its copy of the database as if it initiated the transaction.

Synchronization implies that the passive mirror does not commit the transaction until it knows for sure the active mirror committed the transaction. The alternative is that the passive mirror gets the transaction and applies it without knowing the disposition of the transaction on the active mirror. The latter approach is faster because synchronization requires additional network traffic, I/O, and synchronous communication. However, asynchronous application of the transactions leaves the door open to the chance the mirrors will not be identical.

Fail-Over

Fail-over in the database mirror solution is a process known as “role switching.” In other words, the roles of the mirror partner servers can be simply switched. The active mirror becomes the passive mirror (when it dies or is forced into that role), and the passive mirror takes over as the primary or principal server.

You can switch (or fail-back) the roles at any time after the former principal server is back online (and has its databases back online in a consistent state with the partner).

There are three possible roles in the database mirror solution:

• Automatic fail-over   Can be achieved with the high-safety mode setting and the presence of a witness server (discussed next).

• Manual fail-over   Can be achieved with the high-safety mode setting. The partners must be connected to each other, and the database must already be synchronized.

• Forced fail-over   Can be achieved with both high-performance mode and high-safety mode without automatic fail-over. The surviving server can seize the role of active mirror.

Witness Server

Okay, so let’s go with the synchronous process. Not so fast. There is something else I did not yet tell you. The synchronous solution requires a third server instance, known as a “witness” server. This so-called witness server supports the fail-over process by verifying that the active server is up and functioning normally. So, just as in the log shipping options where you install a monitor server, here you need a witness server.

The passive server is also connected to the witness and seizes the passive role only if, while it is connected to the witness, the active server goes down. This may appear to be an elegant solution; however, it is not a cheap one. You now need to have three SQL Server instances up and running for a synchronous solution to work. And if the active mirror is an Enterprise Edition server, then the others in the “pod” need to be Enterprise Edition as well. In fact, you may be better off with a highly sophisticated mirror technology that replicates every byte on a volume (a solution we will also explore later in this chapter).

The Mirror Session

A database mirroring process, known as a mirroring session, begins as soon as the mirror database is ready and the server instances have been configured. The members of the mirror session all monitor the mirroring process and the state of the mirroring relationship between them.

Once the mirror starts, the target or recipient server will check the log sequence number (LSN) of the transaction log on the source servers and will begin to pull all subsequent transactions down to its instance. The source server immediately dispatches the transaction logs needed by the target, starting with the transaction after the last transaction sent to the server, if any. A queue is maintained between the partners so that unsent transactions that accumulated can be processed. Once the log is received, the target server writes it to disk before applying the transactions to the target database.

All the time during the mirroring session the source database remains available to clients. Upon each and every transaction made on the source server, it is sent directly to the recipient server. What transpires on the target or mirror server is a process that is identical to the log restore process described in Chapter 7, only the mirror process does the work automatically. The target server essentially repeats exactly what the source is doing with the transaction log. It rolls the database forward in the identical fashion, and it shrinks or truncates the transaction logs in the identical fashion. You basically have identical servers, one or more live and one or more ready to assume the role at any point in time.

If synchronization (or the so-called high-safety mode) is used, the source database confirms the new transactions as soon as the recipient has notified it the transactions have been saved to the target disk. Mirror synchronization is then achieved. If the target server for some reason cannot apply the transactions, the mirror server will pause the session and put the database into the SUSPENDED state. You can restart the session only when the failure has been resolved.

Creating a Mirror

You can use either Management Studio or a T-SQL script to configure database mirroring. Just like the script shown in the log shipping example earlier, a mirror script can be very time-consuming. If you use Management Studio, the interactive process automatically configures safety settings based on the mode you choose. Scripting is a little harder because you need to explicitly configure the safety of the transaction processing.

If the SAFETY option is set to FULL, the database mirroring operation is synchronous, after the initial synchronizing phase. If a witness is set in high-safety mode, the session supports automatic fail-over. If the SAFETY option is set to OFF, the database mirroring operation is asynchronous. The session runs in high-performance mode, and the WITNESS option should also be OFF.

Before a mirroring session can begin, the database owner or system administrator must create the mirror database, set up endpoints and logins, and in some cases, create and set up certificates. For a simple mirror configuration follow the tasks described here:

1. In the Database Properties dialog box (shown in Figure 8–22) click Configure Security This action opens the Database Mirroring Security Wizard. This wizard will configure mirroring security on the principal mirror server, the target server in the mirror set, and the monitoring or witness server instance (if used). The first screen (after the initial information screen) lets you choose to configure the security on the witness server. Choose Yes and click Next.

   
   Figure 8–22: Database Properties, Database Mirroring page

2. The next option is a final confirmation (mainly for the Witness server). Accept the defaults shown in Figure 8–23 and click Next to advance.

   
   Figure 8–23: Confirming the architecture

3. The Principal Server Instance screen now loads, as shown in Figure 8–24. If you are configuring a database on the local server, the server instance option will be grayed out, as shown in Figure 8–24. You also need to provide a port number for the mirror communications to listen on. Here the default is 5022. The port you choose should be noted if you intend to mirror across a network (such as the Internet) that is routed and protected with firewalls. Also choose to encrypt the data stream by checking the “Encrypt data sent through this endpoint” option. The endpoint name defaults to “Mirroring,” which you can change if you wish. Click Next to continue configuration.

   
   Figure 8–24: Configuring the Principal Server

4. The identical dialog box as shown in Figure 8–24 loads, only this time you need to choose and log in to the Mirror server instance. Note that SQL Server 2005 understands the Mirror server to be the remote instance that will receive the mirror stream for the source server, the principal server. Log in, choose the same port and encryption settings as in the preceding screen, and click Next to configure the Witness server. It too will have the same settings as the principal and mirror servers in terms of ports, names, and encryption. Click Next.

5. You are now ready to configure service accounts for the mirror partner servers. If you are using the same service accounts for all mirror partners and the Witness server, you can simply leave the text boxes here blank, advance to the next screen, and click Finish. Once you click Finish, you will be given the option to start the mirror process. If databases are ready for mirror send and mirror receive, then simply click the Start Mirroring button. Otherwise, choose the alternative button to delay the mirror start. Before mirroring can start, a full copy of the source database must exist on the target server. This can be achieved by doing a full recovery backup and restore of the principal database to the remote server.

As mentioned earlier, you need to “seed” a mirror by at least taking a full database backup on the source or principal server. This means doing a backup of both the database and the transaction log and restoring both to the target server. It is critical to do this even if the remote database has already been attached and may be working as a production database. The quickest way to achieve the mirror partner database is to detach any target database that may conflict with the restore and then restore it as a new database.

If you install into an existing, attached database, you need to restore using the WITH NORECOVERY option. If, while you are seeding the mirror server, the principal server generates even more log backups, then these need to be applied as well, also using the WITH NORECOVERY option. Only after you apply the latest log backup can you start mirroring.