Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 29, 2004

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

By Marcin Policht

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM