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
RESTORE DATABASE [Sales]
FROM DISK = N'G:\SQL Backup\Sales.bak'
WITH NORECOVERY
GO
RESTORE LOG [Sales]
FROM DISK = N'G:\SQL Backup\Sales-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 1, 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 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.
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.
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
GRANT CONNECT on ENDPOINT::Mirroring TO [BNE-SQL-PR-01\SQL-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 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 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.
