SQL Server 2005 Part 2 – High Availability and Scalability Enhancements – Database Mirroring

In the first
article
of this series, we presented a high-level overview of SQL Server
2005 functionality, based on its most recent Beta 2 implementation. Our goal,
going forward, will be to discuss each of new or improved features in more
details, focusing on engineering and administrative issues. We will start by
covering the high availability and scalability enhancements, such as database
mirroring (introduced in this version) and failover clustering, which is
improved in SQL Server 2005, compared to earlier editions. Note, that while SQL
Server 2005 continues to offer other, currently available technologies in this
category, such as replication and log shipping (providing warm standby
capabilities), as well as standard backup/restore (which can be used for cold
standby solutions), in more or less unchanged form, their effectiveness is
positively impacted by the introduction of database snapshots, new isolation
levels, or online restore and index operations (which we will also be
presenting later).

The primary goal of database mirroring is to increase data availability and
allow failover in case a server hosting the database becomes unavailable (e.g
as the result of hardware or network failure). As a primarily software based
solution, in principle, it is similar to the log shipping, available in SQL
Server 2000. As in log shipping, entries from a transaction log in one database
are transferred and applied from its instance on a primary server (in the new
terminology, referred to as the principal) to its copy on a secondary server
(known as the mirror), which, by the way, implies that a mirrored database must
be in full recovery mode, so its transaction logs are not overwritten. However,
this basic process is modified by introducing a number of additional
enhancements.

Maintaining synchronized copies of a database on two separate servers allows
switching between them on an as needed basis, reversing roles of partners
participating in the mirroring session, (the former principal becomes the
mirror and vice versa). With database mirroring, this process can be automated,
which requires the presence of another instance of SQL Server 2005, although
this functionality is not available in the SQL Server 2005 Express edition,
running on a separate server, referred to as witness. This server monitors
operations of mirroring partners, triggering failover in case of a lack of
heartbeat response from the principal (after the timeout period, which is set
in Beta 2 to 10 seconds but is expected to be configurable in Beta 3) and
ensuring that at any given time there is only one principal within each
mirroring session. The decision is coordinated between the witness and at least
one other operational server. This protects against the "split brain"
scenario, which could occur if direct connectivity between the principal and
the mirror is lost. As long as the synchronization between the two is
maintained, the mirror can take over processing client requests without noticeable
delay (no more than a few seconds) and without any data loss (once the
principal comes back on line, it automatically assumes the role of the mirror
and catches up with changes on the new principal). Automatic failover is
further supplemented by the new MDAC client software (based on .NET provider),
smart enough to redirect client applications transparently to the operational
server.

Even though database mirroring involves three distinct servers, any one of
them can serve multiple roles in distinct database mirroring sessions. For
example, a single witness can monitor multiple mirrored sessions (performance
impact of a single monitored session is negligible) or a particular server can
function as principal and mirror for two distinct database mirroring sessions.
The solution does not place any special hardware demands on participating
servers, beyond the ones expected for typical SQL Server 2005 installation
(this eliminates distance limitations inherent to clustered, shared
storage-based solutions), although it is recommended that all servers run the
same version of the operating system.

Database mirroring introduces the issue intrinsic to every type of data
replication mechanism, which results from the conflict between application
response time on one hand, and data synchronization on the other. In order to
ensure that the database on the mirror is synchronized with its source on the
principal, the latter should wait for confirmation of each transaction being
written to the transaction log of its partner’s database, which in turn,
affects how soon transaction completion can be reported back to the client
application that triggered it. Decision on how synchronization is handled also
affects automatic failover capability and the impact that mirror failure has on
operations of the principal.

