SQL Server 2005 Part 2 - High Availability and Scalability Enhancements - Database Mirroring
November 29, 2004
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.
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).