Chapter 17: Database Mirroring | Professional SQL Server 2005 Administration (Wrox Professional Guides)

Database mirroring is a software solution for increasing the probability that the database will be available. Database mirroring is a brand new feature in SQL Server 2005. Maximizing the database availability is a top priority for most DBAs. It is hard to explain the pain a DBA goes through when a database goes down; you're unable to get it right back online, and at the same time you're answering pointed questions from your manager. Database mirroring will come to the rescue in certain scenarios, which we explain in this chapter. It will help you get the database back online with automatic or manual failover to your mirror database, adding another alternative to the SQL Server arsenal. In this chapter, we explain the database mirroring concepts, show you how to administer the mirrored database, and give you an example of how to implement database mirroring. We also discuss database snapshot, another brand new feature in SQL Server 2005, which you can use with database mirroring to read the mirrored databases.

Overview of Database Mirroring

Database mirroring is a high-availability solution at the database level, implemented on a per-database basis. To maximize database availability, you need to minimize planned as well as unplanned downtime. Planned downtime is very common, such as changes you have to apply to your production system, hardware upgrades, software upgrades (security patches and service packs), database configuration changes, or database storage upgrades. These all require your database or server to be unavailable for short periods of time if everything goes as planned. Unplanned downtime can be caused by hardware failures such as storage failure, by power outages, by human error, or by natural disasters, all of which can cause the production server or data center to be unavailable.

Database mirroring helps minimize both planned and unplanned downtime by:

Providing ways to perform automatic or manual failover for mirrored databases
Keeping the mirrored database up to date with the production database, either synchronously or asynchronously. You can set the operating modes for database mirroring, which we will discuss shortly.
Allowing the mirrored database to be in a remote data center, to provide a foundation for disaster recovery

Note

You cannot mirror the master,msdb,tempdb, or model databases. You can mirror multiple databases in a SQL Server instance, though.

Figure 17-1 illustrates a number of mirroring concepts, which we'll discuss in detail.

image from book
Figure 17-1

Database Mirroring involves two copies of a single database, residing on separate instances of SQL Server, usually on different computers. You can have separate instances of SQL Server 2005 on the same computer, but that would most likely not fit your high-availability requirements other than for testing purposes. At any given time, only one copy of the database is available to clients. This copy of the database is known as the principal database. The SQL Server that hosts this principal database is known as the principal server. Database mirroring works by transferring and applying the stream of database log records to the copy of the database. The copy of the database is known as the mirror database. The SQL Server that hosts this mirror database is known as the mirror server. The principal and mirror servers are each considered a partner in a database mirroring session. As you would guess, a given server may assume the role of principal for one database and the role of mirror for another database. Database mirroring applies every database modification (DML, DDL, and so on) on the principal database to the mirror database, including physical and logical database changes such as database files and indexes. We will discuss the witness server later.

Operating Modes of Database Mirroring

We mentioned that to keep the mirror database up to date, database mirroring transfers and applies the stream of database log records on the mirror database. It is important to understand which operating mode database mirroring is configured in. The following table outlines the operating modes of database mirroring.

Open table as spreadsheet

Operating Mode	Transaction Safety	Transfer Mechanism	Quorum Required	Witness Server	Failover Type
High Performance	OFF	Asynchronous	No	N/A	Forced failover only (with possible data loss). This is a manual step.
High Safety WITHOUT automatic failover	FULL	Synchronous	Yes	No	Manual or forced
High Safety WITH automatic failover	FULL	Synchronous	Yes	Yes	Automatic or manual

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

When you set up database mirroring, you have to decide whether you want the principal database and mirror database to be in sync all the time for full data safety or if you can live with some data loss in case of principal failure. You have two options: SAFETY FULL or SAFETY OFF. These options are part of the ALTER DATABASE statement when you set up database mirroring, as we'll explain later. As you know, in SQL Server, data changes are first recorded in the transaction log before any changes to the actual data pages are made. The transaction-log records are first placed in the database's log buffer in memory and then flushed to the log file on the disk (referred to as "hardening the transaction log") as soon as possible.

If you choose SAFETY FULL, you are setting up database mirroring in high-safety (also known as synchronous mirroring) mode. As the principal server hardens (flushes the log buffer to disk) log records of the principal database to disk, it also sends log buffers to the mirror. The principal then waits for a response from the mirror server. The mirror responds to a commit when it has hardened those same log records to the mirror's log. The commit is then reported to the client. Synchronous transfer guarantees that all transactions in the mirror database's transaction log will be synchronized with the principal database's transaction log, so the transactions are considered safely transferred. Figure 17-2 shows the sequence of events when SAFETY is set to FULL.

image from book
Figure 17-2

Keep in mind that it is guaranteed that you won't lose data and that both the principal and mirror will be in sync as long as the transaction is committed successfully. There is a little cost here because the transaction is not committed until it is hardened to the log on the mirror. There will be a slight increase in response time and reduction in transaction throughput because the principal has to wait for an acknowledgement from the mirror that the transaction is hardened to the mirror log. How much that delay might be depends on many factors like network latency, application architecture, disk throughput, and more. An application with lots of small transactions will experience more impact on response time than with long transactions, because transactions wait for acknowledgement from the mirror, and the wait time adds proportionately more to the response time of short transactions.

If you choose the SAFETY OFF, you are setting up database mirroring in high performance (also known as asynchronous mirroring) mode. In this mode, the log transfer process happens the same way as in synchronous mode, but the difference is that the principal does not wait for acknowledgement from the mirror that the log buffer is hardened to the disk on a commit. As soon as step 3 in Figure 17-2 occurs, the transaction is committed on the principal. The database is synchronized after the mirror server catches up to the principal server. Since the mirror server is busy keeping up with the principal server, if the principal suddenly fails, you may lose data. In this operating mode, there will be minimal impact on response time or transaction throughput, as this mode operates as if there is no mirroring.