To provide flexibility when dealing with different requirements, SQL Server
2005 offers three operating modes, which are determined by presence of the
witness and transaction safety level, configurable on per mirroring session
basis. The safety level can be turned either on or off. With the safety level
set to ON, committed transactions are guaranteed to be synchronized between
mirrored partners, with the safety turned OFF, synchronization is performed on a
continuous basis, but without assurance of full consistency between transaction
logs of both databases.

  • high availability operating mode – synchronous with a
    witness (with transaction safety set to ON) – In this case, transactions
    written to the transaction log of the database on the principal are
    automatically transferred to the transaction log of its mirrored copy. The
    principal waits for the confirmation of each successful write from its mirror
    before committing the corresponding transaction locally, which guarantees
    consistency between the two (following the initial synchronization). This type
    of synchronous operation is the primary prerequisite for the automatic failover
    – the other is the presence and proper functioning of the witness server (which
    means that only the synchronous mode with a witness offers such capability).
    Additionally, availability of the witness also impacts operations in cases when
    the mirror server fails. In such a scenario, if the principal can still
    communicate with the witness, it will continue running (once the witness
    detects that the mirror is back online, it will automatically trigger its
    resynchronization), otherwise (if both mirror and witness are not reachable
    from the principal), the mirrored database is placed in the OFFLINE mode.

  • high protection operating mode – synchronous without a
    witness (with transaction safety set to ON) – uses the same synchronization
    mechanism as the first mode, however, the lack of the witness precludes
    automatic failover capability. The owner of the database can perform manual
    failover as long as the principal is present, by running ALTER DATABASE
    statement with SET PARTNER FAILOVER option from the principal). Alternately,
    the owner can force the service to the mirror the database by running the ALTER
    DATABASE statement with the SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS option
    from the mirror, with potential data loss (if databases are not in synchronized
    state). Unavailability of the mirror (due to server or network link failure)
    causes the primary to place the mirrored database in OFFLINE mode (in order to
    prevent the possibility of having two mirroring partners operating
    simultaneously as principals).

  • high performance operating mode – asynchronous without a
    witness (with transaction safety set to OFF) – In this case, a transaction is committed
    on the principal before it is sent to its partner, which means that it is not
    uncommon for the source database and its mirror to be out of synch. However,
    since the process of transferring transaction log entries to the mirror is
    continuous, the difference is minor. In the case of principle failure, the
    database owner can force service to the mirror database, resulting in the
    former mirror taking on the role of the principal. Forcing the service can
    result in data loss (encompassing all transaction log entries that constituted
    the difference between the mirror and the principal at the time of its
    failure), so it should be used only if such impact can be tolerated. Another
    choice when dealing with the principal failure in this mode (which reduces
    possibility of data loss) is terminating the mirroring session and recovering
    the database on the principal. Unlike in the synchronous mode with a witness,
    unavailability of the mirror leaves the principal operational.

Client application redirection is automatic and transparent to users. This
can be accomplished either by specifying names of both principal and mirror in
the application connection string or by using a new .NET provider-based MDAC
client software with its inherent redirection capabilities. This results in
caching the reference to a mirror server when the connection to a principal is
established. After a disconnect, (which can happen as the result of the principal
failure), the client library attempts first to reestablish the primary
connection and, if this fails, automatically redirect the application to the
mirror database.

Unlike in log shipping situation, a mirrored database is not available for
access (which might be desired in order to offload the live database for
read-only activities, such as reporting), since it is in "recovering"
state (which implies that system databases can not be mirrored and is not
intended as the replacement for replication). You can, however, get around this
limitation by creating a database snapshot of the mirror and use it instead. It
is also not possible to create separate mirrors of the same database. In
addition, it is still your responsibility (as in log shipping) to ensure that
the metadata stored outside of the database (such as SQL Server logins) remains
synchronized, since this activity is not built into the database mirroring
mechanism.

The security of mirroring sessions can be controlled by configuring the listener
ports used for communication between mirrored partners. This is set on a per
server basis with the CREATE ENDPOINT T-SQL statement (this statement must be
executed before mirroring session can be established). The permissions to use endpoints
must be explicitly granted to the login used by the mirroring partner (with the
GRANT CONNECT ON ENDPOINT T-SQL statement), which corresponds to the account
used by its SQL Server Service. Note that this places additional restrictions
on the environment in which mirroring sessions can be established (servers
participating in mirroring must be members of the same or trusted Windows
domain).

In the next article of our series, we will continue discussion on high
availability and scalability enhancements in SQL Server 2005 (Beta 2).

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles