11.6. Mirroring in action

In this section we'll walk through an example of setting up, failing over, and monitoring database mirroring. Before we start the setup process, let's review a number of important design considerations and preparatory steps:

• SQL version—The principal and mirror instances must be running the same version and edition of SQL Server. If the database fails over, the same feature set needs to be available to ensure application behavior continues as normal.

• Collation—Ensure the principal and mirror instances are using the same collations.

• Network latency—As covered earlier, this can have a significant impact on transaction throughput and response time for high-safety (synchronous) mode. There are a number of tools and techniques for simulating varying levels of network latency, and the impact on load should be measured with the highest expected latency before proceeding with the high-safety mode.

• Network quality—Consider the possibility of small network problems causing unwanted automatic failovers. If using synchronous mirroring, you can avoid unwanted failovers by removing the witness server from the mirroring topology.

• Capacity—The capacity of the mirror server should be at least as great as that of the principal. In the event of failover, if the mirror server is unable to handle the load, the benefits of mirroring are obviously reduced. Capacity includes enough free disk space and processing power. Ideally, both the mirror and principal instances are configured identically, with load on the mirroring instance able to sustain the additional load from the principal in a failover event.

• Application failover—To fully capitalize on the automatic failure mode, consider the ability of the application to automatically reconnect to the mirror database when failover occurs—for example, using SNAC with a failover partner specified in the connection string.

• Recovery model—The principal database must be using the full recovery model.

• SQL logins—To enable applications to continue working after a mirroring failover, ensure that the same logins are created on both the principal and mirror instance and that they're created with the same security identifier (SID) values (using the SID = clause of the CREATE LOGIN statement).

So let's get started. Like most tasks, the setup of database mirroring can be performed using either a T-SQL script or SQL Server Management Studio. For our example, we'll use Management Studio.

11.6.1. Mirroring setup

The first step in setting up database mirroring is to initialize the mirror database. This is achieved by restoring a full backup of the principal database and at least one transaction log backup using the WITH NORECOVERY option. A transaction log backup must be restored in order to obtain the latest log sequence number (LSN) to determine the starting point for the redo queue when mirroring starts.

If any additional transaction log backups are made on the principal database before mirroring setup is started, these backups need to be restored using the WITH NORECOVERY option on the mirror database. If any scheduled transaction log backup jobs exist, such as maintenance plans or log shipping jobs, disabling them until mirroring is initialized will simplify the mirroring setup process.

In the previous chapter we covered the process of restoring database backups and transaction logs without recovery; here's an example of doing this on the mirror server:

-- Restore the Sales DB and roll forward using a transaction log restore
RESTORE DATABASE [Sales]
FROM DISK = N'G:SQL BackupSales.bak'
WITH NORECOVERY
GO

RESTORE LOG [Sales]
FROM DISK = N'G:SQL BackupSales-Trn-1.bak'
WITH NORECOVERY
GO

Once the mirrored database is initialized, begin the mirroring setup process by right-clicking on the database to be mirrored and choosing Tasks > Mirror. The resulting screen, shown in figure 11.11, is the starting point for mirroring configuration.

At this point, click the Configure Security button, which will take you to the screen shown in figure 11.12. You can choose whether or not you'd like to include a witness server in the mirroring setup. For our example, we'll choose Yes and click Next.

After choosing to save the security configuration in the witness instance, the next three steps are to configure the principal (see figure 11.13), mirror, and witness instances. In each case, we select the instance to use, the TCP port, the endpoint name, and the encryption choice. Other than the instance name, all other options are supplied with default values, as shown in figure 11.13.

The next screen, shown in figure 11.14, lets you specify the service account for each instance in the mirroring configuration. If you leave these fields blank, you'll have to manually add each service account to each instance, in addition to granting each account access to the mirroring endpoint. For example, in our example after adding the service account as a SQL login, we'd run the following command on each instance:

-- Grant the service account access to the mirroring endpoint
GRANT CONNECT on ENDPOINT::Mirroring TO [BNE-SQL-PR-01SQL-Sales];

Once you've provided this information, the wizard completes and offers to start the mirroring session. At this point, the databases will synchronize and then appear in SQL Server Management Studio, as shown in figure 11.15.

In our case, the Sales database is now in the Principal, Synchronized state, with the mirror remaining in the Restoring state. The list of possible statuses for the principal database appears in table 11.4.

Table 11.4. Mirroring session states
 

Mirroring state	Description
Synchronizing	The mirror DB is catching up on outstanding transactions.
Synchronized	The mirror DB has caught up.
Disconnected	The mirror partners have lost contact.
Suspended	Caused by pausing (covered shortly) or failover. No logs are sent to the mirror DB.
Pending failover	Temporary state at the principal during failover.

Now that we've set up database mirroring, let's take a look at the monitoring process.

11.6.2. Monitoring database mirroring

There are several tools and techniques for monitoring database mirroring, including system stored procedures, catalog views, performance counters, and the GUI-based Database Mirroring Monitor.

Database Mirroring Monitor

You can access the Database Mirroring Monitor in SQL Server Management Studio by right-clicking a database and choosing Tasks > Launch Database Mirroring Monitor. This tool, as you can see in figure 11.16, displays the mirroring status and related information for all mirrored databases on the SQL Server instance.

Information displayed by the Database Mirroring Monitor includes the following:

• Mirroring state

• Role of the server instance (principal or mirror)

• Status of the witness instance if present

• Amount of log in the principal's send queue and the mirror's redo queue

• Oldest unsent transaction

• Current rate of new transactions entering the principal database (kb/sec)

• Current rate at which transactions are being sent to the mirror (kb/sec)

• Current rate at which transactions are being processed at the mirror's redo queue (kb/sec)

• Average delay per transaction in waiting for confirmation of transaction hardening on the mirror (this is specific to high-safety [synchronous] mirroring only, and indicates the overhead of this mode in comparison to high performance [asynchronous] mirroring)

Information displayed by the Database Mirroring Monitor is captured on a regular basis (1 minute by default) by a SQL Server Agent job that updates mirroring information stored in msdb tables. If a mirroring session is created using SQL Server Management Studio, the SQL Server Agent job is created automatically.

System stored procedures

In addition to the Database Mirroring Monitor job, several system stored procedures exist that you can use to view and configure monitoring:

• sp_dbmmonitorupdate—This procedure is called by both the SQL Agent job and Database Mirroring Monitor to perform the updates on the mirroring status table in the msdb database. This database is used by both the Database Mirroring Monitor and the sp_dbmmonitorresults procedure (discussed shortly). When first executed, this procedure will create the msdb table to store database mirroring status information. Then it will insert new status records and purge records older than the retention period (default: 7 days).

• sp_dbmmonitoraddmonitoring—This procedure creates the SQL Server Agent jobs to periodically update the msdb tables containing the mirroring status. Running this procedure is required if database mirroring is established using T-SQL rather than using Management Studio.

• sp_dbmmonitorchangemonitoring—This procedure is used to change the update interval for the SQL Agent job that updates mirroring status information.

• sp_dbmmonitorhelpmonitoring—This procedure returns the current value for the update interval, set using sp_dbmmonitorchangemonitoring.

• sp_dbmmonitordropmonitoring—This procedure stops and removes the SQL Agent job that updates the mirroring status tables in the msdb database.

• sp_dbmmonitorresults—This procedure can be used as an alternative to the Database Mirroring Monitor. It returns the same information but in a text-based format. It takes three parameter values: a value specifying the mirrored database name to return results for, a value indicating the quantity of rows to return, and a value indicating whether you want to update mirroring status as part of the execution.

Catalog views

SQL Server exposes database mirroring metadata through a number of catalog views:

• sys.database_mirroring—This view returns one row for each database on the instance in which the view is queried. Columns returned include the mirroring status, role, safety level, and witness status.

• sys.database_mirroring_endpoints—Returns information about each database mirroring endpoint enabled on the instance.

• sys.database_mirroring_witnesses—Returns a row for each witness role played by the instance containing information, such as the safety level, principal, and mirror server names and synchronization state of the mirroring partners.

Performance counters

On either the principal or mirror server, Windows System Monitor can be used to view database mirroring information, including redo and send queue depth, and log data throughput per second. The counters are exposed using the SQL Server:Database Mirroring performance object.

Warning thresholds

Finally, one important aspect of monitoring of any type is being able to specify threshold values for important metrics and be alerted when such thresholds are exceeded. For database mirroring, thresholds can be set for the following metrics:

• Oldest unsent transaction—This metric is used to alert on the existence of old transactions in the send queue. If transactions exist that exceed the specified age in minutes, an alert is raised using event ID 32040.

• Unsent log—This metric is used to set the maximum allowable size (in kb) of transactions in the send queue. This threshold uses event ID 32042.

• Unrestored log—Similar to the unsent log, this metric applies to the allowable size of the redo queue on the mirror database. This threshold uses event ID 32043.

• Mirror commit overhead— Used for high-safety (synchronous) mirroring, this metric allows an alert to be generated when the average transaction delay to harden log records to the mirror log exceeds a specified number of milliseconds. This threshold uses event ID 32044.

You can set threshold values through the Set Warning Thresholds tab, as shown in figure 11.17, accessible by clicking the Set Thresholds button on the Warnings tab of the Database Mirroring Monitor tool, or by using the system stored procedures sp_dbmmonitorchangealert, sp_dbmmonitorhelpalert, or sp_dbmmonitordropalert.

When a threshold value is exceeded, an informational entry is written to the Windows event log, and as we'll see in chapter 14, alerts on these messages can be created very easily.

At some point in the mirroring session, we may need to suspend mirroring. Let's take a look at that process now.

11.6.3. Suspending and resuming mirroring

A running mirroring session can be suspended using either SQL Server Management Studio or T-SQL as shown here:

-- Suspend Mirroring
ALTER DATABASE Sales
SET PARTNER SUSPEND

When suspended, the mirroring session remains in place for later resumption with the principal database available for use. While suspended, transactions aren't copied to the mirror database. During this time, the principal runs exposed—that is, no failover to the mirror database is possible. Further, transactions can't be truncated from the principal's transaction log until they're copied to the mirror's log, which means transactions will build up in the principals log for the duration of suspension. It follows that the longer the suspension, the larger the transaction log will grow. Further, when resumed, the redo log on the mirror database may be quite large, increasing the failover time if the principal database fails.

So why would you choose to suspend mirroring? Let's imagine we're running in high-safety mode (synchronous mirroring) and we're about to bulk load a very large data file and/or rebuild a large index. We want to complete this process as soon as possible to reduce the impact on users, or within a defined maintenance window. As we explained earlier, transactions won't commit in synchronous mode until they're hardened to the transaction log on the mirror database. For long-running operations such as large bulk loads or maintenance on large indexes, the performance overhead of synchronous mode is magnified, potentially extending the completion time of certain operations to an unacceptable level.

By suspending mirroring, we're able to complete maintenance operations in the time taken in a nonmirrored environment. Once resumed, the transactions will catch up on the mirror. If we accept the possibility of running exposed for the duration of the suspension and ensure adequate transaction log space is available, we're able to maximize performance and transaction throughput during periods of high activity.

A mirroring session can be resumed using T-SQL:

-- Resume Mirroring
ALTER DATABASE Sales
SET PARTNER RESUME

Once resumed, the mirroring session will enter the synchronizing state, with the mirroring database catching up on transactions that have occurred since mirroring was suspended.

In addition to pausing and resuming the mirroring session, we can also initiate failover.

11.6.4. Initiating failover

Manually initiating failover is possible in either high-safety mode (synchronous mirroring) through manual failover, or in high-performance mode (asynchronous mirroring) using forced service.

When the mirroring databases are in the synchronized state, manual failover is specified by running the ALTER DATABASE SET PARTNER FAILOVER command on the principal database, as in this example:

-- Initiate Manual Failover
ALTER DATABASE Sales
SET PARTNER FAILOVER

At this point, clients are disconnected, active transactions are rolled back, and the roles of principal and mirror are swapped. As mentioned earlier, the only failover mode supported for databases mirrored in high-performance mode is the forced service option, which may result in data loss. The forced service option is executed on the mirror database as shown in the following example, and is only available when the principal instance is disconnected:

-- Force Failover - run on mirror instance - data loss possible
ALTER DATABASE Sales
SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

In addition to the T-SQL methods, we can use Management Studio to implement failover mirroring using the Mirroring page of a database's properties window, as shown earlier in figure 11.11.

Before concluding the chapter, let's cover some important considerations when mirroring multiple databases on the same SQL Server instance.

11.6.5. Considerations for mirroring multiple databases

One of the limitations with a database mirroring session is that it's used to mirror a single database with other mirrored databases on the server operating on different mirroring sessions and therefore failing over independently. One of the implications of this for connected databases is that if there are interdependencies between two or more mirrored databases, there's no way of ensuring that they fail over as a group.

Take, for example, an application that uses two or more databases. If each of the databases can fail over independently of the other, and the application uses a single connection string for the server and instance name, problems will result when only one database fails over.

In most cases, a failure will occur at an instance level, causing all mirrored databases to fail over at once, but individual disk failures or temporary network problems may cause single database failovers. To reduce the problems associated with multiple mirrored databases on the same instance, consider these practices:

• Configure all principal databases to fail to the same mirror instance. This ensures shared connection settings can use the one failover instance for multiple databases.

• Set up alerts, covered in chapter 14, for failover events. Such alerts can be used to ensure all databases fail together, and potentially can be used to automate the manual failover of remaining databases.

• Consider alternate high-availability strategies. Depending on the situation, failover clustering may present a better alternative to database mirroring.

There's one final thing to consider for mirroring multiple databases on the same instance: additional worker threads, memory, and other resources are used for each mirroring session. The more mirrored databases, the greater the load on the instance, and the higher the network saturation. It follows that each session won't perform as well as the load increases, and in the worst case, may lead to session failovers. There's no maximum number of mirrored databases per instance. ^[] The load characteristics of each database should be considered in line with the overall server capacity.

^[] Books Online suggests a maximum of 10 mirrored databases for a 32-bit server instance.