Oracle Technology Network
Database
Database Features
High Availability
Database
Database Features
High Availability
Joseph Meeks
& Ashish Ray, Server Technologies, Oracle Corporation
OVERVIEW
Oracle Data Guard
Oracle Data Guard is a high availability feature and disaster recovery (DR) solution for the Oracle database. Available as an integrated feature of Oracle Database Enterprise Edition, it ensures fault isolation, high availability and disaster recovery through the use of one or many standby databases that are synchronized copies of the production or primary database. In the event of a planned or unplanned outage at the production site, Data Guard ensures that a chosen standby database can be easily switched to the production database role, and continue serving enterprise data needs. If desired, database and client failover can be automatic and all activity can be redirected to the new production database with no manual intervention and with zero data loss.
Microsoft Database Mirroring
Database Mirroring is a new SQL Server 2005 technology for increasing database availability. Microsoft describes Database Mirroring as the process of transferring log records directly from one server (the principal) to another (the mirror) with the ability to quickly fail over to the mirrored copy. Client applications can be coded to automatically redirect their connection information, and in the event of a failover, automatically connect to the standby server and database. Under the correct circumstances, Database Mirroring can failover over quickly with no loss of committed data.
Data Guard and Database Mirroring may sound very similar on the surface. Microsoft has even chosen terminology to describe its features that is similar to terminology that Oracle has used to describe Data Guard since Oracle9 i. Data Guard, however has had the benefit of maturing over several releases beginning with Oracle 7, as evidenced in the comprehensive Data Guard feature-set shipping with Oracle Database 10g Release 2. In contrast, the first production version of Database Mirroring was released with SQL Server 2005 Service Pack 1 in April of 2006. This paper looks "under the hood" to provide an in-depth comparison of the two technologies, and the implications of these differences for the user.
Microsoft Database Mirroring – What Existed in Previous Releases
Database Mirroring did not exist in releases prior to SQL Server 2005. For SQL Server 2000, Microsoft would recommend the use of log shipping to maintain a remote "warm standby" replica of a production database for high availability and disaster recovery. Microsoft still promotes the use of the log shipping feature for requirements not suited to Database Mirroring or for requirements that Database Mirroring alone can not address. For example, "Additionally, there are database mirroring scenarios where log shipping may supplement availability. For example, you could have a High Availability database mirroring configuration in-house, and log ship the principal server to a remote site for disaster recovery purposes." [1]. This example where two separate features are required to address the customer's requirement, each with distinct interfaces, prerequisites, and additional manual administrative tasks, is representative of the immaturity of the Microsoft feature set when compared to Oracle Data Guard. Data Guard provides flexible and powerful configuration options that satisfy the full requirement stated above by creating and managing a single integrated Data Guard configuration using a graphical user interface included with Oracle Enterprise Manager Grid Control.
Microsoft's log shipping allows users to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled. This feature is more accurately compared to Oracle's "Managed Standby" feature in Oracle8 i.
How is SQL Server Database Mirroring Different from SQL Server Log Shipping?
Database Mirroring was introduced to address requirements for higher availability and data protection with more automation than can be achieved with log shipping. Database Mirroring continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance. In contrast, log shipping sends transaction log backups from a primary database to a secondary database. Since log shipping does not replicate data until after a transaction log backup is taken, data loss potential is much greater if there is a failure, and the execution of a failover to a secondary server instance is a manual process.
Under the right set of circumstances, Database Mirroring can provide the advantages it promises over log shipping, and Microsoft will recommend that Database Mirroring be used in place of log shipping. However, real life customer environments frequently differ from the ideal circumstances sought by Database Mirroring. Customer environments often include unreliable networks, systems with high production workload, wide area networks with high RTT network latency (the time it takes for a network round-trip from production location to remote location and back again), servers or storage subsystems that break, and human administrators who make mistakes. Adding to the environment challenge, customers frequently demand more functionality than Database Mirroring can offer. For example, customers often wish to maintain more than one standby database for a single production database, so that data is continuously protected by a standby even after failover. Customers want standby resources to be productive assets, used for reporting, or for off-loading backups from the production database. Customers want the ability to quickly repair databases when they are impacted by user error or logical corruptions. When confronted with these more challenging requirements, Microsoft recommends that customers use the more manual process of log shipping, or a combination of log shipping and Database Mirroring to attempt to satisfy requirements [1]
In contrast, Oracle Data Guard is a comprehensive solution for complete range of requirements stated above. Oracle Data Guard advantages are discussed more completely below. In the final analysis, the additional resilience, functionality, and integration delivered by Oracle Data Guard provides customers with higher levels of data availability and protection than is possible with SQL Server 2005.
DATA GUARD: COMPARATIVE STRENGTHS
Database Mirroring is a new feature. In contrast, while Data Guard was formally introduced in Oracle8 i, it is based on technology that has evolved since Oracle 7. This constant evolution over multiple Oracle database releases makes Data Guard production-ready for mission-critical applications at major customer sites all over the world. The following table provides a quick summary of the comparative strengths of Data Guard over database mirroring.
Table 1: Addressing Disaster Recovery – Oracle Data Guard vs. Database Mirroring
Following sections provide further details on Data Guard's comparative strengths.
A mirrored standby can not be continuously open for read/write & reporting
Oracle Data Guard supports two kinds of standby databases – physical standby databases that use Redo Apply technology, and logical standby databases that use SQL Apply technology. These two types of standby databases are well integrated with each other – for example, a single Data Guard configuration can be created to contain a mix of physical and logical standby databases.
The kind of standby database supported by Database Mirroring is similar to physical standby. However – SQL Apply provides a very powerful capability for Data Guard, allowing a logical standby database to be open for read/write access and be utilized as a reporting database while SQL is being applied to it. With real time apply, a Data Guard 10g feature, logical standby databases can also be used as a real time reporting solution.
A standby database in a Database Mirroring configuration cannot be open – whether read, or read/write, while updates are being applied to it. Applications cannot access this standby database in any state. Microsoft recommends using Database Snapshots, another new feature, in combination with Database Mirroring, for limited reporting benefits. However their reporting capabilities are still not as flexible as logical standby's reporting capabilities. For one thing, this reporting is based on stale data, unlike SQL Apply. The SQL Server 2005 documentation mentions other problematic issues in this area:
Because a database snapshot is static, new data is not available. Therefore, new snapshots must be created periodically and new client connections must be directed to the most recent snapshot. Each database snapshot exists independently of any other database snapshots.
Database snapshots on a mirror database may reduce performance on the principal database. Therefore, after all the clients have disconnected from an out-of-date snapshot, it should be deleted. [1]
A mirrored standby cannot be open read-only like Redo Apply
Even though a Data Guard physical standby cannot be open while redo is applied to it, the database can be opened read-only to satisfy read-only reporting requirements. While in this state, redo is still sent to the standby server (and thus protected), it is just not applied to it. After the reporting is complete, redo apply can be restarted with a simple command, or a mouse click. In this manner, a physical standby database can be transitioned back-and-forth between being in redo apply mode and being opened up read-only as many times as possible, to suit specific business requirements.
A mirrored standby does not have any such capability. To do any client/application access, the mirrored database has to failover to become a “principal database”.
Note that the SQL Apply continuously-open or Redo Apply read-only capabilities offer an excellent way to test out or validate the configuration, without causing any disruption to the primary database. In contrast, since applications cannot access the Database Mirroring standby database at any time, the only way such a validation can be done in a Database Mirroring configuration is to do a role change to the mirrored database, which is disruptive to the primary database.
A mirrored standby can't be seamlessly activated to be a read/write database, and back
Once a SQL Server 2005 mirrored standby database is activated to be a principal database, it cannot just go back to be a mirrored database. If it needs to go back to the mirrored state, it has to do a role transition – that disrupts the current principal database. In contrast, a Data Guard physical standby database can be activated to support read-write reporting capabilities, and then – using the Flashback Across Resetlogs feature in Oracle Database 10g Release 2, it can be converted back to a standby database with a single command and with no impact to the production database. This enhances the reporting as well as testing/cloning capabilities of the Data Guard physical standby database.
Database Mirroring has no concept of a single click switchover - to transition the mirror to the production role with zero data loss regardless of protection level
Data Guard role management services include the concept of "switchover". In a Data Guard context, failover addresses sudden, unplanned outages of the production database. In contrast, a Data Guard switchover is the orderly transition of the standby database to the production role due to a planned event. This is a very useful feature during times such as system maintenance to the original production database servers. A Data Guard switchover is executed via a single mouse click by using Enterprise Manager. The significance of a Data Guard switchover is that it can occur in either synchronous or asynchronous transport modes - there are no restrictions. A Data Guard switchover always guarantees zero data loss.
Database Mirroring does not have any such concept. The only option Database Mirroring allows to transition roles in an asynchronous mirroring configuration is to "Force Service". Microsoft states, "Force Service only if you are willing to risk losing some data in order to restore service to the database immediately." [1]. To avoid data loss during a planned event, Mircrosoft recommends upgrading the protection mode to synchronous (transaction safety full), and allow the mirror database to catch up to be completely synchronized with production. Once this is achieved, then a zero data loss "Manual Failover" can occur to transition the mirror to the production role. Not only does this procedure require extra monitoring and manual steps, but it may be problematic for customer configurations with high workload deployed on high latency wide area networks where synchronous shipping is not possible due to unacceptable overhead on the production database.
A mirrored standby cannot be used for backups
A Data Guard physical standby database can be used for backups, which can be used to restore primary databases. This offloads the backup operation from the production database, reduces resource contention on the production server, boosts performance, and enables no-downtime backup windows. Furthermore, using Recovery Manager (RMAN) [2] backups can occur while redo is being applied to the physical standby database. A mirrored database cannot be used for such backups. This is yet another example where customers investing in a SQL Server Database Mirroring configuration will not be able to extract value out of their investment and instead waste money on system resources that can not be used for any other productive purpose.
Database Mirroring does not have any built-in mechanisms to prevent/undo data corruptions related to human errors
Human errors are one of the leading causes of downtime, yet Database Mirroring, in contrast to Data Guard, does not have any built-in mechanisms to prevent data corruptions related to human errors.
One way Data Guard prevents such data corruptions is using delayed apply – through which Redo Apply or SQL Apply can be delayed on the standby by a configurable amount of time. This provides administrators a safety time window to failover to the standby, in case the primary has got corrupted for example, because of a bad batch job that got run on the primary database. This delay is very flexible in that it can be configured on a per-standby basis – for example, a primary database with two standbys may have one standby configured with a delay of 4 hrs, the other with a delay of 12 hrs – for varied protection from such corruptions.
In Oracle Database 10g, administrators may choose not to use delayed apply but use real time apply instead (e.g. to get the benefits of real-time reporting in logical standby databases). If a human error were to occur in such cases, the primary and standby databases may simply be flashed back to a safe point in time in the past, using the Flashback Database feature, providing yet another flexibility for Data Guard in preventing corruptions due to human errors. Database Mirroring completely lacks these capabilities.
Database Mirroring does not support multiple standby's for the same production database
A Data Guard configuration supports multiple standbys, which allows standbys to be used whether they are physical or logical standbys, or whether they are located on a LAN or a WAN. Customers find this option quite flexible to meet their unique business needs. For example, with Data Guard, a multiple standby configuration is possible in which there is a logical standby in a LAN serving as a local reporting database, and a physical standby in a WAN serving as a remote DR database. A Database Mirroring configuration, which allows only a principal-mirrored pair, does not offer this flexibility.
Database Mirroring does not support rolling upgrades across major database releases
Database Mirroring does not support rolling upgrades of SQL Server ServicePacks or SQL Server major releases. Data Guard supports this capability for Oracle Database patchset upgrades or major release upgrades using SQL Apply.
Database Mirroring does not support cascaded standbys
A Database Mirroring does not support cascaded standby configurations in which a mirrored standby can retransmit log data to a second layer of standbys. This helps saving CPU-processing and networking resources around the primary data center. Data Guard supports this capability.
Witness requires an additional SQL Server 2005 instance
The Witness process in a Database Mirroring configuration is a full-fledged SQL Server instance, compared to the simple, lightweight, easy-to-install Observer process in the Fast-Start Failover configuration for Data Guard. A SQL Server license is also required for the third instance where the witness is installed. Note however that a single witness can monitor multiple mirrored sessions.
Pausing Database Mirroring may adversely impact the principal server
In SQL Server, pausing database mirroring stops communication with the mirror database. This means that the mirror is not keeping up with the principal, and the principal database no longer has a current backup copy. One issue is the impact on the principal database's log, which cannot be truncated as long as it is required for rolling forward the backup copy. If the suspension lasts too long, the log could fill up. If the log fills up (either because it reaches its maximum size or the server instance runs out of space), the server instance goes down. To emphasize the potential impact, Microsoft states, "We recommend that you resume a paused session quickly, because as long as a database mirroring session remains paused, the transaction log cannot be truncated. Therefore, if a database mirroring session is paused for too long, the transaction log fills up, making the database unavailable." [1].
Data Guard has no such issue – depending on the protection mode, the redo transmission to the standby database may be deferred at any time without any impact on the primary database.
Database Mirroring does not provide protection for all database data
Each instance of SQL Server has four system databases (master, model, tempdb, and msdb) and one or more user databases. The master database is the most critical one among these, since it records all of the system level information for a SQL Server system, such as login accounts and all system configuration settings. It records the existence of all other databases, including the location of the database files. It also records the initialization information for SQL Server.
Microsoft recommends always having a recent backup of the master database available. However, Database Mirroring does not mirror these system databases, including the master database. This master database has to be manually maintained and synchronized at the mirrored server (e.g. when user-logon accounts change). Not only that, since the mirror needs access to the system databases to run – any changes that are necessary in the system databases need to be done with care, presumably by shutting down the mirroring operation (and hence leaving the user databases unprotected for that time), manually changing the system databases in a synchronized manner, and then resuming the mirroring. Failure to correctly synchronize the multiple system databases as well as perform other administrative tasks out-of-band of Database Mirroring can jeopardize the ability to failover over quickly to the mirrored database should the need arise. Microsoft refers to an example of such concern as follows, "Complete application failover may be delayed if any critical objects on the old principal, such as SQL Agent jobs, have not also been copied to the new principal server." [1]
Since there is only one database in Oracle, Data Guard does not have this deficiency.
Database Mirroring does not have flexible log data transport methods
Database Mirroring essentially offers a single method of data transport to ship data from the principal database to a mirror database. While this single method does allow for two different behaviors - either synchronous or asynchronous, it is a single transport mechanism limited to a shipping a single stream of data in a serial fashion between principal and a mirror database. In synchronous mode, Database Mirroring always requires that the principal database be impacted by the sum of network round trip time and the time it takes for the disk i/o to "harden" the data received by the mirrored database. Database Mirroring can only ship data between a principal and a single mirror database. Database Mirroring only ever has one connection available to it for shipping current transaction data and for also resolving potentially large gaps in data created when the mirror database is disconnected due to network or mirror server outages.
In contrast Data Guard offers a number of distinctly different, yet seamlessly integrated, transport methods designed to meet varying customer requirements.
The default behavior of Data Guard synchronous redo transport services, LGWR SYNC, follows the most conservative approach of waiting for the data received at the standby database to be hardened to disk. Unlike Database Mirroring, Data Guard allows the user an option to configure LGWR SYNC transport so that the standby database acknowledges receipt as soon as data is in memory, eliminating the impact of standby disk I/O on production database throughput. This makes it possible for Data Guard to utilize synchronous shipping over longer distances and higher workload, with minimal increase in data loss exposure.
As discussed above, Data Guard can synchronize multiple standby databases with a single production databases. Customers do this for many reasons. One such use case includes three standby databases, each using a distinctly different data transport. In this use case, the first standby database is local to the production database and uses Data Guard LGWR SYNC transport services to maintain a synchronous, zero-data loss physical standby for High Availability, and to offload the production database of the overhead of taking backups. The second standby database is 2,500 miles away from the production database, and uses LGWR ASYNC redo transport services to maintain an asynchronous remote physical standby for disaster recovery purposes. In the event the customer must fail over to either of these two standby databases, the remaining standby becomes a standby to the new production copy, providing continuos data protection. Yet another standby database is located in a third data center and uses ARCH transport services to maintain an asynchronous logical standby database that is open read/write and used to off load production of query and reporting users. The four databases (one production and three standby databases) in this use case comprise a single Data Guard configuration managed through Enterprise Manager. The flexibility to transparently utilize three different methods of redo transport allows the customer to optimize for their network environment, business requirements, and service level agreements.
Data Guard can also establish multiple network connections between the primary and a given standby database, to utilize different shipping mechanisms optimized for the task at hand. For example, in the case of bulk shipping data needed to resolve gaps following a network or standby database outage, Data Guard transparently opens multiple channels of communication to the standby database to transfer as much data as the network will allow, in parallel, thereby reducing the amount of time that production data is in an unprotected state. This is described more fully in the section below.
Log data transport and log gap resolution is not well architected in Database Mirroring, resulting in increased exposure to data loss
Microsoft collateral makes repeated reference to the fact that if a mirrored database becomes disconnected from the principal, "For as long as the mirroring state says DISCONNECTED, the transaction log space on the principal cannot be reused, even if you back up the transaction log (and truncate). If the log file grows and reaches its maximum size limit or runs out of disk space, the complete database comes to a halt. To prevent this you have two options - either plan for enough disk space for the transaction log to grow and bring the mirror database back before the space fills up, or break the database mirroring session." [1].
When this occurs for a very active database, it is easy to imagine how quickly the transaction log can grow. For example, a redo generation rate (the data that needs to be shipped from the primary to the standby database) for a moderately sized Oracle Data Guard configuration is 2MB/sec. If a mirrored database outage (either network or hardware problem) lasts one hour, the t ransaction log on a SQL Server database with similar volume will grow to a minimum of 7.2GB of data.
Compare this to a Data Guard configuration with the same set of circumstances, (assume an Oracle online redo log size of 250MB). In an Oracle database architecture, there is no impact to the production database when the standby becomes disconnected. As online logs fill, they are still automatically archived (essentially the equivalent of Microsoft's backup of the transaction log and truncating it). Oracle has the same amount of data, 7.2GB, at the production database that is exposed since it has not been able to ship to the standby, but instead of being in a single large transaction log, Oracle has this data in 28 archive logs of 250MB each, and a current online log that is not yet full but will soon be archived. Because this is all standard operating procedure, Oracle does not have the same concern as SQL Server for breaking the mirroring session to avoid the transaction log from becoming full. Instead, the user continues to run with all of the same archive log auto-management policies in place, the availability and performance of the production database is unaffected, and Data Guard continues to monitor the state of the configuration to detect when the standby database reconnects and can be automatically resynchronized.
Let's assume the best case scenario in the above example, and there is enough disk space to have accumulated all of the data generated by both SQL Server and by Data Guard. In SQL Server's case, thankfully, the transaction log has not run out of space, the database has not come to a halt, and the mirroring session is still enabled. On the surface it looks like both databases are at the same state as far as data loss exposure. However, because Data Guard will automatically resolve this 7.2 GB gap much faster than SQL Server, Data Guard provides a much greater level of data protection and high availability. This is how:
In a SQL Server Database Mirroring case, when the mirror reconnects, mirroring restarts shipping from the transaction log. New transactions continue to be written to the log adding new data to the backlog while Database Mirroring does its best to catch up.
In a Data Guard configuration, it is possible for all 28 archive logs generated during the period that the standby was disconnected, to be shipped in parallel to the standby database at the same time. The shipping process is not serial. In fact, once the current online log completes and is archived, another Data Guard process recognizes that the standby has returned, and begins shipping redo from new transactions - it doesn't wait for the backlog to be caught up. This means that the data gap between an Oracle production and standby database is resolved as quickly as the customer's network bandwidth will allow, returning the customer to a protected state. Everything is automatic. No extra set-up or management is required. Of course different customer networks will have different amounts of bandwidth that can be utilized for this resynchronization process. Data Guard enables the user to throttle network consumption by setting a limit on the number of parallel streams that Data Guard can use to ship to the standby location.
Oracle Enterprise Manager provides an easy to use, integrated, management interface
Enterprise Manager enables the fast and efficient creation and management of a Data Guard configuration that can also include use of other Oracle high availability features such as Real Application Clusters, Oracle Recovery Manager, Flashback, Automatic Storage Management, and Online Reorganization. As described above, there are many options to configuring Data Guard. Enterprise Manager provides the user with the best of both worlds - a simple to use interface to make database administrators (DBA's) highly productive, while affording fine grained control and configuration of multiple unique Data Guard configurations from a central console.
A recent study by the Edision Group [3] comparing the manageability of Oracle Database 10 g to SQL Server 2005 arrived at the following conclusion, "After completing our tests and confirming the results with a group of independent third-party database professionals, we have concluded that Oracle Database 10g Release 2 enjoys a sizable manageability advantage over Microsoft SQL Server 2005. As a result, we can state with confidence that Oracle Database 10g Release 2 can offer significant management savings when compared to Microsoft SQL Server 2005."
The same study also found, "In the backup and recovery area, Oracle Database 10g's automatic backup management and human-error recovery features were the primary factors behind its advantage over SQL Server. Oracle took 62% less time and 67% fewer steps than SQL Server in backup and recovery tasks."
And finally, one of the key factors in Oracle extending its lead in manageability beyond what already existed in previous releases was, "...Oracle Database 10g Release 2's improvement in managing day-to-day administrative tasks through further automation of common tasks and enhanced user interface of its management tool, Oracle Enterprise Manager."
While Database Mirroring does provide a management interface with SQL Server Management Studio, it can't match the capability of Oracle Enterprise Manager due to the lack of completeness of the Database Mirroring feature-set compared to Oracle Data Guard and its integration with other Oracle Database High Availability technologies [4].
CONCLUSION
Recognizing the high availability challenges every business faces, Oracle provides comprehensive, unique, powerful, and simple-to-use capabilities that protect businesses against all forms of unplanned downtime, including system faults, data corruption, disasters, and human errors. Oracle achieves this in an environment where the downtime that occurs during planned maintenance activities is also minimized.
Data Guard offers a mature, comprehensive, well-integrated solution for achieving high availability of mission critical data, and fast, reliable disaster recovery with zero or minimal data loss depending on the configuration chosen. Data Guard makes a significant contribution to a comprehensive high availability solution stack – comprised of components such as Oracle Real Application Clusters, Data Guard, Oracle Recovery Manager, Oracle Flashback Technologies, and other high availability features. Enterprise Manager Grid Control provides integrated management of these features, saving customers time, money and system/people resources – factors that are extremely critical in today’s economy. Oracle has also taken the additional step of publishing best practice guidelines for configuring a High Availability solution through its Maximum Availability Architecture framework [5], and making it available for its customers. The long list of Oracle customers who have embraced its High Availability solutions is a testimonial to Oracle’s unparalleled technical leadership and vision in this area.
In contrast, SQL Server Database Mirroring is an immature feature lacking the comprehensive and flexible capabilities of Oracle Data Guard, required by customers in real-life production environments. Microsoft SQL Server 2005 continues to lag several releases behind Oracle in this regard and is not an appropriate choice for today’s business applications that demand scalable performance, high levels of uptime, and ease of management.
REFERENCES
- Database Mirroring in SQL Server 2005, Microsoft Corporation, April 2006
http://msdn2.microsoft.com/en-us/library/ms189852.aspx
- Using Recovery Manager with Oracle Data Guard in Oracle Database 10g,
www.oracle.com/technology/deploy/availability/pdf/RMAN_DataGuard_10g_wp.pdf
- Comparative Management Cost Study of Oracle Database 10g Release 2 and Microsoft SQL Server 2005, by the Edison Group
www.oracle.com/technology/products/manageability/database/pdf/competitive/oracle%2010gr2%20sql%20server%202005%20cmcs%20study.pdf
- Oracle Database High Availability
www.oracle.com/technology/deploy/availability/index.html
- Maximum Availability Architecture (MAA)
www.oracle.com/technology/deploy/availability/htdocs/maa.htm
Joseph Meeks is a Director of Product Management with Oracle's High Availability and Maximum Availability Architecture Group. He has 25 years of experience assisting customers in the design and deployment of highly available systems for mission critical applications.
Ashish Ray (Ashish.Ray at oracle.com) is a Group Product Manager with Oracle's Database High Availability Group. He has 12+ years of combined experience in software architecture design, software development and product management, focusing largely on the reliability, availability and scalability issues of enterprise and e-business computing.
 | Popular Downloads |  |
|
| ||
|
More Database Downloads
|
|
Printer View