We need to mention two important terms here. We will discuss these points additionally later in the chapter.

Send Queue: While sending the log records from the principal to the mirror, if the log records can't be sent at the rate at which they are generated, a queue builds up at the principal. This is known as the send queue. The send queue does not use extra storage or memory. It exists entirely in the transaction log of the principal. It refers to the part of the log that has not yet been sent to the mirror.
Redo Queue: While applying log records on the mirror, if the log records can't be applied at the rate at which they are received, a queue builds up at the mirror. This is known as the redo queue. Like the send queue, the redo queue does not use extra storage or memory. It exists entirely in the transaction log of the mirror. It refers to the part of the hardened log that remains to be applied to the mirror database to roll it forward.

Database Mirroring Example

Now that you understand transaction safety, you can look at an example to better understand the operating modes and other mirroring concepts. You will need to designate three SQL Server instances for this example: one principal server, one mirror server, and one witness server. You will set up high-safety mode with automatic failover with this example. This example assumes that all three SQL server instances are on the network and are running under some domain account, which is admin on the instance and has access to the other instances. You will need the AdventureWorks sample database that comes with SQL Server installation. Also, make sure that you have installed the SP1 on SQL Server 2005.

Preparing the Endpoints

We discuss endpoints in great detail in Chapter 8. You can refer to the "Transport Security" section in that chapter for details. For database-mirroring partners to connect to each other, they must trust each other. That is established by means of TCP endpoints. So on each partner, you have to create the endpoint using the T-SQL statement CREATE ENDPOINT and grant the connect permission on these endpoints using the GRANT CONNECT ON ENDPOINT statement. The endpoint concept is exactly the same as discussed in Chapter 8, so the rules are the same. The only difference is that instead of creating an endpoint for Service Broker, you are creating an endpoint for database mirroring here. The security rules are the same; you can either use the Windows authentication or certificates for authentication. In this example, you will use certificates so that you can learn how to use them for transport authentication. Windows authentication is very easy to establish, so we will leave that for you as an exercise.

First, you need to create the certificates on each partner. You can find all the scripts for this chapter on this book's Web page on www.wrox.com. Open the script CreateCertOnPrincipal.sql and connect to the principal server. The code looks like this:

 USE MASTER GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'PrincipalServerCert') CREATE CERTIFICATE PrincipalServerCert WITH SUBJECT = 'Principal Server Certificate' GO BACKUP CERTIFICATE PrincipalServerCert TO FILE = 'C:\PrincipalServerCert.cer'

For simplicity, we are using the certificates created by SQL Server, but there are other ways of creating and distributing certificates, which will work equally here. The BACKUP CERTIFICATE statement will back up the public key certificate for this private key.

Now create the endpoint on the principal server. Open the script CreateEndPointOnPrincipal.sql and connect to the principal server. Here's the code:

 --Check If Mirroring endpoint exists IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4) CREATE ENDPOINT DBMirrorEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4040) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PrincipalServerCert,                          ENCRYPTION = REQUIRED                          ,ROLE = ALL                        )

In this code, you can see that you have created the endpoint DBMirrorEndPoint, and you have specified the PrincipalServerCert certificate to use for authentication. You also specified ROLE = ALL, which indicates that this server can either act as the principal, mirror, or witness server. If you want this server to act only as the witness, you can specify WITNESS as a parameter. You can also specify the PARTNER option, which indicates that the server can act as either the principal or the mirror but not the witness.

Now create the certificates on both the mirror and the witness. Open the script CreateCertOnMirror.sql and run it on the mirror server:

 USE MASTER GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'MirrorServerCert') CREATE  CERTIFICATE MirrorServerCert WITH SUBJECT = 'Mirror Server Certificate' GO BACKUP CERTIFICATE MirrorServerCert TO FILE = 'C:\MirrorServerCert.cer'

Next, open the script CreateEndPointOnMirror.sql and run it on the mirror server:

 --Check If Mirroring endpoint exists IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4) CREATE ENDPOINT DBMirrorEndPoint STATE=STARTED AS TCP (LISTENER_PORT = 4040) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE MirrorServerCert,                          ENCRYPTION = REQUIRED                          ,ROLE = ALL                        )

Open the script CreateCertOnWitness.sql and run it on the witness server:

 USE MASTER GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys where name = '##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23%&weq^yzYu2005!' GO IF NOT EXISTS (select 1 from sys.databases where [is_master_key_encrypted_by_server] = 1) ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO IF NOT EXISTS (SELECT 1 FROM sys.certificates WHERE name = 'WitnessServerCert') CREATE  CERTIFICATE WitnessServerCert WITH SUBJECT = 'Witness Server Certificate' GO BACKUP CERTIFICATE WitnessServerCert TO FILE = 'C:\WitnessServerCert.cer'

And finally, open the script CreateEndPointOnWitness.sql and run it on the witness server:

 --Check If Mirroring endpoint exists IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4) CREATE ENDPOINT DBMirrorEndPoint STATE=STARTED AS TCP (LISTENER_PORT = 4040) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE WitnessServerCert, ENCRYPTION = REQUIRED                         ,ROLE = ALL                        )

Since all the partners can talk to each other, each partner needs permission to connect to the others. To do that, you have to create logins on each server and associate the logins with certificates from the other two servers and grant connect permission to that user on the endpoint.

First, you have to copy certificates we have created in the previous scripts with the BACKUP CERTIFICATE command to the other two servers. For example, copy the certificate PrincipalServerCert.cer on the principal from the C: drive to both the witness and mirror servers, on some drive. In this example, we assume that they are copied on the C: drive.

Open the script Principal_CreateLoginAndGrant.sql and run it on the principal server:

 USE MASTER GO --For Mirror server to connect IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'MirrorServerUser') CREATE LOGIN MirrorServerUser WITH PASSWORD = '32sdgsgy^%$!' IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'MirrorServerUser') CREATE USER MirrorServerUser; IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'MirrorDBCertPub') CREATE CERTIFICATE MirrorDBCertPub  AUTHORIZATION MirrorServerUser FROM FILE = 'C:\MirrorServerCert.cer' GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO MirrorServerUser GO --For Witness server to connect IF NOT EXISTS(SELECT 1 FROM sys.syslogins WHERE name = 'WitnessServerUser') CREATE LOGIN WitnessServerUser WITH PASSWORD = '32sdgsgy^%$!' IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE name = 'WitnessServerUser') CREATE USER WitnessServerUser; IF NOT EXISTS(SELECT 1 FROM sys.certificates WHERE name = 'WitnessDBCertPub') CREATE CERTIFICATE WitnessDBCertPub  AUTHORIZATION WitnessServerUser FROM FILE = 'C:\WitnessServerCert.cer' GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO WitnessServerUser GO

This script creates two users on the principal server: MirrorServerUser and WitnessServerUser. These users are mapped to the certificates from the mirror and the witness. After that, you granted connect permission on the endpoint. So now the mirror and the witness server have permission to connect to the endpoint on the principal server. You have to perform the same steps on the mirror server and witness server also. Open Mirror_CreateLoginAndGrant.sql and run it on the mirror server. Then open Witness_CreateLoginAndGrant.sql and run it on the witness server.

Now you have configured the endpoints on each server, using certificates. If you want to use the Windows authentication, the steps to configure the endpoints are bit easier than using certificates. All you have to do is the following on each server. This example is for the principal:

 IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4) CREATE ENDPOINT DBMirrorEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4040) FOR DATABASE_MIRRORING ( AUTHENTICATION = WINDOWS, ROLE = ALL) GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO [MyDomain\MirrorServerServiceAccount] GO GRANT CONNECT ON ENDPOINT::DBMirrorEndPoint TO [MyDomain\WitnessServerServiceAccount] GO

Of course, you have to change the logins appropriately. In Windows authentication mode, each server will use the service account under which it is running to connect to the other partners. So you have to grant connect permission on the endpoint to the service account of SQL Server. You can use SQL Server Management Studio to configure the endpoint using Windows authentication. Right-click the database you want to mirror and choose TasksMirror. A wizard will start. Click the Configure Security button, which will take you through the steps to configure the endpoint for database mirroring. The wizard will try to use 5022 as the default TCP port for database mirroring. You can change it if you want to.

Note

You will just need one mirroring endpoint per server; it doesn't matter how many databases you mirror. Make sure to use a port that is not used by other endpoints. You can specify any open port number between 1024 and 32767.

Do not reconfigure an in-use database mirroring endpoint (using ALTER ENDPOINT). The server instances use each other's endpoints to learn the state of the other systems. If the endpoint is reconfigured, it might restart, which can appear to be an error to the other server instances. This is particularly important in high-safety mode with automatic failover, in which reconfiguring the endpoint on a partner could cause a failover to occur.

Preparing the Database for Mirroring

Before you can set up the mirror, you'll need a database to work with. Open the CreateDatabase.sql script. Connect to your designated principal server. The code is here:

 IF NOT EXISTS(SELECT 1 FROM sys.sysdatabases WHERE name = 'TestMirroring') CREATE DATABASE TestMirroring --Although when you create the database it is in full recovery mode --i am doing it here as a reminder that you will need to set the --recovery model to FULL recovery in order to establish mirror the database. ALTER DATABASE TestMirroring SET RECOVERY FULL

This script will create database called TestMirroring for the example here. This is the database we would like to mirror. Connect to your designated principal server and run the script.

Now open BackupDatabase.sql. Connect to principal server. Here is the code:

 --Take a full database backup. BACKUP DATABASE [TestMirroring] TO  DISK = N'D:\Backup\TestMirroring.bak' WITH FORMAT, INIT, NAME = N'TestMirroring-Full Database Backup',STATS = 10 GO

Using this script, back up TestMirroring. You will have to do a full database backup. You will now have to restore this database on your designated mirror server with the NORECOVERY option in the RESTORE DATABASE statement. We assume in the following script that the backup of the principal database exists in the D:\backup folder on the mirror server.

Open RestoreDatabase.sql and connect to the designated mirror server. Here is the code:

 --If the path of the mirror database differs from --the path of the principal database (for instance, their drive letters differ), --creating the mirror database requires that the restore operation --include a MOVE clause. See BOL for details on MOVE option. RESTORE DATABASE [TestMirroring] FROM DISK = 'D:\Backup\TestMirroring.bak' WITH  NORECOVERY ,MOVE N'TestMirroring' TO N'D:\Backup\TestMirroring.mdf' ,MOVE N'TestMirroring_log' TO N'D:\Backup\TestMirroring_log.LDF'

This code will restore the database TestMirroring in NORECOVERY mode on your mirror server. The database must be restored with the NORECOVERY option. Now we have a database ready to be mirrored, but you'll need to understand what it takes to do initial synchronization between the principal and mirror in real life.

Note

To establish the mirroring session, the database name must be the same on both principal and mirror. Also, before you back up the principal, make sure that the database is in FULL recovery model.

Initial Synchronization between Principal and Mirror

You have just backed up and restored the TestMirroring database for mirroring. Of course, mirroring the TestMirroring size database is not a real-life scenario by any means. In real life, you may want to mirror a live database with hundreds of thousands of MB. So depending upon the database size and also the distance between the servers, it may take a long time to copy and restore the database on the mirror. During this time, the principal database may have produced many transaction logs. Before you set up mirroring, you must copy and restore all these transaction logs with the NORECOVERY option on the mirror server. If you do not want to bother copying and restoring these transaction logs on the mirror, you can suspend the transaction-log backups (if you have a SQL job to do backup transaction logs, you can disable that job) until the database on the mirror is restored and the database mirroring session is established (you will learn that very soon). After the database mirroring session is established, you can resume the transaction-log backup on the principal again. It is very important to understand that in this approach, since you have stopped the transaction-log backups, the transaction-log file will grow, so make sure you have enough disk space for log-file growth.

