SQL Server 2008 : Mirroring in action (part 3) - Suspending and resuming mirroring, Initiating failover

10/28/2013 7:51:55 PM

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

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

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.

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

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

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 1.

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, 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.
  •  SQL Server 2008 : High availability with database mirroring - Failover options
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 4) - Iterative Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 3) - List Controls
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 2) - Data-Binding Properties
  •  Programming ASP.NET 3.5 : Data Source-Based Data Binding (part 1) - Feasible Data Sources
  •  SQL Server 2008 : Database mirroring overview, Mirroring modes
  •  SQL Server 2008 : Transaction log shipping - Usage scenarios, Setting up and monitoring log shipping
  •  SQL Server 2008 : High-availability options
  •  SQL Server 2008 : Policy-based management - Advanced policy-based management
  •  SQL Server 2008 : Policy-based management - Enterprise policy management
    Video tutorials
    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Install Windows Server 2012 On VirtualBox

    - How To Disable Windows 8 Metro UI

    - How To Install Windows Store Apps From Windows 8 Classic Desktop

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
    programming4us programming4us