These days, no company can afford to lose even one second of processing time, so maintaining a robust primary database is essential. However, to ensure that corporate functions are always available, having a robust primary database also often means managing an equally robust standby database. To ensure the optimal functionality of both, every company should know how to move from one to the other either intentionally – a ‘switchover’ – or in the event of a crash or crisis – a ‘failover.’
Sometimes, it is imperative to switch your databases from your primary to your standby and from standby to primary. This ‘switchover’ appoints the formerly secondary database to the dominant database role, and relegates the previously primary base to the secondary role. After the switch, there’s no change in the data, however, which remains consistent between the two.
A switchback reverses the process, shifting the new dominant and the new secondary roles back to their original state.
A ‘failover’ responds to a catastrophic failure event when all RAC primary databases fail, and the secondary database becomes the dominant database in that emergency. Failovers occur only when there is no chance of quickly recovering the primary base and the enterprise has no choice but to transition over to the standby as its primary database. Because they are triggered by a catastrophe, there may be data loss during the failover transition, depending on the set level of protection mode that is in effect in the standby base at that time.
Failovers can be ‘complete’ or ‘immediate’:
- Complete failover: Most systems default to a complete failover, which is also the recommended practice. During a complete failover, the process will recover as much data as possible from the original primary database, dependant on any protection barriers that may be in place. The programming also tries to encompass any standby databases that are not affected by the failure, and engage them again as backup databases to the new dominant database. This document discusses the most common ‘complete’ type of failover.
- Immediate failover: For speed, some organizations choose an immediate failover, which moves faster than a complete failover. In this case, however, after invoking the failover, there is no additional data applied to the standby database. It also requires that the original primary database be re-enabled, as well as any other unaffected database that you intend to use as standby databases to the new primary database. This document doesn’t discuss the more unusual ‘immediate’ type of failover.
In the event of a catastrophic failover, which may render the original dominant database unrecoverable, the new standby database must be recreated. You can use the switchback method if this is not the case.
(PBS NOTE: I have included these per chart column)
Transitioning Database Roles
(Switch- or Failovers)
Switching over to a physical secondary database
Switching over to a logical secondary database
Failing over (complete) to a physical secondary database
Failing over (complete) to a logical secondary database
Failing over immediately to either a new physical database or a logical secondary database
Reinstatement or Recreation of a Failed Dominant Database
No action needed.
No action needed.
During the transition, the broker disables the dominant database. It can be reinstated only if there is a sufficient number of flashback logs on the failed database and you had enabled the “Flashback Database” before the failover event occurred. Without these two circumstances, your only other option is to use a backup copy of the dominant database to recreate it again.
During the transition, the broker disables the dominant database. It can be reinstated only if there is a sufficient number of flashback logs on the failed database and you had enabled the “Flashback Database” before the failover event occurred. Without these two circumstances, your only other option is to use a copy of the primary database to recreate it again.
The broker disables all secondary databases that were not affected by the failover. They can be recreated using copies of the new dominant database.
Recovering the Secondary Databases That Were Not Affected by the Role Transition
No action needed.
All physical secondary databases are disabled, then recreated from a copy of the new dominant database. There’s no need to disable logical secondary databases.
Disabled physical standby databases are recoverable if, before the failover, the Flashback Database was enabled and there is a sufficient number of logs on that physical secondary database. Otherwise, you must use a copy of the new dominant database to recreate the physical secondary databases as well as any logical secondary databases that were disabled during the failover.
The primary database is disabled by the broker during the failover transition. A copy of the new primary database informs all other databases.
All standby databases not affected by the failover are disabled by the broker during the process. All subsequent secondary databases are created from a copy of the new primary database.
Today’s databases are complex but remain the backbone of most corporate activities. Maintain the health of your primary and standby databases by knowing how to switchover or failover so your enterprise never loses touch with its foundation.
Subscribe to Our Blog
Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.