SQL Server 2008 : Mirroring in action (part 1) - Mirroring setup

10/28/2013 7:48:59 PM

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.

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.

Here's an example of doing this on the mirror server:

-- Restore the Sales DB and roll forward using a transaction log restore
FROM DISK = N'G:\SQL Backup\Sales.bak'

FROM DISK = N'G:\SQL Backup\Sales-Trn-1.bak'

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 1, is the starting point for mirroring configuration.

Figure 1. The mirroring tab of a database's properties allows mirroring to be established, or if already established, paused, resumed, removed, or failed over.

At this point, click the Configure Security button, which will take you to the screen shown in figure 2. 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.

Figure 2. Selection of a witness server is optional.

After choosing to save the security configuration in the witness instance, the next three steps are to configure the principal (see figure 3), 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 3.

Figure 3. The Mirroring Security Wizard allows each instance in the mirroring configuration to be configured with a TCP port, endpoint, and encryption option.

The next screen, shown in figure 4, 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

Figure 4. For each instance in the mirroring configuration, service accounts are provided.

Figure 5. SQL Server Management Studio marks the role and status of the mirrored database.

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

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

Table 1. Mirroring session states
Mirroring stateDescription
SynchronizingThe mirror DB is catching up on outstanding transactions.
SynchronizedThe mirror DB has caught up.
DisconnectedThe mirror partners have lost contact.
SuspendedCaused by pausing (covered shortly) or failover. No logs are sent to the mirror DB.
Pending failoverTemporary state at the principal during failover.

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

  •  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