In order for the database mirroring session to be established, both databases (principal and mirror) must be in sync. So at some point you have to stop the transaction-log backup on your principal. You have to decide when you want to do it: before backing up the full database and restoring on the mirror server or after backing up the full database. In the first case, you have to plan for transaction-file growth, and in the second case, you have to copy and restore all the transactions logs on the mirror before you establish the mirroring session.

Our suggestion is that if you are mirroring a very large database, it is going to take a long time to back up and restore it on the mirror server. So plan mirroring installation during low-activity periods on your system. Increase the time between each transaction-log backup so that you have a smaller number of logs to copy and restore on the mirror. If you have very minimal database activities, you can stop taking transaction-log backups on the principal, back up the database on the principal, restore on the mirror, establish the mirroring session, and then restart the transaction-log backup job on the principal.

Establishing the Mirroring Session

Now you'll create some database activities in the TestMirroring database before you establish the mirroring session, so you will understand practically what we have just discussed.

Open the script InsertData.sql and connect to principal server. This script creates a table in the TestMirroring database and inserts data into that table from the AdventureWorks.person.address table.

Open BackupLogPrincipal.sql, connect to the principal, and run it:

 USE MASTER GO BACKUP LOG TestMirroring TO DISK = 'd:\BACKUP\TestMirroring1.trn'

That will back up the log of TestMirroring. Now the TestMirroring databases on the principal and the mirror are not in sync. See what happens if you try to establish the mirroring session between these databases.

Open the script SetupMirrorServer.sql and connect to the mirror server. The code is here:

 USE MASTER GO ALTER DATABASE TestMirroring SET PARTNER = 'TCP://MyPrincipalServer:4040'

Run this script. This will run successfully. Now open the script SetupPrincipalServer.sql and connect to the principal server. See the following code:

 USE MASTER GO ALTER DATABASE TestMirroring SET PARTNER = 'TCP://MyMirrorServer:4040'

This script will fail on the principal with following message:

 Msg 1412, Level 16, State 0, Line 1 The remote copy of database "TestMirroring" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

This shows that the database on the mirror is not rolled forward enough to establish the mirroring session. So now you have to restore the log you backed up on the principal to the mirror server with to sync the mirror with the principal. Open the script RestoreLogOnMirror.sql and connect to the mirror server. Here is the code:

 USE MASTER GO RESTORE LOG TestMirroring FROM DISK = 'D:\BACKUP\TestMirroring1.trn' WITH NORECOVERY

This script assumes that you have copied the log in D:\Backup folder on the mirror server. If you put the log somewhere else, substitute that folder location. Run the script. Now the principal and mirror databases are in sync.

Open SetupMirrorServer.sql again, and run it on the mirror server. Then open SetupPrincipal Server.sql, and run it on principal server. It should succeed now. You have just established the mirroring session.

Note that the order in which you execute these scripts is important:

Connect to the mirror server and run SetupMirrorServer.sql.
Connect to the principal server and run SetupPrincipalServer.sql.

When you establish the mirroring session, the transaction safety is set to FULL by default. So the mirroring session is always established in high-safety operating mode without automatic failover.

When you run the SetupPrincipalServer.sql or SetupMirrorServer.sql, you may get the following type of error:

 2006-05-27 17:53:16.630 spid15s      Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://MyMirrorServer:4040'. 2006-05-27 17:55:13.710 spid15s Error: 1443, Severity: 16, State: 2.

It may be possible that the firewall on the mirror or principal server is blocking the connection on the port specified.

High-Safety Operating Mode with Automatic Failover

You have established the mirroring session in high-safety operating mode without automatic failover. Now you want to change it to with automatic failover. This means that if the principal database (or the server hosting it) fails, the database mirroring will failover to the mirror server, and the mirror server will now assume the principal role and serve the database. But you need a third server, the witness for automatic failover to the mirror. The witness just sits there as a third-party and is used by the mirror to verify that the principal is really down, giving a "2 out of 3" agreements for automatic failover. No user action is necessary to failover to the mirror if a witness server is present.

Witness Server

If you choose the SAFETY FULL option, you have an option to set up a witness server, as shown in Figure 17-1. (We'll discuss how to set up a witness server soon.) The presence of the witness server in high-safety mode determines whether you can perform automatic failover or not when the principal database fails. Note that automatic failover will happen when following conditions are met:

Witness and mirror are both connected to principal when the principal is failing (going away).
Safety is set to FULL.
Mirroring state is synchronized.

You must have a separate instance of SQL Server other than the principal and mirror servers to fully take advantage of database mirroring with automatic failover. You can also use the same witness server to participate in multiple, concurrent database mirroring sessions.

Now you'll establish the witness server in your example. Open the SetupWitnessServer.sql and connect to either the principal or mirror server. The code is here:

 USE MASTER GO ALTER DATABASE TestMirroring SET WITNESS = 'TCP://MyWitnessServer:4040'

Yes, you must connect to either the principal or the mirror to run this script. When you run the script, both the principal and mirror servers must be up and running. Run the script. You have now a witness server established, which will provide automatic failover support.

Note

The witness server is optional in database mirroring unless you want automatic failover.

The Quorum

You have seen how to set up the witness server. When you set up the witness server, a quorum is required to make the database available. A quorum is a relationship between two or more connected server instances in a database mirroring session.

Three types of quorums are possible:

A Full quorum, where both partners and witness are connected
A partner-to-partner quorum, when both partners are connected
A witness-to-partner quorum, where a witness and one of the partners are connected

A database that thinks it is the principal in its DBM session must have at least a partial quorum to serve the database. We will talk more about quorum later in the chapter.

High-Safety Operating Mode without Automatic Failover

High safety without automatic failover is the default operating mode set when you establish database mirroring. In this operating mode, a witness is not set up, so automatic failover is not possible. Since the witness server is not present in this operating mode, the principal doesn't need to form a quorum to serve the database. If the principal loses its quorum with the mirror, it will still keep serving the database.

High-Performance Operating Mode

By default, the SAFETY is ON when you establish the mirroring session, so to activate the high-performance operating mode, you have to turn the safety OFF. You set the SAFETY to OFF using ALTER DATABASE TestMirroring SET PARTNER SAFETY OFF.

There will be minimal impact on transaction throughput and response time in this mode. The log transfer to mirror works the same way as in high-safety mode, but since the principal doesn't wait for hardening the log to disk on the mirror, there is a possibility that if the principal goes down unexpectedly, you may lose data.

You can configure the witness server in high-performance mode, but since you cannot do automatic failover in this mode, the witness will not provide any benefits. So you should not define a witness when you configure database mirroring in high-performance mode. You can remove the witness server by running the following command if you want to change the operating mode to high performance with no witness:

 USE Master ALTER DATABASE TestMirroring SET WITNESS = OFF

If you configure the witness server in a high-performance mode session, the enforcement of quorum means that:

If the mirror server is lost, the principal must be connected to the witness. Otherwise, the principal server takes its database offline until either the witness or mirror server rejoins the session.
If the principal server is lost, forcing service to the mirror requires that the mirror server be connected to the witness.

The only way you can failover to the mirror in this mode is by running the following command on the mirror when the principal server is disconnected from the mirroring session. This is called forced failover. See the table presented earlier in this chapter for the different failover types. We will discuss this further later in the chapter.

 USE MASTER ALTER DATABASE <db_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The forced failover causes an immediate recovery of the mirror database. Because of that, it may you lose data. This mode is best used for transferring data over long distances (for disaster recovery to a remote site) or for mirroring an active database where some potential data loss is acceptable. For example, you can use high-performance mode for mirroring your warehouse. Then you can use a database snapshot (we will discuss that later in this chapter) to create a snapshot on the mirror server to create a reporting environment off of the mirrored warehouse.

Database Mirroring and SQL Server 2005 Editions

Now that you understand the operating modes of database mirroring, the following table summarizes which features of database mirroring are available in which SQL Server 2005 editions.

Open table as spreadsheet

Database Mirroring Feature	Enterprise Edition	Developer Edition	Standard Edition	Workgroup Edition	SQL Express
Partner (Principal or Mirror)
Witness
Safety = FULL
Safety = OFF
Available during UNDO after failover
Parallel REDO

The Workgroup and SQL Express editions can only be used as a witness server in mirroring session. Some other features mentioned later in this chapter required either the Enterprise or Developer edition. If you want to use high-performance operating mode, you will need the Enterprise or Developer edition.

SQL Server may use multiple threads to roll forward the log in the redo queue on the mirror database. This is called parallel redo. This feature is only available in the Enterprise or Developer edition. Also, if the mirror server has fewer than five CPUs, SQL Server will only use a single thread for redo. Parallel redo is also optimized by using one thread per four CPUs.

Database Mirroring Catalog Views

In the example so far, you have set up the mirroring session but don't yet know how to get information about the mirroring configuration. So before we move further with failover scenarios and other topics, we'll discuss how you can find out that information. The following are the catalog views you can use to get information about database mirroring. It would be redundant to mention each and every column in database mirroring catalog views here, because Books Online has described them well. Our goal here is to show you when and how you should use these views.

sys.database_mirroring

The most important view to monitor mirroring state, safety level, and witness status (when present) is sys.database_mirroring. See the following query, which you can execute on either partner (principal or mirror), and you will get the same results.

 SELECT  DB_NAME(database_id) AS DatabaseName ,mirroring_role_desc ,mirroring_safety_level_desc ,mirroring_state_desc ,mirroring_safety_sequence ,mirroring_role_sequence ,mirroring_partner_instance ,mirroring_witness_name ,mirroring_witness_state_desc ,mirroring_failover_lsn ,mirroring_connection_timeout ,mirroring_redo_queue FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL

You will use this query often once you establish the mirroring session. If you run this query after you establish the mirroring session in the example scenario, you will see output something like the following. We have put the result in a table for ease of reading. Of course, some values will be different based on your server names and so on.

Open table as spreadsheet

Metadata column in Select list above	Principal values: MyPrincipalServer	Mirror values: MyMirrorServer
DatabaseName	TestMirroring	TestMirroring
mirroring_role_desc	PRINCIPAL	MIRROR
mirroring_safety_level_desc	FULL	FULL
mirroring_state_desc	SYNCHRONIZED	SYNCHRONIZED
mirroring_safety_sequence	2	2
mirroring_role_sequence	3	3
mirroring_partner_instance	TCP://MyMirrorServer .corp.mycompany.com:4040	TCP://MyPrincipalServer .corp.mycompany.com:4040
mirroring_witness_name	TCP://WitServer.corp .mycompany.com:4040	TCP://WitServer.corp .mycompany.com:4040
mirroring_witness_state_desc	CONNECTED	CONNECTED
mirroring_failover_lsn	38000000011500001	38000000011500001
mirroring_connection_timeout	10	10
mirroring_redo_queue	NULL	NULL

Some values in the preceding table are self-explanatory, but others are not. The column mirroring_safety_sequence gives the count of how many times the safety has changed (from FULL to OFF and back) since the mirroring session was established. The column mirroring_role_sequence gives the count of how many times failover has happened since the mirroring session was established.

The column mirroring_failover_lsn gives the log sequence number of the latest transaction that is guaranteed to be hardened to the disk on both partners. In this case, because there is very little database activity, both these numbers are the same, if you use the InsertData.sql script in a WHILE loop (if you change the script, make sure not to use an infinite loop!).

The mirroring_connection_timeout column gives the mirroring connection time out in seconds. This is the number of seconds to wait for a reply from a partner or witness before considering them unavailable. The default time-out value is 10 seconds. If it is null, the database is inaccessible or is not mirrored.

The mirroring_redo_queue is not yet implemented in this release.

sys.database_mirroring_witnesses

If you have a witness established for your mirroring session, you can get some information from the catalog view sys.database_mirroring_witnesses:

 SELECT * FROM sys.database_mirroring_witnesses

You can execute this query on the witness server to list the corresponding principal and mirror server names, database name, and safety level for all the mirroring sessions for which this server is a witness. You can get multiple rows with this query if the same server is acting as a witness for more than one mirroring session. This view will also give you information about how many times failover has happened between mirroring partners since the mirroring session was established in the role_sequence_number column. You can also get the information on whether database mirroring is suspended or not using the is_suspended column. If this column is 1, mirroring is currently suspended.

sys.database_mirroring_endpoints

You can use the following query to get important information about database mirroring endpoints such as port number, whether encryption is enabled or not, authentication type, and endpoint state:

 SELECT  dme.name AS EndPointName ,dme.protocol_desc ,dme.type_desc AS EndPointType ,dme.role_desc AS MirroringRole ,dme.state_desc AS EndPointStatus ,te.port AS PortUsed ,CASE WHEN dme.is_encryption_enabled = 1       THEN 'Yes'       ELSE 'No'  END AS Is_Encryption_Enabled ,dme.encryption_algorithm_desc ,dme.connection_auth_desc FROM sys.database_mirroring_endpoints dme JOIN sys.tcp_endpoints te   ON dme.endpoint_id = te.endpoint_id

This query uses the sys.tcp_endpoints view because the port information is not available in the catalog view sys.database_mirroring_endpoints.

Database Mirroring Role Change

In the example so far, you have learned how to establish the database mirroring session. You must have noticed that if you try to query the TestMirroring database on your mirror server, you will get an error like this:

 Msg 954, Level 14, State 1, Line 1 The database "TestMirroring" cannot be opened. It is acting as a mirror database.

You cannot access the mirrored database. So how do you switch the roles of the mirroring partners? There are three ways you can failover to the mirror server as described in the table earlier in this chapter. The failover types depend on which transaction safety is used (FULL or OFF) and whether a witness server is present or not.

Automatic Failover

Automatic failover is a database mirroring feature in high-availability mode (SAFETY FULL with a witness present). When a failure occurs on the principal, automatic failover is initiated. Since you have set up database mirroring in high-availability mode with a witness server, you can do automatic failover. The following are the events that happen in an automatic failover scenario.

Failure occurs: The principal database becomes unavailable. This could be the result of a power failure, hardware failure, storage failure, or some other reason.
The Failure is detected: The failure is detected by the mirror and the witness. Note that both partners and witness continually ping each other for their presence. Of course, it is not just a simple ping and involves more than just a ping, like whether the SQL Server is available, whether the principal database is available, and so on. There is a timeout specified for the ping, which is set to 10 seconds by default when you set up the database mirroring session. You can change the timeout by using following command: ALTER DATABASE <db_name> SET PARTNER TIMEOUT <value_in_seconds>. If the principal does not respond to the ping message within the timeout period, it is considered to be down, and failure is detected. You should leave the default setting for timeout to 10 seconds, or at least do not change it to less than 10 seconds, because under heavy load and sporadic network conditions, false failures may occur and your database will start failing over back and forth.
A complete redo is performed on the mirror: The mirror database has been in the restoring state until now. The mirror is continuously redoing the log (rolling it forward to the database) until now. When failure is detected, the mirror needs to recover the database. In order to do that, the mirror needs to redo the remaining log entries in the redo queue.
Fail over decision: The mirror now contacts the witness server and decides whether the database should now failover to the mirror or not. Note here that in the high-safety mode with automatic failover, the witness must be present for automatic failover. The decision takes about 1 second, so if the principal comes back up before Step 3 is complete, that failover is terminated.
The Mirror becomes the principal: After both the witness and the mirror have agreed on the failover decision, the database is recovered completely. The redo happens while the failover decision is being made. The mirror's role is switched to principal, recovery is run (this involves setting up various database states and rolling back any in-flight system transactions and starting up rollback of user transactions); then the database is made available. Undo of the user transactions continues in parallel (and holds some locks) until it is completed. The database is now served to the clients and normal operations are performed.
Undo: There may be uncommitted transactions (the transactions shipped to the mirror while the principal was available but not committed before principal went down) in the transaction log of the new principal, which are now rolled back.

Normally, the time taken to failover in this operating mode is very small, usually seconds, but that mostly depends on the redo phase. If the mirror is already caught up with the principal before the principal has gone down, the redo phase will not introduce time lag. The time to apply the redo records depends on the redo queue length and the redo rate on the mirror. Note that the failover will not happen if the mirroring_state is not synchronized. There are some performance counters available, which we will study in the "Performance Monitoring Database Mirroring" section, later in this chapter. From these counters, you can estimate the time it will take to apply the log in the redo queue on the mirror.

To measure the actual time, you can use the profiler or trace an event. See Chapter 13 to learn more about running traces and using the profiler. Let's use the profiler here to measure the actual time it takes to failover. Open SQL Profiler, connect to the mirror server, and select the event Database Mirroring State Change under the Database events group. Run the trace. Two columns in the trace are of interest: TextData and StartTime.TextData provides the description of the database mirroring state change event. StartTime represents the timestamp at which the event took place. Start the profiler. Now stop the SQL Server service on your principal server. Soon, automatic failover will happen. Profiler will trace the events, as shown in Figure 17-3.

image from book
Figure 17-3

Figure 17-3 shows that the event Synchronized Mirror with witness is to contact the witness server that principal is lost, which is step 4 event in Figure 17-2. Then the mirror failed over and is running exposed, which means that the partner is lost. You will also see a message similar to the following in the mirror server error log.

 The mirrored database "TestMirroring" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.

You will also notice in the StartTime column that the actual failover time for this automatic failover was about six seconds.

The length of the failover depends upon the type of failure and the load on the database. Under load, it takes longer to failover than in a no-load condition. Also, a manual failover takes a little more time compared to automatic failover. You will see messages similar to the following in the SQL ErrorLog.

 The mirrored database "TestMirroring" is changing roles from "MIRROR" to "PRINCIPAL" due to Failover from partner. Starting up database 'TestMirroring'. Analysis of database 'TestMirroring' (9) is 81% complete (approximately 0 seconds remain). Analysis of database 'TestMirroring' (9) is 100% complete (approximately 0 seconds remain). Recovery of database 'TestMirroring' (9) is 0% complete (approximately 30 seconds remain). Phase 2 of 3. Recovery of database 'TestMirroring' (9) is 16% complete (approximately 17 seconds remain). Phase 2 of 3. 13 transactions rolled forward in database 'TestMirroring' (9). Recovery of database 'TestMirroring' (9) is 16% complete (approximately 17 seconds remain). Phase 3 of 3. Recovery of database 'TestMirroring' (9) is 100% complete (approximately 0 seconds remain). Phase 3 of 3.

The additional steps during analysis and recovery of the database cause the manual failover to take longer.

When the failover happens, the clients need to be redirected to the new principal server. We will discuss that in the section "Preparing Mirror Server for Failover," along with other things you have to do on the mirror server to prepare it for failover and take the load.

Manual Failover

In a manual failover, you are making a decision to switch the roles of the partners. The current mirror server will become the new principal, and the current principal becomes the new mirror. For manual failover, the SAFETY must be set to FULL. It doesn't matter whether you have the witness set up or not. You can use following command for manual failover:

 ALTER DATABASE TestMirroring SET PARTNER FAILOVER

You have to run this command on the principal server in order to successfully failover. Also, the mirroring_state must be synchronized in order for successful failover. If it is not synchronized, you will get the following message when you try to execute the failover command on the principal:

 Msg 1422, Level 16, State 2, Line 1 The mirror server instance is not caught up to the recent changes to database "TestMirroring". Unable to fail over.

Now try a manual failover using your example server. Open the DatabaseMirroringCommands.sql script. When you did automatic failover earlier, we asked you to stop the original principal SQL Service. Make sure to start that service back up before the manual failover, because both the mirror and principal must be up and running for this step.

If you want to see what's happening behind the scenes, you can start SQL Profiler, connect it to the mirror server, and select the event Database Mirroring State Change under the Database event group. Run the trace. Two columns in the trace are of interest: TextData and StartTime. Also, you can start another instance of profiler and connect to the principal.

Now connect to your principal server and run the command ALTER DATABASE TestMirroring SET PARTNER FAILOVER. You can also use SQL Server Management Studio to do a manual failover as follows: Right-click the principal database and select TasksMirrorFailover. That will pop up a dialog box for confirmation, where you should click OK.

You can use manual failover for planned downtime. We will talk about this in the section "Preparing the Mirror Server for Failover."

Forced Service Failover

For forced service failover, you need to run the following command on the mirror server. You should rarely use this command, since you may lose data.

 ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

When you run this command, the mirror should not be able to connect to principal; otherwise, you will not be able to failover. If your principal is up and running and if the mirror can connect to it when you try to run the command, you will get the following error message:

 Msg 1455, Level 16, State 2, Line 1 The database mirroring service cannot be forced for database "TestMirroring" because the database is not in the correct state to become the principal database.

Now try this exercise using your example server. Since you have set up the database mirroring in full-safety mode with automatic failover, you first need to remove it. Open the DatabaseMirroringCommands.sql script. Run the following command on either the principal or the mirror:

 ALTER DATABASE TestMirroring SET WITNESS OFF

Then run the following command on the principal:

 ALTER DATABASE TestMirroring SET SAFETY OFF

Now the database TestMirroring is set with SAFETY OFF and no witness. Now you can force a service failover. You'll have to simulate the scenario where mirror cannot form a quorum (cannot connect) with principal. To achieve that, stop the SQL Server service on the principal. Now run the following command on the mirror server:

 ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

This command now will force the TestMirroring database on the mirror server to recover and make it online. The mirroring_state (sys.database_mirroring) does not matter (synchronized or not) in this case, because it is a forced failover, and that is why you may lose data in this scenario.

Now see what happens if you bring the original principal server (the one where you stopped SQL Server service) back online. After you bring the server online, the mirroring session will be suspended. You can use the query sys.database_mirroring view in the MirroringCatalogView.sql script to view the mirroring state by connecting on either the principal or the mirror. You can resume the mirroring session by running the following from the DatabaseMirroringCommands.sql script on either the principal or the mirror.

 ALTER DATABASE TestMirroring SET PARTNER RESUME

Database Availability Scenarios

So far we have talked about database mirroring operating modes and how to failover in different operating modes. In this section, we talk about what happens to the database availability to clients when the server is lost. The server might be lost not just because the power is off but because of a communication link failure or some other reason; the point is that the other server in the mirroring session cannot communicate. Several different scenarios exist. To keep matters clear, we will use three server names for this section: ServerA (principal), ServerB (mirror) and ServerC (witness).

Principal is Lost

If the principal server is lost, the failover scenario depends on the transaction safety (FULL or OFF) and whether a witness is present or not.

Scenario 1: Safety FULL with a Witness

We have discussed this scenario in the automatic failover section. In this scenario, the mirror forms a quorum with the witness because the principal is lost. Automatic failover will happen (of course, some conditions must be met as we mentioned earlier for automatic failover to happen), the mirror becomes the new principal server, and the database will be available on the new principal.

In this situation, before the failure, ServerA was the principal, ServerB was the mirror, and ServerC was the witness. ServerA now fails. After failover, ServerB becomes the principal and will serve the database. However, because there is no mirror server after failover (because ServerA is down), ServerB is running exposed, and the mirroring state is DISCONNECTED. If ServerA becomes operational, it will automatically assume the roll of the mirror, except for the fact that the session will be suspended until the admin issues a resume.

If SAFETY is full and you have configured a witness, in order to make database service available, at least two servers should be available to form a quorum. In this scenario, if ServerA fails, ServerB becomes the principal, and it will serve the database. But now if ServerC (witness) goes down, ServerB will not be able to serve the database.

Scenario 2: Safety FULL without a Witness

In this operating mode, safety is high, but automatic failover is not possible. So if the principal fails, the database service is unavailable to the clients. You need to manually perform several steps to make the database service available again. You can force service to make the database available.

In this situation, before the failure, ServerA was the principal, ServerB was the mirror, and there was no witness. ServerA is now lost, so the database is unavailable to clients. In order to make the database available, you need to execute the following commands on the mirror.

 ALTER DATABASE <database name>SET PARTNER OFF RESTORE DATABASE <database name> WITH RECOVERY

These commands will bring the database on ServerB online, and the database server will be available again. When ServerA becomes available, you have to reestablish the mirroring session.

There is another option here, where you do not have to reestablish the mirroring session. Run the following command on ServerB (which is still the mirror after ServerA becomes unavailable).

 ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

That will bring the database online on ServerB, which becomes the principal server. When ServerA comes online, it will automatically assume the role of the mirror. However, the mirroring session will be suspended, meaning no logs will move from ServerB to ServerA. You can resume the mirroring session (start moving logs from ServerB to ServerA) by running the following command.

 ALTER DATABASE TestMirroring SET PARTNER RESUME

When your database is really huge (hundreds of GBs), it is a real pain to backup and restore to reestablish the mirroring session.

Whether you choose to break the mirroring session or force service, you will lose the transactions that haven't yet made it to the mirror at the time of failure.

Scenario 3: SAFETY OFF

When SAFETY is OFF, the witness doesn't add any value, so we recommend that you do not configure a witness in that case. If the principal server is lost in this scenario, the database service becomes unavailable. You have to force the service to make the database service available again.

In this scenario, before the failure, ServerA was the principal and ServerB was the mirror. ServerA now fails, so the database service is not available to clients. You can failover to ServerB using the following command.

 ALTER DATABASE TestMirroring SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

However, the SAFETY is OFF, so it is possible that there were transactions that didn't make it to the mirror at the time of the failure of the principal. These transactions will be lost. Therefore, manual failover with safety off involves acknowledging the possibility of data loss. When ServerA becomes operational again, it will automatically assume the role of the mirror, but the mirroring session will be suspended. You can resume the mirroring session again by running the following command:

 ALTER DATABASE TestMirroring SET PARTNER RESUME

Mirror is Lost

If mirror fails, the principal will continue functioning, so the database service is still available to the clients. The mirroring state will be DISCONNECTED, and the principal is running exposed in this case. You can use the sys.database_mirroring view to find the mirroring state on the principal server.

When the mirror goes down, you have to be a little careful, and you have to take steps to make sure that the principal serves the database without any issue. When the mirror goes down, the mirroring state will be changed to DISCONNECTED, and as long as the state is DISCONNECTED, the transaction-log space cannot be reused even if you back up the transaction log. If your log files keep growing and reach their maximum size limit, or your disk runs out of space, the complete database comes to a halt.

You have some options here:

Make sure you have plenty of disk space for the transaction log to grow on the principal and make sure to bring back the mirror before you run out of space.
Break the database mirroring session using the command ALTER DATABASE TestMirroring SET PARTNER OFF. The problem here is that when your mirror server becomes operational, you will have to reestablish the mirroring session by a backup and restore of the database on principal and performing other steps as we have mentioned in the example. If your database is huge, the backup and restore step could be very painful and can take a long time, so consider the following step.
Break the database mirroring session using the command ALTER DATABASE TestMirroring SET PARTNER OFF. Make a note of the time when the mirror went down. Make sure your job that backs up the transaction log is running on the principal. When the mirror comes back up online, apply all the transaction logs on the mirror database. The first transaction log you would apply is the one you backed up after the mirror went down. Make sure to apply the transaction log on the mirror database with the NORECOVERY option. That way, you do not have to back up the whole database and restore it on the mirror. Of course, you have to perform other steps to reestablish the mirroring session because the session was broken.

Witness Is Lost

If witness is lost, the database mirroring session will continue functioning without interruption. The database will be available. Automatic failover will not happen. When witness comes back online, it will automatically join the mirroring session, of course, as witness. With safety set to FULL, if the witness is lost, and then the mirror or the principal is lost, the database service will be unavailable to the clients.

Mirror and Witness Are Lost

Assume that you have configured the mirroring session with a witness. If the mirror server is unavailable, the principal will still make the database service available, but it is running exposed. If the witness is also lost, the principal becomes isolated and cannot service the clients. Even though the principal database is running, it is not available to the clients. If you try to access the database, you will get the following message:

 Msg 955, Level 14, State 1, Line 1 Database <db_name> is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened.

If both the mirror and witness are lost, the only way you can bring the database service online to clients is by breaking the mirroring session, by running the following command on the principal:

 ALTER DATABASE <database name> SET PARTNER OFF

Once the mirror becomes available, you can reestablish the mirroring session. To reestablish the mirroring session, you may have to back up and restore the database on the mirror, but if you want to avoid that step, refer to the third option in the "Mirror Is Lost" section. Once the witness becomes available, you can join in the witness as well, but you have to establish the mirroring session with the mirror before the witness can join in, as you have seen.